动态 SQL：SQL Server、Oracle、MySQL 和 PostgreSQL 指南

动态 SQL 是数据库的一个方便的特性，它允许您以不同的方式构造 SQL 语句。

在本指南中，您将学习：

• 什么是动态 SQL
• 查看 SQL Server、Oracle、MySQL 和 PostgreSQL 中的几个示例
• 了解优缺点

让我们来看看它。

什么是动态 SQL？

动态 SQL 是 SQL 中的一种技术，可让您在代码运行时动态构建 SQL 语句。可以创建一个通用的SQL语句，写的时候不知道完整的语句，运行代码的时候就完成了。

使用此方法的一个示例是在运行代码之前您不知道要查询哪个表。您可以使用动态 SQL 创建一个查询，该查询在您运行代码时针对您从代码中指定的表运行。

静态 SQL

“常规”类型的 SQL 或动态 SQL 的替代方案是静态 SQL，它是您同时创建和执行的标准 SQL 或 SQL。

如果我们想使用静态 SQL 从客户表中选择一些数据，我们的代码可能如下所示：

SELECT id, customer_nameFROM customerWHERE status = 1;

该语句只是在数据库上运行并返回结果。

动态 SQL

动态 SQL 是通过将 SQL 语句放在字符串变量中然后将其作为语句运行来创建的。

每个数据库的语法都不同，但通常如下所示：

DECLARE statement VARCHAR(2000);SET statement = 'SELECT id, customer_nameFROM customerWHERE status = 1';EXECUTE statement;

声明“语句”变量，设置为 SELECT 查询，然后执行。

动态 SQL 的好处是您可以随心所欲地构造语句，因为它只是一个字符串值。例如，您可以使用串联来添加不同的值、列或表。

动态 SQL 的优缺点

动态 SQL 有一些优点和缺点。

优点：

• 更大的灵活性，因此您可以根据您运行的其他代码创建语句。
• 您通常可以运行静态 SQL 不可用的代码。

缺点：

• SQL 注入的风险较高，这是一种用于访问数据库中非预期区域的技术。
• 在代码运行时创建的 SQL 更难验证，这意味着出错的可能性更高

一般来说，你应该尽量使用静态SQL，而不是尽可能使用动态SQL，并且只在不能使用静态SQL的地方使用动态SQL。

让我们看一些动态 SQL 的例子来更好地理解它。

SQL Server 中的动态 SQL

SQL Server中的动态 SQL可以通过声明一个变量、指定该变量是什么并运行该语句来创建。

让我们看一些例子。

简单示例

这是 SQL Server 中动态 SQL 的一个简单示例。

DECLARE @customerStatus INT = 1;DECLARE @statement VARCHAR(4000);BEGIN  SET @statement = 'SELECT id, customer_name  FROM customer  WHERE status = @customerStatus';  EXECUTE sp_executesql @statement;END;

如果您运行此代码，您将在输出窗口中获得 SELECT 语句的输出。

让我们进一步研究一下这段代码。

• 第一行声明了一个名为 customerStatus 的变量并将其赋值为 1。
• 第二行声明了一个名为 statement 的变量，它是 varchar 类型，长度为 4000。
• Begin 语句开始执行代码。
• Set 语句会将 statement 的变量设置为 = 符号之后的值。在这种情况下，它是 SELECT 语句。
• Set 语句包含变量@customerStatus 的提及，该变量替换了它的值，即 1。
• 运行 Execute 语句，该语句调用 SQL Server 中名为 sp_executesql 的存储过程。这将执行语句变量中的代码。

语句变量设置为：SELECT id, customer_name FROM customer WHERE status = 1。

然后在数据库上运行该语句并显示结果。

在运行时确定表

使用动态 SQL 可以做的另一件事是在运行时或编写代码后确定要查询的表。

假设您有一个查询客户数据的存储过程，但客户数据存储在两个表中：一个用于活动客户，另一个用于非活动客户。存储过程将接受活动或非活动参数，并返回结果。

一种方法是为此参数使用 IF 语句。

CREATE PROCEDURE findCustomers@activeCustomers INTASIF (@activeCustomers = 1)  BEGIN    SELECT id, customer_name    FROM customer;  END;ELSE  BEGIN    SELECT id, customer_name    FROM customer_archive;  END;

这段代码可以工作，但你可以看到这里有一些重复的代码。两个 SELECT 语句非常相似。

另一种方法是确定表类型，然后为该表类型创建查询，这可以使用动态 SQL 完成。

CREATE PROCEDURE findCustomers@activeCustomers INTASDECLARE @customerTable VARCHAR(100);DECLARE @statement VARCHAR(4000);IF (@activeCustomers = 1) SET @customerTable = 'customer';ELSE SET @customerTable = 'archive_customer';SET @statement = 'SELECT id, customer_nameFROM @customerTable';EXECUTE sp_executesql @statement;

此代码将根据 activeCustomers 参数确定要使用的表。然后，它使用该表构造 SQL 查询，并运行该查询。

使用带参数的动态 SQL

您可以使用 sp_executesql 命令创建和运行带参数的动态 SQL。

该命令如下所示：

EXECUTE sp_executesql @statement, @parameterDefinition, @param1=value1...

这由几个部分组成：

• @statement：我们要执行的语句。
• @parameterDefinition：一个字符串，包含我们在语句中引用的所有参数，包括名称和数据类型，用逗号分隔。
• @param1 是@parameterDefinition 中定义的第一个参数，然后您指定它的值。

可以在该语句的末尾添加多个参数。

可以使用此代码演示此概念的一个简单示例，该代码将提供给它的两个数字相乘。

DECLARE @statement VARCHAR(4000);SET statement = 'SELECT @num1 * @num2';EXECUTE sp_executesql @statement, '@num1 int, @num2 int', @num1=3, @num2=4;

此代码的结果将是输出中显示的数字 12。num1 和 num2 这两个输入是在我们执行语句时指定的。只需在运行执行命令时更改值，我们就可以为这些参数使用不同的值。

sp_executesql 的替代方案

上面的示例使用名为 sp_executesql 的内置过程来运行动态 SQL。

在 SQL Server 中还有另一种方法可以做到这一点。

您可以在没有 sp_executesql 的情况下使用 EXEC 或 EXECUTE 命令，但只需将语句包含在括号内：

DECLARE @statement VARCHAR(4000);SET @statement = 'SELECT 1';EXECUTE (@statement):

这将显示 1 的值。您可能会在这里使用比 SELECT 1 更复杂的查询，但这只是演示了这个概念。

SP_Executesql 与 EXECUTE

所以有两种方法可以在 SQL Server 中运行动态 SQL。你应该使用哪一个？

SQL Server 的供应商 Microsoft 建议使用 sp_executesql。有几个原因。

• 多次使用相同的执行计划更容易，这意味着当代码运行多次时性能会更好。
• 使用 sp_executesql 进行参数化查询时，代码更具可读性。
• 它不太容易受到 SQL 注入攻击，这是可以更改 SQL 语句以在数据库上运行恶意 SQL 代码的地方。

这就是在 SQL Server 中使用动态 SQL 的方式。

Oracle 中的动态 SQL

Oracle 数据库还包括运行动态 SQL 的能力。它通过声明一个变量来保存 SQL 语句并运行该变量的值来工作。

让我们看一些例子。

简单示例

这是一个使用PL/SQL 代码块在 Oracle 中构建动态 SQL 的简单示例。

DECLARE  customer_status INTEGER := 1;  statement VARCHAR2(4000);BEGIN  statement := 'SELECT id, customer_name  FROM customer  WHERE status = :customer_status';  EXECUTE IMMEDIATE statement;END;

此代码将在语句变量内运行 SELECT 语句。让我们看看这段代码。

• PL/SQL 代码将从 DECLARE 语句开始。
• 在 DECLARE 部分中，我们声明了两个变量。第一个称为 customer_status 并设置为 1。第二个称为语句，是一个 VARCHAR2，它将是我们运行的语句。
• 然后我们以 BEGIN 语句开始代码执行部分。
• 语句变量设置为 SELECT 语句，其中包含 customer_status 变量。这包括一个 : 在变量名之前，这意味着该值被替换到代码中。
• 然后运行 EXECUTE IMMEDIATE 语句，该语句在语句变量中运行 SQL。

这是 Oracle 中动态 SQL 的一个简单示例。

带输出的动态 SQL

您可以使用立即执行功能将 SQL 查询的结果保存到变量中。这可以在 PL/SQL 中用于将值输出到屏幕或输出面板。

这是与上面相同的 SQL，扩展后将结果保存到变量中。

DECLARE  customer_status INTEGER := 1;  statement VARCHAR2(4000);  customer_id INTEGER;  customer_name VARCHAR2(200);BEGIN  statement := 'SELECT id, customer_name  FROM customer  WHERE status = :customer_status';  EXECUTE IMMEDIATE statement  INTO customer_id, customer_name;  DBMS_OUTPUT.PUT_LINE('ID: ' || customer_id || ', Name: ' || customer_name);END;

此代码包括另外两个变量 customer_id 和 customer_name。Execute Immediate 命令有一个额外的参数 INTO，它将查询的结果存储到这两个变量中。

DBMS_OUTPUT.PUT_LINE 函数将在单行输出中显示结果。

如果有多个结果，则需要在此处循环显示结果，但此代码仅演示了动态 SQL 的概念。

DBMS_SQL 包

存在另一种在 Oracle 中运行动态 SQL 的方法，那就是 DBMS_SQL 包。

这个包包括几个处理和运行动态 SQL 的过程：

• 打开游标
• 解析游标
• 提供绑定变量
• 定义列
• 执行查询
• 获取值
• 关闭光标

这是早期代码的示例，但使用了 DBMS_SQL 包。

DECLARE  customer_status INT := 1;  statement VARCHAR2(4000);  customer_id INTEGER;  customer_name VARCHAR2(200);  cursor_handle INTEGER;  rows_processed INTEGER;BEGIN  statement := 'SELECT id, customer_name  FROM customer  WHERE status = :customer_status';    --Open Cursor  cursor_handle := DBMS_SQL.OPEN_CURSOR;  --Parse statement  DBMS_SQL.PARSE(cursor_handle, statement, DBMS_SQL.native);  --Supply inputs  DBMS_SQL.BIND_VARIABLE(cursor_handle, ':customer_status', customer_status);  --Execute code  rows_processed := DBMS_SQL.EXECUTE(cursor_handle);  --Close cursor  DBMS_SQL.CLOSE_CURSOR(cursor_handle);END;

此代码与前面的代码示例执行相同的操作，只是涉及更多步骤。使用了内置的 DBMS_SQL 包，它可以在每一步验证输入，这很有帮助。

DBMS_SQL 与本机动态 SQL

以下是使用 DBMS_SQL 包和使用本机动态 SQL（使用变量和字符串连接构造 SQL 语句）之间的区别。

这就是在 Oracle 中使用动态 SQL 的方式。

MySQL 中的动态 SQL

MySQL 在最新版本中支持动态 SQL。但是，它们被称为“预备语句”，因此如果您正在寻找 MySQL 中的动态 SQL，您可能会找到有关预备语句的更多信息。

让我们在MySQL中看一个这样的例子。

简单示例

这是 MySQL 中准备好的语句的简单示例。

PREPARE statement FROM 'SELECT id, customer_name FROM customer WHERE status =?';SET @customer_status = 1;EXECUTE statement USING @customer_status;

这个例子只有三行代码：

• Prepare 行将声明一个名为 statement 的变量，并将其设置为已指定的 SQL 查询。一个 ？表示可以提供参数的位置。
• Set 行会将名为 customer_status 的新变量设置为值 1。
• Execute 行将执行语句并接受 customer_status 变量的值。

输出将是 SELECT 查询的结果。

确定要查询的表

您还可以创建一个准备好的语句，该语句将在您运行它时确定要在哪个表上运行。

这是一个例子。此示例在 Prepare 语句之前有 Set 语句，这是可以的。

SET @table = 'customer';SET @sqlQuery = CONCAT('SELECT id, customer_name FROM ', @table);PREPARE statement FROM @sqlQueryEXECUTE statement;

这将对客户表运行查询并显示结果。

PostgreSQL 中的动态 SQL

PostgreSQL像 Oracle、SQL Server 和 MySQL 一样支持动态 SQL。

我发现官方文档令人困惑，并且几个 StackOverflow 答案并没有真正解释如何从头到尾编写一个。一些博客文章也很复杂，有些甚至是不正确的。

简单示例

这是在 PostgreSQL 中运行动态 SQL 的简单示例。我们创建了一个函数来演示这个概念。

CREATE OR REPLACE FUNCTION getCustomer(customer_status INTEGER)RETURNS SETOF customer_test AS  $$DECLARE  sqlQuery VARCHAR(2000);BEGIN  sqlQuery = 'SELECT id, customer_name, status FROM customer_test WHERE status = $1';  RETURN QUERY EXECUTE sqlQuery USING customer_status;END$$ LANGUAGE plpgsql;

可以使用以下语句调用此代码：

SELECT * FROM getCustomer(1);

以下是代码的工作原理：

• 第一行创建一个新函数或替换现有函数。
• customer_status 是函数的参数。
• Returns 行将返回 customer_test 的完整记录，这就是 SETOF 关键字的作用。
• $$ 表示分隔符，这意味着 $$ 之后和最后 $$ 之前的任何分号都不会被视为函数的结尾。
• 我们声明一个名为 sqlQuery 的变量来保存语句。
• 此变量在 Begin 块中设置。
• sqlQuery 包含一个 $1，表示它是一个参数，将在运行时提供。
• Return Query Execute 将运行指定的命令并返回结果。
• Using 关键字允许您为查询指定参数。在这种情况下，提供给函数的 customer_status 值被替换为 $1 在 sqlQuery 值中的位置。
• 然后我们用 END 和 $$ 结束函数。
• 最后一行将语言定义为 plpgsql，这是您在 PostgreSQL 中定义函数的方式。

当你运行 SELECT 语句时，你从函数中选择，函数参数是 1 的值，即 customer_status 值。

结论

在 SQL Server、Oracle、MySQL 和 PostgreSQL 中可以使用动态 SQL。它具有灵活的优点，适用于编写代码时不知道整个查询的情况（例如，从不同的表中选择）。但是，它确实有其缺点，我建议仅在使用静态或“常规”SQL 无法获得所需内容时才使用动态 SQL。

原文标题：Dynamic SQL: A Guide for SQL Server, Oracle, MySQL, and PostgreSQL

原文作者：Ben Brumm

原文链接：https://www.databasestar.com/dynamic-sql/