KingbaseES 为了更好地适应用户的oracle 应用,实现了对 plsql 的支持,用户可以根据需要使用 plsql 或 plpgsql。 以下简要介绍下二者的差异
一、格式差异
1、plpgsql
plpgsql 必须有 label
[ <<label>> ]
[ DECLARE declarations ]
BEGIN
statements
END [ label ];
具体例子:
create or replace function pgsql_test(part integer)
returns integer as $$
declare
v_total integer;
begin
if part = 0 then
select count(*) into v_total from t0;
else
select count(*) into v_total from t1;
end if;
return v_total;
end;
$$ language plpgsql;
2、plsql
plsql 不需要有 label
create or replace function plsql_func01(part integer)
returns integer as
declare
v_total integer;
begin
if part = 0 then
select count(*) into v_total from t0;
else
select count(*) into v_total from t1;
end if;
return v_total;
end;
如果后面没有指明language,KingbaseES 视database_mode参数决定采用哪种解析器。如果database_mode=oracle,默认采用的是plsql 解析器;如果database_mode=pg,则默认采用plpgsql解析器。
二、plsql 支持存储过程
PG11之前,对于plpgsql,函数实际上也称作存储过程,也就是实际没有 create procedure 的概念,函数可以完成数据库的DML操作,实际 function 和 procedure 功能上没有区别。
KingbaseES plsql 同时支持 function and procedure,与oracle function不同,KingbaseES function内部可以进行DML操作。
procedure:过程调用 call plsql_proc01();
create or replace procedure plsql_proc01
as
begin
insert into t0 values(1);
end;
function:函数调用 select plsql_func01();
create or replace function plsql_func01
return integer as
begin
insert into t0 values(1);
return 1;
end;
注:oracle plsql,function 必须作为表达式一部分进行调用,函数体内如果有 DML操作,在调用时是会报错,函数含有自治事务的除外。
三、是否支持嵌套事务
1、plpgsql
plpgsql 并不支持嵌套事务,函数中的事务总是由外层命令(函数的调用者)来控制的,它们本身无法开始或提交事务。
pgisdb=> create or replace function plsql_test(part integer)
pgisdb-> returns integer as
pgisdb-> declare
pgisdb-> v_total integer;
pgisdb-> begin
pgisdb-> if part = 0 then
pgisdb-> select count(*) into v_total from t0;
pgisdb-> commit;
pgisdb-> else
pgisdb-> select count(*) into v_total from t1;
pgisdb-> end if;
pgisdb-> return v_total;
pgisdb-> end;
pgisdb-> /
CREATE FUNCTION
pgisdb=> select plsql_test(0);
ERROR: invalid transaction termination
CONTEXT: PL/SQL function plsql_test(integer) line 7 at COMMIT
2、plsql
对于function,不支持函数体内 commit ,事务是否提交由外层事务决定。在V8R6C5版本,将支持函数commit。
pgisdb=> create or replace function plsql_func01
pgisdb-> return integer as
pgisdb-> begin
pgisdb-> insert into t0 values(1);
pgisdb-> commit;
pgisdb-> return 1;
pgisdb-> end;
pgisdb-> /
CREATE FUNCTION
pgisdb=> select plsql_func01();
ERROR: invalid transaction termination
CONTEXT: PL/SQL function plsql_func01() line 4 at COMMIT
对于procedure,内部允许commit or rollback。
pgisdb=> create or replace procedure plsql_proc01
pgisdb-> as
pgisdb-> begin
pgisdb-> insert into t0 values(1);
pgisdb-> commit;
pgisdb-> end;
pgisdb-> /
CREATE PROCEDURE
pgisdb=> call plsql_proc01();
CALL
注意:如果过程体内含有commit or rollback,则不能在外层再 begin transaction。
pgisdb=> create or replace procedure plsql_proc01
pgisdb-> as
pgisdb-> begin
pgisdb-> insert into t0 values(1);
pgisdb-> commit;
pgisdb-> end;
pgisdb-> /
CREATE PROCEDURE
pgisdb=> begin;
BEGIN
pgisdb=> call plsql_proc01();
ERROR: invalid transaction termination
CONTEXT: PL/SQL function plsql_proc01() line 4 at COMMIT
四、性能比较
create or replace function pgsql_test()
returns integer as $$
declare
v_total integer;
begin
select count(*) into v_total from t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
return v_total;
end;
$$ language plpgsql;
test=# begin
test-# for i in 1..100000 loop
test-# perform pgsql_test();
test-# end loop;
test-# end;
test-# /
ANONYMOUS BLOCK
Time: 2450.671 ms (00:02.451)
============
create or replace function plsql_test()
returns integer as
declare
v_total integer;
begin
select count(*) into v_total from t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
return v_total;
end;
/
test=# begin
test-# for i in 1..100000 loop
test-# perform plsql_test();
test-# end loop;
test-# end;
test-# /
ANONYMOUS BLOCK
Time: 2454.302 ms (00:02.454)
五、其他差异
1、循环变量定义
plsql: 循环变量 i 可以不用提前定义
create or replace procedure plsql_proc() as
begin
for i in select regexp_split_to_table('ab,bc,cd',',') loop
raise notice '%',i;
end loop;
end;
plpgsql: 循环变量必须提取定义
create or replace procedure plpgsql_proc() as
$$
begin
for i in select regexp_split_to_table('ab,bc,cd',',') loop
raise notice '%',i;
end loop;
end;
$$ language plpgsql;
ERROR: loop variable of loop over rows must be a record variable or list of scalar variables
LINE 4: for i in select regexp_split_to_table('ab,bc,cd',',') loop
create or replace procedure plpgsql_proc() as
$$
declare
v_text text;
begin
for v_text in select regexp_split_to_table('ab,bc,cd',',') loop
raise notice '%',v_text;
end loop;
end;
$$ language plpgsql;
2、execute & execute immediate
plsql 支持 execute or execute immediate , plpgsql 只支持 execute