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

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

2、; number       parameter data_type          VARYING   = default   OUTPUT       ,.n   WITH     R

3、ECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION    FOR REPLICATION  AS sql_statement  .n  二、 存儲(chǔ)過程實(shí)例講解1. 使用帶有復(fù)雜 SELECT 語句的簡單過程下面的存儲(chǔ)過程從四個(gè)表的聯(lián)接中返回所有作者（提供了姓名）、出版的書籍以及 出版社。該存儲(chǔ)過程不使用任何參數(shù)。USE pub

4、sIF EXISTS (SELECT name FROM sysobjects          WHERE name = 'au_info_all' AND type = 'P')   DROP PROCEDURE au_info_allGOCREATE PROCEDURE 

5、au_info_allASSELECT au_lname, au_fname, title, pub_name   FROM authors a INNER JOIN titleauthor ta      ON a.au_id = ta.au_id INNER JOIN titles t    &

6、#160; ON t.title_id = ta.title_id INNER JOIN publishers p      ON t.pub_id = p.pub_idGOau_info_all 存儲(chǔ)過程可以通過以下方法執(zhí)行：EXECUTE au_info_all- OrEXEC au_info_all如果該過程是批處理中的第一條語句，則可使用：au_info_all2. 使

7、用帶有參數(shù)的簡單過程下面的存儲(chǔ)過程從四個(gè)表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書籍 以及出版社。該存儲(chǔ)過程接受與傳遞的參數(shù)精確匹配的值。USE pubsIF EXISTS (SELECT name FROM sysobjects          WHERE name = 'au_info' AND type = 'P

8、9;)   DROP PROCEDURE au_infoGOUSE pubsGOCREATE PROCEDURE au_info    lastname varchar(40),    firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name   FR

9、OM authors a INNER JOIN titleauthor ta      ON a.au_id = ta.au_id INNER JOIN titles t      ON t.title_id = ta.title_id INNER JOIN publishers 

10、;p      ON t.pub_id = p.pub_id   WHERE  au_fname = firstname      AND au_lname = lastnameGOau_info 存儲(chǔ)過程可以通過以下方法執(zhí)行：EXECUTE au_info 'Dull', 'Ann&

11、#39;- OrEXECUTE au_info lastname = 'Dull', firstname = 'Ann'- OrEXECUTE au_info firstname = 'Ann', lastname = 'Dull'- OrEXEC au_info 'Dull', 'Ann'- 

12、;OrEXEC au_info lastname = 'Dull', firstname = 'Ann'- OrEXEC au_info firstname = 'Ann', lastname = 'Dull'如果該過程是批處理中的第一條語句，則可使用：au_info 'Dull', 'Ann'- Orau_info

13、60;lastname = 'Dull', firstname = 'Ann'- Orau_info firstname = 'Ann', lastname = 'Dull'3. 使用帶有通配符參數(shù)的簡單過程下面的存儲(chǔ)過程從四個(gè)表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書籍 以及出版社。該存儲(chǔ)過程對(duì)傳遞的參數(shù)進(jìn)行模式匹配，如果沒有提供參數(shù)，則使用 預(yù)設(shè)的默認(rèn)值。USE 

14、;pubsIF EXISTS (SELECT name FROM sysobjects       WHERE name = 'au_info2' AND type = 'P')   DROP PROCEDURE au_info2GOUSE pubsGOCREATE PROCEDURE au_in

15、fo2   lastname varchar(30) = 'D*',   firstname varchar(18) = '*'AS SELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor ta   ON

16、 a.au_id = ta.au_id INNER JOIN titles t   ON t.title_id = ta.title_id INNER JOIN publishers p   ON t.pub_id = p.pub_idWHERE au_fname LIKE firstname   AND&#

17、160;au_lname LIKE lastnameGOau_info2 存儲(chǔ)過程可以用多種組合執(zhí)行。下面只列出了部分組合：EXECUTE au_info2- OrEXECUTE au_info2 'Wh*'- OrEXECUTE au_info2 firstname = 'A*'- OrEXECUTE au_info2 'CKarsOEn'- OrEXECUTE au_info

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

19、ubsGOIF EXISTS(SELECT name FROM sysobjects      WHERE name = 'titles_sum' AND type = 'P')   DROP PROCEDURE titles_sumGOUSE pubsGOCREATE PROCEDURE titles_sum

20、60;TITLE varchar(40) = '*', SUM money OUTPUTASSELECT 'Title Name' = titleFROM titles WHERE title LIKE TITLE SELECT SUM = SUM(price)FROM titlesWHERE title LIKE TITLEGO接下來

21、，將該 OUTPUT 參數(shù)用于控制流語言。 說明  OUTPUT 變量必須在創(chuàng)建表和使用該變量時(shí)都進(jìn)行定義。參數(shù)名和變量名不一定要匹配，不過數(shù)據(jù)類型和參數(shù)位置必須匹配（除非使用  SUM = variable 形式）。 DECLARE TOTALCOST moneyEXECUTE titles_sum 'The*', TOTALCOST OUTPUTIF TOTALCOST <

22、; 200 BEGIN   PRINT ' '   PRINT 'All of these titles can be purchased for less than $200.'ENDELSE   SELECT 'The total cost of these

23、0;titles is $'          + RTRIM(CAST(TOTALCOST AS varchar(20)下面是結(jié)果集：Title Name                       

24、                                        - The Busy Executive's Database 

25、;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.&

26、#160;使用 OUTPUT 游標(biāo)參數(shù)OUTPUT 游標(biāo)參數(shù)用來將存儲(chǔ)過程的局部游標(biāo)傳遞回調(diào)用批處理、存儲(chǔ)過程或觸發(fā)器。首先，創(chuàng)建以下過程，在 titles 表上聲明并打開一個(gè)游標(biāo)：USE pubsIF EXISTS (SELECT name FROM sysobjects       WHERE name = 'titles_cursor' and 

27、type = 'P')DROP PROCEDURE titles_cursorGOCREATE PROCEDURE titles_cursor titles_cursor CURSOR VARYING OUTPUTASSET titles_cursor = CURSORFORWARD_ONLY STATIC FORSELECT *FROM titlesOPEN titles_cursorGO接下來，執(zhí)

28、行一個(gè)批處理，聲明一個(gè)局部游標(biāo)變量，執(zhí)行上述過程以將游標(biāo)賦值給 局部變量，然后從該游標(biāo)提取行。USE pubsGODECLARE MyCursor CURSOREXEC titles_cursor titles_cursor = MyCursor OUTPUTWHILE (FETCH_STATUS = 0)BEGIN   FETCH NEXT FROM MyCursorENDCLOSE MyCursorDE

29、ALLOCATE MyCursorGO6. 使用 WITH RECOMPILE 選項(xiàng)如果為過程提供的參數(shù)不是典型的參數(shù)，并且新的執(zhí)行計(jì)劃不應(yīng)高速緩存或存儲(chǔ)在 內(nèi)存中，WITH RECOMPILE 子句會(huì)很有幫助。USE pubsIF EXISTS (SELECT name FROM sysobjects      WHERE name = 'titles_by

30、_author' AND type = 'P')   DROP PROCEDURE titles_by_authorGOCREATE PROCEDURE titles_by_author LNAME_PATTERN varchar(30) = '*'WITH RECOMPILEASSELECT RTRIM(au_fname) + ' '

31、60;+ RTRIM(au_lname) AS 'Authors full name',   title AS TitleFROM authors a INNER JOIN titleauthor ta    ON a.au_id = ta.au_id INNER JOIN titles t  

32、; ON ta.title_id = t.title_idWHERE au_lname LIKE LNAME_PATTERNGO7. 使用 WITH ENCRYPTION 選項(xiàng)WITH ENCRYPTION 子句對(duì)用戶隱藏存儲(chǔ)過程的文本。下例創(chuàng)建加密過程，使用  sp_helptext 系統(tǒng)存儲(chǔ)過程獲取關(guān)于加密過程的信息，然后嘗試直接從  syscomments 表中獲取關(guān)于該過程的信息。IF E

33、XISTS (SELECT name FROM sysobjects      WHERE name = 'encrypt_this' AND type = 'P')   DROP PROCEDURE encrypt_thisGOUSE pubsGOCREATE PROCEDURE encrypt_thisWITH&

34、#160;ENCRYPTIONASSELECT * FROM authorsGOEXEC sp_helptext encrypt_this下面是結(jié)果集：The object's comments have been encrypted.接下來，選擇加密存儲(chǔ)過程內(nèi)容的標(biāo)識(shí)號(hào)和文本。SELECT c.id, c.text FROM syscomments c INNER JOIN sysobjects 

35、o   ON c.id = o.idWHERE  = 'encrypt_this'下面是結(jié)果集：說明  text 列的輸出顯示在單獨(dú)一行中。執(zhí)行時(shí)，該信息將與 id 列信息出現(xiàn)在 同一行中。id         text         &#

36、160;                                              - -1413580074&#

37、160;?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 sysobjects      WHERE name = 'sp_showindexes

38、' AND type = 'P')   DROP PROCEDURE sp_showindexesGOUSE masterGOCREATE PROCEDURE sp_showindexes   TABLE varchar(30) = 'sys*'AS SELECT  AS TABLE_NAME,  

39、0; AS INDEX_NAME,    indid AS INDEX_IDFROM sysindexes i INNER JOIN sysobjects o   ON o.id = i.id WHERE  LIKE TABLEGO        

40、0;USE pubsEXEC sp_showindexes 'emp*'GO下面是結(jié)果集：TABLE_NAME       INDEX_NAME       INDEX_ID - - -employee         employee_ind    &

41、#160;1employee         PK_emp_id        2(2 row(s) affected)8. 使用延遲名稱解析下面的示例顯示四個(gè)過程以及延遲名稱解析的各種可能使用方式。盡管引用的表或 列在編譯時(shí)不存在，但每個(gè)存儲(chǔ)過程都可創(chuàng)建。IF EXISTS (SELECT name FROM sysobjects

42、60;     WHERE name = 'proc1' AND type = 'P')   DROP PROCEDURE proc1GO- Creating a procedure on a nonexistent table.USE pubsGOCREATE PROCEDURE proc1AS   SELECT *   FROM does_not_existGO  - Here is the statement to