存儲過程大總結(jié)

1、SQLSERVER存儲過程使用說明書引言首先介紹一下什么是存儲過程：存儲過程就是將常用的或很復雜的工作，預先用SQL語句寫好并用一個指定的名稱存儲起來，并且這樣的語句是放在數(shù)據(jù)庫中的，還可以根據(jù)條件執(zhí)行不同SQL語句， 那么以后要叫數(shù)據(jù)庫提供與已定義好的存儲過程的功能相同的服務時，只需調(diào)用execute,即可自動完成命令。請大家先看一個小例子：create proc query_bookasselect * from bookgo-調(diào)用存儲過程exec query_book請大家來了解一下存儲過程的語法。Create PROC  EDURE &

2、#160;procedure_name   number       parameter data_type          VARYING   = default   OUTPUT       ,.n  WITH &

3、#160;   RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION   FOR REPLICATION AS sql_statement  .n 一、參數(shù)簡介1、procedure_name 新存儲過程的名稱。過程名必須符合標識符規(guī)則，且對于數(shù)據(jù)庫及其所有者必須唯一。要創(chuàng)建局部臨時過程，可以在 procedure_name 前面加一個編號符 (

4、#procedure_name)，要創(chuàng)建全局臨時過程，可以在 procedure_name 前面加兩個編號符 (#procedure_name)。完整的名稱（包括 # 或 #）不能超過 128 個字符。指定過程所有者的名稱是可選的。2、;number是可選的整數(shù)，用來對同名的過程分組，以便用一條 Drop PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應用程序使用的過程可以命名為 orderproc;1、orderproc;

5、2 等。Drop PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數(shù)字不應包含在標識符中，只應在 procedure_name 前后使用適當?shù)亩ń绶?、parameter過程中的參數(shù)。在 Create PROCEDURE 語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認值）。存儲過程最多可以有 2100 個參數(shù)。使用符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標識符的規(guī)則。每個過程的參數(shù)僅用于

6、該過程本身；相同的參數(shù)名稱可以用在其它過程中。默認情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。4、data_type參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括 text、ntext 和 image）均可以用作存儲過程的參數(shù)。不過，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定的數(shù)據(jù)類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關(guān)鍵字。說明：對于可以是cursor 數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。5、VARY

7、ING指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標參數(shù)。6、default參數(shù)的默認值。如果定義了默認值，不必指定該參數(shù)的值即可執(zhí)行過程。默認值必須是常量或 NULL。如果過程將對該參數(shù)使用 LIKE 關(guān)鍵字，那么默認值中可以包含通配符（%、_、 和 ）。7、OUTPUT表明參數(shù)是返回參數(shù)。該選項的值可以返回給 EXECUTE。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過程。Text、ntext 和 image 參數(shù)可用作 OUTPUT

8、60;參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以是游標占位符。8、n表示最多可以指定 2100 個參數(shù)的占位符。9、RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONRECOMPILE 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計劃時，請使用 RECOMPILE 選項。ENCRYPTION 表示 

9、SQL Server 加密 syscomments 表中包含 Create PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復制的一部分發(fā)布。說明：在升級過程中，SQL Server 利用存儲在 syscomments 中的加密注釋來重新創(chuàng)建加密過程。10、FOR REPLICATION指定不能在訂閱服務器上執(zhí)行為復制創(chuàng)建的存儲過程。.使用 FOR R

10、EPLICATION 選項創(chuàng)建的存儲過程可用作存儲過程篩選，且只能在復制過程中執(zhí)行。本選項不能和 WITH RECOMPILE 選項一起使用。11、AS指定過程要執(zhí)行的操作。12、sql_statement過程中要包含的任意數(shù)目和類型的 Transact-SQL 語句。但有一些限制。13、 n是表示此過程可以包含多條 Transact-SQL 語句的占位符。14、注釋/*和*/之間的為注釋，可以包含一行和多行的說明文字。15、其他說明存儲過程的最大大小為 128 MB。二、存儲過程的優(yōu)點都有哪些

11、呢？1. 存儲過程只在創(chuàng)造時進行編譯即可，以后每次執(zhí)行存儲過程都不需再重新編譯，而我們通常使用的SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。2. 經(jīng)常會遇到復雜的業(yè)務邏輯和對數(shù)據(jù)庫的操作，這個時候就會用SP來封裝數(shù)據(jù)庫操作。當對數(shù)據(jù)庫進行復雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此復雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務處理結(jié)合一起使用。可以極大的提高數(shù)據(jù)庫的使用效率，減少程序的執(zhí)行時間，這一點在較大數(shù)據(jù)量的數(shù)據(jù)庫的操作中是非常重要的。在代碼上看，SQL語句和程序代碼語句的分離，可以提高程序代碼的可讀性。3. 存儲過程

12、可以設置參數(shù)，可以根據(jù)傳入?yún)?shù)的不同重復使用同一個存儲過程，從而高效的提高代碼的優(yōu)化率和可讀性。4. 安全性高,可設定只有某此用戶才具有對指定存儲過程的使用權(quán)存儲過程的種類：（1）系統(tǒng)存儲過程：以sp_開頭,用來進行系統(tǒng)的各項設定.取得信息.相關(guān)管理工作,如 sp_help就是取得指定對象的相關(guān)信息。（2）擴展存儲過程 以XP_開頭,用來調(diào)用操作系統(tǒng)提供的功能exec master.xp_cmdshell 'ping 10.8.16.1'（3）用戶自定義的存儲過程,這是我們所指的存儲過程常用格式模版：Create pr

13、ocedure procedue_name parameter data_typeoutputwithrecompile|encryption as sql_statement解釋：output：表示此參數(shù)是可傳回的with recompile|encryption recompile:表示每次執(zhí)行此存儲過程時都重新編譯一次;encryption:所創(chuàng)建的存儲過程的內(nèi)容會被加密。三、實例講解實例1：只返回單一記錄集的存儲過程。要求1：查詢表bankMoney的內(nèi)容的存儲過程 create procedure

14、0;sp_query_bankMoneyasselect * from bankMoneygoexec sp_query_bankMoney注*  在使用過程中只需要把中的SQL語句替換為存儲過程名，就可以了很方便吧！實例2（向存儲過程中傳遞參數(shù)）：加入一筆記錄到表bankMoney，并查詢此表中userID= Zhangsan的所有存款的總金額。Create proc insert_bank param1 char(10),param2 varchar(20),param3

15、 varchar(20),param4 int,param5 int outputwith encryption -加密asinsert bankMoney (id,userID,sex,Money) Values(param1,param2,param3, param4)select param5=sum(Money) from bankMoney where userID='Zhangsan'go在SQL Serve

16、r查詢分析器中執(zhí)行該存儲過程的方法是：declare total_price intexec insert_bank '004','Zhangsan','男',100,total_price outputprint '總余額為'+convert(varchar,total_price)go在這里再啰嗦一下存儲過程的3種傳回值（方便正在看這個例子的朋友不用再去查看語法內(nèi)容）: 1.以Return傳回整數(shù) 2.以output格式傳回參數(shù) 3.Re

17、cordset傳回值的區(qū)別: output和return都可在批次程式中用變量接收,而recordset則傳回到執(zhí)行批次的客戶端中。實例3：使用帶有復雜 Select 語句的簡單過程下面的存儲過程從四個表的聯(lián)接中返回所有作者（提供了姓名）、出版的書籍以及出版社。該存儲過程不使用任何參數(shù)。USE pubsIF EXISTS (Select name FROM sysobjects         Where 

18、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 ta &#

19、160;    ON 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_idG

20、Oau_info_all 存儲過程可以通過以下方法執(zhí)行：EXECUTE au_info_all- orEXEC au_info_all如果該過程是批處理中的第一條語句，則可使用：au_info_all實例4：使用帶有參數(shù)的簡單過程Create PROCEDURE au_info      lastname varchar(40),      firstname varchar(20)ASSelec

21、t 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    &

22、#160; ON t.title_id = ta.title_id INNER JOIN publishers p      ON t.pub_id = p.pub_id      Where  au_fname = firstname      AND au

23、_lname = lastnameGOau_info 存儲過程可以通過以下方法執(zhí)行：EXECUTE au_info 'Dull', 'Ann'- orEXECUTE au_info lastname = 'Dull', firstname = 'Ann'- orEXECUTE au_info firstname = 'Ann',

24、 lastname = 'Dull'- orEXEC au_info 'Dull', 'Ann'- orEXEC au_info lastname = 'Dull', firstname = 'Ann'- orEXEC au_info firstname = 'Ann', lastname&#

25、160;= 'Dull'如果該過程是批處理中的第一條語句，則可使用：au_info 'Dull', 'Ann'- orau_info lastname = 'Dull', firstname = 'Ann'- orau_info firstname = 'Ann', lastname = 'Dull'實例5：使用帶有

26、通配符參數(shù)的簡單過程Create PROCEDURE au_info2lastname varchar(30) = 'D%',firstname varchar(18) = '%'ASSelect au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor ta   ON

27、 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&#

28、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

29、2 'Hunter', 'Sheryl'- orEXECUTE au_info2 'H%', 'S%'四、系統(tǒng)存儲過程用戶存儲過程: 用戶也可以編寫自己的存儲過程，并把它存放在數(shù)據(jù)庫中，供客戶端調(diào)用。以上主要是用戶存儲過程，下面介紹一下系統(tǒng)存儲過程。系統(tǒng)存儲過程: SQL Server本身提供了一些存儲過程，用于管理有關(guān)數(shù)據(jù)庫和用戶的信息。 它的目的在于能夠方便地從系統(tǒng)表中查詢信息，或者完成與更新數(shù)據(jù)庫表相關(guān)的管理任務或其它的系統(tǒng)管理任務。 系統(tǒng)存儲過程可以在任意一個數(shù)據(jù)

30、庫中執(zhí)行。創(chuàng)建并存放于系統(tǒng)數(shù)據(jù)庫master中，并且名稱以sp_或者xp_開頭。 部分系統(tǒng)存儲過程： sp_addtype：用于定義一個用戶定義數(shù)據(jù)類型。 sp_configure：用于管理服務器配置選項設置。 xp_sendmail：用于發(fā)送電子郵件或?qū)ず粜畔ⅰ?sp_stored_procedures：用于返回當前數(shù)據(jù)庫中的存儲過程的清單。 sp_help：用于顯示參數(shù)清單和其數(shù)據(jù)類型。 sp_helptext：用于顯示存儲過程的定義文本。 sp_rename：用于修改當前數(shù)據(jù)庫中用戶對象的名稱。 Sp_who:用于顯示使用數(shù)據(jù)庫的當前用戶 sp_help：用于顯示參數(shù)清單和其數(shù)據(jù)類型。

31、sp_depends：用于顯示存儲過程依據(jù)的對象或者依據(jù)存儲過程的對象 sp_helptext：用于顯示存儲過程的定義文本。一個調(diào)用系統(tǒng)存儲過程的例子：exec sp_helptext query_book五、注意事項:存儲過程一般用來完成數(shù)據(jù)查詢和數(shù)據(jù)處理操作，所以在存儲過程中不可以使用創(chuàng)建數(shù)據(jù)庫對象的語句， 即在存儲過程中一般不能含有以下語句： CREATE TABLE ； CREATE VIEW ； CREATE DEFAULT ； CREATE RULE ；CREATE TRIGGER ；CREATE PROCEDURE六、返回值和狀態(tài)信息無論什么時候執(zhí)行存儲過程，總要返回一個結(jié)果碼，

32、用以指示存儲過程的執(zhí)行狀態(tài)。 如果存儲過程執(zhí)行成功，返回的結(jié)果碼是0；如果存儲過程執(zhí)行失敗，返回的結(jié)果碼一般是一個負數(shù)，它和失敗的類型有關(guān)。 我們在創(chuàng)建存儲過程時，也可以定義自己的狀態(tài)碼和錯誤信息。 執(zhí)行存儲過程： 例：執(zhí)行帶參數(shù)的存儲過程,查詢大于歲的學生 create proc show;3 ( pno char(6) ) as select * from person where Pno = pno exec show;3 4 例： CREATE Procedure sp_getstu;1 AS SELECT * FROM 學生 例：帶參數(shù)的存儲過程,查詢大于指定年齡的學生 CREATE

33、 proc sp_getstu;2 (sage int) AS SELECT * FROM 學生WHERE 年齡> sage 例: 帶輸出參數(shù)的存儲過程,查詢指定學生的年齡CREATE proc sp_getstu;3 ( name char(10) , age int output ) AS SELECT age=年齡 FROM 學生WHERE 姓名= name Declare sage int Exec sp_getstu;3 '張三',sage Print sage 例：帶參數(shù)和返回狀態(tài)值的存儲過程。 CREATE PROCedure sp_getstu;3 (sa

34、ge int =NULL ) AS IF sage IS NULL BEGIN PRINT '必須提供一個數(shù)值作參數(shù)！' RETURN 13 END IF NOT EXISTS (SELECT * FROM student WHERE sage > sage) BEGIN PRINT '沒有滿足條件的記錄！' RETURN -103 END SELECT * FROM student WHERE sage > sage RETURN 0 DECLARE status int EXECUTE status=sp_getstu;3 22 print st

35、atus七、存儲過程中游標的使用1、需要游標的數(shù)據(jù)操作 當select語句的結(jié)果中包含多個元組時，使用游標可以逐個存取這些元組 活動集：select語句返回的元組的集合 當前行：活動集中當前處理的那一行。游標即是指向當前行的指針。2、游標分類 滾動游標：游標的位置可以來回移動，可在活動集中取任意元組。 非滾動游標：只能在活動集中順序地取下一個元組。 更新游標：數(shù)據(jù)庫對游標指向的當前行加鎖，當程序讀下一行數(shù)據(jù)時，本行數(shù)據(jù)解鎖，下一行數(shù)據(jù)加鎖。3、定義與使用游標的語句 declare ： declare 游標名scroll cursor for select語句for update of列表名定義

36、一個游標，使之對應一個select語句 for update任選項，表示該游標可用于對當前行的修改與刪除 open 打開一個游標，執(zhí)行游標對應的查詢，結(jié)果集合為該游標的活動集 open 游標名 fetch 在活動集中將游標移到特定的行，并取出該行數(shù)據(jù)放到相應的變量中 fetch next | prior | first | last | current | relative n | absolute m 游標名into 變量表 close 關(guān)閉游標，釋放活動集及其所占資源。需要再使用該游標時，執(zhí)行open語句 close 游標名 deallocate 刪除游標，以后不能再對該游標執(zhí)行open語

37、句 deallocate 游標名 FETCH_STATUS 返回被FETCH 語句執(zhí)行的最后游標的狀態(tài). 0 fetch語句成功 -1 fetch語句失敗 -2 被提取的行不存在4、游標實例 例:查詢電子商務系學生信息,性別為女輸出為female,否則輸出為male？ declare c1 cursor for select sno,sname,ssex from student where sdept='ec' declare sno char(10),sname char(10),ssex char(2) Open c1 Fetch c1 into sno,sname,ss

38、ex While fetch_status=0 Begin if ssex='女' begin set ssex='female' end else begin set ssex='male' end Select sno,sname ,ssex Fetch c1 into sno,sname,ssex end例：    ALTER PROC dbo.dnt_UserRecoveryByUserName    username    NVARCHAR(50)ASBEGIN    DECLARE    uid INT;    DECLARE    tid INT;    DECLARE    replies INT;    DECLARE    temp varchar(50);    SET uid