PostgreSQL动态SQL(兼容oracle DBMS_SQL)
PostgreSQL动态SQL(兼容oracle DBMS_SQL)
oracle中的dbms_sql包可以用来执行动态SQL，让我们在存储过程的动态SQL中使用prepared statement。
oracle中dbms_sql包使用介绍：
例子：
oracle：
DECLARE
stmt VARCHAR2(200);
dept_no_array DBMS_SQL.NUMBER_TABLE;
c NUMBER;
dummy NUMBER;
begin
dept_no_array(1) := 10; dept_no_array(2) := 20; /* Put some values into the array */
dept_no_array(3) := 30; dept_no_array(4) := 40;
dept_no_array(5) := 30; dept_no_array(6) := 40;
stmt := 'delete from emp where deptno = :dept_array'; /* A Dynamic SQL String with a bind variable */
c := DBMS_SQL.OPEN_CURSOR; /* Open a Cursor! */
DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE); /* Parse the Dynamic SQL , making it happen on the native database to which is connected! */
DBMS_SQL.BIND_ARRAY(c, ':dept_array', dept_no_array, 1, 4);
/* Bind only elements 1 through 4 to the cursor Happens 4 times */
dummy := DBMS_SQL.EXECUTE(c);
/* Execute the Query, and return number of rows deleted! */
DBMS_SQL.CLOSE_CURSOR(c);
EXCEPTION WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(c) THEN
DBMS_SQL.CLOSE_CURSOR(c);
END IF;
RAISE;
END;
/
pg中可以使用服务端绑定变量的方法来实现类似的功能，大致步骤为：
1、PREPARE，准备DB端绑定变量SQL
2、EXECUTE，绑定并执行
3、DEALLOCATE，删除绑定变量
因此可以将上述代码改为：
do language plpgsql $$
DECLARE
stmt VARCHAR(200);
dept_no_array numeric[];
c numeric;
begin
dept_no_array[1] := 10; dept_no_array[2] := 20; /* Put some values into the array */
dept_no_array[3] := 30; dept_no_array[4] := 40;
dept_no_array[5] := 30; dept_no_array[6] := 40;
execute format('prepare stmt(numeric) as delete from emp where deptno = $1'); /* A Dynamic SQL String with a bind variable */
foreach c in array dept_no_array[1:4]
loop
execute format('execute stmt(%s)', c); -- 执行绑定SQL
end loop;
DEALLOCATE stmt;
EXCEPTION WHEN OTHERS THEN
DEALLOCATE stmt;
RAISE;
END;
$$;
如果不需要使用绑定变量的话：
do language plpgsql $$
DECLARE
dept_no_array numeric[];
c numeric;
begin
dept_no_array[1] := 10; dept_no_array[2] := 20; /* Put some values into the array */
dept_no_array[3] := 30; dept_no_array[4] := 40;
dept_no_array[5] := 30; dept_no_array[6] := 40;
foreach c in array dept_no_array[1:4]
loop
delete from emp where deptno = c;
end loop;
DEALLOCATE stmt;
EXCEPTION WHEN OTHERS THEN
DEALLOCATE stmt;
RAISE;
END;
$$;
在pg中动态SQL和绑定变量这两种方式性能又如何呢？
1、建表
do language plpgsql $$
declare
begin
execute 'drop table if exists test';
execute 'create table test(id int primary key, info text, crt_time timestamp)';
for i in 0..1023 loop
execute format('drop table if exists test%s', i);
execute format('create table test%s (like test including all)', i);
end loop;
end;
$$;
2、使用动态SQL，写入目标子表
create or replace function dyn_pre(int) returns void as $$
declare
suffix int := mod($1,1024);
begin
execute format('insert into test%s values(%s, md5(random()::text), now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', suffix, $1);
end;
$$ language plpgsql strict;
3、使用绑定变量，写入目标子表
create or replace function dyn_pre(int) returns void as $$
declare
suffix int := mod($1,1024);
begin
execute format('execute p%s(%s)', suffix, $1);
exception when others then
execute format('prepare p%s(int) as insert into test%s values($1, md5(random()::text), now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', suffix, suffix);
execute format('execute p%s(%s)', suffix, $1);
end;
$$ language plpgsql strict;
4、性能测试
vi test.sql
\set id random(1,1000000000)
select dyn_pre(:id);
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 16 -j 16 -T 60
动态SQL：
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 16
duration: 60 s
number of transactions actually processed: 2543070
latency average = 0.377 ms
latency stddev = 0.254 ms
tps = 42376.154026 (including connections establishing)
tps = 42380.640279 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set id random(1,1000000000)
0.376 select dyn_pre(:id);
绑定变量：
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 16
duration: 60 s
number of transactions actually processed: 3105687
latency average = 0.308 ms
latency stddev = 0.399 ms
tps = 51747.630800 (including connections establishing)
tps = 51759.435224 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set id random(1,1000000000)
0.308 select dyn_pre(:id);
1、使用动态SQL，TPS约4.2万。
2、使用函数内绑定变量，TPS约5.1万。
实验使用的是虚拟机，物理机上绑定变量性能差异更明显。
参考链接：
https://docs.oracle.com/database/121/TTPLP/d_sql.htm#TTPLP71257
https://www.postgresql.org/docs/devel/static/sql-prepare.html
