SQL Server 7.0 入門（五）
2024-08-31 00:48:50
供稿：網友
使用sql server開發應用程序
編寫存儲過程與觸發器
存儲過程和觸發器是由用戶創建的、駐留在服務器的一組transact sql查詢語句。觸發器是系統在特定條件下執行的。存儲過程能夠改善應用程序的性能。當客戶程序需要訪問數據時，一般要經過5個步驟才能訪問到數據：
1） 查詢語句被發送到服務器。
2） 服務器編譯sql代碼。
3） 優化產生查詢的執行計劃。
4） 數據引擎執行查詢。
5） 結果發回客戶程序。
存儲過程是在創建時編譯的，當存儲過程第一次執行時，sql server產生查詢執行計劃并將其存儲進來，以利于將來使用。當通過存儲過程發出一個請求時，上述的第2和第3步就沒有了，這能大大改善系統的性能。即使在第1步上也能提高性能。因為此時發送到服務器的語句只是一條存儲過程的execute語句，而不是龐大的、復雜的查詢。這種特性能降低網絡的流量。
除了性能方面的改善外，存儲過程還提供了方便地集中維護應用程序的功能。如果將查詢嵌入到應用程序中。而又需要對查詢進行改變，則應用程序需要重新編譯，并重新發布到所有的客戶端。而在存儲過程中，修改對用戶而言是透明的，它只需要在服務器上重新編譯存儲過程。
存儲過程還能提供安全機制，盡管用戶可能無權執行存儲過程中的命令，但它卻可能有權執行存儲過程本身。有時候，系統管理員不會給用戶以數據修改（update、insert和delete）的權力。創建的存儲過程卻能進行這些操作。當然用戶需要擁有執行該存儲過程的權力。
建立存儲過程
存儲過程可以達到以下目的：
· 帶參數。
· 返回狀態值。
· 調用其它存儲過程。
· 在遠程服務器上執行。
存儲過程在“sysobjects”系統表中有一個表項，其類型為“p”。存儲過程的文本存儲在“syscomments”系統表中。創建存儲過程需要使用transact sql命令create procedure。
例如：
use pubs
go
create procedure ap_getauthorsforpublisher
as
select a.au_lname,a.au_fname
from authors a, titleauthor ta, titles t, publishers p
where a.au_id = ta.au_id
and ta.title_id = t.title_id
and t.pub_id = p.pub_id
and p.pub_name = ’new moon books’
go
create procedure語句的語法如下：
create proc[dure] procedure_name [;number]
[@parameter_name ][output] [,_n] ]
[with {recompile | encryption}]
[for replication]
as
number是用來對相同名字的過程進行分組的整數。分組是將所有的過程通過drop procedure語句組合到一個分組中。
@parameter_name指定參數的名稱。
recompile表示每次執行過程時都要進行編譯。
encryption表示過程的文本在“syscomments”表中要加密。
for replication表示過程不能在提交服務器上執行。
將參數傳遞給存儲過程
存儲過程能夠接受參數。
注意：過程的參數也可以是用戶定義的數據類型。
給參數一個缺省值
用戶還可以為存儲過程中的參數定義缺省值。當在執行時沒有提供所需的參數時，系統就使用缺省值作為參數。如果既沒有定義缺省值，又沒有在執行時提供參數，則sql server就會返回一個錯誤。在存儲過程中定義缺省值，并使用一些邏輯檢測是否指定了參數從而采取相應的行動，這是一種很好的習慣。
例如：
use pubs
go
create procedure ap_getauthorsforpublisher
@publishername varchar(40) = ‘new moon books’
as
select a.au_lname,a.au_fname
from authors a, titleauthor ta, titles t, publishers p
where a.au_id = ta.au_id
and ta.title_id = t.title_id
and t.pub_id = p.pub_id
and p.pub_name = @publishername
go
