ORACLE学习资料第五章

1、1 / 16第五章 游标和触发器游标：隐式游标：%FOUND, %NOTFOUND ,%ROWCOUNT1.%FOUN用法，只有在DML语句影响一行或者多行时，FOUN属性才返回TRUE.下列示例演示了FOUND勺用法:beginupdate employees2 set first_name = first_name| t where employee_id = 2;if SQL%found thendbms_output.put_line（数据已经更新）;- dbms_output.put_line（rowCount = |mrowcount）;elsedbms_output.put_li

2、ne（数据没有找到）;end if;end;/以下代码演示了创建了一个游标，返回employees2表中salary大于300000的记录，注意type的使用：2 / 16declarecsalary employees2.salary%type;cursor emp2_cursor is select salary from employees2where salary 300000;beginopen emp2_cursor ;loopfetch emp2_cursor into csalary;exit when emp2_cursor%notfound;dbms_output.put_

3、line(csalary = |csalary);end loop;end;/以下代码演示了创建了一个游标，返回division_id=SAL 的记录。注意rowtype的使用：employees2表中3 / 16declarecursor employee2_cursor is select * from employees2where division_id=SAL;myrecord employees2%rowtype;beginopen employee2_cursor;fetch employee2_cursor into myrecord;while employee2_curso

4、r%found loopdbms_output.put_line(employee=|myrecord.employee_id);dbms_output.put_line(first =|myrecord.first_name);dbms_output.put_line(last =|myrecord.last_name);fetch employee2_cursor into myrecord;end loop;end;/以下代码演示了带参数的游标，根据division ididNamename查询指4 / 165 / 16定的记录：declaremyrecord employees2%ro

5、wtype;cursor emp_cursor(divisionid varchar2) is select * fromemployees2 where division_id =divisionid;beginopen emp_cursor(&divisionid);-loopfetch emp_cursor into myrecord;while emp_cursor%found loop- exit when emp_cursor%notfound;dbms_output.put_line(employee id |myrecord.employee_id);dbms_outp

6、ut.put_line(division id |myrecord.division_id);dbms_output.put_line(firstname|myrecord.first_name);fetch emp_cursor into myrecord;end loop;close emp_cursor;end;6 / 16/以下代码演示了如何更新employees2表中的first_name字段：set serveroutput ondeclarefirstName varchar2(20);cursor employees2_cursor is select first_namefr

7、om employees2 where employee_id=1 for update offirst_name;beginopen employees2_cursor;loopfetch employees2_cursor into firstName;exit when employees2_cursor%notfound;update employees2set first_Name=jeff where current ofemployees2_cursor;end loop;close employees2_cursor;7 / 16commit;end; /触发器：触发器是当特定

8、事件出现时自动执行的存储过程特定事件可以是执行更新的DML吾句和DDL语句触发器不能被显式调用触发器的功能：自动生成数据自定义复杂的安全权限提供审计和日志记录启用复杂的业务逻辑创建触发器吾法：CREATE OR REPLACE TRIGGER trigger_nameAFTER | BEFORE | INSTEAD OFINSERT OR UPDATE OF column_listOR DELETEON table_or_view_nameREFERENCING OLD AS old / NEW AS newFOR EACH ROWWHEN (condition) pl/sql_block;8

9、 / 16创建触发器， 以下代码演示了插入或者修改employees2表 中的first_name如果等于 scott时触发器就会执行：create or replace trigger tri_employees2before insert or update of first_nameon employees2referencing NEW as newdata OLD as olddata for each rowwhen (newdata.first_name=scott)begin:newdata.salary :=20000;dbms_output.put_line(new.salary:| :newdata.salary);dbms_output.put_line(old.salary:9 / 16| :olddata.salary);end;执行以上触发器：insert intovalues(38,SUP,WOR,scott,mp,50000);或者：update employees2salary=900