本文共 7535 字，大约阅读时间需要 25 分钟。
PostgreSQL , Oracle , pl/sql , 存储过程 , plpgsql , 绑定变量 , DB端绑定变量 , prepare , DEALLOCATE , EXECUTE
Oracle中有一个dbms_sql的包，可以让你在存储过程的动态SQL中使用prepared statement。如果动态SQL的调用频次较高，硬解析可能导致一些性能问题（Oracle硬解析的CPU消耗还是蛮大的）。
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类型呢？
首先借这个链接，解释一下Oracle dbms_sql的使用
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
PREPARE，准备DB端绑定变量SQL
EXECUTE，绑定并执行
DEALLOCATE，删除绑定变量
详见：
因此以上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 $$declarebegin 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万。
转载地址：http://kakdl.baihongyu.com/