lightdb22.3 plorasql与oracle plsql对比(存储过程、函数、匿名块、包)

背景

借存储过程开发, 总结oracle plsql常用四大方面内容(存储过程、函数、匿名块、包)特点, 并与lightdb22.3 plorasql进行对比.总结差异点,为下次完善plorasql做准备

函数

对比结果

（1）oracle函数中部不支持dml语句和事务相关的操作（commit和rollback)
带dml语句和事务相关的操作（commit和rollback)的函数创建能够成功,执行该函数时报错.
（2）在plorasql中支持dml语句,不支持事务相关的操作（commit和rollback)
带事务相关的操作（commit和rollback)的创建函数不报错，调用函数执行报错,与oracle一致
（3）后续开发过程中需要对dml语句进行限制

函数中能调用存储过程,存储过程中能调用函数吗?

这个问题一直困扰着我,换个说法就是,如果能过被调用,函数以怎样的形式被调用?

oracle中调用函数进行赋值,使用:=赋值语句(lightdb支持)

v_sum := get_salary(30,v_num);
get_salary为创建的plsql函数.
为什么使用赋值的方式来调用存储过程,原因:
(1)函数具有返回值,可以将函数的值在存储过程和匿名块中进行传递;
(2)函数本身作为表达式出现在存储过程和匿名块中.

作为表达式进行验证

create or replace function tt_try1 return int as
CURSOR c1 IS
SELECT * FROM ltbench_accounts WHERE abalance > tt_try() ;
emp_rec ltbench_accounts%ROWTYPE;
id int;
BEGIN
OPEN c1;
–COMMIT;
–SELECT tt_try2() INTO id FROM dual;
LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line ('emp_rec.abalance = ’ || emp_rec.abalance);
–UPDATE ltbench_accounts SET abalance = 12 WHERE abalance = 11;
–COMMIT; – releases locks
END LOOP;
return 1;
END;
/
函数tt_try1在定义游标时,使用自定义函数tt_try.

oracle函数中不能使用存储过程(lightdb支持)

oracle编译不报错,执行报错

tt_try2为存储过程.

oracle函数中同样能嵌套匿名块(lightdb不支持)

create or replace function tt_try return int as
  CURSOR c1 IS
    SELECT * FROM ltbench_accounts;
  emp_rec  ltbench_accounts%ROWTYPE;
BEGIN
  OPEN c1;
  --COMMIT;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line ('emp_rec.abalance = ' || emp_rec.abalance);
    --UPDATE ltbench_accounts SET abalance = 12 WHERE abalance = 11;
    --COMMIT;  -- releases locks
  END LOOP;
  return 1;
END;
/

create or replace function tt_try1 return int as
  CURSOR c2 IS
    SELECT * FROM ltbench_accounts WHERE abalance > tt_try() ;
   CURSOR c1 IS
    SELECT * FROM ltbench_accounts;
  emp_rec  ltbench_accounts%ROWTYPE;
 id int;
BEGIN

BEGIN
  OPEN c1;
 --UPDATE ltbench_accounts SET abalance = abalance WHERE abalance = 12;
  --COMMIT;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line ('emp_rec.abalance = ' || emp_rec.abalance);

    --COMMIT;  -- releases locks
  END LOOP;
 CLOSE c1;
END;

  --tt_try2();
  OPEN c2;
  --COMMIT;
 --SELECT tt_try2() INTO id FROM dual; 
  LOOP
    FETCH c2 INTO emp_rec;
    EXIT WHEN c2%NOTFOUND;
    dbms_output.put_line ('emp_rec.abalance = ' || emp_rec.abalance);
    --UPDATE ltbench_accounts SET abalance = 12 WHERE abalance = 11;
    --COMMIT;  -- releases locks
  END LOOP;
  return 1;
END;
/

oracle结果图:

lightdb结果图:

差异原因:与事务处理机制有关.

匿名块

测试一

匿名块：（1）open游标之前使用commit，（2）open游标之后使用commit（3）loop循环里使用commit
预期（oracle执行结果）：update更新的内容，会被提交，不会close掉该游标，可以继续进行fetch操作
结果：与预期一致。

DECLARE
CURSOR c1 IS
      SELECT * FROM ltbench_accounts;
      emp_rec  ltbench_accounts%ROWTYPE;
BEGIN
  UPDATE ltbench_accounts SET id = 1 WHERE abalance = 12;
  commit;
  OPEN c1;
  UPDATE ltbench_accounts SET id = 2 WHERE abalance = 12;
  commit;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line ('emp_rec.abalance = ' || emp_rec.abalance);、
    UPDATE ltbench_accounts SET id = 3 WHERE abalance = 12;
      commit;
  END LOOP;
END;
/

测试二

匿名块（1）open游标之前，使用rollback回滚掉update更新记录；（2）在loopopen游标之后，使用rollback；（3）循环里使用rollback,如有dml语句，会进行回滚，但不会关闭游标从。
预期（oracle执行结果）：update更新的内容，会被回滚；并且可以继续使用游标进行操作
结果：与预期一致。

DECLARE
CURSOR c1 IS
      SELECT * FROM ltbench_accounts;
      emp_rec  ltbench_accounts%ROWTYPE;
BEGIN
  UPDATE ltbench_accounts SET id = 13 WHERE abalance = 12;
  rollback;
  OPEN c1;
  UPDATE ltbench_accounts SET id = 14 WHERE abalance = 12;
  rollback;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line ('emp_rec.abalance = ' || emp_rec.abalance);
    UPDATE ltbench_accounts SET id = 15 WHERE abalance = 12;
      rollback;
  END LOOP;
END;
/

存储过程

测试一

匿名块：（1）open游标之前使用commit，（2）open游标之后使用commit（3）loop循环里使用commit
预期（oracle执行结果）：update更新的内容，会被提交，不会close掉该游标，可以继续进行fetch操作
结果：与预期一致。
`

create or replace procedure tt_try as
CURSOR c1 IS
      SELECT * FROM ltbench_accounts;
      emp_rec  ltbench_accounts%ROWTYPE;
BEGIN
  UPDATE ltbench_accounts SET id = 16 WHERE abalance = 12;
  commit;
  OPEN c1;
  UPDATE ltbench_accounts SET id = 17 WHERE abalance = 12;
  commit;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line ('emp_rec.abalance = ' || emp_rec.abalance);
    UPDATE ltbench_accounts SET id = 18 WHERE abalance = 12;
      commit;
  END LOOP;
END;
/

测试二

存储过程：（1）open游标之前，使用rollback回滚掉update更新记录；（2）在loopopen游标之后，使用rollback；（3）循环里使用rollback,如有dml语句，会进行回滚，但不会关闭游标从。
预期（oracle执行结果）：update更新的内容，会被回滚；并且可以继续使用游标进行操作
结果：与预期一致。

create or replace procedure tt_try as
CURSOR c1 IS
      SELECT * FROM ltbench_accounts;
      emp_rec  ltbench_accounts%ROWTYPE;
BEGIN
  UPDATE ltbench_accounts SET id = 21 WHERE abalance = 12;
  rollback;
  OPEN c1;
  UPDATE ltbench_accounts SET id = 22 WHERE abalance = 12;
  rollback;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line ('emp_rec.abalance = ' || emp_rec.abalance);
    UPDATE ltbench_accounts SET id = 23 WHERE abalance = 12;
      rollback;
  END LOOP;
END;
/

包

oracle中要使用dml语句、commit和rollback，需要声明包中函数或者存储过程为一个自治事务。包中函数或者存储过程声明处加上：pragma autonomous_transaction。
因此lightdb目前在包中不支持commit和rollback和fetch结合使用，其本身使用rollback和commit也是有问题的。

总结

风险和利益成正比。