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/sql.)
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);
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.
Cursor operations and transaction operations (commit and rollback) are used in combination, and the value of lightdb_dblevel_syntax_compatible_type is oracle and search_path need to be set as follows: SET search_path TO public, oracle;
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;
/
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.
Currently stored procedures and plugin auto_explain cannot be used together.
Example: Stored procedure call procedure
--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();
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');
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');
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();
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');
UPDATE test SET x = 5 WHERE y = 'Two';
COMMIT;
end;
/
The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction.
A subprogram marked with this pragma can do SQL operations and commit or roll back those operations,
without committing or rolling back the data in the main transaction.
This is a new feature since version 24.1, you can apply this pragma to functions and procedures that without refcursor parameter.
Syntax
PRAGMA AUTONOMOUS_TRANSACTION;
Examples as below:
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;
/* Even if you roll back in this scope,
the insert into 'debug_output' remains committed,
because it is part of an autonomous transaction. */
ROLLBACK;
END;
/