动态SQL在梧桐数据库的使用介绍

一、前言

  动态SQL是根据不同的条件生成不同的SQL语句，通过使用参数化查询或在应用程序代码中构建SQL字符串实现，对于处理不确定的查询条件或构建复杂的查询非常有用。因为日常开发中经常使用，故在梧桐数据库中测试验证，分享该语法的使用经验。

二、动态SQL的构建及使用

以下通过简单的例子介绍如何使用动态SQL：

CREATE or REPLACE FUNCTION public.p_dw_trend_sql_test(i_date character varying, OUT o_return_code character varying, OUT o_return_msg character varying)
	 RETURNS record AS
$BODY$

DECLARE

vs_dynstr1           varchar;		--定义动态SQL
vd_stat_date         date;		--定义日期参数
vs_stat_date         varchar;		--定义日期参数

begin
vs_stat_date := i_date;
vd_stat_date := date(i_date);

--变量赋值
vs_dynstr1 := '
insert into public.dw_***_***_test
select * from dmdb.dw_***_***_dm
where to_char(create_time,''yyyymm'')=to_char(date'''||vd_stat_date||''',''yyyymm'')
';

RAISE NOTICE 'vs_dynstr1 %', vs_dynstr1;		--打印执行的SQL
execute vs_dynstr1;		--执行动态SQL

o_return_code := 0 ;
o_return_msg := vs_dynstr1;

RETURN;

EXCEPTION WHEN OTHERS THEN
 o_return_code := SQLSTATE;
 o_return_msg  := 'SQL执行报错：'||SQLERRM;
RETURN;

END;
$BODY$
LANGUAGE plpgsql VOLATILE;

• 定义变量名vs_dynstr1：用来暂存动态SQL；

• 变量赋值：注入需要执行的SQL；

• 执行：通EXECUTE执行该动态SQL；

注：该语法需要使用存储过程封装。

总结：

动态SQL可以灵活的处理不同量的输入参数，例如访问指定分区，提升查询效率，具有很大的灵活性和适用性；

然而也增加了代码调试的难度和可读性，按需采用。

三、适用场景

  动态SQL有很好的灵活性和适用性，类比GP数据库使用基本无差异。

使用场景：

• 当SQL中存在不确定的参数时可以使用动态SQL语法；

• 当SQL中存在许多参数，可以通过一个输入通过转化生成其他参数；

• for循环时使用动态SQL语法。