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