oracle存储过程及触发器简单案例

--创建表
CREATE TABLE bank
(
    customerName CHAR(10),  --顾客姓名
    currentMoney number(10)         --当前余额
);
--添加余额不能小于1的约束
ALTER TABLE bank
   ADD CONSTRAINT CK_currentMoney    
       CHECK(currentMoney >= 1);
--插入测试数据
INSERT INTO bank(customerName, currentMoney)
        VALUES('张三', 1000);
INSERT INTO bank(customerName, currentMoney)
        VALUES('李四', 1);
commit;
select * from bank;
--完成转帐操作(总金额异常)
	update bank set currentMoney=currentMoney-1000 where customerName='张三';
	update bank set currentMoney=currentMoney+1000 where customerName='李四';
commit;
--清空数据，
delete bank;
select * from bank;

set serveroutput on;
declare
 li_money number(10):=0;
begin
  update bank set currentMoney=currentMoney+1000 where customerName='李四';
  select currentMoney into li_money  from bank where customerName='李四';
   dbms_output.put_line('李四当前的余额是：'||li_money);
  update bank set currentMoney=currentMoney-1000 where customerName='张三';
 
exception
  when others then
    dbms_output.put_line('撤销提交');
    rollback;
end;
--转帐之后查看结果，李四的钱被回滚回去了
select * from bank;
--Savepoint保存点
declare
 num number(10);
begin
  INSERT INTO bank(customerName, currentMoney) VALUES('王五', 1000);
  savepoint a;
  update bank set currentMoney=currentMoney-100 where customerName='王五';
  savepoint b;
  INSERT INTO bank(customerName, currentMoney) VALUES('赵六', 10);
  savepoint c;
  num:=&myid;
  if num=1 then
     rollback to a;
  elsif num=2 then
     rollback to b;
  else
     rollback to c;
  end if;
end;
select * from bank;
--------------------------------------------------------------------------存储过程-------------------
--建立存储过程的语法：存储过程中可以接收的参数类型:	in类型为输入类型的参数, out类型为输出类型的参数。
	create [or replace] PROCEDURE 过程名(参数列表)  
		AS 
      PLSQL子程序体

--创建无输入参数无输出参数的存储过程（无参无返回值的存储过程）:九九乘法表
create or replace procedure nine
as
 I NUMBER(10):=1;
BEGIN      
           WHILE (I<=9) LOOP
             DECLARE J NUMBER(10):=1;
             BEGIN
               WHILE (J<=I) LOOP
                 DBMS_OUTPUT.put(I||'*'||J||'='||(I*J)||'     ');
                 J:=J+1;
               END LOOP;
              DBMS_OUTPUT.put_line('');
               I:=I+1;
            END;
           END LOOP;
END;


--查询正在使用的组所对应的日志文件： 
select * from v$logfile; 

--调用方式之一
set serveroutput on;
execute nine();
--调用方式二
set serveroutput on;
begin
  nine();
end;

----创建有输入参数无输出参数的存储过程（有参无返回值的存储过程）:XX乘法表
create or replace procedure xtable
(num in number,s varchar2 )
as
 I NUMBER(10):=1; J NUMBER(10):=1;
BEGIN
     FOR K IN  1..num LOOP
       FOR M IN   1..K LOOP
         DBMS_OUTPUT.put(I||'*'||J||'='||(I*J)||'     ');
         J:=J+1;  
       END LOOP;
       J:=1;
       I:=I+1;
       DBMS_OUTPUT.put_line('');
     END LOOP; 
     dbms_output.put_line('显示另一个输入参数'||s);
END;
--调用带输入参数的存储过程记得传递参数
set serveroutput on;
execute xtable(6,'zar');
--创建带输入输出参数的存储过程：自动生成随机卡号为输出内容，前半部分为固定银行标识，使用输入参数
create or replace procedure randcardid
(part char,newid out char)
as
  r number(18,8);  --生成的随机数
  tempstr char(10);  --转换成字符串后的随机数
  tempa char(4);  --卡号前半部分
  tempb char(4);  --卡号后半部
begin
   select trunc(dbms_random.value(10000000,99999999)) into r from dual;
   select to_char(r) into tempstr from dual;
   select substr(tempstr,1,4) into tempa from dual;
   select substr(tempstr,5,4) into tempb from dual;
    newid:=part || tempa || ' ' || tempb; 
end;
--调用带输出参数的存储过程必须要声明变量来接输出参数的值
set serveroutput on;
declare
  outp char(20);
begin
 randcardid('1037 5758 ',outp);
 dbms_output.put_line(outp);
end;
------------------------------------------------------------------------存储函数-----------------------
--存储函数:注意返回值类型，必须有return语句
create or replace function f1
return varchar2
as
begin
  return 'hello';
end;
--调用
select f1() from dual;
set serveroutput on;
begin
 dbms_output.put_line(f1());
end;

--用函数去掉字段中多个重复的空格：
create or replace function mtrim(str varchar2)
return varchar2
as
  v_str varchar2(500):='';
begin
  select regexp_replace(str,' ','') into v_str from dual;
  return v_str;
end;
--测试
select mtrim('    H          A 1          ')||'zar' from dual;
--查看存储过程和函数
Select * from user_source;
----------------------------------------------------------------------触发器-----------------------
--触发器的语法
   CREATE  [or REPLACE] TRIGGER  触发器名
   {BEFORE | AFTER}
   {DELETE | INSERT | UPDATE [OF 列名]}
   ON  表名
   [FOR EACH ROW [WHEN(条件) ] ]
   PLSQL 块


--创建序列,为了测试触发器
create sequence myid;   

--创建表，为了测试没有触发器和有触发器的区别
--drop table person;
create table person(id number(10) primary key,name varchar2(30));
--没有触发器前，要手工调用myid.nextval
insert into person values(myid.nextval,'张三');
insert into person values(myid.nextval,'李四');
insert into person values(myid.nextval,'王五');
commit;
--为使序列自动增长创建的触发器
create or replace trigger tri_id --触发器名称
  	    before insert on person    --tbluser是表名
  	    for each row
declare
       nextid number;
begin  	 
    	   select myid.nextval 		 --myid正是刚才创建的序列
         into nextid  from sys.dual;
    	   :new.id := nextid;  		
      --end if;
end ;

insert into person(name) values('赵六');
insert into person(name) values('钱七');
commit;
select * from person;

--限制写入以J开始的字符
create or replace trigger t1
before insert on person for each row
begin
  dbms_output.put_line('Hello'||:NEW.id||:NEW.name);
  if :NEW.name like 'J%' then
    RAISE_APPLICATION_ERROR(-20000,'限制写入以J开始的串');
    --在触发器中，不能写commit或是rollback
  end if;
end;

set serveroutput on;
insert into person(name) values('Jack');
insert into person(name) values('Mack');
select * from person;
--行级触发器
create or replace trigger trigger1
	   before update
	   on person-- for each row
begin
	      dbms_output.put_line('Hello');
end;
--测试行级触发器：如果person表中有四行，则会显示4个hello.
--如果没有for each row 则只会显示一个hello.
--只有在行级的触发器中，才可以使用:new,:old。
set serveroutput on;
update person set name='zar';

--同一个表中可以添加多个触发器
--删除触发器
drop trigger trigger1;
drop trigger trigger2;
drop trigger trigger3;
drop trigger trigger4;
drop trigger trigger5;
drop trigger t1;
--在同一个表上，使用insert,update,delete触发器
create or replace trigger trigger2
   before insert or update or delete
   on person
begin
    dbms_output.put_line('hello');
end;
--以下都会引发上面的触发器
set serveroutput on;
insert into person(name)  values('ack');
set serveroutput on;
update person set name='Tom';
update person set id=100 where id=10;

--set serveroutput on;
delete from person;
--判断调用了哪个触发器
create or replace trigger trigger3
  before insert or update or delete
  on person for each row
begin
      if INSERTING then
          dbms_output.put_line('这是插入数据');
      elsif DELETING then
          dbms_output.put_line('这是删除数据');
      else
          dbms_output.put_line('这是修改数据');
      end if;
end;

--作用在某个列上的
create or replace trigger trigger5
  before update of name on person
  for each row
Begin
      dbms_output.put_line('之前的值'||:old.name||'现在的值:'||:new.name);
end;

set serveroutput on;
update person set name='Tom';
update person set id=180 where id=23;
select * from person;

--在触发器调用过程：
create or replace trigger trigger6
   before insert on person
   for each row
declare
      outp char(20);
begin
      dbms_output.put_line('Trigger..');
      randcardid('1037 5758 ',outp);--直接调用过程即可
      dbms_output.put_line('随机生成的卡号是 is:'||outp);
end;

set serveroutput on;
insert into person(name)  values('ack');

--查看触发器
Select * from user_triggers;