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下运行。

具体可参见42.2. Structure of PL/oraSQL (light-pg.com)。或百度 lightdb oracle xxx

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