《（Oracle管理）ORACLE学习资料第五章》由会员分享，可在线阅读，更多相关《（Oracle管理）ORACLE学习资料第五章（7页珍藏版）》请在金锄头文库上搜索。
1、第五章 游标和触发器游标：隐式游标：%FOUND, %NOTFOUND ,%ROWCOUNT1%FOUND 用法，只有在DML 语句影响一行或者多行时，%FOUND 属性才返回 TRUE。下列示例演示了 %FOUND 的用法：begin update employees2 set first_name = first_name | t where employee_id = 2;if SQL%found then dbms_output.put_line(数据已经更新); - dbms_output.put_line(rowCount = |mrowcount);else dbms_outpu
2、t.put_line(数据没有找到);end if;end;/以下代码演示了创建了一个游标，返回employees2 表中 salary 大于300000 的记录，注意type 的使用： declare csalary employees2.salary%type; cursor emp2_cursor is select salary from employees2 where salary 300000;begin open emp2_cursor ; loop fetch emp2_cursor into csalary; exit when emp2_cursor%notfound;
3、dbms_output.put_line(csalary = |csalary); end loop;end;/以下代码演示了创建了一个游标，返回employees2 表中 division_id=SAL 的记录。注意rowtype 的使用：declare cursor employee2_cursor is select * from employees2 where division_id=SAL; myrecord employees2%rowtype;begin open employee2_cursor; fetch employee2_cursor into myrecord; w
4、hile employee2_cursor%found loop dbms_output.put_line(employee id =|myrecord.employee_id); dbms_output.put_line(first Name =|myrecord.first_name); dbms_output.put_line(last name =|myrecord.last_name); fetch employee2_cursor into myrecord;end loop;end;/以下代码演示了带参数的游标，根据division id 查询指定的记录： declare myr
5、ecord employees2%rowtype; cursor emp_cursor(divisionid varchar2) is select * from employees2 where division_id =divisionid;begin open emp_cursor(&divisionid);-loop fetch emp_cursor into myrecord; while emp_cursor%found loop - exit when emp_cursor%notfound; dbms_output.put_line(employee id = |myrecor
6、d.employee_id); dbms_output.put_line(division id = |myrecord.division_id); dbms_output.put_line(first name = |myrecord.first_name); fetch emp_cursor into myrecord;end loop;close emp_cursor;end;/以下代码演示了如何更新 employees2 表中的 first_name 字段：set serveroutput on declare firstName varchar2(20); cursor employ
7、ees2_cursor is select first_name from employees2 where employee_id=1 for update of first_name; begin open employees2_cursor; loop fetch employees2_cursor into firstName; exit when employees2_cursor%notfound; update employees2 set first_Name=jeff where current of employees2_cursor; end loop; close em
8、ployees2_cursor; commit; end; /触发器：触发器是当特定事件出现时自动执行的存储过程特定事件可以是执行更新的DML语句和DDL语句触发器不能被显式调用触发器的功能：自动生成数据自定义复杂的安全权限提供审计和日志记录启用复杂的业务逻辑创建触发器语法：CREATE OR REPLACE TRIGGER trigger_nameAFTER | BEFORE | INSTEAD OFINSERT OR UPDATE OF column_list OR DELETEON table_or_view_nameREFERENCING OLD AS old / NEW AS new
9、FOR EACH ROWWHEN (condition)pl/sql_block;创建触发器，以下代码演示了插入或者修改 employees2 表中的first_name 如果等于 scott时触发器就会执行：create or replace trigger tri_employees2 before insert or update of first_name on employees2 referencing NEW as newdata OLD as olddata for each row when (newdata.first_name=scott) begin :newdata.
10、salary :=20000; dbms_output.put_line(new.salary: | :newdata.salary); dbms_output.put_line(old.salary: | :olddata.salary); end;执行以上触发器：insert into employees2 values(38,SUP,WOR,scott,mp,50000);或者：update employees2 set salary=90000,first_name=scott where employee_id=38;以下代码针对数据完整性进行操作： 删除操作： create or
11、replace trigger del_deptid after delete on dept for each row begin delete from employee where deptid = :old.id; end del_deptid; /执行以上触发器： delete from dept where id=1; 查看employee 表中的 deptid 记录;添加操作： create or replace trigger insert_deptafter insert on deptfor each rowbegin insert into employee(id,nam
12、e,deptid) values(6,chenmp,:new.id);end;/ 执行以上触发器：insert into dept values(6,销售部门); 查看employee 表中的 deptid 记录修改操作： create or replace trigger update_deptafter update on deptfor each row begin update employee set deptid = :new.id where deptid = :old.id;end;/执行以上触发器：update dept set id=8 where id=1;查看emplo
13、yee 表中的 deptid 记录以下代码演示了行级触发器：创建表：drop table rowtable; create table rowtable (id number(8) , name varchar2(100);创建序列 create sequence rowtablesequence;创建触发器：create or replace trigger set_sequencebefore insert on rowtablefor each rowdeclare rsequence number(8);beginselect rowtablesequence.nextval into
14、 rsequence from dual; :NEW.id :=rsequence;end;/执行SQL语句： insert into rowtable values(232,scott);以下代码演示了语句级触发器:创建表：create table mylog(curr_user varchar2(100),curr_date date,opera varchar2(10);创建触发create or replace trigger tri_mylogafter insert or delete or update on employees2beginif inserting theninsert into mylog values(user,sysdate,insert);elsif deleting theninsert into mylog values(user,sysdate,delete);elseinsert into mylog values(user,sysdate,update);
