Oracle觸發器和程序包如何創建

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

一、觸發器的概念

1、觸發器的基本概念

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

2、觸發器的分類

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

二、觸發器的創建

基本語法

create or replace trigger<觸發器名><觸發時間><觸發事件>on<表名>|<視圖名>|<數據庫名>|<模式名>
[for each row]
[when<條件表達式>]
begin
<pl/sql語句>
end

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

1、DML觸發器的創建

dml觸發器在執行dml語句時觸發，可以分為insert、update和delete操作，可以定義在操作之前或者之后觸發，也可以指定為行級觸發或者語句級觸發。
1、語句級dml觸發器的創建
它是默認的dml觸發器的創建，不使用for each row子句。語句觸發器所對應的dml語句影響表中符合條件的所有行，但觸發器只執行一次，并且不再使用when條件語句。

1、創建觸發器，當對student表進行添加記錄、更新記錄和刪除記錄的時候，判斷是否是工作時間段，如果不是工作時間段，不允許執行。

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

2、在數據表student上創建觸發器，當在該表上插入、刪除或者更新的時候，記錄操作日志。

create or replace trigger tri_log
after insert or delete or update on student 	--定義觸發事件和觸發時間
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觸發器的創建

在創建之前先創建一個視圖，視圖關聯數據表，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;

這個時候我們向視圖中插入數據會報錯，我們只有通過創建instead of觸發器來實現。

1、創建instead of 觸發器已實現修改視圖test_view中的內容

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、系統觸發器

1、當登錄或者退出的時候，向日志表中添加數據

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

4、觸發器的禁用和啟用

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

5、觸發器的查看和刪除

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

select trigger_name,trigger_type,table_name from user_triggers;

刪除觸發器

drop trigger 觸發器名稱

三、程序包

1、程序包的創建

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

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

2、包體的創建
在包體中包含規范中聲明的過程和函數的實現代碼，此外，還可以聲明其他沒有在包規范中聲明的變量、類型、過程、函數，但他們只能在包中使用，不能被其他應用程序調用。下面就是語法格式

create or replace package body 程序包名稱
isias
[<內部變量聲明>]
[過程體]
[函數體]
end [程序包名稱]

2、程序包的刪除

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

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

以上就是“Oracle觸發器和程序包如何創建”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。