存儲(chǔ)過(guò)程詳解

1、一、存儲(chǔ)過(guò)程簡(jiǎn)介存儲(chǔ)過(guò)程詳解存儲(chǔ)過(guò)程可以說(shuō)是一個(gè)記錄集吧，它是由一些T-SQL語(yǔ)句組成的代碼塊，這些T-SQL語(yǔ)句代碼像一個(gè)方法一樣實(shí)現(xiàn)一些功能（對(duì)單表或多表的增刪改查） ，然后再給這個(gè)代碼塊取 一個(gè)名字，在用到這個(gè)功能的時(shí)候調(diào)用他就行了。存儲(chǔ)過(guò)程的好處：1、由于數(shù)據(jù)庫(kù)執(zhí)行動(dòng)作時(shí)，是先編譯后執(zhí)行的。 然而存儲(chǔ)過(guò)程是一個(gè)編譯過(guò)的代碼塊，所以執(zhí)行效率要比 T-SQL語(yǔ)句高。2、一個(gè)存儲(chǔ)過(guò)程在程序在網(wǎng)絡(luò)中交互時(shí)可以替代大堆的 絡(luò)的通信量，提高通信速率。T-SQL語(yǔ)句，所以也能降低網(wǎng)3、通過(guò)存儲(chǔ)過(guò)程能夠使沒(méi)有權(quán)限的用戶在控制之下間接地存取數(shù)據(jù)庫(kù)，從而確保數(shù)據(jù) 的安全。小結(jié)： 總之存儲(chǔ)過(guò)程是好東西，在做

2、項(xiàng)目時(shí)屬于必備利器，下面介紹存儲(chǔ)過(guò)程的基本語(yǔ)法。二、存儲(chǔ)過(guò)程的語(yǔ)法和參數(shù)講解存儲(chǔ)過(guò)程的一些基本語(yǔ)法：創(chuàng)建存儲(chǔ)過(guò)程 CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 調(diào)用存儲(chǔ)過(guò)程 EXECUTEProcedure_name -存儲(chǔ)過(guò)程如果有參數(shù)，后面加參數(shù)格式為：=value，也可直接為參數(shù)

3、值 value參數(shù)名刪除存儲(chǔ)過(guò)程 drop procedure procedure_name-在存儲(chǔ)過(guò)程中能調(diào)用另外一個(gè)存儲(chǔ)過(guò)程，而不能刪除 另外一個(gè)存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程的參數(shù)：1、 procedure_name ：存儲(chǔ)過(guò)程的名稱，在前面加 #為局部臨時(shí)存儲(chǔ)過(guò)程，加 #為全局臨時(shí)存儲(chǔ)過(guò)程。2、 ;number ：精選文檔沒(méi)有最大數(shù)默認(rèn)值必須是%、使用 中，10、FOR REP LICATION :指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過(guò)程。使用 FOR REP LICATION 項(xiàng)創(chuàng)是可選的整數(shù)，用來(lái)對(duì)同名的過(guò)程分組，以便用一條DROP P ROCEDUR語(yǔ)句即可將同組的過(guò)程一起除去。例如

4、，名為orders的應(yīng)用程序使用的過(guò)程可以命名為orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc語(yǔ)句將除去整個(gè)組。如果名稱中包含定界標(biāo)識(shí) 符，則數(shù)字不應(yīng)包含在標(biāo)識(shí)符中，只應(yīng)在Procedure_name前后使用適當(dāng)?shù)亩ń绶?、parameter :存儲(chǔ)過(guò)程的參數(shù)。可以有一個(gè)或多個(gè)。用戶必須在執(zhí)行過(guò)程時(shí)提供每個(gè)所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。存儲(chǔ)過(guò)程最多可以有 2.100個(gè)參數(shù)。 使用 符號(hào)作為 第一個(gè)字符來(lái)指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識(shí)符的規(guī)則。每個(gè)過(guò)程的參數(shù)僅用于該過(guò)程本身；相同的參數(shù)名稱可以用在其它過(guò)程中。默認(rèn)情況下，參

5、數(shù)只能代替常量， 而不能用于代替表名、列名或其它數(shù)據(jù)庫(kù)對(duì)象的名稱。有關(guān)更多信息，請(qǐng)參見(jiàn)EXECUTE 4、data_type :參數(shù)的數(shù)據(jù)類(lèi)型。所有數(shù)據(jù)類(lèi)型（包括text、ntext和image）均可以用作存儲(chǔ)過(guò)程的參數(shù)。不過(guò)，cursor數(shù)據(jù)類(lèi)型只能用于 OUT PUT參數(shù)。如果指定的數(shù)據(jù)類(lèi)型為cursor，也必須同時(shí)指定 VARYING和OUT PUT關(guān)鍵字。有關(guān) SQL Server提供的數(shù)據(jù)類(lèi)型及其語(yǔ)法的 更多信息，請(qǐng)參見(jiàn)數(shù)據(jù)類(lèi)型。說(shuō)明對(duì)于可以是cursor數(shù)據(jù)類(lèi)型的輸出參數(shù)，目的限制。5、VARYING:。僅適用于游指定作為輸出參數(shù)支持的結(jié)果集（由存儲(chǔ)過(guò)程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化） 標(biāo)

6、參數(shù)。6、default :參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行過(guò)程。常量或NULL。如果過(guò)程將對(duì)該參數(shù)使用LIKE關(guān)鍵字，那么默認(rèn)值中可以包含通配符（_、和 F）。7、OUT PUT :表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給EXECUTE。使用 OUT PUT參數(shù)可將信息返回給調(diào)用過(guò)程。 Text、n text和image參數(shù)可用作 OUT PUT參數(shù)。使用 OUT PUT關(guān)鍵 字的輸出參數(shù)可以是游標(biāo)占位符。8、RECOMPILE:表明SQL Server不會(huì)緩存該過(guò)程的計(jì)劃，該過(guò)程將在運(yùn)行時(shí)重新編譯。在使用非典型 值或臨時(shí)值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計(jì)劃時(shí)，請(qǐng)使

7、用RECO MPILE選項(xiàng)。9、ENCRYPTION: 表示 SQL Server加密syscomments表中包含 CREATE PROCEDURE句文本的條目。ENCRYPTION可防止將過(guò)程作為 SQL Server復(fù)制的一部分發(fā)布。說(shuō)明 在升級(jí)過(guò)程SQL Server利用存儲(chǔ)在 syscomments中的加密注釋來(lái)重新創(chuàng)建加密過(guò)程。WITH建的存儲(chǔ)過(guò)程可用作存儲(chǔ)過(guò)程篩選，且只能在復(fù)制過(guò)程中執(zhí)行。本選項(xiàng)不能和RECOM PILE選項(xiàng)一起使用。11、AS :指定過(guò)程要執(zhí)行的操作。12、sql_statement :過(guò)程中要包含的任意數(shù)目和類(lèi)型的Tran sact-SQL語(yǔ)句。但有一些限制。

8、小結(jié)：看過(guò)這些基本語(yǔ)法后，下面我就根據(jù)語(yǔ)法創(chuàng)建各式的存儲(chǔ)過(guò)程。三、創(chuàng)建存儲(chǔ)過(guò)程UserIDUserNameUserAcco untRegisterIPP assWordRegisterTime12662012-12-31618552013-01-01519112013-01-01120222013-01-01221332013-01-01322442013-01-01423552013-01-01525772013-01-01726882013-01-018NULLNULLNULLNULLNULL針對(duì)上面的表，我使用存儲(chǔ)過(guò)程對(duì)它做一些操作:1. 只返回單一記錄集的存儲(chǔ)過(guò)程創(chuàng)建名為GetUser

9、Account的存儲(chǔ)過(guò)程create Procedure GetUserAcco unt asselect * from UserAcco unt go執(zhí)行上面的存儲(chǔ)過(guò)程exec GetUserAcco unt 結(jié)果：相當(dāng)于運(yùn)行 select * from UserAccount這行代碼，結(jié)果為整個(gè)表的數(shù)據(jù)。2. 沒(méi)有輸入輸出的存儲(chǔ)過(guò)程創(chuàng)建名為GetUserAccount的存儲(chǔ)過(guò)程create Procedure inU serAcco unt asin sert into UserAcco un t (UserName, PassWord,RegisterTime,Registerl P)v

10、alues(9,9,2013-01-02,9)go執(zhí)行上面的存儲(chǔ)過(guò)程exec inU serAcco unt結(jié)果：相當(dāng)于運(yùn)行in sert into UserAccou nt (UserName, PassWord,RegisterTime,Registerl P)values(9,9,2013-01-02,9)這行代碼。3. 有返回值的存儲(chǔ)過(guò)程創(chuàng)建名為GetUserAccount的存儲(chǔ)過(guò)程create Procedure inU serAcco un tRe asin sert into UserAcco un t (UserName, PassWord,RegisterTime,Regis

11、terl P) values(10,10,2013-01-02,10)return rowco untgo執(zhí)行上面的存儲(chǔ)過(guò)程exec inU serAcco un tRe解釋?zhuān)哼@里的rowcount為執(zhí)行存儲(chǔ)過(guò)程影響的行數(shù)，執(zhí)行的結(jié)果是不僅插入了一條數(shù)據(jù)，還返回了一個(gè)值即 return value =1,這個(gè)可以在程序中獲取，稍后在c#調(diào)用存儲(chǔ)過(guò)程中會(huì)有說(shuō)到。4. 有輸入?yún)?shù)和輸出參數(shù)的存儲(chǔ)過(guò)程倉(cāng)y建名為 GetUserAccount的存儲(chǔ)過(guò)程 create Procedure GetUserAcco un tReUserName nchar(20),UserID i nt out putas

12、if(UserName5)select UserlD=COUNT(*) from UserAcco unt where UserID25 elseset UserID=1000go執(zhí)行上面的存儲(chǔ)過(guò)程exec GetUserAccou ntRe 7, null解釋?zhuān)篣serName為輸入?yún)?shù)， UserlD為輸出參數(shù)。運(yùn)行結(jié)果為 userlD為COOUT ( *)即=1。5. 同時(shí)具有返回值、輸入?yún)?shù)、輸出參數(shù)的存儲(chǔ)過(guò)程創(chuàng)建名為GetUserAccount的存儲(chǔ)過(guò)程create Procedure GetUserAcco un tRe1 UserName nchar(20),UserID i n

13、t out putas if(UserName5)select UserlD=COUNT(*) from UserAccou nt where UserID25 elseset UserlD=1000retur n rowco unt go執(zhí)行上面的存儲(chǔ)過(guò)程.exec GetUserAccou ntRel 7, null 結(jié)果：userlD 為 COOUT( * )即=1，Retun Value=1。6. 同時(shí)返回參數(shù)和記錄集的存儲(chǔ)過(guò)程創(chuàng)建名為GetUserAccount的存儲(chǔ)過(guò)程create Procedure GetUserAcco un tRe2 UserName n char(20),

14、 UserID i nt out put asif(UserName5)select UserID=COUNT(*) from UserAcco unt where UserID25 elseset UserID=1000select * from UserAcco untretur n rowco unt go執(zhí)行上面的存儲(chǔ)過(guò)程.exec GetUserAccou ntRe2 7, null結(jié)果：返回執(zhí)行 select * from UserAccount這句代碼的結(jié)果集，同時(shí) userlD為COOUT ( *) 即=1, Retun Value=9。7. 返回多個(gè)記錄集的存儲(chǔ)過(guò)程創(chuàng)建名為GetUserAccount的存儲(chǔ)過(guò)程create Procedure GetUserAcco un tRe3 asselect * from UserAcco untselect * from