42.9. Transaction Management

42.9.1. Operations Cursor With Transcations

In procedures invoked by the CALL command as well as in anonymous code blocks, it is possible to end transactions using the commands COMMIT and ROLLBACK. A new transaction is started automatically after a transaction is ended using these commands, so there is no separate START TRANSACTION command. (Note that BEGIN and END have different meanings in PL/oraSQL.)

Here is a simple example:

CREATE OR REPLACE PROCEDURE transaction_test1(b int) IS
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i < b THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
/

CALL transaction_test1(3);

42.9.1. Operations Cursor With Transcations

This summary mainly introduces the combination of cursor operations and transaction operations (commit and rollback). Specifically divided into the following five categories:

  • Function

    • Call function in function: not supported.

    • Function call process: not supported.

    • Functions and stored procedures in the function package:

      • Transfer function: not supported;

      • Package adjustment process: not supported.

  • Stored procedure

    • Calling a function in a stored procedure: not supported.

    • Stored procedure call procedure: support.

    • Functions and stored procedures in the package are called in the stored procedure:

      • Transfer function: not supported;

      • Package adjustment process: not supported.

  • Anonymous block

    • Call function in anonymous block: not supported.

    • Anonymous block mid-tuning process: support.

    • Functions and stored procedures in the call package in the anonymous block:

      • Transfer function: not supported;

      • Package adjustment process: not supported.

  • Functions in the package

    • Package function call function: not supported.

    • Package function call stored procedure: not supported.

    • The package function calls the functions and stored procedures in the package:

      • Transfer function: not supported;

      • Package adjustment process: not supported.

  • Stored Procedures in Packages

    • Package stored procedure caller: not supported.

    • Package stored procedure call stored procedure: support.

    • Package stored procedures transfer functions and stored procedures in packages:

      • Transfer function: not supported;

      • Package adjustment process: not supported.

Tip

Cursor operations and transaction operations (commit and rollback) are used in combination, and lightdb_syntax_compatible_type and search_path need to be set as follows: set lightdb_syntax_compatible_type = oracle; SET search_path TO public, oracle;

Tip

Calling functions in the same way as stored procedures is not supported in anonymous blocks, such as:

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;
/
     

Tip

Package Notes on Cursor Declarations and Definitions:

  • When declaring a package, the cursor cannot be declared directly in the package;

  • When defining a package body, you cannot define a cursor variable directly in the global area of the package body.

Tip

Currently stored procedures and plugin auto_explain cannot be used together.

Example: Stored procedure call procedure


--preconditional  
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;
/

--commit、rollback
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;
/

--call procedure in procedure
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();
    

Example: stored procedures in the package are called in the stored procedure

--preconditional
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();
    

Example: Anonymous block mid-tuning process


begin
  tt_try_proc();
end;
/
    

Example: stored procedures in the call package in the anonymous block


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

Example: Package stored procedure call stored procedure


--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();

    

Example: Package stored procedures transfer stored procedures in packages


--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();
    

Example: When declaring a package, the cursor cannot be declared directly in the package


--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;
/
    

Example: When defining a package body, you cannot define a cursor variable directly in the global area of the package body


--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;
/