PostgreSQL Oracle 兼容性之 - PL/SQL record, table类型定义
背景
Oracle PL/SQL是非常强大的一门SQL编程语言，许多Oracle用户也使用它来处理一些要求延迟低且数据一致性或可靠性要求很高的业务逻辑。
PostgreSQL也有一门非常高级的内置SQL编程语言，plpgsql。与Oracle PL/SQL语法极其类似，但是还是有一些不一样的地方。
(PS：除了plpgsql，PostgreSQL还支持C，java，python，perl等流行的语言作为数据库的函数编程语言)
本文是针对有Oracle用户遇到的一些函数语法与PostgreSQL不兼容的地方，给出的修改建议。
涉及type xx is table of xxxx index by binary_integer语法、type xx is record语法。
Oracle PL/SQL 例子
CREATE OR REPLACE FUNCTION f_xml(p_xml CLOB) RETURN INT
AS
...
type rec_tk is record
(
tkno VARCHAR2(100) ,
cg_zdj number(12,0) := 0 ,
cg_jsf number(12,0) := 0
);
type tklist is table of rec_tk index by binary_integer;
type rec_cjr is record
(
cjrid varchar2(30) ,
tk tklist
);
type cjr is table of rec_cjr index by binary_integer;
p_cjrs cjr;
FOR j IN 0..v_nllen-1 LOOP
BEGIN
...
p_cjrs(j).cjrid := v_nodevalue;
...
p_cjrs(j).tk(v_tkcount).tkno := v_nodevalue;
p_cjrs(j).tk(v_tkcount).cg_zdj := nvl(v_nodevalue,0);
p_cjrs(j).tk(v_tkcount).cg_jsf := nvl(v_nodevalue,0);
...
v_tkcount:=v_tkcount+1;
END LOOP;
在这个例子中，用到了Oracle在PL/SQL中支持的type定义，以及type table 的定义，这个在PostgreSQL中用法不太一样。
PostgreSQL PL/SQL 兼容性例子
PostgreSQL的type定义需要在数据库中定义，而不是函数中定义。
以上PL/SQL函数在plpgsql中需要作出如下调整：
1.
type rec_tk is record
(
tkno VARCHAR2(100) ,
cg_zdj number(12,0) := 0 ,
cg_jsf number(12,0) := 0
);
type tklist is table of rec_tk index by binary_integer;
修改为
函数外执行创建类型的SQL
create type rec_tk as
(
tkno VARCHAR(100) ,
cg_zdj numeric(12,0) ,
cg_jsf numeric(12,0)
);
2.
type rec_cjr is record
(
cjrid varchar2(30) ,
tk tklist
);
type cjr is table of rec_cjr index by binary_integer;
p_cjrs cjr;
修改为
函数外执行创建类型的SQL
create type rec_cjr as
(
cjrid varchar(30) ,
tk rec_tk[]
);
函数内对table的使用修改为数组的使用，数组的下标从1开始。
p_cjrs rec_cjr[];
3.
p_cjrs(j).cjrid := v_nodevalue;
...
p_cjrs(j).tk(v_tkcount).tkno := v_nodevalue;
p_cjrs(j).tk(v_tkcount).cg_zdj := nvl(v_nodevalue,0);
p_cjrs(j).tk(v_tkcount).cg_jsf := nvl(v_nodevalue,0);
plpgsql目前不能直接修改复合数组对应的composite.element
需要修改为
declare
v_p_cjrs rec_cjr;
v_tk rec_tk;
...
v_p_cjrs.cjrid := v_nodevalue;
p_cjrs[j] := v_p_cjrs.cjrid;
...
v_tk.tkno := v_nodevalue;
v_tk.cg_zdj := nvl(v_nodevalue,0);
v_tk.cg_jsf := nvl(v_nodevalue,0);
v_p_cjrs.tk[v_tkcount] := v_tk;
p_cjrs[j] := v_p_cjrs;
或者请参考如下例子
do language plpgsql $$
declare
vtk rec_tk;
vtk_a rec_tk[];
vcjr rec_cjr;
vcjr_a rec_cjr[];
begin
vtk := row('a', 1,2);
-- or vtk.tkno := 'a'; vtk.cg_zdj := 1; vtk.cg_jsf := 2;
vtk_a[1] := vtk;
vcjr := row('test', vtk_a);
-- or vcjr := row('test', array[row('a',1,2)]);
-- or vcjr.cjrid := 'test'; vcjr.tk := vtk_a;
-- or vcjr_a[1] := row('test', array[row('a',1,2)]);
vcjr_a[1] := vcjr;
raise notice 'vtk %, vtk_a % vcjr % vcjr_a % ', vtk, vtk_a, vcjr, vcjr_a;
end;
$$;
NOTICE: 00000: vtk (a,1,2), vtk_a {"(a,1,2)"} vcjr (test,"{""(a,1,2)""}") vcjr_a {"(test,\"{\"\"(a,1,2)\"\"}\")"}
LOCATION: exec_stmt_raise, pl_exec.c:3216
DO
nvl函数参考PostgreSQL Oracle兼容包orafce。
4.
PostgreSQL array用法简介
http://blog.163.com/digoal@126/blog/static/163877040201201275922529/
https://www.postgresql.org/docs/9.5/static/arrays.html
https://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY
plpgsql编程，基于数组的循环
[ <<label>> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
statements
END LOOP [ label ];
行列转换调用unnest函数即可
https://www.postgresql.org/docs/9.5/static/functions-array.html
unnest(ARRAY[1,2])
1
2
小结
1. 使用composite type替代了PL/SQL的type定义。
2. 使用array替代了PL/SQL的table定义。
3. 复合类型的数组，不能直接修改复合类型的element，需要先用标量修改好后赋值。
RDS PG内核改进建议
1. 新增 CREATE TYPE [ IF NOT EXISTS ] 语法。这样的话用户就不需要将这个语法写在函数外了，可以在函数内直接执行。
2. PL/SQL的type是局部变量，而PostgreSQL的type是全局的，这个也需要注意，如果多个PL/SQL函数用到了同样的type name但是结构不一样，port到plpgsql时，需要创建多个type，在plpgsql中分别使用对应的type name。
3. plpgsql 暂时不支持composite数组直接设置element的值，需要加强plpgsql的语法功能。