• 展開
    湖北國聯(lián)計算機科技有限公司
  • 首頁HOME
  • 公司簡介INTRODUCTION
  • 安全防御DEFENSE
  • 軟件開發(fā)SOFTWARE
  • 物聯(lián)網(wǎng)IOT
  • 運行維護SRE
  • 成功案例CASE
  • 聯(lián)系我們CONTACT
  • Software Technology Sharing |技術(shù)分享

    軟件開發(fā)學(xué)習(xí)之—實例解析SQL存儲過程
    來源:荊州網(wǎng)站建設(shè) 時間:2017-05-19

    存儲過程(Stored Procedure),是一組為了完成特定功能的SQL 語句,類似一門程序設(shè)計語言,也包括了數(shù)據(jù)類型、流程控制、輸入和輸出和它自己的函數(shù)庫。存儲過程可以說是一個記錄集,它是由一些T-SQL語句組成的代碼塊,這些T-SQL語句代碼像一個方法一樣實現(xiàn)一些功能(對單表或多表的增刪改查),然后再給這個代碼塊取一個名字,在用到這個功能的時候調(diào)用他就行了。不過SQL存儲過程對于一些初學(xué)者來說還是比較抽象難理解的,因此本文將由淺至深地剖析SQL存儲過程,幫助你學(xué)習(xí)它。


    存儲過程的優(yōu)點

    1.存儲過程只在創(chuàng)造時進行編譯,以后每次執(zhí)行存儲過程都不需再重新編譯,而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數(shù)據(jù)庫執(zhí)行速度,效率要比T-SQL語句高。


    2.當對數(shù)據(jù)庫進行復(fù)雜操作時,可將此復(fù)雜操作用存儲過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。


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


    4.存儲過程可以重復(fù)使用,可減少數(shù)據(jù)庫開發(fā)人員的工作量。


    5.安全性高,可設(shè)定只有某些用戶才具有對指定存儲過程的使用權(quán)


    存儲過程基本語法

    --------------創(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;1orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符,則數(shù)字不應(yīng)包含在標識符中,只應(yīng)在 procedure_name 前后使用適當?shù)亩ń绶?/span>


    3.@parameter存儲過程的參數(shù)??梢杂幸粋€或多個。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值(除非定義了該參數(shù)的默認值)。存儲過程最多可以有 2.100 個參數(shù)。


    使用 @ 符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標識符的規(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)容可以變化)。僅適用于游標參數(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ù)可以是游標占位符。


    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 語句。但有一些限制。


    實例操作學(xué)習(xí)

    下面通過表Student來具體了解一下存儲過程,因為是要了解存儲過程的簡單用法,所以例子很簡單。



    無參數(shù)存儲過程:

    選出Student表中的所有信息

    create proc StuProc

    as     //此處 as 不可以省略不寫

    begin  //begin end 是一對,不可以只寫其中一個,但可以都不寫

    select S#,Sname,Sage,Ssex from student

    end

    go


    有參數(shù)存儲過程:

    全局變量

    全局變量也稱為外部變量,是在函數(shù)的外部定義的,它的作用域為從變量定義處開始,到本程序文件的末尾。


    選出指定姓名的學(xué)生信息:

    create proc StuProc

    @sname varchar(100)  

    as

    begin

    select S#,Sname,Sage,Ssex from studentwhere sname=@sname

    end

    go


    exec StuProc '趙雷'   //執(zhí)行語句


    上面是在外部給變量賦值,也可以在內(nèi)部直接給變量設(shè)置默認值

    create proc StuProc

    @sname varchar(100)='趙雷'

    as

    begin

    select S#,Sname,Sage,Ssex from studentwhere sname=@sname

    end

    go


    exec StuProc


    也可以把變量的內(nèi)容輸出,使用output

    create proc StuProc

    @sname varchar(100),

    @IsRight int  output //傳出參數(shù)

    as

    if exists (select S#,Sname,Sage,Ssex fromstudent where sname=@sname)

    set @IsRight =1

    else

    set @IsRight=0

    go


    declare @IsRight int

    exec StuProc '趙雷' ,@IsRight output

    select @IsRight

    以上是全局變量,下面來了解局部變量


    局部變量:

    局部變量也稱為內(nèi)部變量。局部變量是在函數(shù)內(nèi)作定義說明的。其作用域僅限于函數(shù)內(nèi)部,離開該函數(shù)后再使用這種變量是非法的。


    局部變量的定義:

    必須先用Declare命令定以后才可以使用,declare{@變量名數(shù)據(jù)類型}


    局部變量的賦值方法:

    set{@變量名=表達式}或者select{@變量名=表達式}


    局部變量的顯示:

    create proc StuProc

    as

    declare @sname varchar(100)

    set @sname='趙雷'

    select S#,Sname,Sage,Ssex from studentwhere sname=@sname

    go


    exec StuProc


    那如果是要把局部變量的數(shù)據(jù)顯示出來怎么辦呢?

    create proc StuProc

    as

    declare @sname varchar(100)

    set @sname=(select Sname from student whereS#=01)

    select @sname

    go


    exec StuProc


    更詳細的實例操作學(xué)習(xí)

    比如,在SQL Server查詢編輯器窗口中用CREATE PROCEDURE語句創(chuàng)建存儲過程PROC_InsertEmployee,用于實現(xiàn)向員工信息表(tb_Employee)中添加信息,同時生成自動編號。其SQL語句如下:

    IF EXISTS (SELECT name  

     FROM   sysobjects  

     WHERE  name ='Proc_InsertEmployee'  

     AND          type = 'P')

    DROP PROCEDURE Proc_InsertEmployee

    GO

    CREATE PROCEDURE Proc_InsertEmployee

    @PName nvarchar(50),

    @PSex nvarchar(4),

    @PAge int,

    @PWage money

    AS

    begin

     declare @PID nvarchar(50)

     select @PID=Max(員工編號) from tb_Employee

     if(@PID is null)

         set @PID='P1001'

     else

         set @PID='P'+cast(cast(substring(@PID,2,4) as int)+1 as nvarchar(50))

     begin

         insert into tb_Employee values(@PID,@PName,@PSex,@PAge,@PWage)

     end

    end

    go


    存儲過程的修改:

    創(chuàng)建完存儲過程之后,如果需要重新修改存儲過程的功能及參數(shù),可以在SQL Server 2005中通過以下兩種方法進行修改:一種是用Microsoft SQL Server Mangement修改存儲過程;另外一種是用T-SQL語句修改存儲過程。


    使用Microsoft SQL Server Mangement修改存儲過程,步驟如下:

       

    1)在SQL Server Management Studio的“對象資源管理器”中,選擇要修改存儲過程所在的數(shù)據(jù)庫(如:db_18),然后在該數(shù)據(jù)庫下,選擇“可編程性”。

       

    2)打開“存儲過程”文件夾,右鍵單擊要修改的存儲過程(如:PROC_SEINFO),在彈出的快捷菜單中選擇“修改”命令,將會出現(xiàn)查詢編輯器窗口。用戶可以在此窗口中編輯T-SQL代碼,完成編輯后,單擊工具欄中的“執(zhí)行(X)”按鈕,執(zhí)行修改代碼。用戶可以在查詢編輯器下方的Message窗口中看到執(zhí)行結(jié)果信息。


    使用Transact-SQL修改存儲過程:


    使用ALTER PROCEDURE語句修改存儲過程,它不會影響存儲過程的權(quán)限設(shè)定,也不會更改存儲過程的名稱。


    語法:

    ALTER PROC [ EDURE ] procedure_name [ ;number ]

       [{ @parameter data_type }  

           [ VARYING ] [ = default ] [ OUTPUT ]

       ][ ,...n ]  

    [ WITH

       {RECOMPILE | ENCRYPTION

          | RECOMPILE , ENCRYPTION   }  

    ]

    [ FOR REPLICATION ]  

    AS

      sql_statement [ ...n ]


    參數(shù)說明:

    procedure_name:是要更改的存儲過程的名稱。


    交叉鏈接:關(guān)于ALTER PROCEDURE語句的其他參數(shù)與CREATE PROCEDURE語句相同,可參見上面的“創(chuàng)建存儲過程的參數(shù)”。


    例如,修改存儲過程PROC_SEINFO,用于查詢年齡大于35的員工信息。SQL語句如下:

    ALTER PROCEDURE [dbo].[PROC_SEINFO]

    AS

    BEGIN

    SELECT * FROM tb_Employee where 員工年齡>35

    END


    存儲過程的刪除:

    使用Microsoft SQL Server Mangement刪除存儲過程,步驟如下:

       

    1)在SQL Server Management Studio的“對象資源管理器”中,選擇要刪除存儲過程所在的數(shù)據(jù)庫(如:db_student),然后在該數(shù)據(jù)庫下選擇“可編程性”。

       

    2)打開“存儲過程”文件夾,右鍵單擊要刪除的存儲過程(如:PROC_SEINFO),在彈出的快捷菜單中選擇“刪除”命令。


    3)單擊“確定”按鈕,即可刪除所選定的存儲過程。

     

    注意:刪除數(shù)據(jù)表后,并不會刪除相關(guān)聯(lián)的存儲過程,只是其存儲過程無法執(zhí)行。


    使用T-SQL刪除存儲過程:

       

    DROP PROCEDURE語句用于從當前數(shù)據(jù)庫中刪除一個或多個存儲過程或過程組。

     

    語法:

    DROP PROCEDURE { procedure } [ ,...n ]


    參數(shù)說明:

       

    Procedure:是要刪除的存儲過程或存儲過程組的名稱。過程名稱必須符合標識符規(guī)則。可以選擇是否指定過程所有者名稱,但不能指定服務(wù)器名稱和數(shù)據(jù)庫名稱。

     

    n:是表示可以指定多個過程的占位符。

       

    例如刪除PROC_SEINFO存儲過程的SQL語句如下。

    DROP PROCEDURE PROC_SEINFO

    例如,刪除多個存儲過程proc10、proc20proc30。

    DROP PROCEDURE proc10, proc20, proc30

    例如,刪除存儲過程組procs(其中包含存儲過程proc1、proc2、proc3)。

    DROP PROCEDURE procs

    注意:SQL語句DROP不能刪除存儲過程組中的單個存儲過程。


    應(yīng)用存儲過程驗證用戶登錄身份:

    目前,驗證用戶登錄身份的方法有多種,而通過調(diào)用存儲過程來實現(xiàn)用戶身份驗證是目前最好的解決方案之一。因為存儲過程在創(chuàng)建時即在服務(wù)器上進行編譯,所以執(zhí)行起來比單個SQL語句要快得多。

       

    本例是通過調(diào)用存儲過程來驗證用戶登錄的用戶名和密碼是否正確。運行本實例,在“用戶名”和“密碼”文本框中輸入相應(yīng)的用戶名和密碼,單擊“登錄”按鈕即可。


    程序開發(fā)步驟:


    1)新建一個網(wǎng)站,將其命名為"index",默認主頁名為Default.aspx。

       

    2Default.aspx頁面涉及到的控件如表1所示。



    3)主要程序代碼如下。

    打開SQL Server Management Studio,并連接到SQL Server2005中的數(shù)據(jù)庫。單擊工具欄中“”按鈕,新建查詢編輯器。在該查詢編輯器中,創(chuàng)建驗證登錄用戶身份的存儲過程PROC_EXISTS,具體的SQL語句如下:

    CREATE PROC PROC_EXISTS

    (

    @UserName NVARCHAR(20),

    @PassWord NVARCHAR(20),

    @ReturnValue int OUTPUT

    )

    AS

    IF EXISTS(select * from tb_member whereuserName=@UserName AND passWord=@PassWord)

         set @ReturnValue= 100

    ELSE

         set @ReturnValue= -100

    GO


    "登錄"按鈕的Click事件下,執(zhí)行驗證登錄用戶身份的存儲過程,如果輸入的用戶名和密碼正確,則彈出對話框提示用戶登錄成功,代碼如下:

    protected void btnLogin_Click(objectsender, EventArgs e)

       {

          //連接數(shù)據(jù)庫

          myConn = newSqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString());

          myCmd = new SqlCommand("PROC_EXISTS", myConn);   //調(diào)用存儲過程,判斷用戶是否存在

          myCmd.CommandType = CommandType.StoredProcedure;

          //為存儲過程的參數(shù)賦值

          SqlParameter userName=new SqlParameter("@UserName",SqlDbType.NVarChar, 20);

          userName.Value=this.txtName.Text.Trim();

          myCmd.Parameters.Add(userName);

          SqlParameter passWord=new SqlParameter("@PassWord",SqlDbType.NVarChar, 20);

          passWord.Value = this.txtPassword.Text.Trim();

          myCmd.Parameters.Add(passWord);

          //指出該參數(shù)是存儲過程的OUTPUT參數(shù)

           SqlParameter ReturnValue = newSqlParameter("@ReturnValue",SqlDbType.Int ,4);

          ReturnValue.Direction = ParameterDirection.Output;

          myCmd.Parameters.Add(ReturnValue);

          try

          {

              myConn.Open();

              myCmd.ExecuteNonQuery();

              if (int.Parse(ReturnValue.Value.ToString()) == 100)

              {

                  Response.Write("<script>alert('您是合法用戶,登錄成功!')</script>");

                   return;

              }

              else

              {

                   Response.Write("<script>alert('您輸入的用戶名和密碼不正確,請重新輸入!')</script>");

                   return;

              }

          }

          catch(Exception ex)

          {

              Response.Write(ex.Message.ToString());

          }

          finally

          {

              myConn.Close();

              myConn.Dispose();

              myCmd.Dispose();

          }}

    以上由湖北國菱荊州軟件開發(fā)、荊州網(wǎng)站建設(shè)工程師整理發(fā)布。喜歡程序開發(fā)的伙伴們歡迎一起交流探討。Email:business@gl-ns.com,電話:0716-6666211.

    荊州地區(qū)政府網(wǎng)站建設(shè) 解決方案 專業(yè)團隊 騰訊第三方平臺 地址:湖北省荊州市沙市區(qū)荊沙大道楚天都市佳園一期C區(qū)29棟112       地址:湖北省松滋市新江口街道才知文化廣場1幢1146-1151室     郵編:434200 聯(lián)系電話:0716-6666211     網(wǎng)站編輯部郵箱:business@gl-ns.com 鄂公網(wǎng)安備 42100202000212號 備案號:鄂ICP備2021015094號-1     企業(yè)名稱:湖北國菱計算機科技有限公司
    亚洲色精品Aⅴ一区区三区,少妇中文高清av无码,久久精品这里只有精品99品,无码乱人伦中文视频 99福利资源久久福利资源 国产精品白浆无码流出嗯啊豆