本文分享自华为云社区《GaussDB数据库SQL系列-动态语句》，作者：Gauss 松鼠会小助手 2。

一、前言

在数据库中构建动态 SQL 语句是指根据不同的条件或参数创建不同的 SQL 语句。这通常是为了适应不同的业务需求，提高 SQL 的灵活性和效率。GaussDB 数据库是一款具备高性能、高可用性和高扩展性的关系型数据库，它提供了丰富的功能和工具，支持动态 SQL 语句的构建。下面我们将介绍如何使用 GaussDB 数据库构建动态 SQL 语句。

二、构建动态 SQL 语句的基本步骤和注意事项

1、基本步骤

• 分析需求：首先需要明确业务需求，了解需要执行哪些 SQL 查询操作，并根据需求的不同来动态构建 SQL 语句。

• 准备参数：根据查询操作的不同，准备相应的参数，如筛选条件、排序规则等。

• SQL 拼接：根据需求和参数，使用字符串拼接方式构建 SQL 语句。

• 执行查询：使用 GaussDB 数据库的查询接口，执行构建好的 SQL 语句并获取查询结果。

• 处理结果：将查询结果进行处理和展示，可以是前端页面或后端接口等形式。

2、主要事项

• 避免 SQL 注入：在拼接 SQL 语句时，务必注意避免 SQL 注入的风险，不要直接拼接用户输入的内容。

• 性能优化：对于大规模数据的查询操作，需要进行性能优化，如使用索引、分页查询等方式来提高查询效率。

• 事务处理：如果涉及事务处理，需要使用 GaussDB 数据库的事务管理功能来确保数据的一致性和可靠性。

• 安全性保障：对于敏感数据的查询操作，需要进行安全性保障，如数据脱敏、权限控制等方式来保护数据的安全。

三、GaussDB 中执行动态查询语句（示例）

GaussDB 提供两种方式：使用 EXECUTE IMMEDIATE、OPEN FOR 实现动态查询。前者通过动态执行 SELECT 语句，后者结合了游标的使用。当需要将查询的结果保存在一个数据集用于提取时，可使用 OPEN FOR 实现动态查询。

1、方式一：EXECUTE IMMEDIATE

--传递并检索值（INTO子句用在USING子句前）：
CREATE OR REPLACE FUNCTION dynamic_f()
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
d_id INT := 2;
d_name VARCHAR(20);
d_salary INT;
BEGIN
EXECUTE IMMEDIATE 'SELECT name,salary FROM company1 WHERE id = :1' INTO d_name,d_salary USING IN d_id;
RETURN '姓名:' || d_name || ' , 薪水:￥' ||d_salary;
END $$;
--执行
CALL dynamic_f();

主要属性说明：

• INTO 的变量 ：用于指定存放单行查询结果的变量。

• USING IN 的变量： 用于指定存放传递给动态 SQL 值的变量，在 SQL 拼接时可用占位符，占位符命名以“:”开始，后面可跟数字、字符或字符串，与 USING 子句的变量一一对应。

执行结果：

2、方式二：OPEN FOR

--使用OPEN FOR打开动态游标来执行
CREATE OR REPLACE FUNCTION dynamic_cur()
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
v_name VARCHAR2(20);
v_salary INT;
TYPE ref_type IS REF CURSOR; --定义游标类型
my_cur ref_type; --定义游标变量
BEGIN
OPEN my_cur FOR 'SELECT name,salary FROM company1 WHERE id = :1' USING '3'; --打开游标, using是可选的
FETCH my_cur INTO v_name, v_salary; --获取数据
WHILE my_cur%FOUND
LOOP
RETURN v_name||'#'||v_salary;
FETCH my_cur INTO v_name, v_salary;
END LOOP;
CLOSE my_cur; --关闭游标
END $$;
--执行
CALL dynamic_cur();

主要属性说明

'WHILE my_cur%FOUND'： 是一个循环控制语句。'my_cur'是一个游标，而'%FOUND'是游标状态。当游标找到符合条件的记录时，这个状态就会为真（也就是说，如果'my_cur%'FOUND 为真，那么就继续执行循环中的代码）。当游标没有更多的记录可返回时（或者达到了游标返回的最大记录数），这个状态就会为假，然后循环就会停止。所以，'WHILE my_cur%FOUND'的意思是：当游标'my_cur'还有记录可返回时，就继续执行循环中的代码。

执行结果

四、GaussDB 中的动态非查询语句（示例）

其实这个可以简单的理解为非“SELECT 语句”，基本写法跟前面的示例类似，下面继续以 company1 表为例：

--使用EXECUTE IMMEDIATE执行动态非查询语句
CREATE OR REPLACE FUNCTION dynamic_cur()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
v_id INT := 4;
v_name VARCHAR2(10) := 'ZhangSan';
v_age INT := 30;
v_address VARCHAR2(10) := 'BeiJing';
v_salary INT := 30000;
v_newname VARCHAR2(10) := 'company4';
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO company1 VALUES(:1, :2, :3, :4, :5)' USING v_id, v_name, v_age,v_address,v_salary;
EXECUTE IMMEDIATE 'ALTER TABLE company1 RENAME to ' || v_newname;
END $$;
--执行
CALL dynamic_cur();
--查看结果
SELECT * FROM company4;

执行结果

五、小结

通过使用 GaussDB 数据库构建动态 SQL 语句，数据应用部门可以更好地应对不断变化的数据查询需求，提高应用程序的性能和可维护性。本文主要介绍了如何使用 GaussDB 数据库构建动态 SQL 语句的基本步骤和注意事项，并通过实际案例进行了演示，欢迎大家测试、交流。

作者：酷哥

点击关注，第一时间了解华为云新鲜技术~