в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:59:34Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/PL SQL/Transaction - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия