一篇关于SQL中的动态SQL

2023-02-27

sql 动态 exec

在介绍动态SQL前我们先看看什么是静态SQL静态SQL静态SQL语句一般用于嵌入式SQL应用中，在程序运行前，SQL语句必须是确定的，例如SQL语句中涉及的列名和表名必须是存在的。静态SQL语句的编译是在应用程序运行前进行的，编译的结果会存储在数据库内部。而后程序运行时，数据库将直接执行编译好的SQ

在介绍动态SQL前我们先看看什么是静态SQL

静态SQL

静态 SQL 语句一般用于嵌入式 SQL 应用中，在程序运行前，SQL 语句必须是确定的，例如 SQL 语句中涉及的列名和表名必须是存在的。静态 SQL 语句的编译是在应用程序运行前进行的，编译的结果会存储在数据库内部。而后程序运行时，数据库将直接执行编译好的 SQL 语句，降低运行时的开销。

动态SQL

动态 SQL 语句是在应用程序运行时被编译和执行的，例如，使用 DB2 的交互式工具 CLP 访问数据库时，用户输入的 SQL 语句是不确定的，因此 SQL 语句只能被动态地编译。动态 SQL 的应用较多，常见的 CLI 和 JDBC 应用程序都使用动态 SQL。

动态SQL作用

自动化管理任务。例如：对于数据库实例中的每个数据库，查询其元数据，为其执行BACKUP DATABASE语句。
改善特定任务的性能。例如，构造参数化的特定查询，以重用以前缓存过的执行计划。
对实际数据进行查询的基础上，构造代码元素。例如，当事先不知道再PIVOT运算符的IN子句中应该出现哪些元素时，动态构造PIVOT查询。

动态SQL执行方法

使用EXEC(EXECUTE的缩写)命令和使用SP_EXECUTERSQL。

EXEC命令执行

语法

EXECUTE (SQL语句)

注：EXECUTE 命令有两个用途，一个是用来执行存储过程，另一个是执行动态SQL

不带参数示例

在变量@SQL中保存了一个字符串，该字符串中包含一条查询语句，再用EXEC调用保存在变量中的批处理代码，我们可以这样写SQL：

EXEC ('SELECT * FROM Customers') 
1.

结果如下：

与我们直接执行SELECT * FROM Customers一样。

带参数示例

还是上面的示例，我们换一种写法

DECLARE @SQL AS VARCHAR(100); 
DECLARE @Column AS VARCHAR(20); 
SET @Column = '姓名' 
SET @SQL = 'SELECT ' + @Column + ' FROM Customers'; 
EXEC (@SQL) 
1.
2.
3.
4.
5.

结果如下：

SP_EXECUTERSQL执行

语法

EXEC SP_EXECUTERSQL 参数1,参数2,参数3

注意：SP_EXECUTERSQL是继EXEC后另一种执行动态SQL的方法。使用这个存储过程更加安全和灵活，因为它支持输入和输出参数。注意的是，与EXEC不同的是,SP_EXECUTERSQL只支持使用Unicode字符串作为其输入的批处理代码。

示例

构造了一个对Customers表进行查询的批处理代码，在其查询过滤条件中使用一个输入参数@CusID

DECLARE @SQL AS NVARCHAR(100); 
SET @SQL=N'SELECT * FROM Customers 
WHERE 客户ID=@CusID;'; 
 
EXEC SP_EXECUTESQL 
    @STMT=@SQL, 
    @PARMS=N'@CusID AS INT', 
    @CusID=1; 
1.
2.
3.
4.
5.
6.
7.
8.