SQL存儲過程全面實例講解

1、SQL實例講解一、 創(chuàng)建存儲過程結(jié)構(gòu)CREATE PROCEDURE創(chuàng)建存儲過程，存儲過程是保存起來的可以接受和返回用戶提供的參數(shù)的  Transact-SQL 語句的集合?？梢詣?chuàng)建一個過程供永久使用，或在一個會話中臨時 使用（局部臨時過程），或在所有會話中臨時使用（全局臨時過程）。也可以創(chuàng)建 在 Microsoft SQL Server啟動時自動運行的存儲過程。語法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  二、 存儲過程實例講解1. 使用帶有復(fù)雜 SELECT 語句的簡單過程下面的存儲過程從四個表的聯(lián)接中返回所有作者（提供了姓名）、出版的書籍以及 出版社。該存儲過程不使用任何參數(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 存儲過程可以通過以下方法執(zhí)行：EXECUTE au_info_all- OrEXEC au_info_all如果該過程是批處理中的第一條語句，則可使用：au_info_all2. 使

7、用帶有參數(shù)的簡單過程下面的存儲過程從四個表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書籍 以及出版社。該存儲過程接受與傳遞的參數(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 存儲過程可以通過以下方法執(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ù)的簡單過程下面的存儲過程從四個表的聯(lián)接中只返回指定的作者（提供了姓名）、出版的書籍 以及出版社。該存儲過程對傳遞的參數(shù)進行模式匹配，如果沒有提供參數(shù)，則使用 預(yù)設(shè)的默認值。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 存儲過程可以用多種組合執(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è)置的某個值。下面的示例創(chuàng)建一個存儲過程  (titles_sum)，并使用一個可選 的輸入?yún)?shù)和一個輸出參數(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ù)名和變量名不一定要匹配，不過數(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 游標參數(shù)OUTPUT 游標參數(shù)用來將存儲過程的局部游標傳遞回調(diào)用批處理、存儲過程或觸發(fā)器。首先，創(chuàng)建以下過程，在 titles 表上聲明并打開一個游標：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、行一個批處理，聲明一個局部游標變量，執(zhí)行上述過程以將游標賦值給 局部變量，然后從該游標提取行。USE pubsGODECLARE MyCursor CURSOREXEC titles_cursor titles_cursor = MyCursor OUTPUTWHILE (FETCH_STATUS = 0)BEGIN   FETCH NEXT FROM MyCursorENDCLOSE MyCursorDE

29、ALLOCATE MyCursorGO6. 使用 WITH RECOMPILE 選項如果為過程提供的參數(shù)不是典型的參數(shù)，并且新的執(zhí)行計劃不應(yīng)高速緩存或存儲在 內(nèi)存中，WITH RECOMPILE 子句會很有幫助。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 選項WITH ENCRYPTION 子句對用戶隱藏存儲過程的文本。下例創(chuàng)建加密過程，使用  sp_helptext 系統(tǒng)存儲過程獲取關(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.接下來，選擇加密存儲過程內(nèi)容的標識號和文本。SELECT c.id, c.text FROM syscomments c INNER JOIN sysobjects 

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

36、160;                                              - -1413580074&#

37、160;?e?(1 row(s) affected)H. 創(chuàng)建用戶定義的系統(tǒng)存儲過程下面的示例創(chuàng)建一個過程，顯示表名以 emp 開頭的所有表及其對應(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. 使用延遲名稱解析下面的示例顯示四個過程以及延遲名稱解析的各種可能使用方式。盡管引用的表或 列在編譯時不存在，但每個存儲過程都可創(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