mysql存储过程动态传值存储过程动态sql

转载

epeppanda 2024-01-01 09:49:10

文章标签 mysql存储过程动态传值存储过程动态SQL SQL sql 文章分类 MySQL 数据库

介绍

sp_executesql与EXECUTE命令

例1.0

例1.1

存储过程中的动态SQL

例2.0

在动态SQL中使用Like操作符，IN操作符和Order By

例3.0 - 使用LIKE操作符

例3.1 - 使用IN操作符

例3.2 - 使用Order By子句

结论

本文介绍如何在存储过程中构建和执行动态SQL。

介绍

在存储过程中的动态SQL是单个Transact-SQL语句或存储在变量中并使用SQL命令执行的一组语句。在SQL Server中可能有几种实现方法。本文将向您展示一种很好的方法。在详细解释之前，让我告诉你“何时使用动态SQL？” 我们不能肯定地说静态SQL将满足我们所有的编程需求。当我们需要根据不同的搜索参数检索一组记录时，需要动态SQL。比如说——员工搜索屏幕或通用报告，需要SELECT根据不同的WHERE子句执行不同的语句。

注意：最重要的是，变量中的动态SQL查询不会被编译、解析、检查错误，直到它们被执行。

sp_executesql与EXECUTE命令

可以使用EXECUTE命令或sp_executesql语句执行动态构建的Transact-SQL 语句。在这篇文章中我的示例里，我将使用sp_executesql，其更高效、执行更快并且还支持参数替换。如果我们使用EXECUTE命令来执行SQL String，那么所有参数都应该转换为字符，并在执行之前作为Query的一部分。但该sp_executesql声明提供了一种更好的实现方法。它允许我们将参数值替换为SQL String中指定的任何参数。在进入实际示例之前，让我用一个简单的例子来区分这两个命令。使用WHERE子句中的ID从employee表中选择一条记录。

使用EXECUTE命令的基本语法：

EXECUTE(@SQLStatement)

使用sp_executesql的基本语法：

sp_executesql [@SQLStatement],[@ParameterDefinitionList],
[@ParameterValueList]

例1.0

/* Using EXECUTE Command */
/* Build and Execute a Transact-SQL String with a single parameter 
 value Using EXECUTE Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String with parameter value */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = ' + 
CAST(@EmpID AS NVARCHAR(10))
/* Execute Transact-SQL String */
EXECUTE(@SQLQuery)

在上面的示例1.0中，声明了两个变量。第一个变量@EmpID用作SQL查询的参数，第二个变量@SQLQuery用于构建SQL String。您可以清楚地看到变量@EmpID被强制转换为NVarchar 类型并作为SQL字符串的一部分。如果打印@SQLQuery字符串（PRINT @SQLQuery），您将获得实际的SQL查询，如下所示：

SELECT * FROM tblEmployees WHERE EmployeeID = 1001

最后，使用该EXECUTE命令执行上述查询。

例1.1

/* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

在示例1.1中，声明了三个变量。该变量@EmpID用作SQL查询的参数，第二个变量@SQLQuery用于构建SQL字符串，第三个变量@ParameterDefinition用于在执行SQL字符串之前指定参数格式。如果您打印@SQLQuery字符串（PRINT @SQLQuery），您将获得如下所示的查询：

SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID

在这个例子中，您可以清楚地看到该参数@EmpID包含在语句中。最后，sp_executesql获取必要的信息来执行参数替换并执行动态构建的SQL字符串。

@SQLQuery - >包含SQL语句
@ParameterDefinition - >包含参数定义
@EmpID - >包含要替换为SQL语句中的参数的参数值。

注意：动态SQL字符串中包含的参数必须在“参数定义列表”和“参数值列表”中具有相应的条目。

存储过程中的动态SQL

本文的这一部分解释了一个真实的示例和示例过程“如何在存储过程中构建和执行动态SQL？”

例2.0

让我们举一个简单的例子——Employee表有普通字段，如EmployeeID，Name，Department，Designation，JoiningDate，Salary 和Description。您可以使用以下Transact-SQL CREATE TABLE语句在数据库中创建表Employee。

/* Transact-Sql to create the table tblEmployees */
CREATE TABLE tblEmployees
(
    EmployeeID       SMALLINT IDENTITY(1001,1) NOT NULL,
    EmployeeName     NVARCHAR(100) NOT NULL,
    Department       NVARCHAR(50) NOT NULL,
    Designation      NVARCHAR(50) NOT NULL,
    JoiningDate      DATETIME NOT NULL,
    Salary           DECIMAL(10,2) NOT NULL,
    [Description]    NVARCHAR(1000) NULL 
)

以下INSERT语句将一些示例记录插入tblEmployee 表中：

/* Transact SQL to insert some sample records into tblEmployee table */
INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('John Smith', 'IT Research', 'Research Analyst', 
 '02/08/2005', 23000.00, 'Analyst since 2005')

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('John Micheal', 'IT Operations', 'Manager', 
 '07/15/2007', 15000.00, NULL)

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('Will Smith', 'IT Support', 'Manager', 
 '05/20/2006', 13000.00, 'Joined last year as IT Support Manager')

我们的程序员可能会获得开发Employee搜索屏幕或生成Employee列表报告的任务，该报告将搜索数据库并根据搜索条件返回结果。在这种情况下，搜索界面应足够灵活，以便在数据库中搜索所有可能的标准。用户可能需要搜索以下详细信息：

使用Name搜索特定的Employee明细
在特定Department中的Employee列表
在特定Designation中的 Employee列表
去年加入该组织的Employee列表
Employees Salary > =某些特定金额的s 列表
上面列出的任何这些条件或所有这些条件

我在这里列出了一些可能的条件。可能还有许多其他可能性完全取决于用户要求。这里让我们列出几个可能的标准，并编写一个构建动态SQL的存储过程，它将用于搜索Employee表中的详细信息。以下CREATE PROCEDURE语句将创建一个存储过程“sp_EmployeeSelect”，其中包含必要的输入参数和变量以构建动态SQL。

/* This stored procedure builds dynamic SQL and executes 
using sp_executesql */
Create Procedure sp_EmployeeSelect
    /* Input Parameters */
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary    Decimal(10,2)
        
AS
    Set NoCount ON
    /* Variable Declaration */
    Declare @SQLQuery AS NVarchar(4000)
    Declare @ParamDefinition AS NVarchar(2000) 
    /* Build the Transact-SQL String with the input parameters */ 
    Set @SQLQuery = 'Select * From tblEmployees where (1=1) ' 
    /* check for the condition and build the WHERE clause accordingly */
    If @EmployeeName Is Not Null 
         Set @SQLQuery = @SQLQuery + ' And (EmployeeName = @EmployeeName)'

    If @Department Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Department = @Department)' 
  
    If @Designation Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Designation = @Designation)'
  
    If @Salary Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Salary >= @Salary)'

    If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
         Set @SQLQuery = @SQLQuery + ' And (JoiningDate 
         BETWEEN @StartDate AND @EndDate)'
    /* Specify Parameter Format for all input parameters included 
     in the stmt */
    Set @ParamDefinition =      ' @EmployeeName NVarchar(100),
                @Department NVarchar(50),
                @Designation NVarchar(50),
                @StartDate DateTime,
                @EndDate DateTime,
                @Salary    Decimal(10,2)'
    /* Execute the Transact-SQL String with all parameter value's 
       Using sp_executesql Command */
    Execute sp_Executesql     @SQLQuery, 
                @ParamDefinition, 
                @EmployeeName, 
                @Department, 
                @Designation, 
                @StartDate, 
                @EndDate,
                @Salary
                
    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

此示例存储过程将少量参数作为输入，并使用两个变量来构建和执行。@SQLQuery用于构建动态SQL语句。@ParamDefinition用于定义参数的格式。在每个步骤中构建SQL字符串时，使用IF-statement来检查输入的参数是否为Null。如果不是NULL，则该参数将包含在SQL语句中，该语句基本上在SQL语句的WHERE 子句中添加条件。您可以在过程中清楚地看到变量@ParamDefinition包含所有参数列表，最后使用sp_Executesql得到SQL-query、参数列表和参数值来执行SELECT语句。

让我们考虑一下上面列出的一些标准，看看这个存储过程是如何工作的。

Employee使用名称搜索特定详细信息。

/* 1. Search for specific Employee Detail with the Name say 'John Smith'. */
EXEC sp_EmployeeSelect 'John Smith', NULL, NULL, NULL, NULL, NULL

执行上述语句将列出Employee “ John Smith” 的详细信息。

在特定的Department中的Employee列表，和
在特定的Designation中Employee列表。

/* 2. List of Employees in a specific Department. AND 
3. List of Employees in a specific Designation. */
/* Say Department = 'IT Operations'  AND  Designation = 'Manager'*/
EXEC sp_EmployeeSelect NULL, 'IT Operations', 'Manager', NULL, NULL, NULL

执行上述声明将列出在IT运营部门中的Manager的详细信息。

在动态SQL中使用Like操作符，IN操作符和Order By

当我们构建动态SQL时，可能会有一些我们需要使用LIKE操作符，IN操作符和Order BY子句的实例。但是这些操作符和Order By子句使用的参数不像通常在使用sp_executesql对“ =”和“Between”操作符时那样工作。通常sp_executesql不会对order by子句执行参数替换，这样做会导致列引用问题。直接使用LIKE操作符和IN操作符会导致语法错误，当我们将参数包含在动态SQL语句中时，无法纠正。可以通过在动态SQL语句中包含实际参数值来解决此问题。以下示例显示在使用sp_executesql时如何使用Like操作符、IN操作符和OrderBy子句。

例3.0 - 使用LIKE操作符

例3.0使用LIKE操作符选择Employee名称为'John'的列表。在此示例中，参数不包含在SQL语句中，而是将参数的实际值添加到SQL语句中。所以在这里，不需要参数定义来执行SQL字符串。这同样适用于下面显示的其他两个例子：

/* Variable Declaration */
DECLARE @EmpName AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @EmpName = 'John' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeName LIKE '''+ '%' + @EmpName + '%' + '''' 
EXECUTE sp_executesql @SQLQuery

例3.1 - 使用IN操作符

Example 3.1使用IN操作符选择Employee细节（ID = 1001,1003）：

/* Variable Declaration */
DECLARE @EmpID AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single 
parameter value Using sp_executesql Command */
SET @EmpID = '1001,1003' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeID IN(' + @EmpID + ')'
EXECUTE sp_executesql @SQLQuery

例3.2 - 使用Order By子句

例3.2 Employee 按“ Department”列对记录进行排序。

/* Variable Declaration */
DECLARE @OrderBy AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @OrderBy = 'Department' 
SET @SQLQuery = 'SELECT * FROM tblEmployees Order By ' + @OrderBy

EXECUTE sp_executesql @SQLQuery

结论

在本文中，我已经用几个例子对“如何在存储过程中构建和执行动态SQL”进行了解释。希望本文能够以一种友好的方式帮助您理解和编写动态SQL。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：java springseruity 配置如果实现单点登录怎么实现 spring security oauth2单点登录

下一篇：动态系统机器学习动态系统的建模与分析

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯