SQL存儲過程教程II

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

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

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

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

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

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

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

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

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

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

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

12、有復雜 SELECT 語句的簡單過程 下面的存儲過程從四個表的聯(lián)接中返回所有作者 (提供了姓名) 、出版的書 籍以及出版社。該存儲過程不使用任何參數(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 存儲過程可以通過以下方法執(zhí)行：EXECUTE au_info_all- OrEXEC au_info_all如果該過程是批處理中的第一條語句，則可使用：au_info_all天涯風云 19:31:29B. 使用帶有參數(shù)的簡單過程 下面的存儲過程從四個表的聯(lián)接中只返回指定的作者 (提供了姓名) 、出版 的書籍以及出版社。該存儲過程接受與傳遞的參數(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 存儲過程可以通過以下方法執(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天涯風云 19:31:41C. 使用帶有通配符參數(shù)的簡單過程 下面的存儲過程從四個表的聯(lián)接中只返回指定的作

17、者 （提供了姓名） 、出版 的書籍以及出版社。 該存儲過程對傳遞的參數(shù)進行模式匹配， 如果沒有提供參數(shù)， 則使用預設的默認值。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 存儲過程可以用多種組合執(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%天涯風云 19:32:22D. 使用OUTPUT參數(shù)OUTPUT數(shù)允許外部過程、批處理或多條 Tran sact-SQL語句訪問在過程 執(zhí)行期間設置的某個值。下面的示例創(chuàng)建一個存儲過程 (titles_sum) ，并使用 一個可選的輸入?yún)?shù)和一個輸出參數(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)建表和使用該變量時都進行定義參

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)下面是結果集：Title NameThe Busy Executives Database GuideThe Gourmet MicrowaveThe Psychology of Computer Cooking(3 row(s)