Oracle學習筆記之觸發器_ZenDei技術網路在線

觸發器觸發器(trigger)是一些過程，與表關係密切，用於保護表中的數據，當一個基表被修改(INSERT、UPDATE或DELETE)時，觸發器自動執行，例如通過觸發器可實現多個表間數據的一致性和完整性。觸發器和應用程式無關。觸發器的類型有三種： (1)DML觸發器。Oracle可以在DML( ...

觸發器

觸發器(trigger)是一些過程，與表關係密切，用於保護表中的數據，當一個基表被修改(INSERT、UPDATE或DELETE)時，觸發器自動執行，例如通過觸發器可實現多個表間數據的一致性和完整性。觸發器和應用程式無關。

觸發器的類型有三種：

(1)DML觸發器。Oracle可以在DML(數據操縱語句)語句進行觸發，可以在DML操作前或操作後進行觸發，並且可以在每個行或該語句操作上進行觸發。

(2)替代觸發器。由於在Oracle中不能直接對有兩個以上的表建立的視圖進行操作，所以給出了替代觸發器。它是Oracle專門為進行視圖操作的一種處理方法。

(3)系統觸發器。在Oracle8i時，提供了第三種類型的觸發器叫系統觸發器。它可以在Oracle資料庫系統的時間中進行觸發，如Oracle資料庫的關閉或打開等。

創建觸發器有以下限制：

(1)代碼大小。觸發器代碼大小必須小於32K。

(2)觸發器中有效語句可以包括DML語句，但不能包括DDL語句。ROLLBACK、COMMIT、SAVEPOINT也不能使用。

(3)LONG、LONG RAW和LOB的限制：

① 不能插入數據到LONG或LONG RAW；

② 來自LONG或LONG RAW的數據可以轉換成字元型(如char、varchar2)，但是不能超過32K；

③ 使用LONG或LONG RAW不能聲明變數；

④ 在LONG或LONG RAW列中不能使用:NEW和:PARENT；

⑤ 在LOB中的:NEW變數不能修改。

每張基表最多可

建立12個觸發器，它們是：

(1) BEFORE INSERT；

(2) BEFORE INSERT FOR EACH ROW；

(3) AFTER INSERT；

(4) AFTER INSERT FOR EACH ROW；

(5) BEFORE UPDATE；

(6) BEFORE UPDATE FOR EACH ROW；

(7) AFTER UPDATE；

(8) AFTER UPDATE FOR EACH ROW；

(9) BEFORE DELETE；

(10) BEFORE DELETE FOR EACH ROW；

(11) AFTER DELETE；

(12) AFTER DELETE FOR EACH ROW。

例如：做一個觸發器，當刪除dept表中部門時，將emp表中該部門人員信息清空；

createorreplacetrigger emp_dept

afterdelete on dept

foreachrow

declare

begin

deletefrom emp where emp.deptno=:old.deptno;

--表示刪除或者修改前該記錄舊的數據；

end emp_dept;

例如：修改上題，要求將刪除的dept和emp表數據備份到指定表 deptOld和empOld表結構和dept、emp結構一致；

createorreplacetrigger emp_dept

afterdelete on dept

foreachrow

declare

cursor cur isselect*from emp where emp.deptno=:old.deptno;

e emp%rowtype;

department dept%rowtype;

begin

deletefrom emp where emp.deptno=:old.deptno;

ifsql%foundthen

open cur;

loop

fetch cur into e;

insertinto empOld values(e.empno,e.ename,e.job,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno);

exitwhen cur%notfound;

endloop;

endif;

insertinto deptOld values(:old.deptno,:old.dname,:old.loc);

end emp_dept;

create or replace trigger deleteDeptTrigger

after delete on dept

for each row

declare

type cur_type is ref cursor;

cur cur_type;

emp_row emp%rowtype;

begin

open cur for

select * from emp where deptno=:old.deptno;

delete from emp where deptno=:old.deptno;

loop

fetch cur into emp_row;

exit when cur%notfound;

insert into empOld values(emp_row.empno,emp_row.ename,emp_row.job,emp_row.mgr,emp_row.hiredate,emp_row.sal,emp_row.comm,emp_row.deptno);

end loop;

close cur;

insert into deptOld values(:old.deptno,:old.dname,:old.loc);

end deleteDeptTrigger;

305講解內容：

create or replace trigger deptTrigger

after delete on dept

for each row

declare

dept_deptno number(5);

type cur is ref cursor return emp%rowtype;

empcur cur;

emprow emp%rowtype;

begin

dept_deptno:=:old.deptno;

open empcur for select * from emp where emp.deptno=dept_deptno;

delete from emp where deptno=dept_deptno;

loop

fetch empcur into emprow;

insert into oldemp values(e.nextval,emprow.empno,emprow.ename,emprow.job,emprow.mgr,emprow.hiredate,emprow.sal,emprow.comm,emprow.deptno);

exit when empcur%notfound;

end loop;

end deptTrigger;

創建替代(Instead_of)觸發器：

Instead_of用於對視圖的DML觸發。由於視圖有可能由多個表進行關聯(Join)而成，因

而並非所有的關聯都是可更新的。但是可以按如下例子來創建觸發器。

例如：

在scott資料庫中創建視圖和觸發器，以說明替代觸發器。

CREATEORREPLACE VIEW emp_avgSalView

AS SELECT deptno,AVG(sal) AS avgSAL FROM emp

GROUPby deptno;

創建該視圖的替代觸發器：

CREATETRIGGER empAvgSalDel

INSTEADOFDELETEON emp_avgSalView FOREACHROW

BEGIN

DELETEFROM emp WHERE deptno=:OLD.deptno;

END cs_kc_avg_del;

創建系統觸發器：

Oracle8i開始提供的系統觸發器可以在DDL或資料庫系統上被觸發。

DDL指的是數據定義語句，如CREATE、ALTER和DROP等。而數據

庫系統事件包括資料庫伺服器的啟動或關閉，用戶登錄與退出等。

--創建當一個用戶userA登錄時自動記錄一些信息的觸發器。

CREATETRIGGER loguserAconnects

AFTER LOGON ONSCHEMA

BEGIN

INSERTINTO LOGIN VALUES(‘userA’,’loguserAconnects fired’);

END loguserAconnects;

面試題筆試題查詢：

1、通過case when行編列，列邊行；

2、刪除重覆行、留重覆行中的一行記錄。

3、Nvl和nvl2 區別

4、說出常用的10個資料庫函數

5、索引如何使用？ 何時失效？

6、Sql語句級別的優化？

7、函數和存儲過程區別？

8、手寫存儲過程。

9、Oracle分頁語句和mysql分頁語句？

10、Rowid？ 什麼是事務？

例如：查詢2000-3000之間的sal和、 4000-6000之間sal和、6000-8000sal和、

Select sum(

Case when sal between 2000 and 3000

Then sal else 0 end

) ,sum(

Case when sal between 4000and 6000

Then sal else 0 end

) from emp

Select count(

Case when sal between 2000 and 3000

Then empno else null end

) ,count(

Case when sal between 4000and 6000

Then empno else null end

) from emp

綜合實例：

模擬銀行匯款、取款、以及存款、餘額查詢操作：

Id、卡號、密碼、存款餘額

create table bankMsg( id number(15) primary key,cardno varchar2(20) unique not null,pwd varchar2(20) not null,

money number(15,2)

);

Id、卡號、操作標示符（A存款、B取款、C轉賬）、操作時間、操作金額

create table bankMsgHistory(id number(5) primary key,cardno varchar2(20),

flag varchar2(1),markDate date,money number(15,2)

)

寫一個函數驗證是否登陸成功；

寫一個存儲過程、用於操作存款、取款、轉賬以及餘額查詢；

（註意：由於觸發器獲取不到操作標示符因此不寫這個觸發器了）寫一個觸發器，當bankMsg表被修改以後，觸發操作記錄表數據新增；

登陸操作函數：

create or replace function bankLoginByCardno(cardno_param in varchar2,pwd_param in varchar2) return number is

total number(5);

flag number(1):=0;

begin

select count(*) into total from bankMsg where cardno =cardno_param and pwd=pwd_param;

if total >0 then

flag:=1;

end if ;

return flag;

end bankLoginByCardno;

存儲過程：

--該存儲過程，根據用戶名密碼登陸，卡號為用戶名，pwd自定義，

--根據操作標示Flag （A存款、B取款、C轉賬）對銀行卡信息數據進行修改，然後觸發記錄表記錄操作記錄

username ：用戶名

pwd：密碼

controlFlag操作標示符

money金額

targetCardno對方卡號

succssOrError 是否操作成功，1 成功，0 失敗

errorMsg 操作失敗原因out類型參數

showMsg 查詢餘額信息的out類型變數

create or replace procedure userBankCardnoMsg(username in varchar2, pwd in varchar2,

controlFlag in varchar2,money_param in number,targetCardno in varchar2,successOrError out number,

errorMsg out varchar2,

showMsg out bankMsg%rowType

) is

flag number(5):=0;

flag2 number(5):=0;

money number(15,2);

begin

successOrError:=0;--成功失敗標示符預設為0

flag:=bankloginbycardno(username,pwd);

-- money_param:=nvl(money_param,0);

if flag=1 then--登陸成功

select money into money from bankMsg where cardno=username;

case controlFlag

when 'A' then--存款

update bankMsg set money=money+money_param where cardno=username;--修改金額

insert into bankMsgHistory values(bankcardno_seq.nextval,username,controlFlag,sysdate,money_param);

successOrError:=1;

errorMsg:='存款成功';

select * into showMsg from bankMsg where cardno=username;

when 'B' then--取款

if money>=money_param then--判斷是否有足夠餘額

update bankMsg set money=money-money_param where cardno=username;--修改金額

insert into bankMsgHistory values(bankcardno_seq.nextval,username,controlFlag,sysdate,money_param);

successOrError:=1;

errorMsg:='取款成功';

select * into showMsg from bankMsg where cardno=username;

else

successOrError:=0;

errorMsg:='取款失敗，餘額不足';

end if;

when 'C' then--轉賬

select count(*) into flag2 from bankMsg where cardno=targetCardno;--查看目標賬號是否存在

if flag2<=0 then

successOrError:=0;

errorMsg:='轉賬失敗，目標卡號不存在';

else

if money>=money_param then--判斷是否有足夠餘額

update bankMsg set money=money-money_param where cardno=username;--修改金額

insert into bankMsgHistory values(bankcardno_seq.nextval,username,controlFlag,sysdate,money_param);

update bankMsg set money=money+money_param where cardno=targetCardno;--修改目標賬號金額

insert into bankMsgHistory values(bankcardno_seq.nextval,targetCardno,'A',sysdate,money_param);

successOrError:=1;

errorMsg:='轉賬成功';

select * into showMsg from bankMsg where cardno=username;

else

successOrError:=0;

errorMsg:='轉賬失敗，餘額不足';

end if;

when 'D' then --查看餘額

select * into showMsg from bankMsg where cardno=username;

successOrError:=1;

errorMsg:='查詢操作成功';

else

successOrError:=0;

errorMsg:='操作失敗';

end case;

else

successOrError:=0;

errorMsg:='登陸失敗';

end if;

end userBankCardnoMsg;

測試代碼：

-- Created on 2017/10/23 by ADMINISTRATOR

declare

successFlag number(1);

errorMsg varchar2(50);

showMsg bankMsg%rowType;

begin

-- Test statements here

--測試存款

userBankCardnoMsg('111111','123','A',10,null,successFlag,errorMsg,showMsg);

if successFlag=1 then