SQLストアドプロシージャの例の詳細な説明

SQL ストアド プロシージャの例の詳細な説明

ストアド プロシージャ (ストアド プロシージャ) は、次の目的で設計されたプロシージャのグループです。 SQL ステートメントには、プログラミング言語と同様に、データ型、フロー制御、入出力、および独自の関数ライブラリも含まれています。

ストアド プロシージャは、レコード セットであると言えます。これは、いくつかの T-SQL ステートメントで構成されるコード ブロックです。これらの T-SQL ステートメント コードは、メソッド (単一テーブルへの追加や、複数のテーブル)、削除、変更、チェック) を実行し、このコード ブロックに名前を付けて、この関数が使用されるときに呼び出すだけです。ただし、SQL ストアド プロシージャは依然として比較的抽象的であり、一部の初心者にとっては理解しにくいため、この記事では、SQL ストアド プロシージャを浅いものから深いものまで分析して学習に役立てます。

推奨: 「SQL ビデオ チュートリアル 」

ストアド プロシージャの利点

1. ストアド プロシージャは、作成時にのみ実行されます。コンパイルされると、今後ストアド プロシージャを実行するたびに再コンパイルする必要はありません。一般に、SQL ステートメントは実行されるたびに 1 回コンパイルされます。そのため、ストアド プロシージャを使用すると、データベースの実行速度が向上し、ストアド プロシージャを使用した方が効率的です。 T-SQL ステートメント。

2. データベース上で複雑な操作を実行する場合、複雑な操作をストアド プロシージャにカプセル化し、データベースが提供するトランザクション処理と組み合わせて使用​​できます。

3. ストアド プロシージャは、プログラムがネットワーク上で対話するときに多数の T-SQL ステートメントを置き換えることができるため、ネットワーク トラフィックを削減し、通信速度を向上させることもできます。

4. ストアド プロシージャは再利用できるため、データベース開発者の作業負荷を軽減できます。

5. 高いセキュリティ。特定のユーザーのみが指定されたストアド プロシージャを使用する権利を持つように設定できます。

ストアド プロシージャの基本構文

--------------创建存储过程-----------------
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    --在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

ストアド プロシージャを作成するためのパラメータ

●procedure_name: ストアド プロシージャの名前。ローカル一時ストアド プロシージャを示すには前に # を追加し、グローバル一時ストアド プロシージャを示すには ## を追加します。手順。

#number: はオプションの整数で、同じ名前のプロシージャをグループ化するために使用されます。これにより、同じグループのプロシージャを DROP PROCEDURE ステートメントと一緒に削除できます。たとえば、orders という名前のアプリケーションは、orderproc;1、orderproc;2 などという名前のプロシージャを使用します。 DROP PROCEDURE orderproc ステートメントは、グループ全体を削除します。名前に区切り文字で区切られた識別子が含まれる場合は、識別子に数字を含めず、適切な区切り文字をprocedure_nameの前後にのみ使用する必要があります。

# @parameter: ストアド プロシージャのパラメータ。 1 つ以上存在する可能性があります。ユーザーは、プロシージャを実行するときに、宣言された各パラメーターの値を指定する必要があります (そのパラメーターのデフォルト値が定義されている場合を除く)。ストアド プロシージャには、最大 2100 個のパラメータを含めることができます。

# 最初の文字として @ 記号を使用してパラメータ名を指定します。パラメータ名は識別子の規則に準拠する必要があります。各プロシージャのパラメータは、そのプロシージャ自体でのみ使用され、同じパラメータ名を他のプロシージャで使用できます。デフォルトでは、パラメータは定数のみを置換でき、テーブル名、列名、または他のデータベース オブジェクトの名前を置換するために使用することはできません。詳細については、「実行」を参照してください。

# data_type: パラメータのデータ型。 text、ntext、image を含むすべてのデータ型をストアド プロシージャのパラメータとして使用できます。ただし、カーソルのデータ型は OUTPUT パラメータでのみ使用できます。指定したデータ型がカーソルの場合は、VARYING キーワードと OUTPUT キーワードも指定する必要があります。 SQL Server が提供するデータ型とその構文の詳細については、「データ型」を参照してください。

説明 カーソル データ型にできる出力パラメータの数に最大制限はありません。

# VARYING: 出力パラメーターとしてサポートされる結果セットを指定します (ストアド プロシージャによって動的に構築され、内容は変更される可能性があります)。カーソルパラメータにのみ適用されます。

# デフォルト: パラメータのデフォルト値。デフォルト値が定義されている場合、プロシージャを実行するためにこのパラメータの値を指定する必要はありません。デフォルト値は定数または NULL である必要があります。プロシージャでこのパラメータに LIKE キーワードを使用する場合、ワイルドカード文字 (%、_、[]、および [^]) をデフォルト値に含めることができます。

## ● OUTPUT: パラメータが戻りパラメータであることを示します。このオプションの値は EXEC[UTE] に返すことができます。 OUTPUT パラメータを使用して、呼び出し側プロシージャに情報を返します。 text、ntext、image パラメータは OUTPUT パラメータとして使用できます。 OUTPUT キーワードを使用した出力パラメータは、カーソル プレースホルダにすることができます。

# RECOMPILE: SQL Server がプロシージャのプランをキャッシュせず、プロシージャが実行時に再コンパイルされることを示します。 RECOMPILE オプションは、非定型値または一時的な値を使用して作業しており、メモリにキャッシュされた実行プランを上書きしたくない場合に使用します。

# ENCRYPTION: CREATE PROCEDURE ステートメントのテキストを含む、SQL Server の暗号化された syscomments テーブル内のエントリを表します。 ENCRYPTION を使用して、プロシージャが SQL Server レプリケーションの一部として公開されないようにします。説明 アップグレード プロセス中に、SQL Server は syscomments に保存されている暗号化コメントを使用して暗号化プロセスを再作成します。

● FOR REPLICATION:指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。  

● AS:指定过程要执行的操作。 

● sql_statement:过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。 

实例操作学习

下面通过表Student来具体了解一下存储过程，因为是要了解存储过程的简单用法，所以例子很简单。 

无参数存储过程

选出Student表中的所有信息 

create proc StuProc
as      //此处 as 不可以省略不写
begin   //begin 和 end 是一对，不可以只写其中一个，但可以都不写
select S#,Sname,Sage,Ssex from student
end
go

有参数存储过程

全局变量

全局变量也称为外部变量，是在函数的外部定义的，它的作用域为从变量定义处开始，到本程序文件的末尾。

选出指定姓名的学生信息: 

create proc StuProc
@sname varchar(100)   
as 
begin
select S#,Sname,Sage,Ssex from student where sname=@sname
end
go
exec StuProc '赵雷'   //执行语句

上面是在外部给变量赋值，也可以在内部直接给变量设置默认值 

create proc StuProc
@sname varchar(100)='赵雷'
as 
begin
select S#,Sname,Sage,Ssex from student where sname=@sname
end
go
exec StuProc

也可以把变量的内容输出，使用output 

create proc StuProc
@sname varchar(100),
@IsRight int  output //传出参数
as 
if exists (select S#,Sname,Sage,Ssex from student where sname=@sname)
set @IsRight =1
else
set @IsRight=0
go
declare @IsRight int 
exec StuProc '赵雷' , @IsRight output
select @IsRight

以上是全局变量，下面来了解局部变量 

局部变量

局部变量也称为内部变量。局部变量是在函数内作定义说明的。其作用域仅限于函数内部，离开该函数后再使用这种变量是非法的。

局部变量的定义

必须先用Declare命令定以后才可以使用，declare{@变量名 数据类型}

局部变量的赋值方法

set{@变量名=表达式}或者select{@变量名=表达式}

局部变量的显示

create proc StuProc
as 
declare @sname varchar(100)
set @sname='赵雷'
select S#,Sname,Sage,Ssex from student where sname=@sname
go
exec StuProc

那如果是要把局部变量的数据显示出来怎么办呢？ 

create proc StuProc
as 
declare @sname varchar(100)
set @sname=(select Sname from student where S#=01)
select @sname
go
exec StuProc

更详细的实例操作学习

比如，在SQL Server查询编辑器窗口中用CREATE PROCEDURE语句创建存储过程PROC_InsertEmployee，用于实现向员工信息表（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

存储过程的修改

创建完存储过程之后，如果需要重新修改存储过程的功能及参数，可以在SQL Server 2005中通过以下两种方法进行修改：一种是用Microsoft SQL Server Mangement修改存储过程；另外一种是用T-SQL语句修改存储过程。 

使用Microsoft SQL Server Mangement修改存储过程，步骤如下： 

（1）在SQL Server Management Studio的“对象资源管理器”中，选择要修改存储过程所在的数据库（如：db_18），然后在该数据库下，选择“可编程性”。 

（2）打开“存储过程”文件夹，右键单击要修改的存储过程（如：PROC_SEINFO），在弹出的快捷菜单中选择“修改”命令，将会出现查询编辑器窗口。用户可以在此窗口中编辑T-SQL代码，完成编辑后，单击工具栏中的“执行（X）”按钮，执行修改代码。用户可以在查询编辑器下方的Message窗口中看到执行结果信息。 

使用Transact-SQL修改存储过程： 

使用ALTER PROCEDURE语句修改存储过程，它不会影响存储过程的权限设定，也不会更改存储过程的名称。 

语法：

ALTER PROC [ EDURE ] procedure_name [ ; number ] 
    [ { @parameter data_type }  
         [ VARYING ] [ = default ] [ OUTPUT ] 
    ] [ ,...n ]  
[ WITH 
    { RECOMPILE | ENCRYPTION 
        | RECOMPILE , ENCRYPTION   }  
] 
[ FOR REPLICATION ]  
AS 
    sql_statement [ ...n ]

参数说明

procedure_name：是要更改的存储过程的名称。

交叉链接：关于ALTER PROCEDURE语句的其他参数与CREATE PROCEDURE语句相同，可参见上面的“创建存储过程的参数”。

例如，修改存储过程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的“对象资源管理器”中，选择要删除存储过程所在的数据库（如：db_student），然后在该数据库下选择“可编程性”。  

（2）打开“存储过程”文件夹，右键单击要删除的存储过程（如：PROC_SEINFO），在弹出的快捷菜单中选择“删除”命令。 

（3）单击“确定”按钮，即可删除所选定的存储过程。  

注意：删除数据表后，并不会删除相关联的存储过程，只是其存储过程无法执行。

使用T-SQL删除存储过程： 

DROP PROCEDURE语句用于从当前数据库中删除一个或多个存储过程或过程组。 

语法：

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

参数说明：   

Procedure：是要删除的存储过程或存储过程组的名称。过程名称必须符合标识符规则。可以选择是否指定过程所有者名称，但不能指定服务器名称和数据库名称。    

n：是表示可以指定多个过程的占位符。 

    

例如删除PROC_SEINFO存储过程的SQL语句如下。 

DROP PROCEDURE PROC_SEINFO

例如，删除多个存储过程proc10、proc20和proc30。 

DROP PROCEDURE proc10, proc20, proc30

例如，删除存储过程组procs（其中包含存储过程proc1、proc2、proc3）。 

DROP PROCEDURE procs

注意：

SQL语句DROP不能删除存储过程组中的单个存储过程。

应用存储过程验证用户登录身份：

目前，验证用户登录身份的方法有多种，而通过调用存储过程来实现用户身份验证是目前最好的解决方案之一。因为存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句要快得多。 

本例是通过调用存储过程来验证用户登录的用户名和密码是否正确。运行本实例，在“用户名”和“密码”文本框中输入相应的用户名和密码，单击“登录”按钮即可。 

程序开发步骤： 

（1）新建一个网站，将其命名为"index"，默认主页名为Default.aspx。    

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

（3）主要程序代码如下。 

打开SQL Server Management Studio，并连接到SQL Server2005中的数据库。单击工具栏中“ ”按钮，新建查询编辑器。

在该查询编辑器中，创建验证登录用户身份的存储过程PROC_EXISTS，具体的SQL语句如下： 

CREATE PROC PROC_EXISTS 
( 
@UserName NVARCHAR(20), 
@PassWord NVARCHAR(20), 
@ReturnValue int OUTPUT 
) 
AS 
IF EXISTS(select * from tb_member where userName=@UserName AND passWord=@PassWord) 
       set @ReturnValue= 100 
ELSE 
       set @ReturnValue= -100 
GO

在"登录"按钮的Click事件下，执行验证登录用户身份的存储过程，如果输入的用户名和密码正确，则弹出对话框提示用户登录成功，代码如下： 

protected void btnLogin_Click(object sender, EventArgs e) 
    { 
        //连接数据库 
        myConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); 
        myCmd = new SqlCommand("PROC_EXISTS", myConn);   //调用存储过程，判断用户是否存在
        myCmd.CommandType = CommandType.StoredProcedure; 
        //为存储过程的参数赋值 
        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); 
        //指出该参数是存储过程的OUTPUT参数 
        SqlParameter ReturnValue = new SqlParameter("@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(&#39;您是合法用户，登录成功！&#39;)</script>"); 
                return; 
            } 
            else 
            { 
                Response.Write("<script>alert(&#39;您输入的用户名和密码不正确，请重新输入！&#39;)</script>"); 
                return; 
            } 
        } 
        catch(Exception ex) 
        { 
            Response.Write(ex.Message.ToString()); 
        } 
        finally 
        { 
            myConn.Close(); 
            myConn.Dispose(); 
            myCmd.Dispose(); 
        }}

以上がSQLストアドプロシージャの例の詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。