程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 基於存儲過程的詳細介紹

基於存儲過程的詳細介紹

編輯:關於SqlServer

存儲過程簡介

--------------------------------------------------------------------------------什麼是存儲過程:存儲過程可以說是一個記錄集吧,它是由一些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 前後使用適當的定界符。

3.@parameter: 存儲過程的參數。可以有一個或多個。用戶必須在執行過程時提供每個所聲明參數的值(除非定義了該參數的默認值)。存儲過程最多可以有 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 GetUserAccount
as
select * from UserAccount
go

-------------執行上面的存儲過程----------------
exec GetUserAccount

 結果:相當於運行 select * from UserAccount 這行代碼,結果為整個表的數據。

2.沒有輸入輸出的存儲過程
復制代碼 代碼如下:
-------------創建名為GetUserAccount的存儲過程----------------

create Procedure inUserAccount
as
insert 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 inUserAccountRe
as
insert into UserAccount (UserName,[PassWord],RegisterTime,RegisterIP) values(10,10,'2013-01-02',10)
return @@rowcount
go

-------------執行上面的存儲過程----------------

exec inUserAccountRe

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

4.有輸入參數和輸出參數的存儲過程
復制代碼 代碼如下:
-------------創建名為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

-------------執行上面的存儲過程----------------

exec GetUserAccountRe '7',null

解釋:@UserName為輸入參數,@UserID為輸出參數。 運行結果為@userID為COOUT(*)即 =1。

5. 同時具有返回值、輸入參數、輸出參數的存儲過程
復制代碼 代碼如下:
-------------創建名為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

-------------執行上面的存儲過程----------------

exec GetUserAccountRe1 '7',null

結果:@userID為COOUT(*)即 =1,Retun Value=1。

6.同時返回參數和記錄集的存儲過程
復制代碼 代碼如下:
-------------創建名為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

-------------執行上面的存儲過程----------------

exec GetUserAccountRe2 '7',null

 結果:返回執行 select * from UserAccount 這句代碼的結果集,同時@userID為COOUT(*)即 =1,Retun Value=9。

7.返回多個記錄集的存儲過程
復制代碼 代碼如下:
-------------創建名為GetUserAccount的存儲過程----------------

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

-------------執行上面的存儲過程----------------

exec GetUserAccountRe3

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

小結:上面我們創建了各式的存儲過程,下面看我們在c#中怎樣調用這些存儲過程。

 c#調用存儲過程

 這裡調用的存儲過程為上面我寫的那些各式各樣的存儲過程。
 復制代碼 代碼如下:
 View Code

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數據庫的一些全局變量
復制代碼 代碼如下:
View Code

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服務器安裝的日期、版本和處理器類型。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved