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

书接上回

这次增加的内容：
（1）包
（2）打开游标的方式
（2.1）open cursorname for sql_variable;
Oracle、lightdb都支持
（2.2）open cursorname for sql_variable using value.
Oracle、lightdb都支持
（3）lightdb 游标操作和事务操作（commit和rollback）结合使用的情况

其中包和打开游标的方式会在下面测试用例中用到，不做过多介绍。

lightdb 游标操作和事务操作（commit和rollback）结合使用的情况

主要介绍游标操作和事务操作（commit和rollback）结合使用的情况。具体分为以下5大类：
1、函数
函数中调函数：不支持
函数中调过程：不支持
函数中调包中函数、存储过程：
(1)调包函数：不支持
(2)调包过程：不支持
2、存储过程
存储过程中调函数：不支持
存储过程中调过程：支持
存储过程中调包中函数、存储过程：
(1)调包函数：不支持
(2)调包过程：支持
3、匿名块
匿名块中调函数:不支持
匿名块中调过程：支持
匿名块中调包中函数、存储过程：
(1)调包函数：不支持
(2)调包过程：支持
4、包中函数
包函数调函数：不支持
包函数调存储过程：不支持
包函数调包中函数、存储过程：
(1)调包函数：不支持
(2)调包过程：不支持
5、包中存储过程
包存储过程调函数：不支持
包存储过程调存储过程：支持
包存储过程调包中函数、存储过程
(1)调包函数:不支持
(2)调包过程:支持

测试案例

存储过程调用存储过程

--前提条件
create extension IF NOT EXISTS orafce;
set lightdb_syntax_compatible_type = oracle;
SET search_path TO public, oracle;

create table ltbench_accounts(id int, abalance int);

--insert
declare
  irevert int := 9999;
  icount int := 1000;
begin
  loop
    execute immediate 'insert into ltbench_accounts values(:er,:rt)' using irevert, icount;
    icount := icount - 1;
    irevert := irevert -1;
    exit when icount < 1;
  end loop;
end;
/

create or replace procedure tt_try_proc as
  CURSOR c1 IS
      SELECT * FROM ltbench_accounts;
  CURSOR c2 IS
      SELECT * FROM ltbench_accounts;
  emp_rec  ltbench_accounts%ROWTYPE;
BEGIN
  OPEN c2;
  OPEN c1;
  UPDATE ltbench_accounts SET id = 25 WHERE abalance = 12;
  rollback;
  UPDATE ltbench_accounts SET id = 17 WHERE abalance = 13;
  commit;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line ('emp_rec.id = ' || emp_rec.id || 'emp_rec.abalance = ' || emp_rec.abalance);
    UPDATE ltbench_accounts SET id = 23 WHERE abalance = 12;
    rollback;
  END LOOP;
  LOOP
    FETCH c2 INTO emp_rec;
    EXIT WHEN c2%NOTFOUND;
    dbms_output.put_line ('emp_rec.id = ' || emp_rec.id || 'emp_rec.abalance = ' || emp_rec.abalance);
    UPDATE ltbench_accounts SET id = 18 WHERE abalance = 13;
    commit;
  END LOOP;
  commit;
  rollback;
END;
/
create or replace procedure tt_try_dproc as
begin
  tt_try_proc();
  dbms_output.put_line ('This is just a test');
end;
/
call tt_try_dproc();

存储过程调包中的存储过程

前提条件

create table test(x int, y varchar(100));
insert into test values(1, 'One');
insert into test values(2, 'Two');
insert into test values(3, 'Three');

创建包

--create package
create or replace package pkg is
  CURSOR c1 IS SELECT x,y FROM test;
  function c_open return int;
  function c_fetch return int;
  procedure c_open_fetch;
end;
/

--create package boby
create or replace package body pkg IS
  function c_open return int as
  begin
    OPEN c1;
    return 0;
  end;
  /
  
  --The fetch in the package, the cursor will not be cleared
  function c_fetch return int AS
    v_x  test.x%TYPE;
    v_y  test.y%TYPE;
  begin
    LOOP
      FETCH c1 INTO v_x, v_y;
      EXIT WHEN c1%NOTFOUND;
      dbms_output.put_line( v_x || '.' || v_y);
    END LOOP;
    --CLOSE c1;
    return 0;
  end;

  procedure c_open_fetch as
    v_x  test.x%TYPE;
    v_y  test.y%TYPE;
  BEGIN
    OPEN c1;
    COMMIT;
    LOOP
      FETCH c1 INTO v_x, v_y;
      EXIT WHEN c1%NOTFOUND;
      dbms_output.put_line( v_x || '.' || v_y);
      IF c1%FOUND THEN
        UPDATE test SET x = 4 WHERE y = 'Two';
        ROLLBACK;
      END IF;
    END LOOP;
    CLOSE c1;
  end;
begin
  dbms_output.put_line('initializer');
    --same as oracle, package not support dml, commit and roolback.
    --UPDATE test SET x = 5 WHERE y = 'Two';
    --COMMIT;
end;
/

--procedure of package callled by procedure
create or replace procedure tt_try_proc as
begin
  pkg.c_open_fetch();
  dbms_output.put_line ('This is just a test');
end;
/

call tt_try_proc();

匿名块调存储过程

begin
  tt_try_proc();
end;
/

匿名块调用包中的存储过程

--procedure of package called by anonymous block
begin
  pkg.c_open_fetch();
  dbms_output.put_line ('This is just a test');
end;
/

存储过程调包中的存储过程

--create package
create or replace package pkg_proc is
  CURSOR c1 IS SELECT x,y FROM test;
  procedure c_proc;
end;
/

--create package boby
create or replace package body pkg_proc IS
  procedure c_proc as
    v_x  test.x%TYPE;
    v_y  test.y%TYPE;
  BEGIN
    OPEN c1;
    COMMIT;
    LOOP
      tt_try_proc();
      FETCH c1 INTO v_x, v_y;
      EXIT WHEN c1%NOTFOUND;
      dbms_output.put_line( v_x || '.' || v_y);
      IF c1%FOUND THEN
        UPDATE test SET x = 4 WHERE y = 'Two';
        ROLLBACK;
      END IF;
    END LOOP;
    CLOSE c1;
  end;

begin
  dbms_output.put_line('initializer');
    --same as oracle, package not support dml, commit and roolback.
    --UPDATE test SET x = 5 WHERE y = 'Two';
    --COMMIT;
end;
/

call pkg_proc.c_proc();

包存储过程调包中存储过程

--procedure of package called by procedure of package
--create package of pkg_proc
create or replace package pkg_proc is
  CURSOR c1 IS SELECT x,y FROM test;
  procedure c_proc;
end;
/
--create package boby of pkg_proc
create or replace package body pkg_proc IS
  procedure c_proc as
    v_x  test.x%TYPE;
    v_y  test.y%TYPE;
  BEGIN
    OPEN c1;
    COMMIT;
    LOOP
      FETCH c1 INTO v_x, v_y;
      EXIT WHEN c1%NOTFOUND;
      dbms_output.put_line( v_x || '.' || v_y);
      IF c1%FOUND THEN
        UPDATE test SET x = 4 WHERE y = 'Two';
        ROLLBACK;
      END IF;
    END LOOP;
    CLOSE c1;
  end;
begin
  dbms_output.put_line('initializer');
    --same as oracle, package not support dml, commit and roolback.
    --UPDATE test SET x = 5 WHERE y = 'Two';
    --COMMIT;
end;
/

--create package of pkg_call_proc
create or replace package pkg_call_proc is
  CURSOR c1 IS SELECT x,y FROM test;
  procedure c_call_proc;
end;
/

--create package boby of pkg_call_proc
create or replace package body pkg_call_proc IS
   procedure c_call_proc as
    v_x  test.x%TYPE;
    v_y  test.y%TYPE;
  BEGIN
    OPEN c1;
    COMMIT;
    LOOP
      pkg_proc.c_proc();
      FETCH c1 INTO v_x, v_y;
      EXIT WHEN c1%NOTFOUND;
      dbms_output.put_line( v_x || '.' || v_y);
      IF c1%FOUND THEN
        UPDATE test SET x = 4 WHERE y = 'Two';
        ROLLBACK;
      END IF;
    END LOOP;
    CLOSE c1;
  end;

begin
  dbms_output.put_line('initializer');
    --same as oracle, package not support dml, commit and roolback.
    --UPDATE test SET x = 5 WHERE y = 'Two';
    --COMMIT;
end;
/

call pkg_call_proc.c_call_proc();

在声明一个包时，不能在包中直接进行游标的声明

--Declare cursors in packages using sys_refcursor
--error
create or replace package pkg is
  c1 sys_refcursor;
  function c_open return int;
  function c_fetch return int;
  procedure c_open_fetch;
end;
/

在定义一个包体时，不能直接在包体的全局区定义一个游标变量

--Declare the cursor in the package body  using sys_refcursor
create or replace package pkg is
 function c_open return int;
 function c_fetch return int;
 procedure c_open_fetch;
end;
/

--error
create or replace package body pkg IS
  c1 sys_refcursor;
  function c_open return int as
  begin
    OPEN c1 for SELECT x,y FROM test;
    return 0;
  end;
  
  function c_fetch return int AS
    v_x  test.x%TYPE;
    v_y  test.y%TYPE;
  begin
    LOOP
      FETCH c1 INTO v_x, v_y;
      EXIT WHEN c1%NOTFOUND;
      dbms_output.put_line( v_x || '.' || v_y);
    END LOOP;
    --CLOSE c1;
    return 0;
  end;

  procedure c_open_fetch as
    v_x  test.x%TYPE;
    v_y  test.y%TYPE;
  BEGIN
    OPEN c1 for SELECT x,y FROM test;
    COMMIT;
    LOOP
      FETCH c1 INTO v_x, v_y;
      EXIT WHEN c1%NOTFOUND;
      dbms_output.put_line( v_x || '.' || v_y);
      IF c1%FOUND THEN
        UPDATE test SET x = 4 WHERE y = 'Two';
        ROLLBACK;
      END IF;
      END LOOP;
    CLOSE c1;
  end;

begin
  dbms_output.put_line('initializer');
    --same as oracle, package not support dml, commit and roolback.
    --UPDATE test SET x = 5 WHERE y = 'Two';
    --COMMIT;
end;
/

注意

1、游标操作和事务操作（提交和回滚）结合使用，lightdb_syntax_compatible_type和search_path需要如下进行设置：
set lightdb_syntax_compatible_type = oracle;
SET search_path TO public, oracle;
2、匿名块不支持以调用存储过程相同的方式调用函数：
create or replace function func return int as
begin
dbms_output.put_line (‘This is just a test’);
end;
/

begin
func();
dbms_output.put_line (‘This is just a test’);
end;
/
3、包中游标声明和定义注意：
（1）在声明一个包时，不能在包中直接进行游标的声明；
（2）在定义一个包体时，不能直接在包体的全局区定义一个游标变量；
（3）游标可以声明在包中函数或者存储过程内，但范围只在包中函数或者存储过程内有效。