Oracle DBMS_SQL动态SQL迁移PostgreSQL：未知数量变量绑定问题

我正在将Oracle数据库应用迁移至PostgreSQL，原应用大量使用动态结构，需改写依赖Oracle DBMS_SQL的存储过程：构建动态SQL时通过循环添加未知数量的不同类型变量（原使用ANYDATA类型，现计划替换为JSONB数组），再通过dbms_sql.bind_variable绑定变量。以下为Oracle PL/SQL代码片段：

Oracle 构建动态SQL的循环代码 #

v_col_id := v_cols.first;
while v_col_id is not null loop
  if in_col_info.exists(v_col_id) then
    v_cmd := v_cmd || ',';
    v_cmd := v_cmd || ':' || in_col_info(v_col_id).name;
    v_cmd := v_cmd || ',';
    v_cmd := v_cmd || ':' ||
             mod_com.get_pf_col_name(in_col_info(v_col_id).name);
  end if;
  v_col_id := v_cols.next(v_col_id);
end loop;

Oracle 绑定变量的循环代码 #

v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, v_cmd, dbms_sql.native);
v_col_id := v_cols.first;
while v_col_id is not null loop
  if in_col_info.exists(v_col_id) then
    bind_anydata_value(v_cursor,
                       in_col_info(v_col_id),
                       v_cols(v_col_id));
    dbms_sql.bind_variable(v_cursor,
                           model_common.get_pf_col_name(in_col_info(v_col_id).name),
                           -1);
  end if;
  v_col_id := v_cols.next(v_col_id);
end loop;

v_result := dbms_sql.execute(v_cursor);

咨询问题：

• 能否通过PostgreSQL的EXECUTE...USING子句绑定未知数量的变量？
• 是否有类似Oracle dbms_sql.bind_variable的直接绑定方法？
• 能否借助format函数实现需求？

问题解答 #

1. EXECUTE...USING 能否绑定未知数量的变量？ #

PostgreSQL的EXECUTE...USING本身要求参数数量固定，但可以通过数组/JSONB容器间接传递未知数量的变量，再在动态SQL中解析提取。

比如将所有变量打包成数组，在动态SQL中用下标访问：

DECLARE
  v_sql text;
  v_vars anyarray := ARRAY['val1', 123, true]; -- 多类型变量数组
BEGIN
  -- 构建带占位符的SQL，$1代表整个数组
  v_sql := 'SELECT $1[1], $1[2], $1[3]';
  EXECUTE v_sql USING v_vars;
END;

如果变量类型差异大，用JSONB更灵活：

DECLARE
  v_sql text;
  v_vars jsonb := '["val1", 123, true, {"key": "val"}]'::jsonb;
BEGIN
  v_sql := 'SELECT $1->>0, ($1->1)::int, ($1->2)::boolean';
  EXECUTE v_sql USING v_vars;
END;

2. 有没有类似dbms_sql.bind_variable的直接绑定方法？ #

PostgreSQL没有和Oracle dbms_sql.bind_variable完全对应的逐次绑定API，但可以通过以下方式实现类似效果：

• 用PL/pgSQL循环构建动态参数列表：先收集所有变量到数组，再生成带$1,$2,...占位符的SQL，最后配合EXECUTE...USING传递数组元素（PostgreSQL支持用USING unnest(v_vars)展开数组为参数，注意类型一致性）。
• 利用pg_prepare和pg_execute系统函数：先预编译SQL，再通过数组传递参数，模拟分步绑定逻辑：

DECLARE
  v_stmt_name text := 'dyn_stmt_' || md5(random()::text);
  v_sql text := 'INSERT INTO tbl(col1, col2) VALUES($1, $2)';
  v_vars anyarray := ARRAY('val', 456);
BEGIN
  EXECUTE 'PREPARE ' || quote_ident(v_stmt_name) || ' AS ' || v_sql;
  EXECUTE 'EXECUTE ' || quote_ident(v_stmt_name) || ' USING $1' USING v_vars;
  DEALLOCATE v_stmt_name;
END;

不过这种方式不如EXECUTE...USING简洁，仅在特殊场景使用。

3. 能否借助format函数实现？ #

完全可以，format函数是PostgreSQL构建动态SQL的核心工具，能安全生成带占位符或标识符的SQL，配合EXECUTE...USING实现变量绑定。

比如模拟Oracle代码中循环生成占位符的逻辑：

DECLARE
  v_cmd text := 'INSERT INTO target_tbl(';
  v_cols text[] := ARRAY('col_a', 'col_b', 'col_c');
  v_values_placeholders text;
  v_vars anyarray := ARRAY('val_a', 100, '2024-01-01'::date);
BEGIN
  -- 生成列名部分（用%I确保标识符安全）
  v_cmd := v_cmd || format('%s)', array_to_string(array_agg(format('%I', col)), ', ')) FROM unnest(v_cols) col;
  
  -- 生成$1,$2...形式的占位符
  SELECT array_to_string(array_agg(format('$%s', idx)), ', ')
  INTO v_values_placeholders
  FROM generate_subscripts(v_vars, 1) idx;
  
  v_cmd := v_cmd || ' VALUES(' || v_values_placeholders || ')';
  
  -- 执行动态SQL，绑定变量数组
  EXECUTE v_cmd USING unnest(v_vars);
END;

format的%I用于转义标识符（避免SQL注入），配合$n占位符和USING绑定变量，比直接拼接字面量更安全，也能避免类型转换问题。

内容的提问来源于stack exchange，提问作者chrobotek