Oracle觸發(fā)器和程序包如何創(chuàng)建

今天小編給大家分享一下Oracle觸發(fā)器和程序包如何創(chuàng)建的相關(guān)知識(shí)點(diǎn)，內(nèi)容詳細(xì)，邏輯清晰，相信大部分人都還太了解這方面的知識(shí)，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

一、觸發(fā)器的概念

1、觸發(fā)器的基本概念

觸發(fā)器本身是一段程序代碼，類似于存儲(chǔ)過程和函數(shù)，但是與存儲(chǔ)過程和函數(shù)不同的是，存儲(chǔ)過程和函數(shù)創(chuàng)建后保存在oracle數(shù)據(jù)庫中，如果要執(zhí)行需要用戶調(diào)用才可以。觸發(fā)器創(chuàng)建完成后，以獨(dú)立的對(duì)象存儲(chǔ)在oracle數(shù)據(jù)庫中，根據(jù)事先定義的觸發(fā)事件來決定是否執(zhí)行，只要發(fā)生這些觸發(fā)事件，觸發(fā)器就會(huì)自動(dòng)執(zhí)行。另外，觸發(fā)器不能接收參數(shù)。
觸發(fā)器通常由觸發(fā)器頭部和觸發(fā)體兩部分組成，可以具體細(xì)分為如下幾方面。
1、作用對(duì)象：包括數(shù)據(jù)表、視圖、數(shù)據(jù)庫和模式。
2、觸發(fā)事件：指可以引起觸發(fā)器執(zhí)行的事件。例如，DML語句中對(duì)數(shù)據(jù)表或者視圖執(zhí)行數(shù)據(jù)操作的insert、update、delete語句；DDL語句中執(zhí)行創(chuàng)建、修改或者刪除的cretae、alter、drop語句以及數(shù)據(jù)庫系統(tǒng)的事件，包括oracle系統(tǒng)啟動(dòng)或者退出、異常錯(cuò)誤等。
3、觸發(fā)條件：由when子句指定的一個(gè)邏輯表達(dá)式，當(dāng)觸發(fā)事件發(fā)生時(shí)，如果該邏輯表達(dá)式的值為true時(shí)，觸發(fā)器就會(huì)自動(dòng)執(zhí)行。
4、觸發(fā)事件：指觸發(fā)器指令執(zhí)行是在觸發(fā)事件發(fā)生之前，還是在觸發(fā)事件發(fā)生之后。
5、觸發(fā)級(jí)別或者觸發(fā)頻率：分為語句級(jí)和行級(jí)觸發(fā)器。語句級(jí)觸發(fā)器是默認(rèn)的，指觸發(fā)事件發(fā)生后，觸發(fā)器只執(zhí)行一次；行級(jí)觸發(fā)器指觸發(fā)事件每作用于一個(gè)記錄，觸發(fā)器就執(zhí)行一次。

2、觸發(fā)器的分類

根據(jù)觸發(fā)器的使用范圍不同，可以把觸發(fā)器分為3種：DML觸發(fā)器、instead of觸發(fā)器和系統(tǒng)觸發(fā)器。
1、dml觸發(fā)器
在執(zhí)行dml語句時(shí)觸發(fā)，可以定義為insert、update、delete操作，也可以定義在操作之前或者之后觸發(fā)，也可以指定為行級(jí)觸發(fā)或者語句級(jí)觸發(fā)。
2、instead of觸發(fā)器
它也被稱為替代觸發(fā)器，它是oracle專門為視圖設(shè)計(jì)的一種觸發(fā)器。在oracle數(shù)據(jù)庫中，一般不能直接對(duì)兩個(gè)以上的表建立的視圖進(jìn)行一般的觸發(fā)器操作，如果必須修改，就使用替代觸發(fā)器。
3、系統(tǒng)觸發(fā)器
在oracle數(shù)據(jù)庫的系統(tǒng)事件發(fā)生時(shí)進(jìn)行觸發(fā)，例如系統(tǒng)啟動(dòng)或者退出、異常錯(cuò)誤等，這種系統(tǒng)觸發(fā)器稱為數(shù)據(jù)庫觸發(fā)器；或者發(fā)生ddl語句時(shí)觸發(fā)，例如執(zhí)行創(chuàng)建、修改或者刪除的create、alter、drop語句等，這種觸發(fā)器稱為模式觸發(fā)器。

二、觸發(fā)器的創(chuàng)建

基本語法

create or replace trigger<觸發(fā)器名><觸發(fā)時(shí)間><觸發(fā)事件>on<表名>|<視圖名>|<數(shù)據(jù)庫名>|<模式名>
[for each row]
[when<條件表達(dá)式>]
begin
<pl/sql語句>
end

其中的參數(shù)說明如下：
1、or replace為可選參數(shù)，如果數(shù)據(jù)庫中已經(jīng)存在要?jiǎng)?chuàng)建的觸發(fā)器，則先把原先的觸發(fā)器刪除，再重新建立觸發(fā)器，或者說覆蓋原先的觸發(fā)器。
2、觸發(fā)時(shí)間包含before和after兩種，before指觸發(fā)器是在觸發(fā)事件發(fā)生之前執(zhí)行，after指觸發(fā)器在觸發(fā)事件發(fā)生之后執(zhí)行。
3、觸發(fā)事件，例如insert，update，delete，create，alter，drop等。
4、<pl/sql語句>是要執(zhí)行的觸發(fā)器操作。

1、DML觸發(fā)器的創(chuàng)建

dml觸發(fā)器在執(zhí)行dml語句時(shí)觸發(fā)，可以分為insert、update和delete操作，可以定義在操作之前或者之后觸發(fā)，也可以指定為行級(jí)觸發(fā)或者語句級(jí)觸發(fā)。
1、語句級(jí)dml觸發(fā)器的創(chuàng)建
它是默認(rèn)的dml觸發(fā)器的創(chuàng)建，不使用for each row子句。語句觸發(fā)器所對(duì)應(yīng)的dml語句影響表中符合條件的所有行，但觸發(fā)器只執(zhí)行一次，并且不再使用when條件語句。

1、創(chuàng)建觸發(fā)器，當(dāng)對(duì)student表進(jìn)行添加記錄、更新記錄和刪除記錄的時(shí)候，判斷是否是工作時(shí)間段，如果不是工作時(shí)間段，不允許執(zhí)行。

create or replace trigger tri_detect
	before insert or update or delete 	--定義觸發(fā)時(shí)間和觸發(fā)事件
	on student
begin
	if to_char<sysdate,'HH24:MI'> not between '09:00' and '17:00';	--給出判斷條件的工作時(shí)間
		or to_char<sysdate,'DY','nls_date_lancuage=american'> in <'sat','sun'> then
			raise_application_error<-20005,'不是正常的工作時(shí)間，不能執(zhí)行dml操作'>;	--不符合條件，給出提示信息
	end if;
end;

2、在數(shù)據(jù)表student上創(chuàng)建觸發(fā)器，當(dāng)在該表上插入、刪除或者更新的時(shí)候，記錄操作日志。

create or replace trigger tri_log
after insert or delete or update on student 	--定義觸發(fā)事件和觸發(fā)時(shí)間
declare
	var_user varchar2<20>;
	var_action varchar2<20>;	--定義局部變量
begin
	select distinct user into var_user from all_users;	--獲取用戶
	if inserting then	--判斷是否是“插入”
		insert into stu_log values<var_user,'插入',sysdate>;
	elsif deleting the	--判斷是否是“刪除”
		insert into stu_log values<var_user,'刪除',sysdate>;
	else	--判斷是否是“更新”
		insert into stu_log values<var_user,'更新',sysdate>;
	end if;
end;

2、INSTEAD OF觸發(fā)器的創(chuàng)建

在創(chuàng)建之前先創(chuàng)建一個(gè)視圖，視圖關(guān)聯(lián)數(shù)據(jù)表，student、teacher。

create view test_view as
select s.id,s.name,t.id,t.name
from student s,teacher t
where s.id=t.sid;

這個(gè)時(shí)候我們向視圖中插入數(shù)據(jù)會(huì)報(bào)錯(cuò)，我們只有通過創(chuàng)建instead of觸發(fā)器來實(shí)現(xiàn)。

1、創(chuàng)建instead of 觸發(fā)器已實(shí)現(xiàn)修改視圖test_view中的內(nèi)容

create or replace trigger tri_view
	instead of insert
	on test_view
	for each row
declare
	var_row dept%rowtype;
begin
	select * into var_row from student where id=:new.id;
	if sql%found then
		insert into student<id,name,class> values <:new.id,:new.name,:new.class>;
	end if;
exception
	when no_data_found then
		insert into student<id,name> values<:new.id,:new.name>;
		insert into teacher<id,name,sid> values<:new.id,:new.name,:new.sid>；
end;

3、系統(tǒng)觸發(fā)器

1、當(dāng)?shù)卿浕蛘咄顺龅臅r(shí)候，向日志表中添加數(shù)據(jù)

create or replace trigger user_login
	after logon on database	--登錄觸發(fā)
begin
	insert into user_log<user_name,login_date> values <ora_login_user,systimestamp>;
end user_login;

4、觸發(fā)器的禁用和啟用

在oracle數(shù)據(jù)庫中，觸發(fā)器可以修改他的狀態(tài)，使其有效或者無效，即啟用或者禁用
語法格式如下。
alter trigger 觸發(fā)器名稱 [disable | enable]
參數(shù)說明：disable是觸發(fā)器禁用參數(shù)，既使觸發(fā)器處于無效狀態(tài)；enable是觸發(fā)器啟用參數(shù)，即使觸發(fā)器處于有效狀態(tài)。
例如下面禁用觸發(fā)器
alter trigger tri_ddl disable;

5、觸發(fā)器的查看和刪除

想查看所有的觸發(fā)器信息，可以使用數(shù)據(jù)字典user_triggers，這個(gè)數(shù)據(jù)字典有很多字段可以查看所有觸發(fā)器的名稱、類型、表名、擁有者等信息。

select trigger_name,trigger_type,table_name from user_triggers;

刪除觸發(fā)器

drop trigger 觸發(fā)器名稱

三、程序包

1、程序包的創(chuàng)建

程序包由包規(guī)范和包體兩部分組成，因此程序包的創(chuàng)建也分為兩部分，分別為包規(guī)范的創(chuàng)建和包體的創(chuàng)建。
1、包規(guī)范的創(chuàng)建
包規(guī)范提供應(yīng)用程序的接口，在包規(guī)范中聲明程序包中可以使用的變量、數(shù)據(jù)類型、存儲(chǔ)過程、函數(shù)、異常等。不過函數(shù)和過程只包括原型信息，即只有頭部的聲明，不包括任何實(shí)現(xiàn)的代碼，其實(shí)現(xiàn)代碼在包體中定義。以下是語法格式。

create or replace package 程序包名稱
isias
類型聲明 | 變量聲明 | 異常聲明 | 過程聲明 | 函數(shù)聲明
end [程序包名稱]

2、包體的創(chuàng)建
在包體中包含規(guī)范中聲明的過程和函數(shù)的實(shí)現(xiàn)代碼，此外，還可以聲明其他沒有在包規(guī)范中聲明的變量、類型、過程、函數(shù)，但他們只能在包中使用，不能被其他應(yīng)用程序調(diào)用。下面就是語法格式

create or replace package body 程序包名稱
isias
[<內(nèi)部變量聲明>]
[過程體]
[函數(shù)體]
end [程序包名稱]

2、程序包的刪除

刪除包括兩步，先刪除程序包體，再刪除包規(guī)范。語法如下

drop package body 程序包名稱
drop package 程序包名稱

以上就是“Oracle觸發(fā)器和程序包如何創(chuàng)建”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會(huì)為大家更新不同的知識(shí)，如果還想學(xué)習(xí)更多的知識(shí)，請(qǐng)關(guān)注億速云行業(yè)資訊頻道。