Detailed explanation of sql stored procedure examples

Detailed explanation of sql stored procedure examples

Stored Procedure (Stored Procedure) is a group of procedures designed to complete specific functions. SQL statements, similar to a programming language, also include data types, flow control, input and output, and its own function library.

A stored procedure can be said to be a record set. It is a code block composed of some T-SQL statements. These T-SQL statement codes implement some functions like a method (adding to a single table or multiple tables). Delete, modify, and check), and then give this code block a name, and just call it when this function is used. However, SQL stored procedures are still relatively abstract and difficult to understand for some beginners, so this article will analyze SQL stored procedures from shallow to deep to help you learn it.

Recommended: "SQL Video Tutorial"

Advantages of stored procedures

1. Stored procedures are only performed when creating Compiled, there is no need to recompile every time the stored procedure is executed in the future. Generally, SQL statements are compiled once every time they are executed. Therefore, using stored procedures can improve the execution speed of the database and is more efficient than T-SQL statements.

2. When performing complex operations on the database, the complex operations can be encapsulated in stored procedures and used in conjunction with the transaction processing provided by the database.

3. A stored procedure can replace a large number of T-SQL statements when the program interacts on the network, so it can also reduce the network traffic and increase the communication rate.

4. Stored procedures can be reused, which can reduce the workload of database developers.

5. High security, you can set that only certain users have the right to use the specified stored procedure

Basic syntax of stored procedures

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

Parameters for creating stored procedures

● procedure_name: The name of the stored procedure. Add # in front to indicate a local temporary stored procedure, and add ## to indicate a global temporary stored procedure.

● number: is an optional integer, used to group procedures with the same name, so that the same group of procedures can be removed together with a DROP PROCEDURE statement. For example, an application named orders uses procedures named orderproc;1, orderproc;2, and so on. The DROP PROCEDURE orderproc statement drops the entire group. If the name contains a delimited identifier, the number should not be included in the identifier and appropriate delimiters should only be used before and after procedure_name.

● @parameter: Parameters of the stored procedure. There can be one or more. The user must provide a value for each declared parameter when executing the procedure (unless a default value for that parameter is defined). Stored procedures can have up to 2100 parameters.

● Use the @ symbol as the first character to specify the parameter name. Parameter names must conform to the rules for identifiers. The parameters of each procedure are used only in the procedure itself; the same parameter names can be used in other procedures. By default, parameters can only replace constants and cannot be used to replace table names, column names, or the names of other database objects. See EXECUTE for more information.

● data_type: The data type of the parameter. All data types, including text, ntext, and image, can be used as parameters for stored procedures. However, the cursor data type can only be used with OUTPUT parameters. If the specified data type is a cursor, the VARYING and OUTPUT keywords must also be specified. For more information about the data types provided by SQL Server and their syntax, see Data Types.

Explanation There is no maximum limit on the number of output parameters that can be cursor data types.

● VARYING: Specify the result set supported as an output parameter (dynamically constructed by the stored procedure, the content can change). Applies only to cursor parameters.

● default: The default value of the parameter. If a default value is defined, you do not have to specify a value for this parameter in order to execute the procedure. The default value must be a constant or NULL. If the procedure will use the LIKE keyword for this parameter, wildcard characters (%, _, [], and [^]) can be included in the default value.

● OUTPUT: Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC[UTE]. Use the OUTPUT parameter to return information to the calling procedure. Text, ntext, and image parameters are available as OUTPUT parameters. Output parameters using the OUTPUT keyword can be cursor placeholders.

● RECOMPILE: Indicates that SQL Server will not cache the plan for the procedure and the procedure will be recompiled at runtime. Use the RECOMPILE option when you are working with atypical or temporary values ​​and do not want to overwrite the execution plan cached in memory.

● ENCRYPTION: Represents the entry in the SQL Server encrypted syscomments table that contains the text of the CREATE PROCEDURE statement. Use ENCRYPTION to prevent a procedure from being published as part of SQL Server replication. Description During the upgrade process, SQL Server uses the encryption comments stored in syscomments to recreate the encryption process.

● 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(); 
        }}

The above is the detailed content of Detailed explanation of sql stored procedure examples. For more information, please follow other related articles on the PHP Chinese website!