第十章 存儲過程

1、第九章存儲過程（StedProcedure）當使用SQLServer創(chuàng)建應用時，TRANSACTSQL語言是應用程序與SQLServer數(shù)據(jù)庫之間的主要編程接口。使用TRANSACTSQL語言進行程序設計時，有兩種方式：一種方式是在應用程序中直接使用TSQL語句向SQLServer發(fā)送命令；另一種方式就是使用存儲過程。1、存儲過程的概述11存儲過程的概念存儲過程是一種數(shù)據(jù)庫對象，由一組預編譯的T_SQL語句組成，這些語句在一個名稱下存儲

2、，并作為一個單元進行處理。存儲過程類似于其他編程語言中的函數(shù)或過程：能夠使用傳遞給它的參數(shù)，能夠調(diào)用其它存儲過程甚至本身，能夠返回一個狀態(tài)碼來表示是否成功執(zhí)行。SQLServer中有三類存儲過程：系統(tǒng)存儲過程（sp_為前綴）、用戶自定義存儲過程和擴展存儲過程（xp_為前綴，擴展了SQLServer的功能，使得用戶能調(diào)用外部例程(自已編寫的程序或系統(tǒng)提供的命令)）。12存儲過程的功能特點在SQLServer中，存儲過程是一種非常強有力的數(shù)

3、據(jù)庫對象，利用它能夠顯著提高應用程序的性能。[1]功能?接收輸入?yún)?shù)并以輸出參數(shù)的形式為調(diào)用過程或批處理返回多個值?包含對數(shù)據(jù)庫操作的多條語句，可以調(diào)用其他存儲過程?為調(diào)用存儲過程或批處理返回一個狀態(tài)值，以表示執(zhí)行狀態(tài)[2]存儲過程的優(yōu)點?模塊化編程。一旦創(chuàng)建了一個存儲過程，就可以在應用程序中多次調(diào)用它，而且由于存儲過程獨立于應用程序，所以可以在不影響應用程序源代碼的前提下修改它。?加快執(zhí)行速度。在創(chuàng)建一個存儲過程時，SQLServer

4、要對它進行分析和優(yōu)化，以獲得最好的執(zhí)行性能；當一個存儲過程被首次執(zhí)行后，它就會駐留內(nèi)存，當再次調(diào)用時，就不必再加載了，從而提高了整個系統(tǒng)的執(zhí)行速度。?減少網(wǎng)絡通信量。使用存儲過程，客戶端的應用程序可以通過一條簡單的執(zhí)行命令來執(zhí)行存放在服務器端的存儲過程，而不必傳輸成百上千行的SQL語句代碼，因此可以大大減少網(wǎng)絡阻塞。?提供安全機制。可以通過存儲過程來間接將某些權限賦給用戶。?復雜業(yè)務規(guī)則和約束的一致性實現(xiàn)。存儲過程足夠強大，甚至能夠?qū)崿F(xiàn)

5、最復雜的業(yè)務規(guī)則，這是因為存儲過程可以同時合并過程語句和面向集合的語句。13存儲過程的數(shù)據(jù)返回方式存儲過程可以通過四種方式把數(shù)據(jù)返回到調(diào)用處：?輸出參數(shù)。既可以返回數(shù)據(jù)(數(shù)值型或字符值等)，也可以返回游標變量（游標是可以逐行檢索的結(jié)果集）。?反回值。始終是整型值。?結(jié)果集。這些語句包含在該存儲過程內(nèi)或該存儲過程所調(diào)用的任何其它存儲過程內(nèi)。?全局游標。可從存儲過程外引用的全局游標。14使用存儲過程管理數(shù)據(jù)在三層結(jié)構MIS的程序設計中，數(shù)據(jù)

6、管理層對數(shù)據(jù)的操作完全由存儲過程來實現(xiàn)，特別是Web應用程序。數(shù)據(jù)管理層中對數(shù)據(jù)的查找、插入、更新和刪除等操作都由相應的存（二）語法說明23例例1：查找指定日期后簽定的訂單（創(chuàng)建有返回結(jié)果集的存儲過程）createprocedurep1@datexdatetimeasfromderswherederdate=@datex[executep1‘199711’]例2：求產(chǎn)品的平均單價（創(chuàng)建有返回參數(shù)的存儲過程）createprocedure

7、[dbo].[p2]@avgpricemoneyoutputas@avgprice=avg(unitprice)fromproducts[declare@xmoneyexecutep2@xoutput@x]例3：判斷是否有1997年2月20號簽定的訂單，有返回0，沒有返回1（創(chuàng)建有返回值的存儲過程）createprocedure[dbo].[p3]@datexdatetimeasifexists(fromderswherederdate

8、=@datex)return0elsereturn1[declare@xintexecute@x=p31997220@x]例4：根據(jù)最后姓名模糊查找職員信息（創(chuàng)建帶有缺省值參數(shù)的存儲過程）createprocedure[dbo].[p4]@namexnvar(20)=D%asfromemployeeswherelastnamelike@namex%[executep4][executep4‘B’]例5：建立存儲過程更一般的方法（SQLS