SQL存儲(chǔ)過程教程II

1、說明 SQL Server 是將空字符串解釋為單個(gè)空格還是解釋為真正的空字符串，由兼 容級(jí)別設(shè)置控制。如果兼容級(jí)別小于或等于65 ，SQL Server 就將空字符串解釋為單個(gè)空格。如果兼容級(jí)別等于 70 ，則 SQL Server 將空字符串解釋為空字符串。獲得有關(guān)存儲(chǔ)過程的信息 若要顯示用來創(chuàng)建過程的文本，請(qǐng)?jiān)谶^程所在的數(shù)據(jù)庫中執(zhí)行 sp_helptext ， 并使用過程名作為參數(shù)。說明 使用 ENCRYPTION 選項(xiàng)創(chuàng)建的存儲(chǔ)過程不能使用 sp_helptext 查看若要顯示有關(guān)過程引用的對(duì)象的報(bào)表，請(qǐng)使用 sp_depends 若要為過程重命名，請(qǐng)使用 sp_rename 。引用對(duì)象

2、SQL Server 允許創(chuàng)建的存儲(chǔ)過程引用尚不存在的對(duì)象。在創(chuàng)建時(shí)，只進(jìn)行 語法檢查。 執(zhí)行時(shí)，如果高速緩存中尚無有效的計(jì)劃， 則編譯存儲(chǔ)過程以生成執(zhí) 行計(jì)劃。 只有在編譯過程中才解析存儲(chǔ)過程中引用的所有對(duì)象。 因此，如果語法 正確的存儲(chǔ)過程引用了不存在的對(duì)象，則仍可以成功創(chuàng)建，但在運(yùn)行時(shí)將失敗， 因?yàn)樗玫膶?duì)象不存在。延遲名稱解析和兼容級(jí)別SQL Server 允許 Transact-SQL 存儲(chǔ)過程在創(chuàng)建時(shí)引用不存在的表。這種 能力稱為延遲名稱解析。 不過，如果 Transact-SQL 存儲(chǔ)過程引用了該存儲(chǔ)過程 中定義的表，而兼容級(jí)別設(shè)置(通過執(zhí)行 sp_dbcmptlevel 來設(shè)

3、置)為 65 ，則 在創(chuàng)建時(shí)會(huì)發(fā)出警告信息。 而如果在運(yùn)行時(shí)所引用的表不存在， 將返回錯(cuò)誤信息。執(zhí)行存儲(chǔ)過程成功執(zhí)行CREATBPROCEDURS句后，過程名稱將存儲(chǔ)在sysobjects 系統(tǒng) 表中，而 CREATE PROCEDURE的文本將存儲(chǔ)在 syscomments中。第一次執(zhí) 行時(shí)，將編譯該過程以確定檢索數(shù)據(jù)的最佳訪問計(jì)劃。使用 cursor 數(shù)據(jù)類型的參數(shù)存儲(chǔ)過程只能將cursor數(shù)據(jù)類型用于OUTPUT參數(shù)。如果為某個(gè)參數(shù)指定 了 cursor數(shù)據(jù)類型，也必須指定 VARYING和OUTPUT參數(shù)。如果為某個(gè)參數(shù) 指定了 VARYING關(guān)鍵字，則數(shù)據(jù)類型必須是cursor，并且

4、必須指定OUTPUT關(guān) 鍵字。說明 cursor數(shù)據(jù)類型不能通過數(shù)據(jù)庫 API (例如OLE DB ODBC ADO和 DB-Library)綁定到應(yīng)用程序變量上。因?yàn)楸仨毾冉壎∣UTPUT參數(shù)，應(yīng)用程序 才可以執(zhí)行存儲(chǔ)過程，所以帶有 cursor OUTPUT 參數(shù)的存儲(chǔ)過程不能通過數(shù)據(jù)庫 API 調(diào)用。只有將 cursor OUTPUT 變量賦值給 Transact-SQL 局部 cursor 變量時(shí)，才可以通過 Transact-SQL 批處理、存儲(chǔ)過程或觸發(fā)器調(diào)用這些過程。Cursor 輸出參數(shù)在執(zhí)行過程時(shí)，以下規(guī)則適用于 cursor 輸出參數(shù)：對(duì)于只進(jìn)游標(biāo)，游標(biāo)的結(jié)果集中返回的行只

5、是那些存儲(chǔ)過程執(zhí)行結(jié)束時(shí)處于 或超出游標(biāo)位置的行，例如：在過程中的名為 RS 的 100 行結(jié)果集上打開一個(gè)非滾動(dòng)游標(biāo)。過程提取結(jié)果集 RS 的頭 5 行過程返回到其調(diào)用者返回到調(diào)用者的結(jié)果集 RS 由 RS 的第 6 到 100 行組成，調(diào)用者中的游標(biāo) 處于 RS 的第一行之前。對(duì)于只進(jìn)游標(biāo)， 如果存儲(chǔ)過程完成后， 游標(biāo)位于第一行的前面， 則整個(gè)結(jié)果 集將返回給調(diào)用批處理、 存儲(chǔ)過程或觸發(fā)器。 返回時(shí)，游標(biāo)將位于第一行的前面。對(duì)于只進(jìn)游標(biāo)， 如果存儲(chǔ)過程完成后， 游標(biāo)的位置超出最后一行的結(jié)尾， 則 為調(diào)用批處理、存儲(chǔ)過程或觸發(fā)器返回空結(jié)果集。說明 空結(jié)果集與空值不同。則將空值傳遞回調(diào)用批處理

6、、 存儲(chǔ)過 但該游標(biāo)從未打開過， 也會(huì)出現(xiàn)這種對(duì)于可滾動(dòng)游標(biāo)， 在存儲(chǔ)過程執(zhí)行結(jié)束時(shí)， 結(jié)果集中的所有行均會(huì)返回給調(diào) 用批處理、 存儲(chǔ)過程或觸發(fā)器。 返回時(shí)，游標(biāo)保留在過程中最后一次執(zhí)行提取時(shí) 的位置。對(duì)于任意類型的游標(biāo)， 如果游標(biāo)關(guān)閉， 程或觸發(fā)器。 如果將游標(biāo)指派給一個(gè)參數(shù)， 情況。說明 關(guān)閉狀態(tài)只有在返回時(shí)才有影響。 例如， 可以在過程中關(guān)閉游標(biāo)， 稍 后再打開游標(biāo)，然后將該游標(biāo)的結(jié)果集返回給調(diào)用批處理、存儲(chǔ)過程或觸發(fā)器。臨時(shí)存儲(chǔ)過程SQL Server 支持兩種臨時(shí)過程：局部臨時(shí)過程和全局臨時(shí)過程。局部臨時(shí) 過程只能由創(chuàng)建該過程的連接使用。 全局臨時(shí)過程則可由所有連接使用。 局部臨 時(shí)

7、過程在當(dāng)前會(huì)話結(jié)束時(shí)自動(dòng)除去。 全局臨時(shí)過程在使用該過程的最后一個(gè)會(huì)話 結(jié)束時(shí)除去。通常是在創(chuàng)建該過程的會(huì)話結(jié)束時(shí)。臨時(shí)過程用 # 和 # 命名，可以由任何用戶創(chuàng)建。創(chuàng)建過程后，局部過程 的所有者是唯一可以使用該過程的用戶。 執(zhí)行局部臨時(shí)過程的權(quán)限不能授予其他 用戶。如果創(chuàng)建了全局臨時(shí)過程， 則所有用戶均可以訪問該過程， 權(quán)限不能顯式 廢除。只有在tempdb數(shù)據(jù)庫中具有顯式CREATBPROCEDURfi限的用戶，才可 以在該數(shù)據(jù)庫中顯式創(chuàng)建臨時(shí)過程 （不使用編號(hào)符命名） 。可以授予或廢除這些 過程中的權(quán)限。說明 頻繁使用臨時(shí)存儲(chǔ)過程會(huì)在 tempdb 中的系統(tǒng)表上產(chǎn)生爭用， 從而對(duì) 性能產(chǎn)

8、生負(fù)面影響。建議使用 sp_executesql 代替。 sp_executesql 不在系統(tǒng) 表中存儲(chǔ)數(shù)據(jù)，因此可以避免這一問題。自動(dòng)執(zhí)行存儲(chǔ)過程SQL Server 啟動(dòng)時(shí)可以自動(dòng)執(zhí)行一個(gè)或多個(gè)存儲(chǔ)過程。 這些存儲(chǔ)過程必須由系 統(tǒng)管理員創(chuàng)建，并在 sysadmin 固定服務(wù)器角色下作為后臺(tái)過程執(zhí)行。這些過程不 能有任何輸入?yún)?shù)。對(duì)啟動(dòng)過程的數(shù)目沒有限制，但是要注意，每個(gè)啟動(dòng)過程在執(zhí)行時(shí)都會(huì)占用一 個(gè)連接。如果必須在啟動(dòng)時(shí)執(zhí)行多個(gè)過程，但不需要并行執(zhí)行，則可以指定一個(gè)過 程作為啟動(dòng)過程，讓該過程調(diào)用其它過程。這樣就只占用一個(gè)連接。在啟動(dòng)時(shí)恢復(fù)了最后一個(gè)數(shù)據(jù)庫后，即開始執(zhí)行存儲(chǔ)過程。若要跳過這

9、些存儲(chǔ) 過程的執(zhí)行， 請(qǐng)將啟動(dòng)參數(shù)指定為跟蹤標(biāo)記 4022 。如果以最低配置啟動(dòng) SQL Server （使用 -f 標(biāo)記），則啟動(dòng)存儲(chǔ)過程也不會(huì)執(zhí)行。若要?jiǎng)?chuàng)建啟動(dòng)存儲(chǔ)過程，必須作為 sysadmin 固定服務(wù)器角色的成員登錄，并 在 master 數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程。使用 sp_procoption 可以：將現(xiàn)有存儲(chǔ)過程指定為啟動(dòng)過程。停止在 SQL Server 啟動(dòng)時(shí)執(zhí)行過程查看 SQL Server 啟動(dòng)時(shí)執(zhí)行的所有過程的列表 存儲(chǔ)過程嵌套存儲(chǔ)過程可以嵌套，即一個(gè)存儲(chǔ)過程可以調(diào)用另一個(gè)存儲(chǔ)過程。在被調(diào)用過程 開始執(zhí)行時(shí)，嵌套級(jí)將增加，在被調(diào)用過程執(zhí)行結(jié)束后，嵌套級(jí)將減少。如果超出 最

10、大的嵌套級(jí)， 會(huì)使整個(gè)調(diào)用過程鏈?zhǔn)??？捎肗ESTLEVE函數(shù)返回當(dāng)前的嵌套級(jí)。若要估計(jì)編譯后的存儲(chǔ)過程大小，請(qǐng)使用下列性能監(jiān)視計(jì)數(shù)器。* 各種分類的高速緩存對(duì)象均可以使用這些計(jì)數(shù)器，包括特殊 sql 、準(zhǔn)備 sql 、過程、觸發(fā)器等。sql_statement 限制除了 SET SHOWPLAN_TEX和SET SHOWPLAN_AL之外（這兩個(gè)語句必須是批處 理中僅有的語句），任何 SET 語句均可以在存儲(chǔ)過程內(nèi)部指定。所選擇的 SET 選 項(xiàng)在存儲(chǔ)過程執(zhí)行過程中有效，之后恢復(fù)為原來的設(shè)置。如果其他用戶要使用某個(gè)存儲(chǔ)過程，那么在該存儲(chǔ)過程內(nèi)部，一些語句使用的 對(duì)象名必須使用對(duì)象所有者的名稱

11、限定。這些語句包括：ALTER TABLECREATE INDEXCREATE TABLE所有 DBCC 語句DROP TABLEDROP INDEXTRUNCATE TABLEUPDATE STATISTICS權(quán)限CREATE PROCEDUR的權(quán)限默認(rèn)授予sysadmi n固定服務(wù)器角色成員和db_owner 和 db_ddladmin 固定數(shù)據(jù)庫角色成員。 sysadmin 固定服務(wù)器角色成員和 db_owner固定數(shù)據(jù)庫角色成員可以將 CREATE PROCEDURES限轉(zhuǎn)讓給其他用戶。 執(zhí) 行存儲(chǔ)過程的權(quán)限授予過程的所有者，該所有者可以為其它數(shù)據(jù)庫用戶設(shè)置執(zhí)行權(quán) 限。示例A. 使用帶

12、有復(fù)雜 SELECT 語句的簡單過程 下面的存儲(chǔ)過程從四個(gè)表的聯(lián)接中返回所有作者 (提供了姓名) 、出版的書 籍以及出版社。該存儲(chǔ)過程不使用任何參數(shù)。USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = au_info_all AND type = P)DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor taO

13、N a.au_id = ta.au_id INNER JOIN titles tON t.title_id = ta.title_id INNER JOIN publishers pON t.pub_id = p.pub_idGOau_info_all 存儲(chǔ)過程可以通過以下方法執(zhí)行：EXECUTE au_info_all- OrEXEC au_info_all如果該過程是批處理中的第一條語句，則可使用：au_info_all天涯風(fēng)云 19:31:29B. 使用帶有參數(shù)的簡單過程 下面的存儲(chǔ)過程從四個(gè)表的聯(lián)接中只返回指定的作者 (提供了姓名) 、出版 的書籍以及出版社。該存儲(chǔ)過程接受與傳遞的參數(shù)

14、精確匹配的值。USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = au_info AND type = P) DROP PROCEDURE au_infoGOUSE pubsGOCREATE PROCEDURE au_infolastname varchar(40),firstname varchar(20) ASSELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INN

15、ER JOIN titles tON t.title_id = ta.title_id INNER JOIN publishers pON t.pub_id = p.pub_idWHERE au_fname = firstnameAND au_lname = lastnameGOau_info 存儲(chǔ)過程可以通過以下方法執(zhí)行：EXECUTE au_info Dull, Ann- OrEXECUTE au_info lastname = Dull, firstname = Ann - OrEXECUTE au_info firstname = Ann, lastname = Dull - OrEX

16、EC au_info Dull, Ann- OrEXEC au_info lastname = Dull, firstname = Ann - OrEXEC au_info firstname = Ann, lastname = Dull如果該過程是批處理中的第一條語句，則可使用：au_info Dull, Ann- Orau_info lastname = Dull, firstname = Ann - Orau_info firstname = Ann, lastname = Dull天涯風(fēng)云 19:31:41C. 使用帶有通配符參數(shù)的簡單過程 下面的存儲(chǔ)過程從四個(gè)表的聯(lián)接中只返回指定的作

17、者 （提供了姓名） 、出版 的書籍以及出版社。 該存儲(chǔ)過程對(duì)傳遞的參數(shù)進(jìn)行模式匹配， 如果沒有提供參數(shù)， 則使用預(yù)設(shè)的默認(rèn)值。USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = au_info2 AND type = P)DROP PROCEDURE au_info2GOUSE pubsGOCREATE PROCEDURE au_info2lastname varchar(30) = D%,firstname varchar(18) = %ASSELECT au_lname, au_fname, title, pub_nameF

18、ROM authors a INNER JOIN titleauthor taON a.au_id = ta.au_id INNER JOIN titles tON t.title_id = ta.title_id INNER JOIN publishers pON t.pub_id = p.pub_idWHERE au_fname LIKE firstnameAND au_lname LIKE lastnameGOau_info2 存儲(chǔ)過程可以用多種組合執(zhí)行。下面只列出了部分組合：EXECUTE au_info2- OrEXECUTE au_info2 Wh%- OrEXECUTE au_i

19、nfo2 firstname = A%- OrEXECUTE au_info2 CKarsOEn- OrEXECUTE au_info2 Hunter, Sheryl- OrEXECUTE au_info2 H%, S%天涯風(fēng)云 19:32:22D. 使用OUTPUT參數(shù)OUTPUT數(shù)允許外部過程、批處理或多條 Tran sact-SQL語句訪問在過程 執(zhí)行期間設(shè)置的某個(gè)值。下面的示例創(chuàng)建一個(gè)存儲(chǔ)過程 (titles_sum) ，并使用 一個(gè)可選的輸入?yún)?shù)和一個(gè)輸出參數(shù)。首先，創(chuàng)建過程：USE pubsGOIF EXISTS(SELECT name FROM sysobjectsWHERE n

20、ame = titles_sum AND type = P)DROP PROCEDURE titles_sumGOUSE pubsGOCREATEPROCEDURtitEles_sum TITLvEarchar(40) = %, SUmMoney OUTPUTASSELECT Title Name = titleFROM titlesWHERE title LIKE TITLESELECT SUM = SUM(price)FROM titlesWHERE title LIKE TITLEGO接下來，將該OUTPUT參數(shù)用于控制流語言。說明 OUTPUTS量必須在創(chuàng)建表和使用該變量時(shí)都進(jìn)行定義參

21、數(shù)名和變量名不一定要匹配， 不過數(shù)據(jù)類型和參數(shù)位置必須匹配 (除非使 用 SUM = variable 形式)。DECLARE TOTALCOST moneyEXECUTE titles_sum The%, TOTALCOST OUTPUTIF TOTALCOST 200BEGINPRINT PRINT All of these titles can be purchased for less than $200. ENDELSESELECT The total cost of these titles is $+ RTRIM(CAST(TOTALCOST AS varchar(20)下面是結(jié)果集：Title NameThe Busy Executives Database GuideThe Gourmet MicrowaveThe Psychology of Computer Cooking(3 row(s)