Oracle PL/SQL/PL SQL/Transaction
Содержание
- 1 Autonomous transactions.
- 2 Calling an autonomous function from SQL.
- 3 Commit an insert statement in PL SQL
- 4 interaction between savepoints and autonomous transactions.
- 5 set transaction use rollback segment
- 6 The pragma is legal in top-level anonymous blocks:
- 7 The pragma is not legal in nested blocks:
- 8 The pragma is valid in a packaged procedure.
- 9 The pragma is valid in both standalone and local subprograms.
Autonomous transactions.
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> CREATE OR REPLACE PROCEDURE Autonomous AS
2 PRAGMA AUTONOMOUS_TRANSACTION; 3 BEGIN 4 INSERT INTO MyTable VALUES (-10, "Hello from Autonomous!"); 5 COMMIT; 6 END Autonomous; 7 /
Procedure created. SQL> SQL> BEGIN
2 INSERT INTO MyTable VALUES (-10, "Hello from the parent!"); 3 4 Autonomous; 5 6 ROLLBACK; 7 END; 8 /
BEGIN
ERROR at line 1: ORA-00034: cannot COMMIT in current PL/SQL session ORA-06512: at "sqle.AUTONOMOUS", line 5 ORA-06512: at line 4
SQL> SQL> SELECT * FROM MyTable WHERE num_col = -10; no rows selected SQL> SQL> DECLARE
2 PRAGMA AUTONOMOUS_TRANSACTION; 3 BEGIN 4 INSERT INTO MyTable (num_col) VALUES (1); 5 END; 6 /
DECLARE
ERROR at line 1: ORA-06519: active autonomous transaction detected and rolled back ORA-06512: at line 5
SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
Calling an autonomous function from SQL.
<source lang="sql">
SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> CREATE OR REPLACE FUNCTION LogParam(p1 IN NUMBER)
2 RETURN NUMBER AS 3 PRAGMA AUTONOMOUS_TRANSACTION; 4 BEGIN 5 INSERT INTO MyTable (num_col, char_col) 6 VALUES (p1, "Logged!"); 7 COMMIT; 8 RETURN p1; 9 END LogParam; 10 /
Function created. SQL> SQL> SELECT LogParam(1) FROM dual; SELECT LogParam(1) FROM dual
*
ERROR at line 1: ORA-00034: cannot COMMIT in current PL/SQL session ORA-06512: at "sqle.LOGPARAM", line 7
SQL> SQL> SELECT num_col
2 FROM MyTable 3 WHERE char_col = "Logged!";
no rows selected SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
Commit an insert statement in PL SQL
<source lang="sql">
SQL> SQL> create table t ( x int ); Table created. SQL> SQL> SQL> begin
2 for i in 1 .. 100000 3 loop 4 insert into t values ( i ); 5 commit ; 6 end loop; 7 end; 8 /
PL/SQL procedure successfully completed. SQL> SQL> drop table t; Table dropped. SQL> SQL> SQL> --
</source>
interaction between savepoints and autonomous transactions.
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> CREATE OR REPLACE PROCEDURE AutoProc AS
2 PRAGMA AUTONOMOUS_TRANSACTION; 3 BEGIN 4 ROLLBACK TO SAVEPOINT A; 5 END AutoProc; 6 /
Procedure created. SQL> SQL> BEGIN
2 SAVEPOINT A; 3 INSERT INTO MyTable (char_col) 4 VALUES ("Savepoint A!"); 5 AutoProc; 6 END; 7 /
BEGIN
ERROR at line 1: ORA-00034: cannot ROLLBACK in current PL/SQL session ORA-06512: at "sqle.AUTOPROC", line 4 ORA-06512: at line 5
SQL> SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
set transaction use rollback segment
<source lang="sql"> 2
SQL> create table t
2 as 3 select * from all_objects;
Table created. SQL> SQL> SQL> set echo on SQL> drop table done; Table dropped. SQL> create table done( object_id int ); Table created. SQL> insert into done values ( 0 ); 1 row created. SQL> SQL> declare
2 l_cnt number; 3 l_max number; 4 begin 5 select object_id into l_cnt from done; 6 select max(object_id) into l_max from t; 7 8 while ( l_cnt < l_max ) 9 loop 10 update t 11 set object_name = lower(object_name) 12 where object_id > l_cnt 13 and object_id <= l_cnt+100; 14 15 update done set object_id = object_id+100; 16 17 commit; 18 set transaction use rollback segment rbs_small; 19 l_cnt := l_cnt + 100; 20 end loop; 21 end; 22 /
PL/SQL procedure successfully completed. SQL> SQL> drop table t; Table dropped. SQL> SQL> --
</source>
The pragma is legal in top-level anonymous blocks:
<source lang="sql">
SQL> DECLARE
2 PRAGMA AUTONOMOUS_TRANSACTION; 3 BEGIN 4 COMMIT; 5 END; 6 /
PL/SQL procedure successfully completed. SQL> SQL> SQL>
</source>
The pragma is not legal in nested blocks:
<source lang="sql">
SQL> BEGIN
2 DECLARE 3 PRAGMA AUTONOMOUS_TRANSACTION; 4 BEGIN 5 COMMIT; 6 END; 7 END; 8 / PRAGMA AUTONOMOUS_TRANSACTION; *
ERROR at line 3: ORA-06550: line 3, column 12: PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be specified here
SQL> SQL> SQL>
</source>
The pragma is valid in a packaged procedure.
<source lang="sql">
SQL> SQL> SQL> CREATE OR REPLACE PACKAGE Auto2 AS
2 PROCEDURE P; 3 END Auto2; 4 /
Package created. SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE PACKAGE BODY Auto2 AS
2 PROCEDURE P IS 3 PRAGMA AUTONOMOUS_TRANSACTION; 4 BEGIN 5 COMMIT; 6 END P; 7 END Auto2; 8 /
Package body created. SQL> show errors No errors. SQL>
</source>
The pragma is valid in both standalone and local subprograms.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PROCEDURE Auto1 AS
2 PRAGMA AUTONOMOUS_TRANSACTION; 3 4 PROCEDURE Local IS 5 PRAGMA AUTONOMOUS_TRANSACTION; 6 BEGIN 7 ROLLBACK; 8 END Local; 9 BEGIN 10 Local; 11 COMMIT; 12 END Auto1; 13 /
Procedure created. SQL> show errors No errors. SQL> SQL>
</source>