в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:01Z

1 версия

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

==Mark function with "pragma autonomous_transaction"==



<source lang="sql">

SQL> create table emp (
2 empno number(10),
3 viewed date );
Table created.
SQL>
SQL> create or replace function AUDIT_ROW(id number) return number is
2 begin
3 insert into emp values (id, sysdate);
4 return 0;
5 end;
6 /
Function created.
SQL> show errors
No errors.
SQL>
SQL> create or replace function AUDIT_ROW(id number) return number is
2 pragma autonomous_transaction;
3 begin
4 insert into emp values (id, sysdate);
5 commit;
6 return 0;
7 end;
8 /
Function created.
SQL> show errors
No errors.
SQL>
SQL> drop table emp;
Table dropped.

</source>

==pragma autonomous_transaction and exception==



<source lang="sql">

SQL> create table myTable (
2 tname varchar2(30),
3 cname varchar2(30),
4 changed date);
Table created.
SQL>
SQL> create or replace procedure RUN_DDL(m varchar2) is
2 pragma autonomous_transaction;
3 begin
4 execute immediate m;
5 end;
6 /
Procedure created.
SQL>
SQL> create or replace procedure ADD_COLUMN(p_table varchar2, p_column varchar2) is
2 v number;
3 begin
4 insert into myTable values (p_table, p_column, sysdate);
5 run_ddl("alter table "||p_table||" add "||p_column);
6 v := 1/0;
7 end;
8 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> drop table myTable;
Table dropped.

</source>

==pragma autonomous_transaction and raise exception==



<source lang="sql">

SQL>
SQL>
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
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> create or replace procedure sal_check( p_deptno in number)is
2 pragma autonomous_transaction;
3 avg_sal number;
4 max_sal number;
5 begin
6 select avg(sal), max(sal)
7 into avg_sal, max_sal
8 from emp
9 where deptno = p_deptno;
10 if ( max_sal/2 > avg_sal )
11 then
12 raise_application_error(-20001,"Rule violated");
13 end if;
14 end;
15 /
Procedure created.
SQL>
SQL> update emp set sal = sal*1.1;
14 rows updated.
SQL>
SQL> update emp set sal = 99999.99 where ename = "WARD";
1 row updated.
SQL>
SQL> exec sal_check(30);
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.

</source>

==pragma autonomous_transaction and rollback==



<source lang="sql">

SQL>
SQL> create table t ( msg varchar2(25) );
Table created.
SQL> create or replace procedure Autonomous_Insert
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into t values ( "Autonomous Insert" );
6 commit;
7 end;
8 /
Procedure created.
SQL> create or replace procedure NonAutonomous_Insert
2 as
3 begin
4 insert into t values ( "NonAutonomous Insert" );
5 commit;
6 end;
7 /
Procedure created.
SQL> begin
2 insert into t values ( "Anonymous Block" );
3 NonAutonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from t;
MSG
-------------------------
Anonymous Block
NonAutonomous Insert
SQL> delete from t;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> begin
2 insert into t values ( "Anonymous Block" );
3 Autonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from t;
MSG
-------------------------
Autonomous Insert
SQL>
SQL> drop table t;
Table dropped.
SQL>

</source>

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

Oracle PL/SQL/Stored Procedure Function/Autonomous transaction - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия