ORACLE学习资料第五章.doc

第五章 游标和触发器游标：隐式游标：%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_output.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; 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; while 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 myrecord 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 = |myrecord.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 employees2_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 employees2_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 newFOR 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.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 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,name,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;查看employee 表中的 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 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);end if;end;/INSTEAD OF 触发器 INSTEAD OF 触发器是在视图上而不是在表上定义的触发器，它是用来替换所使用实际语句的触发器。 以下代码创建了视图：create view employee_job asselect e.job_id,e.employee_id,e.first_name,e.last_name, from employees2 e,jobs j where e.job_id = j.job_id;以下代码创建 INSTEAD OF 触发器。create or replace trigger tri_viewinstead of insert on employee_jobfor each rowbegin insert into jobs values(:new.job_id,:); insert into employees2(employee_id,first_name,last_name,job_id) values(:new.employee_id,:new.first_name,:new.last_name,:new.job_id);end;/执行以下语句查看操作： insert into employee_job values(OTH,43,abc,dd,OTHER);模式触发器：可以在模式级的操作上建立触发器，如：create ,alter,drop,grant,revoke 和truncate 等 DDL语句：以下示例对用户所删除的所有对象进行日志记录。1 创建数据库表： drop table dropped_obj; CREATE TABLE dropped_obj( obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DATE); 2创建触发器：CREATE OR REPLACE TRIGGER log_drop_objAFTER DROP ON SCHEMABEGIN INSERT INTO dropped_obj VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);END;/ 3创建和删除对象： 创建对象：CREATE TABLE for_drop ( x CHAR ); 删除对象：DROP TABLE for_drop;4查看日志表中的信息： SELECT * FROM dropped_obj;起用和禁用触发器： 以下代码演示了