lightdb匿名块及对oracle pl/sql的支持（oracle pl/sql转plpgsql）

先看postgresql中的匿名块
do $$
declare i record;

begin
for i in 100000..300000
loop 
insert
    into
    scott.departments
select
    100000 + i,
    'dept' || 100000 + i,
    now(),
    now();

commit;
end loop;

END$$;

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT table_schema, table_name FROM information_schema.tables
             WHERE table_type = 'VIEW' AND table_schema = 'public'
    LOOP
        EXECUTE 'GRANT ALL ON ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' TO webuser';
    END LOOP;
END$$;

　　虽然支持匿名块，但$$总觉得不方便，和mysql一样。虽然自从postgresql 11开始增加了对存储过程（在此之前仅支持函数）的支持，并且其中支持事务（函数不支持内部事务提交）。

　　在lightdb 22.2中，已经完全支持oracle PL/SQL。对于从oracle迁移到lightdb的用户，将真正享受到一份存储过程代码可同时运行在oracle/lightdb中。如下：

DECLARE
   howmany     NUMBER;
BEGIN
    for howmany in 1..16 loop
        insert into lem_db_log select i,current_timestamp,'','field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100),'field' || round(random()*100) from generate_series(1,50000000) i;
    end loop;
END;
/

上面结构上语法上完全是一个oracle pl/sql语法的，它可以直接在lightdb下运行。

当然匿名块中也可以带事务，如下：

-- 匿名块事务性能性能测试
DECLARE                        
   howmany     NUMBER;
BEGIN
    for howmany in 1000000..2000000 loop
        update simple_example_local set (varchar01,varchar02,varchar03) = ('joinid01' || howmany,'joinid02' || howmany,'joinid03' || howmany) where id = howmany; commit;
    end loop;
END;
/

 嵌套表与关联数组在动态动态bulk collect中的使用：

select dbms_output.serveroutput(true);
declare
type i_list is table of integer;
i_arr i_list;
begin
execute immediate 'select 1' bulk collect into i_arr;
for i in 1..i_arr.count loop
dbms_output.put_line('id(' || i || '): ' || i_arr(i));
end loop;
end;
/


select dbms_output.serveroutput(true);
declare
type i_list is table of integer index by binary_integer;
i_arr i_list;
begin
 select 1 bulk collect into i_arr;
for i in 1..i_arr.count loop
dbms_output.put_line('id(' || i || '): ' || i_arr(i));
end loop;
end;
/


select dbms_output.serveroutput(true);
declare
type i_list is table of integer index by binary_integer;
i_arr i_list;
begin
execute immediate 'select 1' bulk collect into i_arr;
for i in 1..i_arr.count loop
dbms_output.put_line('id(' || i || '): ' || i_arr(i));
end loop;
end;
/

 

具体可参见42.2. Structure of PL/oraSQL (light-pg.com)。或百度 lightdb oracle xxx特性，基本能搜到lightdb对oracle的兼容情况。

oracle pl/sql转plpgsql可以使用工具：http://www.sqlines.com/oracle-to-postgresql、https://www.convert-in.com/o2pcode.htm、https://github.com/barsgroup/plsql-postgresql-converter