預存程序詳解 -SQLServer

標籤：lan   ack   bind   eve   amp   篩選   comm   標識符   錯誤   

 

來源：http://www.cnblogs.com/knowledgesea/archive/2013/01/02/2841588.html

 

預存程序簡介

什麼是預存程序：預存程序可以說是一個記錄集吧，它是由一些T-SQL語句組成的代碼塊，這些T-SQL語句代碼像一個方法一樣實現一些功能（對單表或多表的增刪改查），然後再給這個代碼塊取一個名字，在用到這個功能的時候調用他就行了。

預存程序的好處：

1.由於資料庫執行動作時，是先編譯後執行的。然而預存程序是一個編譯過的代碼塊，所以執行效率要比T-SQL語句高。

2.一個預存程序在程式在網路中互動時可以替代大堆的T-SQL語句，所以也能降低網路的通訊量，提高通訊速率。

3.通過預存程序能夠使沒有許可權的使用者在控制之下間接地存取資料庫，從而確保資料的安全。

小結：總之預存程序是好東西，在做項目時屬於必備利器，下面介紹預存程序的基本文法。

預存程序的文法和參數講解

預存程序的一些基本文法：

--------------建立預存程序-----------------CREATE PROC [ EDURE ] procedure_name [ ; number ]    [ { @parameter data_type }        [ VARYING ] [ = default ] [ OUTPUT ]    ] [ ,...n ][ WITH    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ][ FOR REPLICATION ]AS sql_statement [ ...n ]--------------調用預存程序-----------------EXECUTE Procedure_name ‘‘ --預存程序如果有參數，後面加參數格式為：@參數名=value，也可直接為參數值value--------------刪除預存程序-----------------drop procedure procedure_name    --在預存程序中能調用另外一個預存程序，而不能刪除另外一個預存程序

建立預存程序的參數：
1.procedure_name ：預存程序的名稱，在前面加#為局部暫存預存程序，加##為全域暫存預存程序。

2.; number：是可選的整數，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應用程式使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數字不應包含在標識符中，只應在 procedure_name 前後使用適當的定界符。 

[email protected]： 預存程序的參數。可以有一個或多個。使用者必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的預設值）。預存程序最多可以有 2.100 個參數。 
使用 @ 符號作為第一個字元來指定參數名稱。參數名稱必須符合標識符的規則。每個過程的參數僅用於該過程本身；相同的參數名稱可以用在其它過程中。預設情況下，參數只能代替常量，而不能用於代替表名、列名或其它資料庫物件的名稱。有關更多資訊，請參見 EXECUTE。 

4.data_type：參數的資料類型。所有資料類型（包括 text、ntext 和 image）均可以用作預存程序的參數。不過，cursor 資料類型只能用於 OUTPUT 參數。如果指定的資料類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關鍵字。有關 SQL Server 提供的資料類型及其文法的更多資訊，請參見資料類型。 
說明 對於可以是 cursor 資料類型的輸出參數，沒有最大數目的限制。 

5.VARYING： 指定作為輸出參數支援的結果集（由預存程序動態構造，內容可以變化）。僅適用於遊標參數。 

6.default： 參數的預設值。如果定義了預設值，不必指定該參數的值即可執行過程。預設值必須是常量或 NULL。如果過程將對該參數使用 LIKE 關鍵字，那麼預設值中可以包含萬用字元（%、_、[] 和 [^]）。

7.OUTPUT ：表明參數是返回參數。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數可將資訊返回給調用過程。Text、ntext 和 image 參數可用作 OUTPUT 參數。使用 OUTPUT 關鍵字的輸出參數可以是遊標預留位置。 

8.RECOMPILE: 表明 SQL Server 不會緩衝該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩衝在記憶體中的執行計畫時，請使用 RECOMPILE 選項。

9.ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 複製的一部分發布。 說明 在升級過程中，SQL Server 利用儲存在 syscomments 中的加密注釋來重新建立加密過程。 

10.FOR REPLICATION :指定不能在訂閱伺服器上執行為複製建立的預存程序。.使用 FOR REPLICATION 選項建立的預存程序可用作預存程序篩選，且只能在複製過程中執行。本選項不能和 WITH RECOMPILE 選項一起使用。 

11.AS :指定過程要執行的操作。

12.sql_statement :過程中要包含的任意數目和類型的 Transact-SQL 陳述式。但有一些限制。

小結：看過這些基本文法後，下面我就根據文法建立各式的預存程序。

 建立預存程序

 

UserAccount
UserID	UserName	PassWord	RegisterTime	RegisterIP
12	6	6	2012-12-31	6
18	5	5	2013-01-01	5
19	1	1	2013-01-01	1
20	2	2	2013-01-01	2
21	3	3	2013-01-01	3
22	4	4	2013-01-01	4
23	5	5	2013-01-01	5
25	7	7	2013-01-01	7
26	8	8	2013-01-01	8
NULL	NULL	NULL	NULL	NULL

針對上面的表，我使用預存程序對它做一些操作：

1. 只返回單一記錄集的預存程序 

-------------建立名為GetUserAccount的預存程序----------------create Procedure GetUserAccountasselect * from UserAccountgo-------------執行上面的預存程序----------------exec GetUserAccount

 結果：相當於運行 select * from UserAccount 這行代碼，結果為整個表的資料。

2.沒有輸入輸出的預存程序 

-------------建立名為GetUserAccount的預存程序----------------create Procedure inUserAccountasinsert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,‘2013-01-02‘,9)go-------------執行上面的預存程序----------------exec inUserAccount

 結果：相當於運行 insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,‘2013-01-02‘,9) 這行代碼。

3.有傳回值的預存程序 

-------------建立名為GetUserAccount的預存程序----------------create Procedure inUserAccountReasinsert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(10,10,‘2013-01-02‘,10)return @@rowcountgo-------------執行上面的預存程序----------------exec inUserAccountRe

 解釋：這裡的@@rowcount為執行預存程序影響的行數，執行的結果是不僅插入了一條資料，還返回了一個值即 return value =1  ，這個可以在程式中擷取，稍後在c#調用預存程序中會有說到。

4.有輸入參數和輸出參數的預存程序 

-------------建立名為GetUserAccount的預存程序----------------create Procedure GetUserAccountRe@UserName nchar(20),@UserID int outputasif(@UserName>5)select @UserID=COUNT(*) from UserAccount where UserID>25elseset @UserID=1000go-------------執行上面的預存程序----------------exec GetUserAccountRe ‘7‘,null

 解釋：@UserName為輸入參數，@UserID為輸出參數。 運行結果為@userID為COOUT（*）即 =1。

5. 同時具有傳回值、輸入參數、輸出參數的預存程序 

-------------建立名為GetUserAccount的預存程序----------------create Procedure GetUserAccountRe1@UserName nchar(20),@UserID int outputasif(@UserName>5)select @UserID=COUNT(*) from UserAccount where UserID>25elseset @UserID=1000return @@rowcountgo-------------執行上面的預存程序----------------exec GetUserAccountRe1 ‘7‘,null

 結果：@userID為COOUT（*）即 =1，Retun Value=1。

6.同時返回參數和記錄集的預存程序 

-------------建立名為GetUserAccount的預存程序----------------create Procedure GetUserAccountRe2@UserName nchar(20),@UserID int outputasif(@UserName>5)select @UserID=COUNT(*) from UserAccount where UserID>25elseset @UserID=1000select * from UserAccountreturn @@rowcountgo-------------執行上面的預存程序----------------exec GetUserAccountRe2 ‘7‘,null

 結果：返回執行 select * from UserAccount 這句代碼的結果集，同時@userID為COOUT（*）即 =1，Retun Value=9。 

7.返回多個記錄集的預存程序 

-------------建立名為GetUserAccount的預存程序----------------create Procedure GetUserAccountRe3asselect * from UserAccountselect * from UserAccount where UserID>5go-------------執行上面的預存程序----------------exec GetUserAccountRe3

 結果：返回兩個結果集，一個為 select * from UserAccount，另一個為 select * from UserAccount where UserID>5 。

小結：上面我們建立了各式的預存程序，下面看我們在c#中怎樣調用這些預存程序。

 c#調用預存程序

 這裡調用的預存程序為上面我寫的那些各式各樣的預存程序。

public partial class ProcedureTest : System.Web.UI.Page    {        public static  string conn = ConfigurationManager.ConnectionStrings["StuRelationDBConnectionString"].ConnectionString;        public SqlConnection con = new SqlConnection(conn);        protected void Page_Load(object sender, EventArgs e)        {            runGetUserAccountRe3();        }        //只返回單一記錄集的預存程序GetUserAccount        public void runGetUserAccount()        {            SqlDataAdapter dp = new SqlDataAdapter(common("GetUserAccount"));            DataSet ds = new DataSet();            // 填充dataset             dp.Fill(ds);            rpt.DataSource = ds;            rpt.DataBind();        }        //沒有輸入輸出的預存程序inUserAccount        public void runinUserAccount()        {                        con.Open();            Label1.Text = common("inUserAccount").ExecuteNonQuery().ToString();            con.Close();        }        //有傳回值的預存程序inUserAccountRe        public void runinUserAccountRe()        {            // 建立參數             SqlCommand cmd = common("inUserAccountRe");            IDataParameter[] parameters = {                  new SqlParameter("rval", SqlDbType.Int,4)              };            // 將參數類型設定為 傳回值類型             parameters[0].Direction = ParameterDirection.ReturnValue;            // 添加參數             cmd.Parameters.Add(parameters[0]);            con.Open();            // 執行預存程序並返回影響的行數             Label1.Text = cmd.ExecuteNonQuery().ToString();            con.Close();            // 顯示影響的行數和傳回值             Label1.Text += "-" + parameters[0].Value.ToString();        }        //有輸入參數和輸出參數的預存程序        public void runGetUserAccountRe()        {            SqlCommand cmd = common("GetUserAccountRe");            // 建立參數             IDataParameter[] parameters = {                  new SqlParameter("@UserName", SqlDbType.NChar,20) ,                  new SqlParameter("@UserID", SqlDbType.Int) ,             };            // 設定參數類型             parameters[0].Value = "7";              parameters[1].Direction = ParameterDirection.Output;  // 設定為輸出參數             // 添加參數             cmd.Parameters.Add(parameters[0]);            cmd.Parameters.Add(parameters[1]);            con.Open();            // 執行預存程序並返回影響的行數             Label1.Text = cmd.ExecuteNonQuery().ToString();            con.Close();                       // 顯示影響的行數和輸出參數             Label1.Text += "-" + parameters[1].Value.ToString();                   }        //同時具有傳回值、輸入參數、輸出參數的預存程序GetUserAccountRe1        public void runGetUserAccountRe1()        {            SqlCommand cmd = common("GetUserAccountRe1");            // 建立參數             IDataParameter[] parameters = {                  new SqlParameter("@UserName", SqlDbType.NChar,20) ,                  new SqlParameter("@UserID", SqlDbType.Int) ,                 new SqlParameter("rval", SqlDbType.Int,4)              };            // 設定參數類型             parameters[0].Value = "7";            parameters[1].Direction = ParameterDirection.Output;  // 設定為輸出參數             parameters[2].Direction = ParameterDirection.ReturnValue;  //設定為傳回值            // 添加參數             cmd.Parameters.Add(parameters[0]);            cmd.Parameters.Add(parameters[1]);            cmd.Parameters.Add(parameters[2]);            con.Open();            // 執行預存程序並返回影響的行數             Label1.Text = cmd.ExecuteNonQuery().ToString();            con.Close();            // 顯示影響的行數和輸出參數             Label1.Text += "-輸出參數為：" + parameters[1].Value.ToString();            Label1.Text += "-傳回值為：" + parameters[2].Value.ToString();        }        //同時返回參數和記錄集的預存程序GetUserAccountRe2        public void runGetUserAccountRe2()        {            SqlCommand cmd = common("GetUserAccountRe2");            // 建立參數             IDataParameter[] parameters = {                  new SqlParameter("@UserName", SqlDbType.NChar,20) ,                  new SqlParameter("@UserID", SqlDbType.Int) ,                 new SqlParameter("rval", SqlDbType.Int,4)              };            // 設定參數類型             parameters[0].Value = "7";            parameters[1].Direction = ParameterDirection.Output;  // 設定為輸出參數             parameters[2].Direction = ParameterDirection.ReturnValue;  //設定為傳回值            // 添加參數             cmd.Parameters.Add(parameters[0]);            cmd.Parameters.Add(parameters[1]);            cmd.Parameters.Add(parameters[2]);            con.Open();            // 執行預存程序並返回影響的行數             Label1.Text = cmd.ExecuteNonQuery().ToString();            DataSet ds = new DataSet();            SqlDataAdapter dt = new SqlDataAdapter(cmd);            dt.Fill(ds);            rpt.DataSource = ds;            rpt.DataBind();            con.Close();            // 顯示影響的行數和輸出參數             Label1.Text += "-輸出參數為：" + parameters[1].Value.ToString();            Label1.Text += "-傳回值為：" + parameters[2].Value.ToString();        }        //返回多個記錄集的預存程序        public void runGetUserAccountRe3()        {            DataSet ds = new DataSet();            SqlDataAdapter dt = new SqlDataAdapter(common("GetUserAccountRe3"));            dt.Fill(ds);            rpt1.DataSource = ds.Tables[0].DefaultView;            rpt1.DataBind();            rpt2.DataSource = ds.Tables[1].DefaultView;            rpt2.DataBind();        }                public SqlCommand common(string proName)        {                        SqlCommand cmd = new SqlCommand();            // 設定sql串連             cmd.Connection = con;                        // 如果執行語句             cmd.CommandText = proName;            // 指定執行語句為預存程序             cmd.CommandType = CommandType.StoredProcedure;            return cmd;        }    }

附帶SQLServer資料庫的一些全域變數

select APP_NAME ( ) as w --當前會話的應用程式select @@IDENTITY   --返回最後插入的標識值 select USER_NAME()    --返回使用者資料庫使用者名稱SELECT @@CONNECTIONS  --返回自上次SQL啟動以來串連或試圖串連的次數。 SELECT GETDATE() --目前時間 SELECT @@CPU_BUSY/100  --返回自上次啟動SQL 以來 CPU 的工作時間，單位為毫秒USE tempdb SELECT @@DBTS  as w  --為當前資料庫返回當前 timestamp 資料類型的值。這一 timestamp 值保證在資料庫中是唯一的。 select @@IDENTITY as w --返回最後插入的標識值 SELECT @@IDLE  as w  --返回SQL自上次啟動後閑置的時間，單位為毫秒 SELECT @@IO_BUSY AS w   --返回SQL自上次啟動後用於執行輸入和輸出操作的時間，單位為毫秒 SELECT @@LANGID AS w   --返回當前所使用語言的本地語言識別項(ID)。 SELECT @@LANGUAGE AS w   --返回當前使用的語言名 SELECT @@LOCK_TIMEOUT as w  --當前會話的當前鎖逾時設定，單位為毫秒。 SELECT @@MAX_CONNECTIONS  as w  --返回SQL上允許的同時使用者串連的最大數。返回的數不必為當前配置的數值 EXEC sp_configure  --顯示當前伺服器的全域配置設定 SELECT @@MAX_PRECISION as w --返回 decimal 和 numeric 資料類型所用的精度層級，即該伺服器中當前設定的精度。預設最大精度38。 select @@OPTIONS  as w  --返回當前 SET 選項的資訊。 SELECT @@PACK_RECEIVED as w  --返回SQL自啟動後從網路上讀取的輸入資料包數目。 SELECT @@PACK_SENT as w  --返回SQ自上次啟動後寫到網路上的輸出資料包數目。 SELECT @@PACKET_ERRORS as w  --返回自SQL啟動後，在SQL串連上發生的網路資料包錯誤數。 SELECT @@SERVERNAME as w --返回運行SQL伺服器名稱。 SELECT @@SERVICENAME  as w --返回SQL正在其下啟動並執行註冊表鍵名 SELECT @@TIMETICKS  as w --返回SQL伺服器一刻度的微秒數 SELECT @@TOTAL_ERRORS AS w  --返回 SQL伺服器自啟動後，所遇到的磁碟讀/寫錯誤數。 SELECT @@TOTAL_READ  as w  --返回 SQL伺服器自啟動後讀取磁碟的次數。 SELECT @@TOTAL_WRITE as w  --返回SQL伺服器自啟動後寫入磁碟的次數。 SELECT @@TRANCOUNT  as w  --返回當前串連的活動事務數。 SELECT @@VERSION as w  --返回SQL伺服器安裝的日期、版本和處理器類型。 

預存程序詳解 -SQLServer