SQL 저장 프로시저 예제에 대한 자세한 설명

SQL 저장 프로시저 예제에 대한 자세한 설명

저장 프로시저(Stored Procedure)는 특정 기능을 완성하기 위한 SQL 문의 집합으로, 프로그래밍 언어와 유사하며 데이터 유형, 프로세스 제어, 입력 및 출력과 자체 함수 라이브러리.

저장 프로시저는 레코드 세트라고 할 수 있습니다. 이는 일부 T-SQL 문으로 구성된 코드 블록입니다. 이러한 T-SQL 문 코드는 메서드(단일 테이블 추가, 삭제, 수정 및 쿼리)와 같은 일부 기능을 구현합니다. 또는 여러 테이블) 그런 다음 이 코드 블록에 이름을 지정하고 이 함수가 사용될 때 호출하면 됩니다. 그러나 SQL 저장 프로시저는 여전히 상대적으로 추상적이고 일부 초보자가 이해하기 어렵습니다. 따라서 이 기사에서는 SQL 저장 프로시저를 얕은 부분부터 깊은 부분까지 분석하여 학습하는 데 도움을 줄 것입니다.

추천: "SQL 동영상 튜토리얼"

저장 프로시저의 장점

1. 저장 프로시저는 생성될 때만 컴파일되므로 나중에 저장 프로시저가 실행될 때마다 다시 컴파일할 필요가 없습니다. 일반 SQL문은 한번 실행하면 한번 컴파일되므로 저장 프로시저를 사용하면 데이터베이스 실행 속도가 향상되고 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    --在存储过程中能调用另外一个存储过程，而不能删除另外一个存储过程

저장 프로시저 생성을 위한 매개변수

● 프로시저_이름: 저장 프로시저 이름 , 앞에 #을 추가하면 로컬 임시 저장 프로시저를 나타내고, ##을 추가하면 전역 임시 저장 프로시저를 나타냅니다.

● 숫자: 동일한 이름을 가진 프로시저를 그룹화하는 데 사용되는 선택적 정수입니다. 따라서 동일한 프로시저 그룹을 DROP PROCEDURE 문과 함께 제거할 수 있습니다. 예를 들어 주문이라는 애플리케이션은 orderproc;1, orderproc;2 등의 프로시저를 사용합니다. DROP PROCEDURE orderproc 문은 전체 그룹을 삭제합니다. 이름에 구분 식별자가 포함된 경우 숫자는 식별자에 포함되어서는 안 되며 적절한 구분 기호는 프로시저_이름 앞과 뒤에만 사용해야 합니다.

● @parameter: 저장 프로시저의 매개변수입니다. 하나 이상이 있을 수 있습니다. 사용자는 프로시저를 실행할 때 선언된 각 매개변수에 대한 값을 제공해야 합니다(해당 매개변수에 대한 기본값이 정의되지 않은 경우). 저장 프로시저는 최대 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 테이블의 항목을 나타냅니다. 프로시저가 SQL Server 복제의 일부로 게시되는 것을 방지하려면 ENCRYPTION을 사용하십시오. 설명 업그레이드 프로세스 중에 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!