1、.：.；第五章 游標(biāo)和觸發(fā)器游標(biāo)：隱式游標(biāo)：%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(數(shù)據(jù)曾經(jīng)更新); - dbms_output.put_line(rowCount = |mrowcount);else dbms_o

2、utput.put_line(數(shù)據(jù)沒有找到);end if;end;/以下代碼演示了創(chuàng)建了一個游標(biāo)，前往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%notfou

3、nd; dbms_output.put_line(csalary = |csalary); end loop;end;/以下代碼演示了創(chuàng)建了一個游標(biāo)，前往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 myrecor

4、d; 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;/以下代碼演示了帶參數(shù)的游標(biāo)，根據(jù)division id 查詢指定的記錄： declare

5、 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 = |myr

6、ecord.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 em

7、ployees2_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; clos

8、e employees2_cursor; commit; end; /觸發(fā)器：觸發(fā)器是當(dāng)特定事件出現(xiàn)時自動執(zhí)行的存儲過程特定事件可以是執(zhí)行更新的DML語句和DDL語句觸發(fā)器不能被顯式調(diào)用觸發(fā)器的功能：自動生成數(shù)據(jù)自定義復(fù)雜的平安權(quán)限提供審計和日志記錄啟用復(fù)雜的業(yè)務(wù)邏輯創(chuàng)建觸發(fā)器語法：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

9、 newFOR EACH ROWWHEN (condition)pl/sql_block;創(chuàng)建觸發(fā)器，以下代碼演示了插入或者修正 employees2 表中的first_name 假設(shè)等于 scott時觸發(fā)器就會執(zhí)行：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 :newd

10、ata.salary :=20000; dbms_output.put_line(new.salary: | :newdata.salary); dbms_output.put_line(old.salary: | :olddata.salary); end;執(zhí)行以上觸發(fā)器：insert into employees2 values(38,SUP,WOR,scott,mp,50000);或者：update employees2 set salary=90000,first_name=scott where employee_id=38;以下代碼針對數(shù)據(jù)完好性進展操作： 刪除操作： create

11、 or replace trigger del_deptid after delete on dept for each row begin delete from employee where deptid = :old.id; end del_deptid; /執(zhí)行以上觸發(fā)器： delete from dept where id=1; 查看employee 表中的 deptid 記錄;添加操作： create or replace trigger insert_deptafter insert on deptfor each rowbegin insert into employee(id

12、,name,deptid) values(6,chenmp,:new.id);end;/ 執(zhí)行以上觸發(fā)器：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;/執(zhí)行以上觸發(fā)器：update dept set id=8 where id=1;查看e

13、mployee 表中的 deptid 記錄以下代碼演示了行級觸發(fā)器：創(chuàng)建表：drop table rowtable; create table rowtable (id number(8) , name varchar2(100);創(chuàng)建序列 create sequence rowtablesequence;創(chuàng)建觸發(fā)器：create or replace trigger set_sequencebefore insert on rowtablefor each rowdeclare rsequence number(8);beginselect rowtablesequence.nextval

14、into rsequence from dual; :NEW.id :=rsequence;end;/執(zhí)行SQL語句： insert into rowtable values(232,scott);以下代碼演示了語句級觸發(fā)器:創(chuàng)建表：create table mylog(curr_user varchar2(100),curr_date date,opera varchar2(10);創(chuàng)建觸發(fā)create or replace trigger tri_mylogafter insert or delete or update on employees2beginif inserting the

15、ninsert 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 觸發(fā)器 INSTEAD OF 觸發(fā)器是在視圖上而不是在表上定義的觸發(fā)器，它是用來交換所運用實踐語句的觸發(fā)器。 以下代碼創(chuàng)建了視圖：create view employee_job asselect e.job_id,e.emplo

16、yee_id,e.first_name,e.last_name, from employees2 e,jobs j where e.job_id = j.job_id;以下代碼創(chuàng)建 INSTEAD OF 觸發(fā)器。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

17、,job_id) values(:new.employee_id,:new.first_name,:new.last_name,:new.job_id);end;/執(zhí)行以下語句查看操作： insert into employee_job values(OTH,43,abc,dd,OTHER);方式觸發(fā)器：可以在方式級的操作上建立觸發(fā)器，如：create ,alter,drop,grant,revoke 和truncate 等 DDL語句：以下例如對用戶所刪除的一切對象進展日志記錄。創(chuàng)建數(shù)據(jù)庫表： drop table dropped_obj; CREATE TABLE dropped_obj(

18、 obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DATE); 2創(chuàng)建觸發(fā)器：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創(chuàng)建和刪除對象： 創(chuàng)建對象：CREATE TABLE for_drop ( x CHAR ); 刪除對象：DROP TABLE for_drop;4查看日志表中的信息： SELECT * FROM dropped_obj;起用和禁用觸發(fā)器： 以下代碼演示了