PostgreSQL Oracle 兼容性之 - DBMS_SQL(存储过程动态SQL中使用绑定变量-DB端prepare statement)
背景
Oracle中有一个dbms_sql的包，可以让你在存储过程的动态SQL中使用prepared statement。如果动态SQL的调用频次较高，硬解析可能导致一些性能问题（Oracle硬解析的CPU消耗还是蛮大的）。
https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#i1028953
DMBS_SQL里面包含的一些类型
Bulk SQL Types
BFILE_TABLE
BINARY_DOUBLE_TABLE
BLOB_TABLE
CLOB_TABLE
DATE_TABLE
INTERVAL_DAY_TO_SECOND_TABLE
INTERVAL_YEAR_TO_MONTH_TABLE
NUMBER_TABLE
TIME_TABLE
TIME_WITH_TIME_ZONE_TABLE
TIMESTAMP_TABLE
TIMESTAMP_WITH_LTZ_TABLE
UROWID_TABLE
VARCHAR2_TABLE
其中本文用到的 NUMBER_TABLE
TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
在PostgreSQL的INLINE CODE或者FUNCTION中如何使用动态SQL，如何使用绑定变量，如何使用BULK类型呢？
PostgreSQL INLINE CODE或函数的动态SQL、绑定变量使用
首先借这个链接，解释一下Oracle dbms_sql的使用
https://stackoverflow.com/questions/21335493/can-somebody-explain-dbms-sql-number-table
Better to understand DBMS_SQL itself to some extent, before understanding NUMBER_TABLE.
( I do this for My Learning!)
NUMBER_TABLE
is Actually,
TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
So, only numbers are allowed!
FlowChart on How DBMS_SQL Works! :
Your interested area comes in bind variable box
-----------
-- | open_cursor |
-- -----------
-- |
-- |
-- v
-- -----
-- ------------>| parse |
-- | -----
-- | |
-- | | ---------
-- | v |
-- | -------------- |
-- |-------->| bind_variable | |
-- | ^ ------------- |
-- | | | |
-- | -----------| |
-- | |<--------
-- | v
-- | query?---------- yes ---------
-- | | |
-- | no |
-- | | |
-- | v v
-- | ------- -------------
-- |----------->| execute | ->| define_column |
-- | ------- | -------------
-- | |------------ | |
-- | | | ----------|
-- | v | v
-- | -------------- | -------
-- | ->| variable_value | | ------>| execute |
-- | | -------------- | | -------
-- | | | | | |
-- | ----------| | | |
-- | | | | v
-- | | | | ----------
-- | |<----------- |----->| fetch_rows |
-- | | | ----------
-- | | | |
-- | | | v
-- | | | -----------------
-- | | | | column_value |
-- | | | | variable_value |
-- | | | -----------------
-- | | | |
-- | |<--------------------------
-- | |
-- -----------------|
-- |
-- v
-- ------------
-- | close_cursor |
-- ------------
例子
Example: In a DELETE statement, for example, you could bind in an array in the WHERE clause and have the statement be run for each element in the array:
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;
/
P.S. Pure rip-off, with some more commments ,from Oracle
PostgreSQL 服务端绑定变量的用法与之类似
PREPARE，准备DB端绑定变量SQL
EXECUTE，绑定并执行
DEALLOCATE，删除绑定变量
详见：
https://www.postgresql.org/docs/devel/static/sql-prepare.html
因此以上Oracle的代码可以改成如下：
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;
$$;
例子
建表
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;
$$;
使用动态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;
使用绑定变量，写入目标子表
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;
性能对比
vi test.sql
\set id random(1,1000000000)
select dyn_pre(:id);
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120
1、使用动态SQL，TPS约5.2万。
2、使用函数内绑定变量，TPS约13.4万。
参考
https://www.postgresql.org/docs/devel/static/sql-prepare.html
https://stackoverflow.com/questions/21335493/can-somebody-explain-dbms-sql-number-table