西西軟件下載最安全的下載網(wǎng)站、值得信賴的軟件下載站！

首頁 → 西西教程 → 數(shù)據(jù)庫教程 → 什么是存儲過程？Sql 存儲過程知識詳解

什么是存儲過程？Sql 存儲過程知識詳解

相關(guān)軟件相關(guān)文章發(fā)表評論 來源：西西整理時間：2013/1/2 20:01:07字體大�。�A-A+

作者：西西點擊：0次評論：3次標(biāo)簽： 存儲過程

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

存儲過程的好處：

1.由于數(shù)據(jù)庫執(zhí)行動作時，是先編譯后執(zhí)行的。然而存儲過程是一個編譯過的代碼塊，所以執(zhí)行效率要比T-SQL語句高。

2.一個存儲過程在程序在網(wǎng)絡(luò)中交互時可以替代大堆的T-SQL語句，所以也能降低網(wǎng)絡(luò)的通信量，提高通信速率。

3.通過存儲過程能夠使沒有權(quán)限的用戶在控制之下間接地存取數(shù)據(jù)庫，從而確保數(shù)據(jù)的安全。

小結(jié)：總之存儲過程是好東西，在做項目時屬于必備利器，下面介紹存儲過程的基本語法。

存儲過程的語法和參數(shù)講解

存儲過程的一些基本語法：

--------------創(chuàng)建存儲過程-----------------

CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]

[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

--------------調(diào)用存儲過程-----------------

EXECUTE Procedure_name '' --存儲過程如果有參數(shù)，后面加參數(shù)格式為：@參數(shù)名=value，也可直接為參數(shù)值value

--------------刪除存儲過程-----------------

drop procedure procedure_name    --在存儲過程中能調(diào)用另外一個存儲過程，而不能刪除另外一個存儲過程
創(chuàng)建存儲過程的參數(shù)：
1.procedure_name ：存儲過程的名稱，在前面加#為局部臨時存儲過程，加##為全局臨時存儲過程。

2.; number：是可選的整數(shù)，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應(yīng)用程序使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標(biāo)識符，則數(shù)字不應(yīng)包含在標(biāo)識符中，只應(yīng)在 procedure_name 前后使用適當(dāng)?shù)亩ń绶?nbsp;

3.@parameter： 存儲過程的參數(shù)�？梢杂幸粋�或多個。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認值）。存儲過程最多可以有 2.100 個參數(shù)。 
使用 @ 符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標(biāo)識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其它過程中。默認情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱。有關(guān)更多信息，請參見 EXECUTE。 

4.data_type：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括 text、ntext 和 image）均可以用作存儲過程的參數(shù)。不過，cursor 數(shù)據(jù)類型只能用于 OUTPUT 參數(shù)。如果指定的數(shù)據(jù)類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關(guān)鍵字。有關(guān) sql server 提供的數(shù)據(jù)類型及其語法的更多信息，請參見數(shù)據(jù)類型。 
說明 對于可以是 cursor 數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。 

5.VARYING： 指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。 
6.default： 參數(shù)的默認值。如果定義了默認值，不必指定該參數(shù)的值即可執(zhí)行過程。默認值必須是常量或 NULL。如果過程將對該參數(shù)使用 LIKE 關(guān)鍵字，那么默認值中可以包含通配符（%、_、[] 和 [^]）。
7.OUTPUT ：表明參數(shù)是返回參數(shù)。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用過程。Text、ntext 和 image 參數(shù)可用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符。 
8.RECOMPILE: 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計劃時，請使用 RECOMPILE 選項。
9.ENCRYPTION: 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復(fù)制的一部分發(fā)布。 說明 在升級過程中，SQL Server 利用存儲在 syscomments 中的加密注釋來重新創(chuàng)建加密過程。 
10.FOR REPLICATION :指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲過程。.使用 FOR REPLICATION 選項創(chuàng)建的存儲過程可用作存儲過程篩選，且只能在復(fù)制過程中執(zhí)行。本選項不能和 WITH RECOMPILE 選項一起使用。 
11.AS :指定過程要執(zhí)行的操作。
12.sql_statement :過程中要包含的任意數(shù)目和類型的 Transact-SQL 語句。但有一些限制。
小結(jié)：看過這些基本語法后，下面我就根據(jù)語法創(chuàng)建各式的存儲過程。
 創(chuàng)建存儲過程
PSE: collapse; FONT: 14px/21px verdana, 'ms song', Arial, Helvetica, sans-serif; WHITE-SPACE: normal; ORPHANS: 2; LETTER-SPACING: normal; HEIGHT: 203px; COLOR: rgb(57,57,57); BORDER-TOP: rgb(192,192,192) 1px solid; BORDER-RIGHT: rgb(192,192,192) 1px solid; WORD-SPACING: 0px; PADDING-TOP: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px">
UserAccount
UserIDUserNamePassWordRegisterTimeRegisterIP
126                   6                   2012-12-316
185                   5                   2013-01-015
191                   1                   2013-01-011
202                   2                   2013-01-012
213                   3                   2013-01-013
224                   4                   2013-01-014
235                   5                   2013-01-015
257                   7                   2013-01-017
268                   8                   2013-01-018
NULLNULLNULLNULLNULL
針對上面的表，我使用存儲過程對它做一些操作：
1. 只返回單一記錄集的存儲過程 
-------------創(chuàng)建名為GetUserAccount的存儲過程----------------
create Procedure GetUserAccount
as
select * from UserAccount
go

-------------執(zhí)行上面的存儲過程----------------
exec GetUserAccount
 結(jié)果：相當(dāng)于運行 select * from UserAccount 這行代碼，結(jié)果為整個表的數(shù)據(jù)。
2.沒有輸入輸出的存儲過程 
-------------創(chuàng)建名為GetUserAccount的存儲過程----------------

create Procedure inUserAccount
as
insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9)
go

-------------執(zhí)行上面的存儲過程----------------

exec inUserAccount
 結(jié)果：相當(dāng)于運行 insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(9,9,'2013-01-02',9) 這行代碼。
3.有返回值的存儲過程 
-------------創(chuàng)建名為GetUserAccount的存儲過程----------------

create Procedure inUserAccountRe
as
insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(10,10,'2013-01-02',10)
return @@rowcount
go

-------------執(zhí)行上面的存儲過程----------------

exec inUserAccountRe
 解釋：這里的@@rowcount為執(zhí)行存儲過程影響的行數(shù)，執(zhí)行的結(jié)果是不僅插入了一條數(shù)據(jù)，還返回了一個值即 return value =1  ，這個可以在程序中獲取，稍后在c#調(diào)用存儲過程中會有說到。
4.有輸入?yún)?shù)和輸出參數(shù)的存儲過程 
-------------創(chuàng)建名為GetUserAccount的存儲過程----------------

create Procedure GetUserAccountRe
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
go

-------------執(zhí)行上面的存儲過程----------------

exec GetUserAccountRe '7',null
 解釋：@UserName為輸入?yún)?shù)，@UserID為輸出參數(shù)。 運行結(jié)果為@userID為COOUT（*）即 =1。
5. 同時具有返回值、輸入?yún)?shù)、輸出參數(shù)的存儲過程 
-------------創(chuàng)建名為GetUserAccount的存儲過程----------------

create Procedure GetUserAccountRe1
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
return @@rowcount
go

-------------執(zhí)行上面的存儲過程----------------

exec GetUserAccountRe1 '7',null
 結(jié)果：@userID為COOUT（*）即 =1，Retun Value=1。
6.同時返回參數(shù)和記錄集的存儲過程 
-------------創(chuàng)建名為GetUserAccount的存儲過程----------------

create Procedure GetUserAccountRe2
@UserName nchar(20),
@UserID int output
as
if(@UserName>5)
select @UserID=COUNT(*) from UserAccount where UserID>25
else
set @UserID=1000
select * from UserAccount
return @@rowcount
go

-------------執(zhí)行上面的存儲過程----------------

exec GetUserAccountRe2 '7',null
 結(jié)果：返回執(zhí)行 select * from UserAccount 這句代碼的結(jié)果集，同時@userID為COOUT（*）即 =1，Retun Value=9。 
7.返回多個記錄集的存儲過程 
-------------創(chuàng)建名為GetUserAccount的存儲過程----------------

create Procedure GetUserAccountRe3
as
select * from UserAccount
select * from UserAccount where UserID>5
go

-------------執(zhí)行上面的存儲過程----------------

exec GetUserAccountRe3
 結(jié)果：返回兩個結(jié)果集，一個為 select * from UserAccount，另一個為 select * from UserAccount where UserID>5 。
小結(jié)：上面我們創(chuàng)建了各式的存儲過程，下面看我們在c#中怎樣調(diào)用這些存儲過程。
 這里調(diào)用的存儲過程為上面我寫的那些各式各樣的存儲過程。
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()
        {
            // 創(chuàng)建參數(shù) 
            SqlCommand cmd = common("inUserAccountRe");
            IDataParameter[] parameters = { 
                 new SqlParameter("rval", SqlDbType.Int,4) 
             };
            // 將參數(shù)類型設(shè)置為 返回值類型 
            parameters[0].Direction = ParameterDirection.ReturnValue;
            // 添加參數(shù) 
            cmd.Parameters.Add(parameters[0]);
            con.Open();
            // 執(zhí)行存儲過程并返回影響的行數(shù) 
            Label1.Text = cmd.ExecuteNonQuery().ToString();
            con.Close();
            // 顯示影響的行數(shù)和返回值 
            Label1.Text += "-" + parameters[0].Value.ToString();
        }

        //有輸入?yún)?shù)和輸出參數(shù)的存儲過程
        public void runGetUserAccountRe()
        {
            SqlCommand cmd = common("GetUserAccountRe");
            // 創(chuàng)建參數(shù) 
            IDataParameter[] parameters = { 
                 new SqlParameter("@UserName", SqlDbType.NChar,20) , 
                 new SqlParameter("@UserID", SqlDbType.Int) ,
             };
            // 設(shè)置參數(shù)類型 
            parameters[0].Value = "7";  
            parameters[1].Direction = ParameterDirection.Output;  // 設(shè)置為輸出參數(shù) 
            // 添加參數(shù) 
            cmd.Parameters.Add(parameters[0]);
            cmd.Parameters.Add(parameters[1]);
            con.Open();
            // 執(zhí)行存儲過程并返回影響的行數(shù) 
            Label1.Text = cmd.ExecuteNonQuery().ToString();
            con.Close();           
            // 顯示影響的行數(shù)和輸出參數(shù) 
            Label1.Text += "-" + parameters[1].Value.ToString();
           
        }

        //同時具有返回值、輸入?yún)?shù)、輸出參數(shù)的存儲過程GetUserAccountRe1
        public void runGetUserAccountRe1()
        {
            SqlCommand cmd = common("GetUserAccountRe1");
            // 創(chuàng)建參數(shù) 
            IDataParameter[] parameters = { 
                 new SqlParameter("@UserName", SqlDbType.NChar,20) , 
                 new SqlParameter("@UserID", SqlDbType.Int) ,
                 new SqlParameter("rval", SqlDbType.Int,4) 
             };
            // 設(shè)置參數(shù)類型 
            parameters[0].Value = "7";
            parameters[1].Direction = ParameterDirection.Output;  // 設(shè)置為輸出參數(shù) 
            parameters[2].Direction = ParameterDirection.ReturnValue;  //設(shè)置為返回值
            // 添加參數(shù) 
            cmd.Parameters.Add(parameters[0]);
            cmd.Parameters.Add(parameters[1]);
            cmd.Parameters.Add(parameters[2]);
            con.Open();
            // 執(zhí)行存儲過程并返回影響的行數(shù) 
            Label1.Text = cmd.ExecuteNonQuery().ToString();
            con.Close();
            // 顯示影響的行數(shù)和輸出參數(shù) 
            Label1.Text += "-輸出參數(shù)為：" + parameters[1].Value.ToString();
            Label1.Text += "-返回值為：" + parameters[2].Value.ToString();

        }

        //同時返回參數(shù)和記錄集的存儲過程GetUserAccountRe2
        public void runGetUserAccountRe2()
        {
            SqlCommand cmd = common("GetUserAccountRe2");
            // 創(chuàng)建參數(shù) 
            IDataParameter[] parameters = { 
                 new SqlParameter("@UserName", SqlDbType.NChar,20) , 
                 new SqlParameter("@UserID", SqlDbType.Int) ,
                 new SqlParameter("rval", SqlDbType.Int,4) 
             };
            // 設(shè)置參數(shù)類型 
            parameters[0].Value = "7";
            parameters[1].Direction = ParameterDirection.Output;  // 設(shè)置為輸出參數(shù) 
            parameters[2].Direction = ParameterDirection.ReturnValue;  //設(shè)置為返回值
            // 添加參數(shù) 
            cmd.Parameters.Add(parameters[0]);
            cmd.Parameters.Add(parameters[1]);
            cmd.Parameters.Add(parameters[2]);
            con.Open();
            // 執(zhí)行存儲過程并返回影響的行數(shù) 
            Label1.Text = cmd.ExecuteNonQuery().ToString();
            DataSet ds = new DataSet();
            SqlDataAdapter dt = new SqlDataAdapter(cmd);
            dt.Fill(ds);
            rpt.DataSource = ds;
            rpt.DataBind();
            con.Close();
            // 顯示影響的行數(shù)和輸出參數(shù) 
            Label1.Text += "-輸出參數(shù)為：" + 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();
            // 設(shè)置sql連接 
            cmd.Connection = con;            
            // 如果執(zhí)行語句 
            cmd.CommandText = proName;
            // 指定執(zhí)行語句為存儲過程 
            cmd.CommandType = CommandType.StoredProcedure;
            return cmd;
        }
    }
附帶SQLServer數(shù)據(jù)庫的一些全局變量
select APP_NAME ( ) as w --當(dāng)前會話的應(yīng)用程序

select @@IDENTITY   --返回最后插入的標(biāo)識值 
select USER_NAME()    --返回用戶數(shù)據(jù)庫用戶名

SELECT @@CONNECTIONS  --返回自上次SQL啟動以來連接或試圖連接的次數(shù)。 
SELECT GETDATE() --當(dāng)前時間 
SELECT @@CPU_BUSY/100  --返回自上次啟動SQL 以來 CPU 的工作時間，單位為毫秒

USE tempdb SELECT @@DBTS  as w  --為當(dāng)前數(shù)據(jù)庫返回當(dāng)前 timestamp 數(shù)據(jù)類型的值。這一 timestamp 值保證在數(shù)據(jù)庫中是唯一的。 
select @@IDENTITY as w --返回最后插入的標(biāo)識值 
SELECT @@IDLE  as w  --返回SQL自上次啟動后閑置的時間，單位為毫秒 
SELECT @@IO_BUSY AS w   --返回SQL自上次啟動后用于執(zhí)行輸入和輸出操作的時間，單位為毫秒 
SELECT @@LANGID AS w   --返回當(dāng)前所使用語言的本地語言標(biāo)識符(ID)。 
SELECT @@LANGUAGE AS w   --返回當(dāng)前使用的語言名 
SELECT @@LOCK_TIMEOUT as w  --當(dāng)前會話的當(dāng)前鎖超時設(shè)置，單位為毫秒。 
SELECT @@MAX_CONNECTIONS  as w  --返回SQL上允許的同時用戶連接的最大數(shù)。返回的數(shù)不必為當(dāng)前配置的數(shù)值 
EXEC sp_configure  --顯示當(dāng)前服務(wù)器的全局配置設(shè)置 
SELECT @@MAX_PRECISION as w --返回 decimal 和 numeric 數(shù)據(jù)類型所用的精度級別，即該服務(wù)器中當(dāng)前設(shè)置的精度。默認最大精度38。 
select @@OPTIONS  as w  --返回當(dāng)前 SET 選項的信息。 
SELECT @@PACK_RECEIVED as w  --返回SQL自啟動后從網(wǎng)絡(luò)上讀取的輸入數(shù)據(jù)包數(shù)目。 
SELECT @@PACK_SENT as w  --返回SQ自上次啟動后寫到網(wǎng)絡(luò)上的輸出數(shù)據(jù)包數(shù)目。 
SELECT @@PACKET_ERRORS as w  --返回自SQL啟動后，在SQL連接上發(fā)生的網(wǎng)絡(luò)數(shù)據(jù)包錯誤數(shù)。 
SELECT @@SERVERNAME as w --返回運行SQL服務(wù)器名稱。 
SELECT @@SERVICENAME  as w --返回SQL正在其下運行的注冊表鍵名 
SELECT @@TIMETICKS  as w --返回SQL服務(wù)器一刻度的微秒數(shù) 
SELECT @@TOTAL_ERRORS AS w  --返回 SQL服務(wù)器自啟動后，所遇到的磁盤讀/寫錯誤數(shù)。 
SELECT @@TOTAL_READ  as w  --返回 SQL服務(wù)器自啟動后讀取磁盤的次數(shù)。 
SELECT @@TOTAL_WRITE as w  --返回SQL服務(wù)器自啟動后寫入磁盤的次數(shù)。 
SELECT @@TRANCOUNT  as w  --返回當(dāng)前連接的活動事務(wù)數(shù)。 
SELECT @@VERSION as w  --返回SQL服務(wù)器安裝的日期、版本和處理器類型。 

              

              

        

    


    

        

            
    
            
            
        
      
    
    
      
    
        
    相關(guān)文章
    
          
      
    
      
    
        
    相關(guān)評論
    
        
    閱讀本文后您有什么感想? 已有人給出評價!
    
        
    
                
      
                    
    • 8   喜歡喜歡 
      • 
                    
    • 3    頂頂
      • 
                    
    • 1     難過難過
      • 
                    
    • 5    囧囧
      • 
                    
    • 3    圍觀圍觀
      • 
                     
    • 2    無聊無聊
      • 
                  
    
                  
    
        
    
            
            
    
                    

    
  
    
    
    熱門評論
    
    
    
      
    
    
  
    
  
  
  
    
    
    最新評論
    
    
    
      
    
    
  
  
    
  
  
    
    
    
      
    發(fā)表評論 查看所有評論(3)
    
      
      
    昵稱: 
        
        
        
      
    
      
    
      表情: 
       高興
       可
       汗
       我不要
       害羞
       好
       下下下
       送花
       屎
       親親
       
    
      
      
    
      字數(shù): 0/500
      (您的評論需要經(jīng)過審核才能顯示)