游标和触发器

1、游标和触 发 器1游标简介 2-1Oracle 服务器执行 PL/SQL 程序内存单元保存到游标中一次处理一行检索行提取行2游标简介 2-2逐行处理查询结果，以编程的方式访问数据游标的类型：隐式游标REF 游标显式游标游标类型 游标的属性有：%FOUND 检查是否从结果集中提取到了数据，提取到返回 TRUE%NOTFOUND 与%FOUND相反%ROWCOUNT 返回到当前为止已经提取到的实际行数%ISOPEN - 游标是否打开，始终为FALSE3游标4显式游标 2-1显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行显式游标的操作过程： Declare-Open-Fetch-

2、 -Close数据库打开游标30George344Roger245James1Stud_mrksStud_namestud_no提取行变量 关闭游标 空5显式游标 2-2SQLSET SERVER OUTPUT ONSQLDECLARE my_toy_price toys.toyprice%TYPE; CURSOR toy_cur IS SELECT toyprice FROM toys WHERE toyprice250; BEGIN OPEN toy_cur; LOOP FETCH toy_cur INTO my_toy_price; EXIT WHEN toy_cur%NOTFOUND;

3、 DBMS_OUTPUT.PUT_LINE (TOYPRICE=:玩具单价=：|my_toy_price); END LOOP; CLOSE toy_cur; END;声明游标打开游标提取行关闭游标6带参数的显式游标声明显式游标时可以带参数以提高灵活性声明带参数的显式游标的语法如下：CURSOR ( ) IS select_statement;SQL SET SERVEROUTPUT ONSQL DECLAREdesig VARCHAR2(20);emp_code VARCHAR2(5);empnm VARCHAR2(20);CURSOR emp_cur(desig VARCHAR2) IS

4、SELECT empno, ename FROM employee WHERE designation=desig; BEGINdesig:= 1000;OPEN emp_cur(desig);LOOPFETCH emp_cur INTO emp_code,empnm;EXIT WHEN emp_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE(emp_code| |empnm); END LOOP;CLOSE emp_cur; END;7使用显式游标更新行 2-1允许使用游标删除或更新活动集中的行声明游标时必须使用 SELECT FOR UPDATE语句 CURSOR IS

5、 SELECT statement FOR UPDATE;UPDATE SET WHERE CURRENT OF 更新的语法DELETE FROM WHERE CURRENT OF 删除的语法 8使用显式游标更新行 2-2SQL SET SERVEROUTPUT ONSQL DECLARE new_price NUMBER; CURSOR cur_toy IS SELECT toyprice FROM toys WHERE toyprice100 FOR UPDATE OF toyprice;BEGIN OPEN cur_toy; LOOP FETCH cur_toy INTO new_pri

6、ce; EXIT WHEN cur_toy%NOTFOUND; UPDATE toys SET toyprice = 1.1*new_price WHERE CURRENT OF cur_toy; END LOOP; CLOSE cur_toy; COMMIT;END;9循环游标 2-1循环游标用于简化游标处理代码当用户需要从游标中提取所有记录时使用循环游标的语法如下：FOR IN LOOPEND LOOP;10循环游标 2-2SQL SET SERVER OUTPUT ONSQL DECLARE CURSOR mytoy_cur IS SELECT toyid, toyname, toypr

7、ice FROM toys; BEGIN FOR toy_rec IN mytoy_cur LOOP DBMS_OUTPUT.PUT_LINE( 玩具编号：| |toy_rec.toyid| |玩具名称：| |toy_rec.toyname| |玩具单价：| |toy_rec.toyprice); END LOOP; END;11隐式游标 4-1显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下 而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标隐式游标的名字为SQL，这是由ORACLE 系

8、统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。 格式调用为： SQL%注：INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标 SQL%FOUND 布尔型属性,当最近一次读记录时成功返回，则值为true； SQL%NOTFOUND 布尔型属性,与%found相反； SQL %ROWCOUNT 数字型属性, 返回已从游标中读取得记录数； SQL %ISOPEN 布尔型属性, 取值总是FALSE。SQL命令执行完毕立即关闭隐式游标。12隐式游标 4-2SQL SET SERVEROUTPUT ONSQ

9、L BEGINUPDATE toys SET toyprice=270WHERE toyid= P005;IF SQL%FOUND THENDBMS_OUTPUT.PUT_LINE(表已更新);END IF; END;/只有在 DML 语句影响一行或多行时，才返回 True13隐式游标 4-3SQL SET SERVEROUTPUT ONSQL DECLARE v_TOYID TOYS.ID%type := &TOYID; v_TOYNAME TOYS.NAME%Type := &TOYNAME; BEGIN UPDATE TOYS SET NAME = v_TOYNAME WHERE toy

10、id=v_TOYID; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE(编号未找到。); ELSEDBMS_OUTPUT.PUT_LINE(表已更新);END IF; END;/如果 DML 语句不影响任何行，则返回 True 14隐式游标 4-4SQL SET SERVEROUTPUT ON SQL BEGINUPDATE vendor_masterSET venname= Rob MathewWHERE vencode=V004;DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT); END; /返回 DML 语句影响的行数15SELE

11、CT INTO 语句 2-1SQL SET SERVEROUTPUT ONSQL DECLARE empid VARCHAR2(10);desig VARCHAR2(10); BEGINempid:= &Employeeid;SELECT designation INTO desig FROM employee WHERE empno=empid; EXCEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(职员未找到); END; /如果没有与SELECT INTO语句中的条件匹配的行，将引发NO_DATA_FOUND异常16SELECT IN

12、TO 语句 2-2SQL SET SERVEROUTPUT ONSQL DECLARE empid VARCHAR2(10); BEGINSELECT empno INTO empid FROM employee; EXCEPTIONWHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(该查询提取多行); END; /如果 SELECT INTO 语句返回多个值，将引发TOO_MANY_ROWS异常17REF游标应用：过程返回结果集由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参

13、数，必须要用pagkage了.所以要分两部分，1， 建一个程序包。如下：CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE Test_CURSOR IS REF CURSOR;end TESTPACKAGE;2，建立存储过程，存储过程为：CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS BEGIN OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;END TESTC;可以看到，它是把游标（可以理解为一个指针），作为一个o

14、ut 参数来返回值的。18练 习编写游标打印出emp表中的职员编号、姓名、上级编号、工资要求职员编号和上级编号都为偶数只打印其中的五条记录注意考虑游标不足五条记录的情况19触发器14.1 概述 14.2 语句触发器 14.3 行触发器 14.4 instead of触发器 14.5 系统事件触发器 14.6 用户事件触发器 14.7 禁用触发器和事务处理 14.8 查看触发器信息 14.9 事件属性函数 2014.1 概述触发器是当特定事件出现时自动执行的存储过程特定事件可以是执行更新的DML语句和DDL语句触发器不能被显式调用使用触发器可以完成的功能如下：允许或限制对表的修改；自动生成派生列

15、；强制数据一致性；提供审计和日志记录；防止无效的事务处理；启用复杂的业务逻辑。在Oracle系统中，触发器有4个组成部分，即触发器名称、触发语句、触发器限制和触发操作。下面详细介绍这些组成部分。21例子-触发器create or replace trigger tri_emp_empnobefore insert or update -insert or update 意思是指insert 或update语句执行时就触发on emp -触发器依赖的表for each row -行触发器begin :m:=0; end;-测试select * from emp;update emp set sa

16、l=1301 where empno=7934;update emp set sal=sal*1.01; 22触发器名称 触发器名称就是触发器的名称。数据库中的每一个对象都有名称，触发器对象也不例外。一般应该采用描述性名称来定义触发器名称。在触发器名称中，应该包括触发器执行的时间、执行的操作、涉及的表和列等。下面是一个定义触发器部分语句的示例：create trigger trigger_emp_empno23触发语句 触发语句就是那些导致Oracle执行触发器的事件。触发器事件可以是下面的内容：表和一些视图上的数据操纵语言语句，例如insert、update、delete；模式对象上的数据定

17、义语言语句，例如create、alter、drop；数据库的启动和关闭；系统错误；各种系统活动。24触发器限制 即使导致触发器激活的事件发生了，但是也不意味着Oracle就会执行它。激活事件发生之后，触发器首先判断在触发器上是否存在限制，然后才会在执行触发器之间评估所有的限制。某个限制条件可能如下：when(new_value.empno7782)25触发操作 触发操作是触发器的主体。当发生如下两件事情时Oracle才会执行代码块：使用了合适的语句；如果提供了触发器限制，则评估为真。26触发器的类型 Oracle系统有5种类型的触发器。每一种类型的触发器都可以完成不同的任务。这5种类型的触发器

18、分别是：语句触发器、行触发器、instead of触发器、系统事件触发器和用户事件触发器。触发器类型用户事件触发器DML触发器系统事件触发器语句级触发器行级触发器INSTEAD OF触发器2714.2 语句触发器语句触发器是在表上或某些视图上执行的特定语句的触发器。语句触发器能够与insert、update或delete语句或这些语句的任意组合关联。用户既可以在表的insert或update语句上使用单独的触发器，也可以在表的insert和update语句的组合上使用触发器，甚至可以在单独的表上拥有多个insert语句触发器。28创建语句触发器 用户可能希望在表上创建进行安全检查的insert

19、、update或delete语句触发器，以便根据用户的具体业务规则验证正在进行操作的用户是否具有指定的权限。这种类型的简单触发器如图14-1所示。29级联触发器 这里需要考虑的是级联触发器的概念。这个概念意味着用户触发器的操作将会导致另一个触发器激活，以此类推。如果用户有3个表A、B和C。在表A上有一个insert触发器，它会向表B中插入一行。而在表B上有一个向表C插入一行的触发器，那么这就是级联触发器。在开发应用程序时，一定要注意这种现象，因为这种级联可能会对性能产生影响，甚至可能带来不可预料的后果。30修改触发器 可以使用create or replace命令更新或改变触发器。该命令可以改

20、变触发器的主体、限制子句等。也可以从根本上重新定义触发器。如果需要，可以使用create or replace命令处理某个触发器，将其从foo表上的insert类型触发器改变为bar表上的update触发器。为了从数据库中删除触发器，可以使用drop trigger 命令。31确定哪个语句起作用 在很多情况下，需要知道哪个语句导致了触发器被激活。如果只在一种类型的语句上建立触发器，那么这是一件非常简单的事情。但是，如果创建了一个触发器，但是该触发器针对多种类型的语句，那么想要知道是哪个语句导致了触发器的激活，这好像是很难的事情。实际上，这也很简单，用户可以询问Oracle是哪一个语句导致了触发

21、器被激活。所需的事情就是引用inserting、updating或deleting条件谓词，引用方式如下。如果值为真，那么就是相应的语句类型初始化了触发器。32before和after 通过前面的示例可以看到，语句触发器能够在语句执行前(before)被激活，这使得它非常适合于强化安全、启用业务规则、进行日志操作等。能否建立after语句触发器吗？回答是肯定的。在after语句触发器中，Oracle会在语句完成之后执行它们，而不是在语句执行前执行它们。Before和after触发在语句触发器中使用起来没区别3314.3 行触发器用户不仅可以建立为表上各个insert或update或delete

22、语句激活的触发器，而且还可以定义受到影响的各行激活的触发器。这些触发器称为行触发器。行触发器的定义方式与语句触发器类似，但是有以下两个例外：(1) 行触发器要在触发器定义的触发语句中包含for each row子句，还可以包含referencing子句。(2) 在beforefor each row触发器中，可以引用受到影响的行值，甚至可以在触发器中设置这些值。在行级触发器中的区别在于，after的触发器不能对:new的值进行更新34行触发器的作用行触发器的作用主要是可以使触发器的编写人员有能力为主键提供默认设置，并且还可以格式化输入表中的数据。可以在:new_value列值上应用initca

23、p()、substr()、upper()、lower()等函数，因此可以格式化数据，确保数据的一致性。这样使得在查询中选择数据或建立约束变得更容易。如果可以确保数据采用了特定的格式，那么就可以在where子句中建立更简单的谓词。35简单的行触发器-简单的行触发器create or replace trigger trigger_person_insert_updatebefore inserton personfor each row -行触发器begin select count(*)+1 into :new.PersonID from person;end;-测试insert into p

24、erson(PersonName) values(rkk);insert into person(PersonID,PersonName) values(16,aaa);select * from person36复杂点的行触发器-复杂点的行触发器-beforecreate or replace trigger tr_emp_salbefore update of sal -更新sal列才触发on empfor each rowbegin if :new.sal:old.sal then raise_application_error(-20010,工资只允许升不许降!); end if;en

25、d;3714.4 instead of触发器instead of触发器是Oracle系统用来替换所使用的实际语句而执行的触发器，与导致触发器激活的语句执行之前或执行之后执行的语句触发器和行触发器不同。例如，如果在视图上执行insert操作的用户，且该视图上有instead of插入触发器，那么Oracle系统就不会执行用户的触发器。然后，用户的触发器就可以执行编码所要执行的所有操作。它可以向基本表或另外的表中插入用户数据，还可以对用户试图插入数据的事实进行日志记录，还可以从完全没有关系的表中删除数据。当然，这些操作都依赖于用户的编码。另外，需要使用instead of触发器的地方是修改对象视图

26、、嵌套表，以及其他使用了distinct、group by、connect by、start with等子句和count、sum等合计函数的地方。在定义instead of触发器时注意，这里没有before instead of触发器和after instead of触发器。这是因为instead of触发器总是等同于after行触发器。instead of触发器会为每个受影响的行激活一次，用户不能修改它们的:new值。用于用户不能直接使用 DML 语句修改的视图INSTEAD OF 触发器38instead of触发器-创建视图create view emp_dept asselect em

27、p.*,dept.dname,dept.loc from empinner join dept on emp.deptno=dept.deptnoselect * from emp_dept-更新视图update emp_dept set ename=aa where empno=7369 -失败-创建instead of 触发器create or replace trigger emp_dept_triggerinstead of updateon emp_dept -视图begin update emp set ename=:new.ename where empno=:old.empno

28、;end;update emp_dept set ename=aa where empno=7369 再次更新，成功3914.5 系统事件触发器Oracle系统还允许开发人员建立与特定系统事件相关的触发器。这些特定事件如下：数据库启动；数据库关闭；服务器错误等。这些事件都是实例范围的。这些触发器不像前面介绍的那些触发器那样与特定的表或视图关联。 4014.6 用户事件触发器用户事件触发器也称为客户触发器，是能够与insert、update、delete以外的用户登录、注销、DML、DDL操作事件相关联的触发器。这些用户事件包括了下面的语句和命令的before和after触发器：createal

29、terdropanalyzeassociate statisticsdisassociate statisticsauditnoauditcommentgrantrevokerenametruncate下面事件只有before触发器logoff下面事件只有after触发器suspend41触发器总结执行 非insert delete update 语句时执行在发生打开、关闭、登录和退出数据库等系统事件时执行执行DML语句时执行无论受影响的行数是多少，都只执行一次对DML语句修改的每个行执行一次用于用户不能直接使用 DML 语句修改的视图或其他表用户事件触发器系统事件级触发器DML 触发器语句级

30、触发器行级触发器INSTEAD OF 触发器4214.7 禁用触发器和事务处理到目前为止已经介绍了怎样使用create、create or replace和drop语句来执行与触发器有关的操作。如果希望改变触发器的主体，那么可以使用create or replace语句。如果需要删除今后不再使用的触发器，那么可以使用drop语句。如果需要临时禁用触发器，那么可以使用alter语句，语法格式如下：alter trigger disable;alter trigger enable; -激活Alter table emp disable all triggers; -禁用emp表所有触发器Alter table emp enable all triggers; -激活所有触发器43事务处理有关事务处理