第十章 存儲(chǔ)過程

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

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

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

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

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

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

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

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