41.10. 事务管理
Prev UpChapter 41. pl/sql(兼容Oracle PL/SQL) — SQL过程化语言Home Next

41.10. 事务管理

41.10.1. 使用事务操作游标
41.10.2. 自治事务

在由CALL命令调用的过程中，以及在匿名代码块中， 可以使用COMMIT和ROLLBACK命令结束事务。 使用这些命令结束事务后，会自动启动一个新事务，因此没有单独的START TRANSACTION命令。 （注意，PL/oraSQL中BEGIN和END具有不同的含义。）

以下是一个简单的示例： 

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

41.10.1. 使用事务操作游标

本文主要介绍了游标操作和事务操作（提交和回滚）的组合。主要分为以下五类：

  • 功能

    • 在函数中调用函数：不支持。

    • 函数调用过程：不支持。

    • 函数包中的函数和存储过程：

      • 传递函数：不支持；

      • 包调整过程：不支持。

  • 存储过程

    • 在存储过程中调用函数：不支持。

    • 存储过程调用过程：支持。

    • 在存储过程中调用包中的函数和存储过程：

      • 传递函数：不支持；

      • 包调整过程：不支持。

  • 匿名块

    • 在匿名块中调用函数：不支持。

    • 匿名块中间调整过程：支持。

    • 在匿名块中调用包中的函数和存储过程：

      • 传递函数：不支持；

      • 包调整过程：不支持。

  • 包中的函数

    • 包函数调用函数：不支持。

    • 包函数调用存储过程：不支持。

    • 包函数调用包中的函数和存储过程：

      • 传递函数：不支持；

      • 包调整过程：不支持。

  • 包中的存储过程

    • 包存储过程调用者：不支持。

    • 包存储过程调用存储过程：支持。

    • 包存储过程传递函数和包中的存储过程：

      • 传递函数：不支持；

      • 包调整过程：不支持。

Tip

要使用游标操作和事务操作（提交和回滚），并且 lightdb_dblevel_syntax_compatible_type 的值为 oracle， 需要将 search_path 设置为以下内容： SET search_path TO public, oracle;

Tip

在匿名块中不支持与存储过程相同的方式调用函数，例如： 

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

游标声明和定义的包说明：

  • 在声明包时，不能直接在包中声明游标；

  • 在定义包体时，不能在包体的全局区域直接定义游标变量。

Tip

目前无法同时使用存储过程和插件 auto_explain。

示例：调用存储过程 


--preconditional
-- the value of lightdb_dblevel_syntax_compatible_type must be oracle
create extension IF NOT EXISTS orafce;
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();

示例：在存储过程中调用存储在包中的存储过程 

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

示例：在定义包体时，不能直接在包体的全局区域定义游标变量 


--在包体内使用sys_refcursor声明游标变量
create or replace package pkg is
	function c_open return int;
	function c_fetch return int;
	procedure c_open_fetch;
end;
/

--错误示例
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');
  UPDATE test SET x = 5 WHERE y = 'Two';
  COMMIT;
end;
/

41.10.2. 自治事务

AUTONOMOUS_TRANSACTION语法改变事务中子程序的执行方式。通过此语法，子程序的提交，撤销可以独立于主事务。

此功能从版本24.1开始支持，在Oracle模式下，此语法可应用于不含游标类型参数的函数和存储过程。

语法 

PRAGMA AUTONOMOUS_TRANSACTION;

示例如下: 

CREATE OR REPLACE PROCEDURE lower_salary (emp_id NUMBER, amount NUMBER) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE employees
  SET salary =  salary - amount
  WHERE employee_id = emp_id;

  COMMIT;
END lower_salary;
/

CREATE OR REPLACE FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO debug_output (message) VALUES (msg);
  COMMIT;
  RETURN msg;
END;
/

DECLARE
  my_emp_id    NUMBER(6);
  my_last_name VARCHAR2(25);
  my_count     NUMBER;
BEGIN
  my_emp_id := 120;
 
  SELECT debugging.log_msg(last_name)
  INTO my_last_name
  FROM employees
  WHERE employee_id = my_emp_id;
 
  /* 即使回滚debug_output表仍然被插入数据 */
  ROLLBACK;
END;
/
Prev Up Next
41.9. 集合 Home 41.11. 包