觸發器一 觸發器介紹
觸發器是一種特殊的存儲過程，它在插入，刪除或修改特定表中
的數據時觸發執行，它比數據庫本身標準的功能有更精細和更復雜的
數據控制能力。數據庫觸發器有以下的作用：
* 安全性。可以基于數據庫的值使用戶具有操作數據庫的某種權利。
# 可以基于時間限制用戶的操作，例如不允許下班后和節假日
修改數據庫數據。
# 可以基于數據庫中的數據限制用戶的操作，例如不允許股票
的價格的升幅一次超過10%。
* 審計。可以跟蹤用戶對數據庫的操作。
# 審計用戶操作數據庫的語句。
# 把用戶對數據庫的更新寫入審計表。
* 實現復雜的數據完整性規則。
# 實現非標準的數據完整性檢查和約束。觸發器可產生比規則
更為復雜的限制。與規則不同，觸發器可以引用列或數據庫對
象。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨。
# 提供可變的缺省值。
* 實現復雜的非標準的數據庫相關完整性規則。觸發器可以對數
據庫中相關的表進行連環更新。例如，在auths表author_code列上的
刪除觸發器可導致相應刪除在其它表中的與之匹配的行。
# 在修改或刪除時級聯修改或刪除其它表中的與之匹配的行。
# 在修改或刪除時把其它表中的與之匹配的行設成NULL值。
# 在修改或刪除時把其它表中的與之匹配的行級聯設成缺省值。
# 觸發器能夠拒絕或回退那些破壞相關完整性的變化，取消試
圖進行數據更新的事務。當插入一個與其主健不匹配的外部鍵
時，這種觸發器會起作用。例如，可以在books.author_code
列上生成一個插入觸發器，如果新值與auths.author_code列
中的某值不匹配時，插入被回退。
* 同步實時地復制表中的數據。
* 自動計算數據值，如果數據的值達到了一定的要求，則進行特
定的處理。例如，如果公司的帳號上的資金低于5萬元則立即給財務人
員發送警告數據。
ORACLE與SYBASE數據庫的觸發器有一定的區別，下面將分別講述
這兩種數據庫觸發器的作用和寫法。
二 ORACLE 觸發器
ORACLE產生數據庫觸發器的語法為：
create [or replace] trigger 觸發器名 觸發時間 觸發事件
on 表名
[for each row]
pl/sql 語句
其中：
觸發器名：觸發器對象的名稱。由于觸發器是數據庫自動執行
的，因此該名稱只是一個名稱，沒有實質的用途。
觸發時間：指明觸發器何時執行，該值可取：
before---表示在數據庫動作之前觸發器執行；
after---表示在數據庫動作之后出發器執行。
觸發事件：指明哪些數據庫動作會觸發此觸發器：
insert：數據庫插入會觸發此觸發器；
update：數據庫修改會觸發此觸發器；
delete：數據庫刪除會觸發此觸發器。
表 名：數據庫觸發器所在的表。
for each row：對表的每一行觸發器執行一次。如果沒有這一
選項，則只對整個表執行一次。
舉例：下面的觸發器在更新表auths之前觸發，目的是不允許在
周末修改表：
create trigger auth_secure
before insert or update or delete //對整表更新前觸發
on auths
begin
if(to_char(sysdate,'DY')='SUN'
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');
end if;
end
三 SYBASE數據庫觸發器
SYBASE數據庫觸發器的作用與ORACLE非常類似，僅有較小的差異。
SYBASE產生觸發器的語法為：
CREATE TRIGGER 觸發器名
ON 表名
FOR INSERT，UPDATE，DELETE
AS
SQL_statement |
FOR INSERT，UPDATE
AS
IF UPDATE(column_name) [AND|OR UPDATE(column_name)]...
SQL_statements
上面FOR子句用來指定在觸發器上的哪些數據更新命令可激活該
觸發器。IF UPDATE子句檢查對指定列的操作類型，在IF UPDATE子句
中可指定多個列。
與ORACLE不同，對于每條SQL語句，觸發器只執行一次。觸發器
在數據更新語句完成以后立即執行。觸發器和啟動它的語句被當作一
個事務處理，事務可以在觸發器中回退。
下面舉例說明SYBASE觸發器的寫法。
create trigger forinsert_books
on books
for insert
as
if(select count(*) from auths,inserted
where auths.author_code=insert.author_code)!=@@rowcount
begin
rollback transaction
print "books 表中 author_code 列的值在auths 表中不存在。"
end
----------------------------------------------------------------------------------------------------------
存儲過程一 存儲過程介紹
存儲過程是由流控制和SQL語句書寫的過程，這個過程經編譯和優化
后存儲在數據庫服務器中，使用時只要調用即可。在ORACLE中，若干個
有聯系的過程可以組合在一起構成程序包。
使用存儲過程有以下的優點：
* 存儲過程的能力大大增強了SQL語言的功能和靈活性。存儲過程可
以用流控制語句編寫，有很強的靈活性，可以完成復雜的判斷和較復雜的
運算。
* 可保證數據的安全性和完整性。
# 通過存儲過程可以使沒有權限的用戶在控制之下間接地存取數據
庫，從而保證數據的安全。
# 通過存儲過程可以使相關的動作在一起發生，從而可以維護數據
庫的完整性。
* 再運行存儲過程前，數據庫已對其進行了語法和句法分析，并給出
了優化執行方案。這種已經編譯好的過程可極大地改善SQL語句的性能。
由于執行SQL語句的大部分工作已經完成，所以存儲過程能以極快的速度執
行。
* 可以降低網絡的通信量。
* 使體現企業規則的運算程序放入數據庫服務器中，以便：
# 集中控制。
# 當企業規則發生變化時在服務器中改變存儲過程即可，無須修改
任何應用程序。企業規則的特點是要經常變化，如果把體現企業規則的運
算程序放入應用程序中，則當企業規則發生變化時，就需要修改應用程序
工作量非常之大（修改、發行和安裝應用程序）。如果把體現企業規則的
運算放入存儲過程中，則當企業規則發生變化時，只要修改存儲過程就可
以了，應用程序無須任何變化。
不同數據庫存儲過程的寫法不一，在后面的講座中將分別介紹ORACLE
和SYBASE存儲過程的用法。
二 ORACLE 的存儲過程
ORACLE 創建存儲過程的語法為：
create [or replace] procedure 過程名
參數1 [in|out|in out] 數據類型
[,參數2 [in|out|in out] 數據類型]...
{is|as} pl/sql 語句
下面舉例說明ORACLE數據庫存儲過程的寫法和用法。
可以建立一個存儲過程，每當用戶修改數據庫的重要數據時，即把
用戶的用戶名、日期和操作類型記錄下來：
create procedure update_log is
begin
insert into update_log_tab(use_name,update_date,operation)
values(user,sysdate,'update'
end;
可以在恰當的位置調用這個存儲過程來記錄用戶對表的修改。例如下面在
表sal_comm上建立一個修改觸發器，每當用戶修改此表后，用戶的名稱、修改
時間和操作即被記錄在了表update_log_tab中：
create trigger audit_update
after update on sal_comm
for each row
begin
update_log
end
三 Sybase的存儲過程
盡管Sybase存儲過程的功能和寫法與ORACLE類似，但他們之間還是
有一定的差別。下面講述SYBASE的存儲過程。
SYBASE可以用CREATE PROCedure命令生成存儲過程：
CREATE PROCedure 存儲過程名 [；number]
[[(] @parameter_name datatype [=default] [OUTput]
[, @parameter_name datatype [=default] [OUTput]]...[)]]
[WITH RECOMPILE]
AS SQL_statements
下面是一個查詢作者編碼、名稱和生日的存儲過程：
create proc p_auths @author_code varchar(10)
as
select author_code, name, birthdate
from auths
where author_code=@author_code
下面執行過程p_auths:
p_auths @author_code=A00001
在CREATE PROC語句中，可以為參數賦缺省值，該值可以是任何常量。
當用戶不提供參數值時，該值便作為參數值提供給過程。
Sybase的存儲過程是集中存儲在SQL Server中的預先定義且已經編譯好的事務。存儲過程由SQL語句和流程控制語句組成。
它的功能包括:接受參數;調用另一過程;返回一個狀態值給調用過程或批處理,指示調用成功或失敗;返回若干個參數值給調
用過程或批處理,為調用者提供動態結果;在遠程SQL Server中運行等。
存儲過程的性能特
1.存儲過程是預編譯過的,這就意味著它與普通的SQL語句或批處理的SQL語句不同。當首次運行一個存儲過程時,SQL Server的查詢處理器對其進行分析,在排除了語法錯誤之后形成存儲在系統中的可執行方案。由于查詢處理的大部分工作已經完成,所以存儲過程執行速度很快。
2.存儲過程和待處理的數據都放在同一臺運行SQL Server的計算機上,使用存儲過程查詢當地的數據,效率自然很高。
3.存儲過程一般多由Client端通過存儲過程的名字進行調用,即跨網傳送的只是存儲過程的名字及少量的參數(如果有的話),而不是構成存儲過程的許多SQL語句,因此可以減少網絡傳輸量,加快系統響應速度。
4.存儲過程還有著如同C語言子函數那樣的被調用和返回值的方便特性。所以,存儲過程大大增強了SQL語言的功能、效率和靈活性。掌握和應用好存儲過程,對進一步發揮Sybase數據庫系統的強大功能有著重要的意義。
存儲過程的語法規則
建立存儲過程的語法規則為:
CREATE PROCedure[owner.]procedurename[;number]
[[(]@parameter_name datatype[=default][OUTput]
[,@parameter_name datatype[=default][OUTput]]...[)]]
[WITH RECOMPILE]
AS SQL_statements
使用存儲過程的語法規則為:
[EXECute][@return-status=]
[[[server.]database.]owner.]procedurename[;number]
[[@parameter_name=]value|[@parameter_name=]@varialbe[OUT put]
[,[@parameter_name=]value|[@parameter_name=]@variable[OU Tput]...]]
[WITH RECOMPILE]
下面簡要介紹這兩個命令的常用選項以及建立和使用存儲過程的要點,關于選項的更為詳細的說明請參考有關手冊。
1.[[[server.]database.]owner.]procedure_name:存儲過程的名字。
2.@parameter_name datatype[=default][OUTput]:形式參數(形參)的名稱、類型。dfault是賦予的缺省值(可選),OUTput指定本參數為輸出參數(可選)。形參是存儲過程中的自變量,可以有多個,名字必須以@打頭,最長30個字符。
3.SQL_statements:定義存儲過程功能的SQL語句。
4.@return_status:接受存儲過程返回狀態值的變量。
5.[@parameter_name=]value:實際參數(實參),@parameter_name 為實參的名稱(可選)。如果某個實參以@parameter_name=value提供, 那么隨后的實參也都要采用這一形式提供。
6.[@parameter_name=]@varialbe[OUTput]:將變量@varialbe中的值作為實參傳遞給形參@parameter_name(可選),如果變量@varialb e是用來接受返回的參數值,則選項OUTput不可缺少。
存儲過程的建立和使用
我們將通過幾個例子進行介紹。
假設有一個用下述語句生成的技能工資表RS_LS_GZ_JiNeng:
create table RS_LS_GZ_JiNeng /*技能工資表*/
(GeRen_id char(4), /*個人代碼*/
RiQi smalldatetime, /*執行日期*/
YuanYin_id char(1) null, /*變動原因代碼*/
JinE smallmoney) /*技能工資金額*/
該表存儲著某單位員工多年來技能工資的歷史檔案。
例1.如果要查詢全體員工的技能工資變動歷史,則可先建立一個存儲過程p_RsGz_JiNeng_All:
create procedure p_RsGz_JiNeng_All
as
select *
from RS_LS_GZ_JiNeng
order by GeRenid,RiQi
然后用批處理語句調用存儲過程p_RsGz_JiNeng_All進行查詢:
execute p_RsGz_JiNeng_All
本例只顯示查詢到的數據,無輸入、輸出參量,是最簡單的一個存儲過程。
例2.如果要查詢某人技能工資的變動歷史,可建立另一個存儲過程p_RsGz_JiNeng:
create procedure p_RsGz_JiNeng
@c_GeRenId char(4)
as
select *from RS_LS_GZ_JiNeng
where GeRen_id=@c_GeRenId
order by RiQi
之后用批處理語句調用存儲過程p_Rs_Gz_JiNeng進行查詢:
declare @GeRenId char(4)
select @GeRenId="0135" /*設要查詢員工的個人代碼為"0135" */
execute p_RsGz_JeNeng @c_GeRenId=@GeRenId
存儲過程p_RsGz_JiNeng中定義了一個形參@c_GeRenId,是字符型變量。在調用該過程的批處理中,既可以用具體的值也可以用變量作為實參。用變量作實參(如本例)時,必須用delare語句加以說明。注意,在批處理的調用過程語句@c_GeRenId=@GeRenId中的@c_GeRenId是存儲過程p_RsGz_JiNeng中的形參名,不是批處理中的變量,所以不能將它列入d eclare語句的變量單中。
例3.如果要計算當月工資,就必須從工資歷史中查出員工距離當前最近的一次技能工資變動的結果:
create procedure p_RsGz_JiNeng_Slt
(@c_GeRenId char(4),@sm_JinE smallmoney output)
as
select @sm_JinE=JinE
from RS_LS_GZ_JiNeng
where RiQi=(select max(RiQi)
from RS_LS_GZ_JiNeng
where GeRenid=@c-GeRenId)/*找出歷史記錄中距離當前最近的日期*/
調用存儲過程p_RsGz_JiNeng_Slt進行查詢:
declare @GeRenId char(4),@JinE smallmoney
select @GeRenid="0135"/*設要查詢員工的個人代碼為"0135"*/
select @JinE=0
execute p_RsGz_JiNeng_slt @c_GeRenId=@GeRenId,@sm_JinE=@ JinE output
這里,變量@JinE用來存儲過程形參@sm_JinE傳回的金額。在調用過程語句中,@sm_JiE = @JinE output中的output不可省略。否則, 變量@JinE將得不到形參傳回的數值而始終為零(等于初值)。
例4.查到了個人代碼為"0135"員工的技能工資就顯示其歷史紀錄,查不到則顯示一條出錯信息。
create procedure p_RsGz_JiNeng_Rtn
@c_GeRenId char(4)
as
declare @ErrCode smallint
select @ErrCode=0
if exists(select* from RS-LS-GZ-JiNeng
where GeRenid=@c-GeRenId)
begin
select *
from RS_LS_GZ_JiNeng
whrer GeRen_id=@c_GeRenId
order by RiQi
return @ErrCode
end
esle
begin
select @ErrCode=1
return @ErrCode
end
調用存儲過程p_RsGz_JiNeng_Rtn:
declare @GeRenId char(4),@RtnCode smallint
select @GeRenId="0135"
select @RtnCode=0
execute @RtnCode=p_RsGz_JiNeng_Rtn @c_GeRenId=@GeRenId
if @RtnCode=1
print"No this one!"
存儲過程p_RsGz_JiNeng_Rtn向調用者返回一個存儲在變量@ErrC ode里的值,這個值被稱為狀態值,它向調用者反映存儲過程執行的成敗狀態。在本例中,如果查不到指定員工技能工資的任何記錄時,就認為"查無此人",返回出錯狀態值1。否則,返回成功狀態值0。
調用過程的批處理語句使用變量@RtnCode存儲返回的狀態值,一旦檢出存儲過程p_RsG_ JiNeng_Rtn返回了錯誤標志(@RtnCode=1),就顯示一條信息"No this one!"。
小結
上述四個例子簡要介紹了存儲過程常用的幾種形式,從中我們已經可以領略到它的編程特色以及使用上的靈活性和方便性。
雖然上述例子在調用存儲過程時都是用SQL的批處理語句實現的, 但并不意味著這是唯一的方法。例如在存儲過程中調用存儲過程(即所謂過程嵌套)的現象就很常見。另外,在其它Sybase數據庫開發系統(如PowerBuilder)的script語句中調用Sybase的存儲過程也非常普遍。