SQL存儲(chǔ)過程全面實(shí)例講解

1、SQL實(shí)例講解、 創(chuàng)建存儲(chǔ)過程結(jié)構(gòu)CREATE PROCEDURE 創(chuàng)建存儲(chǔ)過程，存儲(chǔ)過程是保存起來的可以接受和返回用戶提供的參數(shù)的Tran sact-SQL語(yǔ)句的集合?？梢詣?chuàng)建一個(gè)過程供永久使用，或在一個(gè)會(huì)話中臨時(shí) 使用（局部臨時(shí)過程） ，或在所有會(huì)話中臨時(shí)使用（全局臨時(shí)過程） 。也可以創(chuàng)建 在 Microsoft SQL Server 啟動(dòng)時(shí)自動(dòng)運(yùn)行的存儲(chǔ)過程。語(yǔ)法CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYP

2、TION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 、 存儲(chǔ)過程實(shí)例講解1. 使用帶有復(fù)雜SELECTS句的簡(jiǎn)單過程、出版的書籍以及下面的存儲(chǔ)過程從四個(gè)表的聯(lián)接中返回所有作者（提供了姓名） 出版社。該存儲(chǔ)過程不使用任何參數(shù)。USE pubsIF EXISTS (SELECT name FROM sysobjects WHERE name = au_info_all AND type = P) DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELE

3、CT au_lname, au_fname, title, pub_name FROM 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_idGOau_info_all 存儲(chǔ)過程可以通過以下方法執(zhí)行：EXECUTE au_info_all- OrEXEC au_info_all如果該過程是批處理中的第一條語(yǔ)句，則可使用：au_info_all、出版的書籍

4、2. 使用帶有參數(shù)的簡(jiǎn)單過程 下面的存儲(chǔ)過程從四個(gè)表的聯(lián)接中只返回指定的作者（提供了姓名） 以及出版社。該存儲(chǔ)過程接受與傳遞的參數(shù)精確匹配的值。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_nameFR

5、OM 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 = firstnameAND au_lname = lastnameGOau_info 存儲(chǔ)過程可以通過以下方法執(zhí)行：EXECUTE au_info Dull, Ann- OrEXECUTE au_info lastname = Dull, firstname =

6、Ann - OrEXECUTE au_info firstname = Ann, lastname = Dull - OrEXEC au_info Dull, Ann- OrEXEC au_info lastname = Dull, firstname = Ann - OrEXEC au_info firstname = Ann, lastname = Dull如果該過程是批處理中的第一條語(yǔ)句，則可使用：au_info Dull, Ann- Orau_info lastname = Dull, firstname = Ann - Or au_info firstname = Ann, last

7、name = Dull3. 使用帶有通配符參數(shù)的簡(jiǎn)單過程 下面的存儲(chǔ)過程從四個(gè)表的聯(lián)接中只返回指定的作者（提供了姓名） 、出版的書籍 以及出版社。該存儲(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_info2 lastname varchar(30) = D*, firstname varchar(1

8、8) = *ASSELECT au_lname, au_fname, title, pub_nameFROM 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 lastnameGO au_info2 存儲(chǔ)過程可以用多種組合執(zhí)行。下面只列出了部分組合：EXEC

9、UTE au_info2- OrEXECUTE au_info2 Wh* - OrEXECUTE au_info2 firstname = A* - OrEXECUTE au_info2 CKarsOEn - OrEXECUTE au_info2 Hunter, Sheryl - OrEXECUTE au_info2 H*, S*4. 使用OUTPUT參數(shù)OUTPUT參數(shù)允許外部過程、批處理或多條 Tran sact-SQL語(yǔ)句訪問在過程執(zhí)行期 間設(shè)置的某個(gè)值。下面的示例創(chuàng)建一個(gè)存儲(chǔ)過程 (titles_sum) ，并使用一個(gè)可選 的輸入?yún)?shù)和一個(gè)輸出參數(shù)。首先，創(chuàng)建過程：USE pubsGO

10、IF EXISTS(SELECT name FROM sysobjectsWHERE name = titles_sum AND type = P) DROP PROCEDURE titles_sumGOUSE pubsGOCREATE PROCEDURE titles_sum TITLE varchar(40) = *, SUM money OUTPUT ASSELECT Title Name = titleFROM titlesWHERE title LIKE TITLE SELECT SUM = SUM(price) FROM titlesWHERE title LIKE TITLEGO

11、接下來，將該OUT PUT參數(shù)用于控制流語(yǔ)言。說明OUT PUT變量必須在創(chuàng)建表和使用該變量時(shí)都進(jìn)行定義。參數(shù)名和變量名不一定要匹配，不過數(shù)據(jù)類型和參數(shù)位置必須匹配(除非使用 SUM = variable 形式)。DECLARE TOTALCOST moneyEXECUTE titles_sum The*, TOTALCOST OUTPUTIF TOTALCOST 200 BEGINPRINT PRINT All of these titles can be purchased for less than $200.ENDELSESELECT The total cost of these t

12、itles is $+ RTRIM(CAST(TOTALCOST AS varchar(20)面是結(jié)果集：Title NameThe Busy Executives Database GuideThe Gourmet MicrowaveThe Psychology of Computer Cooking (3 row(s) affected)Warning, null value eliminated from aggregate.All of these titles can be purchased for less than $200.5. 使用OUT PUT游標(biāo)參數(shù)OUTPUT游標(biāo)參數(shù)

13、用來將存儲(chǔ)過程的局部游標(biāo)傳遞回調(diào)用批處理、存儲(chǔ)過程或觸發(fā)器。首先，創(chuàng)建以下過程，在 titles 表上聲明并打開一個(gè)游標(biāo)：USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = titles_cursor and type = P) DROP PROCEDURE titles_cursorGOCREATE PROCEDURE titles_cursor titles_cursor CURSOR VARYING OUTPUT ASSET titles_cursor = CURSORFORWARD_ONLY STATIC FORSELE

14、CT *FROM titlesOPEN titles_cursorGO接下來，執(zhí)行一個(gè)批處理，聲明一個(gè)局部游標(biāo)變量，執(zhí)行上述過程以將游標(biāo)賦值給 局部變量，然后從該游標(biāo)提取行。USE pubsGODECLARE MyCursor CURSOREXEC titles_cursor titles_cursor = MyCursor OUTPUTWHILE (FETCH_STATUS = 0)BEGINFETCH NEXT FROM MyCursorENDCLOSE MyCursorDEALLOCATE MyCursorGO6. 使用 WITH RECOMPILB選項(xiàng)如果為過程提供的參數(shù)不是典型的參數(shù)

15、，并且新的執(zhí)行計(jì)劃不應(yīng)高速緩存或存儲(chǔ)在內(nèi)存中，WITH RECO MP ILEF句會(huì)很有幫助。USE pubsIF EXISTS (SELECT name FROM sysobjectsWHERE name = titles_by_author AND type = P)DROP PROCEDURE titles_by_authorGOCREATE PROCEDURE titles_by_author LNAME_PATTERN varchar(30) = *WITH RECOMPILEASSELECT RTRIM(au_fname) + + RTRIM(au_lname) AS Author

16、s full name, title AS TitleFROM authors a INNER JOIN titleauthor taON a.au_id = ta.au_id INNER JOIN titles tON ta.title_id = t.title_idWHERE au_lname LIKE LNAME_PATTERN GO7. 使用 WITH ENCRYPTION選項(xiàng)WITH ENCR YP TIONF句對(duì)用戶隱藏存儲(chǔ)過程的文本。下例創(chuàng)建加密過程，使用 sp_helptext 系統(tǒng)存儲(chǔ)過程獲取關(guān)于加密過程的信息，然后嘗試直接從 syscomments 表中獲取關(guān)于該過程的信息

17、。IF EXISTS (SELECT name FROM sysobjectsWHERE name = encrypt_this AND type = P) DROP PROCEDURE encrypt_thisGOUSE pubsGOCREATE PROCEDURE encrypt_thisWITH ENCRYPTIONASSELECT *FROM authorsGOEXEC sp_helptext encrypt_this面是結(jié)果集：The objects comments have been encrypted.接下來，選擇加密存儲(chǔ)過程內(nèi)容的標(biāo)識(shí)號(hào)和文本。SELECT c.id, c.t

18、extFROM syscomments c INNER JOIN sysobjects o ON c.id = o.idWHERE = encrypt_this面是結(jié)果集：id 列信息出現(xiàn)在說明 text 列的輸出顯示在單獨(dú)一行中。執(zhí)行時(shí)，該信息將與 同一行中。id text 1413580074 ?e?(1 row(s) affected)H. 創(chuàng)建用戶定義的系統(tǒng)存儲(chǔ)過程下面的示例創(chuàng)建一個(gè)過程，顯示表名以 emp 開頭的所有表及其對(duì)應(yīng)的索引。如果 沒有指定參數(shù)，該過程將返回表名以sys 開頭的所有表（及索引） 。IF EXISTS (SELECT name FROM sysob

19、jectsWHERE name = sp_showindexes AND type = P) DROP PROCEDURE sp_showindexesGOUSE masterGOCREATE PROCEDURE sp_showindexes TABLE varchar(30) = sys*ASSELECT AS TABLE_NAME, AS INDEX_NAME, indid AS INDEX_IDFROM sysindexes i INNER JOIN sysobjects oON o.id = i.idWHERE LIKE TABLEGOUSE p

20、ubsEXEC sp_showindexes emp*GO面是結(jié)果集：TABLE_NAME INDEX_NAME INDEX_IDemployeeemployee_ind 1employeePK_emp_id 2 (2 row(s) affected)8. 使用延遲名稱解析 下面的示例顯示四個(gè)過程以及延遲名稱解析的各種可能使用方式。盡管引用的表或 列在編譯時(shí)不存在，但每個(gè)存儲(chǔ)過程都可創(chuàng)建。IF EXISTS (SELECT name FROM sysobjects WHERE name = proc1 AND type = P)DROP PROCEDURE proc1GO- Creating a procedure on a nonexistent table.USE pubsGOCREATE PROCEDURE proc1ASSELECT *FROM does_not_existGO - Here is the statement to