Oracle PL/SQL Tutorial/PL SQL Statements/Autonomous Transaction
Содержание
Audit as Autonomous Transaction
Handling auditing and security with autonomous transactions
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> create sequence audit_seq
2 /
Sequence created.
SQL> Create table audit_emp (action_nr NUMBER,action_cd VARCHAR2(2000), descr_tx VARCHAR2(2000),user_cd VARCHAR2(10), date_dt DATE)
2 /
Table created.
SQL>
SQL>
SQL>
SQL> create or replace procedure p_log_audit
2 (what_tx VARCHAR2, descr_tx VARCHAR2,
3 who_tx VARCHAR2, when_dt DATE)
4 is
5 pragma autonomous_transaction;
6 begin
7 insert into Audit_emp
8 values(audit_seq.nextval, what_tx, descr_tx,
9 who_tx, when_dt);
10 commit;
11 end;
12 /
Procedure created.
SQL>
SQL> create or replace trigger bu_emp
2 before update of salary on employee
3 referencing new as new old as old for each row
4 begin
5 p_log_audit ("update","update of emp.salary", user, SYSDATE);
6 end;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL> update employee set salary = 10000;
8 rows updated.
SQL>
SQL> select * from audit_emp;
ACTION_NR
----------
ACTION_CD
---------------------------------
DESCR_TX
---------------------------------
USER_CD DATE_DT
---------- ---------
1
update
update of emp.salary
sqle 09-JUN-07
2
update
update of emp.salary
sqle 09-JUN-07
3
update
update of emp.salary
sqle 09-JUN-07
4
update
update of emp.salary
sqle 09-JUN-07
5
update
update of emp.salary
sqle 09-JUN-07
6
update
update of emp.salary
sqle 09-JUN-07
7
update
update of emp.salary
sqle 09-JUN-07
8
update
update of emp.salary
sqle 09-JUN-07
8 rows selected.
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 10000 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 10000 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 10000 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 10000 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 10000 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 10000 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 10000 New York Manager
08 James Cat 17-SEP-96 15-APR-02 10000 Vancouver Tester
8 rows selected.
SQL>
SQL> drop table audit_emp;
Table dropped.
SQL>
SQL> drop sequence audit_seq;
Sequence dropped.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Autonomous Transactions
Oracle can suspend a transaction and transfer execution control to an independent child transaction.
This child transaction is called an autonomous transaction.
An autonomous transaction is completely independent of the calling transaction.
An autonomous transaction does not share resources, locks, or any commit dependencies with the main transaction.
Autonomous transactions can include just as much functionality as any other database transactions.
Autonomous transactions are useful for creating software components that can be reused in numerous applications.
One advantage of using an autonomous transaction is that DML can be executed and committed, even if the main transaction is rolled back.
Setting up the syntax for an autonomous transaction
Autonomous Transaction Syntax
declare
pragma autonomous_transaction;
begin
...
number of statements
...
commit;(or rollback;) ?End of transaction 1
...
number of statements
...
commit;(or rollback;) ?End of transaction 2
end;
A pragma autonomous transaction is a PL/SQL compiler directive to define an autonomous transaction.
This PL/SQL compiler directive can be used to define:
Top-level anonymous blocks
Local, standalone, or packaged functions and procedures
Database triggers
Methods of object types
Quote from:
Oracle PL/SQL For Dummies (Paperback)
by Michael Rosenblum (Author), Paul Dorsey (Author)
# Paperback: 414 pages
# Publisher: For Dummies (June 13, 2006)
# Language: English
# ISBN-10: 0764599577
# ISBN-13: 978-0764599576
Commit an Insert statement in a "pragma autonomous_transaction" procedure
SQL>
SQL> create table t ( msg varchar2(4000) );
Table created.
SQL>
SQL> create or replace procedure auto_proc
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into t values ( "A row for you" );
6 commit;
7 end;
8 /
Procedure created.
SQL>
SQL> create or replace
2 procedure proc( read_committed in boolean )
3 as
4 begin
5 if ( read_committed ) then
6 set transaction isolation level read committed;
7 else
8 set transaction isolation level serializable;
9 end if;
10
11 auto_proc;
12
13 for x in ( select * from t ) loop
14 dbms_output.put_line( x.msg );
15 end loop;
16 commit;
17 end;
18 /
Procedure created.
SQL>
SQL> exec proc( TRUE )
A row for you
PL/SQL procedure successfully completed.
SQL>
SQL> delete from t;
1 row deleted.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> exec proc( FALSE )
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table t;
Table dropped.
SQL>
Mark an anonymous block "pragma autonomous_transaction"
SQL>
SQL> create or replace package global_variables
2 as
3 x number;
4 end;
5 /
Package created.
SQL>
SQL> begin
2 global_variables.x := 5;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL> declare
2 pragma autonomous_transaction;
3 begin
4 global_variables.x := 10;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> set serveroutput on
SQL> exec dbms_output.put_line( global_variables.x );
10
PL/SQL procedure successfully completed.
SQL>
SQL>
Non-Working p_log_audit
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> create sequence audit_seq
2 /
Sequence created.
SQL> Create table audit_emp (action_nr NUMBER,action_cd VARCHAR2(2000), descr_tx VARCHAR2(2000),user_cd VARCHAR2(10), date_dt DATE)
2 /
Table created.
SQL>
SQL> Create or replace procedure p_log_audit(what_tx VARCHAR2,descr_tx VARCHAR2,who_tx VARCHAR2, when_dt DATE) is
2 begin
3 insert into audit_emp
4 values(audit_seq.nextval, what_tx, descr_tx,who_tx, when_dt);
5 commit;
6 end;
7 /
Procedure created.
SQL>
SQL> create or replace trigger bu_emp
2 before update of salary on employee
3 referencing new as new old as old for each row
4 begin
5 p_log_audit ("update","update of emp.salary", user, SYSDATE);
6 end;
7 /
Trigger created.
SQL>
SQL>
SQL> update employee set salary = 10000;
update employee set salary = 10000
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "sqle.P_LOG_AUDIT", line 5
ORA-06512: at "sqle.BU_EMP", line 2
ORA-04088: error during execution of trigger "sqle.BU_EMP"
SQL>
SQL> drop table audit_emp;
Table dropped.
SQL>
SQL> drop sequence audit_seq;
Sequence dropped.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
"pragma autonomous_transaction" package member procedure
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> create or replace package my_pkg
2 as
3 procedure run;
4 end;
5 /
Package created.
SQL>
SQL> create or replace package body my_pkg
2 as
3 cursor global_cursor is select ename from emp;
4 procedure show_results
5 is
6 pragma autonomous_transaction;
7 l_ename emp.ename%type;
8 begin
9 if ( global_cursor%isopen )
10 then
11 dbms_output.put_line( "NOT already opened cursor" );
12 else
13 dbms_output.put_line( "Already opened" );
14 open global_cursor;
15 end if;
16
17 loop
18 fetch global_cursor into l_ename;
19 exit when global_cursor%notfound;
20 dbms_output.put_line( l_ename );
21 end loop;
22 close global_cursor;
23 end;
24
25
26 procedure run
27 is
28 begin
29 update emp set ename = "x";
30
31 open global_cursor;
32 show_results;
33
34 show_results;
35
36 rollback;
37 end;
38
39 end;
40 /
Package body created.
SQL>
SQL> exec my_pkg.run
NOT already opened cursor
x
x
x
x
x
x
x
x
x
x
x
x
x
x
Already opened
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>