Oracle学习笔记之触发器

时间:2021-02-04 05:12:09

                                                                                                             触发器

触发器(trigger)是一些过程,与表关系密切,用于保护表中的数据,当一个基表被修改(INSERT、UPDATE或DELETE)时,触发器自动执行,例如通过触发器可实现多个表间数据的一致性和完整性。触发器和应用程序无关。

触发器的类型有三种:

(1)DML触发器。Oracle可以在DML(数据操纵语句)语句进行触发,可以在DML操作前或操作后进行触发,并且可以在每个行或该语句操作上进行触发。

(2)替代触发器。由于在Oracle中不能直接对有两个以上的表建立的视图进行操作,所以给出了替代触发器。它是Oracle专门为进行视图操作的一种处理方法。

(3)系统触发器。在Oracle8i时,提供了第三种类型的触发器叫系统触发器。它可以在Oracle数据库系统的时间中进行触发,如Oracle数据库的关闭或打开等。

创建触发器有以下限制:

(1)代码大小。触发器代码大小必须小于32K。

(2)触发器中有效语句可以包括DML语句,但不能包括DDL语句。ROLLBACK、COMMIT、SAVEPOINT也不能使用。

(3)LONG、LONG RAW和LOB的限制:

①   不能插入数据到LONG或LONG RAW;

②   来自LONG或LONG RAW的数据可以转换成字符型(如char、varchar2),但是不能超过32K;

③   使用LONG或LONG RAW不能声明变量;

④   在LONG或LONG RAW列中不能使用:NEW和:PARENT;

⑤   在LOB中的:NEW变量不能修改。

每张基表最多可

建立12个触发器,它们是:

(1)   BEFORE INSERT;

(2)   BEFORE INSERT FOR EACH ROW;

(3)   AFTER INSERT;

(4)   AFTER INSERT FOR EACH ROW;

(5)   BEFORE UPDATE;

(6)   BEFORE UPDATE FOR EACH ROW;

(7)   AFTER UPDATE;

(8)   AFTER UPDATE FOR EACH ROW;

(9)   BEFORE DELETE;

(10) BEFORE DELETE FOR EACH ROW;

(11) AFTER DELETE;

(12) AFTER DELETE FOR EACH ROW。


例如:做一个触发器,当删除dept表中部门时,将emp表中该部门人员信息清空;

createorreplacetrigger emp_dept

  afterdelete  on   dept

  foreachrow

declare

  

begin

deletefrom emp where emp.deptno=:old.deptno;

--表示删除或者修改前该记录旧的数据;

end emp_dept;

 

 例如:修改上题,要求将删除的dept和emp表数据备份到指定表 deptOld和empOld表结构和dept、emp结构一致;

 createorreplacetrigger emp_dept

  afterdelete  on   dept

  foreachrow

declare

   cursor cur isselect*from emp where emp.deptno=:old.deptno;

   e emp%rowtype;

   department dept%rowtype;

  

begin

    deletefrom emp where emp.deptno=:old.deptno;

    ifsql%foundthen

       open cur;

       loop

            fetch cur into e;

              insertinto empOld values(e.empno,e.ename,e.job,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno);

         exitwhen cur%notfound;

       endloop;

    endif;

           insertinto deptOld values(:old.deptno,:old.dname,:old.loc);

end emp_dept;

 

create or replace trigger deleteDeptTrigger

  after delete on dept 

  for each row

declare

   type cur_type is ref cursor;

   cur cur_type;

   emp_row emp%rowtype;

begin

   open cur for

   select * from emp where deptno=:old.deptno; 

 

   delete from emp where deptno=:old.deptno;

   loop

      fetch cur into emp_row;

      exit when cur%notfound;

      insert into empOld values(emp_row.empno,emp_row.ename,emp_row.job,emp_row.mgr,emp_row.hiredate,emp_row.sal,emp_row.comm,emp_row.deptno);

   end loop;

   close cur;

   insert into deptOld values(:old.deptno,:old.dname,:old.loc);

 

enddeleteDeptTrigger;

 

305讲解内容:

create or replace trigger deptTrigger

  after delete on dept 

  for each row

declare

  dept_deptno number(5);

  type cur is ref cursor return emp%rowtype;

  empcur cur;

  emprow emp%rowtype;

begin

       dept_deptno:=:old.deptno;

    open empcur for select * from emp where emp.deptno=dept_deptno;

    delete from emp where deptno=dept_deptno;

   

    loop

      fetch empcur into emprow;

      insert into oldemp values(e.nextval,emprow.empno,emprow.ename,emprow.job,emprow.mgr,emprow.hiredate,emprow.sal,emprow.comm,emprow.deptno);

      exit when empcur%notfound;

    end loop;

 

enddeptTrigger;

创建替代(Instead_of)触发器:

Instead_of用于对视图的DML触发。由于视图有可能由多个表进行关联(Join)而成,因

而并非所有的关联都是可更新的。但是可以按如下例子来创建触发器。

例如:

在scott数据库中创建视图和触发器,以说明替代触发器。

      CREATEORREPLACE VIEW emp_avgSalView

    AS   SELECT deptno,AVG(sal)   AS   avgSAL    FROM  emp

     GROUPby deptno;

创建该视图的替代触发器:

       CREATETRIGGER empAvgSalDel

   INSTEADOFDELETEON  emp_avgSalView  FOREACHROW

  BEGIN

   DELETEFROM  emp  WHERE  deptno=:OLD.deptno;

  ENDcs_kc_avg_del;

 

创建系统触发器:

Oracle8i开始提供的系统触发器可以在DDL或数据库系统上被触发。

DDL指的是数据定义语句,如CREATE、ALTER和DROP等。而数据

库系统事件包括数据库服务器的启动或关闭,用户登录与退出等。

--创建当一个用户userA登录时自动记录一些信息的触发器。

  CREATETRIGGER loguserAconnects

    AFTER LOGON ONSCHEMA

  BEGIN

    INSERTINTO LOGIN VALUES(‘userA’,’loguserAconnects fired’);

  END loguserAconnects;

 

面试题笔试题查询:

1、通过case when行编列,列边行;

2、删除重复行、留重复行中的一行记录。

3、Nvl和nvl2 区别

4、说出常用的10个数据库函数

5、索引如何使用? 何时失效?

6、Sql语句级别的优化?

7、函数和存储过程区别?

8、手写存储过程。

9、Oracle分页语句和mysql分页语句?

10、Rowid什么是事务?

例如:查询2000-3000之间的sal和、 4000-6000之间sal和、6000-8000sal和、

Select  sum(

Case whensal between 2000 and 3000

Then salelse 0 end

) ,sum(

 Case when sal between 4000and 6000

Then salelse 0 end

) from emp

Select  count(

Case when sal between 2000 and 3000

Then empno else null end

) ,count(

 Case when sal between 4000and 6000

Then empno else null end

) fromemp

 

综合实例:

模拟银行汇款、取款、以及存款、余额查询操作:

Id、卡号、密码、存款余额

create tablebankMsg( id number(15) primary key,cardnovarchar2(20) unique not null,pwdvarchar2(20) not null,

 money number(15,2)

);

Id、卡号、操作标示符(A存款、B取款、C转账)、操作时间、操作金额

create tablebankMsgHistory(id number(5) primary key,cardnovarchar2(20),

 flag varchar2(1),markDatedate,money number(15,2)

)

 

写一个函数验证是否登陆成功;

写一个存储过程、用于操作存款、取款、转账以及余额查询;

(注意:由于触发器获取不到操作标示符因此不写这个触发器了)写一个触发器,当bankMsg表被修改以后,触发操作记录表数据新增;

登陆操作函数:

create or replace function bankLoginByCardno(cardno_param in varchar2,pwd_param in varchar2) return number is

    total number(5);

    flag number(1):=0;

begin

  select count(*) into total from bankMsg where cardno =cardno_param and pwd=pwd_param; 

  if total >0 then

    flag:=1;

  end if ;

  return flag;

endbankLoginByCardno;

存储过程:

--该存储过程,根据用户名密码登陆, 卡号为用户名,pwd自定义,

--根据操作标示Flag (A存款、B取款、C转账) 对银行卡信息数据进行修改,然后触发记录表记录操作记录

/*

 username :用户名

 pwd:密码

 controlFlag操作标示符

 money金额

 targetCardno对方卡号

 succssOrError 是否操作成功,1 成功,0 失败

 errorMsg 操作失败原因out类型参数

 showMsg 查询余额信息的out类型变量

*/

create or replace procedure userBankCardnoMsg(username in varchar2, pwd in varchar2,

controlFlag in varchar2,money_param in number,targetCardno in varchar2,successOrError out number,

errorMsg out varchar2,

showMsg out bankMsg%rowType

) is

 flag number(5):=0;

 flag2 number(5):=0;

 money number(15,2);

begin

  successOrError:=0;--成功失败标示符默认为0

  flag:=bankloginbycardno(username,pwd);

 --money_param:=nvl(money_param,0);

  if flag=1 then--登陆成功

     select money into money from bankMsg where cardno=username;

     case controlFlag

       when 'A' then--存款

             update bankMsg set money=money+money_param    where cardno=username;--修改金额

             insert into bankMsgHistory values(bankcardno_seq.nextval,username,controlFlag,sysdate,money_param);

             successOrError:=1;

             errorMsg:='存款成功';

             select * into showMsg from bankMsg where cardno=username;

       when 'B' then--取款

            if money>=money_param then--判断是否有足够余额

                update bankMsg set money=money-money_param    where cardno=username;--修改金额

                insert into bankMsgHistory values(bankcardno_seq.nextval,username,controlFlag,sysdate,money_param);

                successOrError:=1;

                errorMsg:='取款成功';

                select * into showMsg from bankMsg where cardno=username;

            else

                successOrError:=0;

                errorMsg:='取款失败,余额不足';

            end if;

                

       when 'C' then--转账

             select count(*) into flag2 from bankMsg where cardno=targetCardno;--查看目标账号是否存在

              if flag2<=0 then

                 successOrError:=0;

                  errorMsg:='转账失败,目标卡号不存在';

               else

                              

                  if money>=money_param then--判断是否有足够余额

                        update bankMsg set money=money-money_param    where cardno=username;--修改金额

                        insert into bankMsgHistory values(bankcardno_seq.nextval,username,controlFlag,sysdate,money_param);

                        update bankMsg set money=money+money_param where cardno=targetCardno;--修改目标账号金额

                        insert into bankMsgHistory values(bankcardno_seq.nextval,targetCardno,'A',sysdate,money_param);

                        successOrError:=1;

                        errorMsg:='转账成功';

                        select * into showMsg from bankMsg where cardno=username;

                  else

                        successOrError:=0;

                        errorMsg:='转账失败,余额不足';

                 end if;

              

               end if;

            

      

        when 'D' then --查看余额

             select * into showMsg from bankMsg where cardno=username;

              successOrError:=1;

              errorMsg:='查询操作成功';

        else

          successOrError:=0;

          errorMsg:='操作失败';

        end case;

      

      else

          successOrError:=0;

          errorMsg:='登陆失败';

     

      

        

  end if;

 

 

end userBankCardnoMsg;

测试代码:

-- Created on 2017/10/23 by ADMINISTRATOR

declare

    successFlag number(1);

    errorMsg varchar2(50);

    showMsg bankMsg%rowType;

begin

  -- Test statements here

   /*

   --测试存款

  userBankCardnoMsg('111111','123','A',10,null,successFlag,errorMsg,showMsg);

 if successFlag=1then

   dbms_output.put_line(errorMsg);

   dbms_output.put_line('卡号:'||showMsg.Cardno||'余额'||showMsg.Money);

  else

   dbms_output.put_line(errorMsg);

  end if;

  */

  /*--测试取款

  userBankCardnoMsg('111111','123','B',10,null,successFlag,errorMsg,showMsg);

 if successFlag=1then

   dbms_output.put_line(errorMsg);

   dbms_output.put_line('卡号:'||showMsg.Cardno||'余额'||showMsg.Money);

  else

   dbms_output.put_line(errorMsg);

  end if;

  */

 

  /*--测试转账

  userBankCardnoMsg('123321','123','C',10,'12321',successFlag,errorMsg,showMsg);

 if successFlag=1then

    dbms_output.put_line(errorMsg);

   dbms_output.put_line('卡号:'||showMsg.Cardno||'余额'||showMsg.Money);

  else

   dbms_output.put_line(errorMsg);

  end if;

  */

  --测试查询余额

   userBankCardnoMsg('222222','123','D',0,null,successFlag,errorMsg,showMsg);

 if successFlag=1 then

    dbms_output.put_line(errorMsg);

    dbms_output.put_line('卡号:'||showMsg.Cardno||'余额'||showMsg.Money);

  else

    dbms_output.put_line(errorMsg);

  end if;

 

end;

 

 

--------------------例子-----------------------------

存储过程:

create or replace procedureTest_emp(username in varchar2,pwdin varchar2,flag in varchar2,resout p_1.cur_type,msgout varchar2 )

is f boolean;

begin

   f:=test_login(username,pwd);

  if fthen--验证通过

       case whenflag='1' then--查询所有数据

          openres for select * fromemp;

          msg:='查询所有记录成功';

       whenflag='2' then--查询当前数据

          openres for select * fromemp where emp.ename=usernameand emp.empno=pwd;

            msg:='查询当前记录成功';

       whenflag='3' then--清空emp表

           delete fromemp;

            msg:='截断表成功';

       end case;

  else

     msg:='验证不通过';

  end if;

end Test_emp;

 

----自定义函数:

create or replace functionTest_login(username in varchar2,pwdin varchar2) return boolean is

  Result boolean  ;

  total integer;

begin

  select count(*) intototal from emp whereemp.ename=username andemp.empno=pwd; 

  iftotal>0 then

    return true;

  else

    return false;

  end if;

end Test_login;

 

-------测试----------

-- Created on 2015/3/24 byADMINISTRATOR

declare

   res p_1.cur_type;

   msg varchar2(200);

   flag varchar2(1):='3';

   r emp%rowtype;

begin

   test_emp('SMITH','7369',flag,res,msg);

  dbms_output.put_line(msg);

   ifflag='1' then

   

      loop

           fetchres into r;

        exit whenres%notfound;

     

         dbms_output.put_line(r.empno||r.ename||r.hiredate);

      end loop;

   elsifflag='2' then

       loop

          fetchres into r;

        exit whenres%notfound;

      

         dbms_output.put_line(r.empno||r.ename||r.hiredate);

      end loop;

   elsifflag='3' then

    null;

   end if;

   exception when others then null;

end;