第五章游標和觸發器
游標：
隱式游標：%FOUND, %NOTFOUND ,%ROWCOUNT
1．%FOUND 用法，只有在DML 語句影響一行或者多行時，%FOUND 屬性才返回 TRUE。下列示例演示了 %FOUND 的用法：
begin
update employees2 set first_name = 'scott' 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語句
觸發器不能被顯式調用
觸發器的功能：
自動生成數據
自定義復雜的安全權限
提供審計和日志記錄
啟用復雜的業務邏輯
觸發器的類型包括：行級觸發器，語句級觸發器、INSTEAD OF 觸發器、模式觸發器、數據庫級觸發器。
創建觸發器語法：
CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]]
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (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;
以下代碼針對數據完整性進行操作(刪除dept 表中的ID 同時把外鍵表employee表中對應的 deptid 信息刪除)：
創建對應的表：
create table dept(deptId number(10),deptName varchar2(20));
insert into dept(deptid,deptname) values(1,'學術部')
insert into dept(deptid,deptname) values(2,'教質部')
insert into dept(deptid,deptname) values(3,'市場部')
insert into dept(deptid,deptname) values(4,'財務部')
create table emp(empid number(10),empName varchar2(20),deptid number(10))
insert into emp(empid,empName,deptid) values(1,'scott',1)
insert into emp(empid,empName,deptid) values(2,'jack',2)
insert into emp(empid,empName,deptid) values(3,'mike',3)
insert into emp(empid,empName,deptid) values(4,'jeff',4)
insert into emp(empid,empName,deptid) values(5,'vera',1)
insert into emp(empid,empName,deptid) values(6,'linda',2)
insert into emp(empid,empName,deptid) values(7,'Marho',3)
insert into emp(empid,empName,deptid) values(7,'dick',4)
刪除操作：
create or replace trigger del_deptid
after delete on dept
for each row
begin
delete from employee where deptid = :old.id;
end del_deptid;
/
執行以上觸發器(由於在dept 表中創建了觸發器，刪除dept 表對應ID 記錄同時刪除employee 表中對應的deptid 信息)：
delete from dept where id=1; 查看employee 表中的 deptid 記錄;
添加操作：
create or replace trigger insert_dept
after insert on dept
for each row
begin
insert into employee(id,name,deptid) values('6','chenmp',:new.id);
end;
/
執行以上觸發器：
insert into dept values(6,'銷售部門');
查看employee 表中的 deptid 記錄
修改操作(在dept 表中創建UPDATE 觸發器,修改DEPT 表ID 同時修改employee 表中的DEPTID)：
create or replace trigger update_dept
after update on dept
for 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_sequence
before insert on rowtable
for each row
declare
rsequence number(8);
begin
select 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_mylog
after insert or delete or update on employees2
begin
if inserting then
insert into mylog values(user,sysdate,'insert');
elsif deleting then
insert into mylog values(user,sysdate,'delete');
else
insert into mylog values(user,sysdate,'update');
end if;
end;
/
模式觸發器：可以在模式級的操作上建立觸發器，如：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_obj
AFTER DROP ON SCHEMA
BEGIN
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;
起用和禁用觸發器：
以下代碼演示了禁用biu_emp_deptno 觸發器：
ALTER TRIGGER biu_emp_deptno DISABLE;
以下代碼演示了啟用biu_emp_deptno 觸發器：
ALTER TRIGGER biu_emp_deptno enable;
可以使用：
Alter table table_name{enable | disable} all triggers;
禁用或者起用在特定表上建立的所有觸發器。
刪除觸發器：
Drop trigger trigger_name;
查看觸發器信息，可以使用user_trigers 數據字典視圖。
Desc user_triggers
