PL / SQL supports nested transactions

2010-07-21  来源:本站原创  分类:Database  人气:160 

Usage scenario:
When a PL / SQL program execution error need to output log information to the database when the table will encounter this problem.
log content to be submitted to the table in front of the error, but the content can not be submitted. Although we can first Rollback on error log, etc. Well, then commit. but this is too much trouble after all. In fact PL / SQL itself to support nested transactions.
Read the following small example:
CREATE OR REPLACE procedure Test1
as
PRAGMA AUTONOMOUS_TRANSACTION; - the key is the phrase
begin
- Set transaction read write;
insert into aaa values (1,4,7);
commit;
end;
/

CREATE OR REPLACE procedure Test2
as
begin
- Set transaction read write;
insert into aaa values (2,5,8);
Test1;
insert into aaa values (3,6,9);
rollback;
end;
/
When execute Test2 finished when inserted in the AAA table row (1,4,7).

-------------------------------------------------- -------------------------------------------------- ----------------------
Theory of Knowledge:
AUTONOMOUS TRANSACTION (self-government Services) Description:

Low-based version of ORACLE to do some projects, at times, encounter some headaches., For instance to the implementation of the current composition of a number of DML transaction (Services), in order to record each step some information to track DML table, because the atomic transaction, the tracking information submissions will determine the main issues Yu commit or rollback. In this way more difficult to write programs was increased and, programmers had to put the tracking information recorded in the structure of a similar array and then after the end of the main services to track them into the table. hey, really troublesome!

Is there a simple way to solve a similar problem?

ORACLE8i the AUTONOMOUS TRANSACTION (autonomous transactions, the following AT) is a very good answer.

AT is the main transaction (the MT) call, but independent of its affairs. AT is called in the execution, MT is suspended, the AT-house, a series of DML can be executed, and commit or rollback.

Note that since the independence of AT, its commit and rollback does not affect the implementation of the MT effect. AT the end of the implementation, the main access control transaction, they can continue to execute.

AT the definition of how to achieve it? We look at its syntax. Really very simple.

Just following PL / SQL statements part of the combined PRAGMA AUTONOMOUS_TRANSACTION on it.

1. Top of the anonymous PL / SQL block

2. Functions or Procedure (separate statement or declaration in the package in the can)

3. SQL Object Type method

4. Trigger.

For example:

In a separate procedure in the statement of AT

CREATE OR REPLACE PROCEDURE

Log_error (error_msg IN VARCHAR2 (100))

IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

Insert into Error_log values (sysdate, error_msg);

COMMIT;

END;

Let's look at an example, (win2000 advanced server + oracle8.1.6, connect as scott)

A table:

create table msg (msg varchar2 (120));

First of all, write with ordinary affairs of anonymous PL / SQL block:

declare

cnt number: = -1; -) Global variables

procedure local is

begin

select count (*) into cnt from msg;

dbms_output.put_line ('local: # of rows is' | | cnt);

insert into msg values ('New Record');

commit;

end;

begin

delete from msg;

commit;

insert into msg values ('Row 1');

local;

select count (*) into cnt from msg;

dbms_output.put_line ('main: # of rows is' | | cnt);

rollback;

local;

insert into msg values ('Row 2');

commit;

local;

select count (*) into cnt from msg;

dbms_output.put_line ('main: # of rows is' | | cnt);

end;

Operating result (note open serveroutput)

local: # of rows is 1 -> subroutine local can 'see' the main block of uncommitted records anonymous

main: # of rows is 2 -> main anonymous block can 'see '2 records (they are all local commit out of)

local: # of rows is 2 -> subroutine local first 'see '2 records, and then commit a third records

local: # of rows is 4 -> subroutine local they 'see' the newly added records (they are all local commit out of), and then commit the fifth record

main: # of rows is 5 -> main anonymous block last 'see' all the records.

From this example, we see the location of COMMIT and ROLLBACK anonymous both in the main block or subroutine, will influence the current transaction.

Now with AT rewrite what anonymous block procedure local:

...

procedure local is

pragma AUTONOMOUS_TRANSACTION;

begin

...

Re-run (note open serveroutput)

local: # of rows is 0 -> local subroutine can not be 'seen' Lord anonymous block uncommitted records (because it is independent)

main: # of rows is 2 -> main anonymous block can 'see '2 records, but only one was commited.

local: # of rows is 1 -> subroutine local can 'see' it records the previous commit, but the main anonymous block rollback records have been ahead of the

local: # of rows is 3 -> subroutine local can 'see '3 records, including records of the main anonymous block commit

main: # of rows is 4 -> main anonymous block last 'see' all the records.

Obviously, AT is independent, in its execution, MT was suspended. AT the COMMIT, ROLLBACK does not affect the MT implementation.

The use of AT, there are a number of considerations, a simple list is as follows:

1. In the anonymous PL / SQL block, only the top-level anonymous PL / SQL block can be set to AT

2. If the AT attempts to access a resource controlled by MT, a deadlock may occur.

3. Package can not be declared as AT, only the package function and procedure owned by AT can be declared

4. AT program must commit or rollback at the end, or will cause Oracle error ORA-06519: active autonomous transaction detected and rolled back

In program development, if the use of AUTONOMOUS TRANSACTION full features, would achieve the effect of duplication of efforts.

相关文章
  • PL / SQL supports nested transactions 2010-07-21

    Usage scenario: When a PL / SQL program execution error need to output log information to the database when the table will encounter this problem. log content to be submitted to the table in front of the error, but the content can not be submitted. A

  • PL \ SQL User's Guide and Reference reproduced 2011-04-18

    Chapter PL / SQL Overview First, understand the PL / SQL The main features of Understanding PL / SQL best way is to start from a simple example. The following procedure is used to deal with a tennis racket orders. First, declare a variable of type NU

  • Chapter 5 using Oracle PL / SQL 2011-05-12

    Chapter 4 Oracle synonyms, sequences, views, indexes Chapter 6 Oracle cursor Chapter 5 using Oracle PL / SQL 1, the technical objectives Understanding PL / SQL functions and features Understanding of data types and their usage Understand the logic of

  • PL \ SQL User's Guide and Reference 3 reproduced 2011-04-20

    Chapter III of PL / SQL data types First, the predefined data types Oracle data types can be divided into four categories, namely, scalar types, composite types, reference types and LOB types. Scalar type has no internal components; and complex type

  • PL \ SQL User's Guide and Reference 5.2.2 Reserved 2011-04-22

    Fifth, what is the record Record is related to data items stored in a centralized unit, each has its own name and data type. Suppose we have a variety of employee data, such as name, salary and employment dates, these items are logically associated,

  • PL \ SQL User's Guide and Reference 6.1 reproduced 2011-04-22

    Chapter VI of the PL / SQL and Oracle interaction between A, PL / SQL supports the SQL List PL / SQL extends SQL, more powerful and easy to use. We can use PL / SQL Oracle data flexibly operate safely, because it supports all SQL data manipulation st

  • PL \ SQL User's Guide and Reference 6.2 reproduced 2011-04-22

    Other OPEN-FOR statements can open the same query using a different cursor variables. Prior to re-open the cursor variable is not close it (but used multiple times for a static cursor OPEN operation, the system raises the predefined exception CURSOR_

  • PL \ SQL User's Guide and Reference 10.1 Reserved 2011-04-22

    Chapter X PL / SQL object type First, the role of abstract Abstraction is a high-level description of real-world entity or modeling. It can exclude irrelevant details, so that our daily lives more organized. For example, when driving a car, we do not

  • Chapter 5 Using PL / SQL 2011-05-24

    Chapter 5 Using Oracle PL / SQL 1, the technical objectives Understanding PL / SQL functions and features Understanding of data types and their usage Understand the logic of comparison Understand the control structure Control error handling 2, What i

  • 16.PL/SQL block structure and examples 2010-05-07

    Han Shunping. Fun oralce 24 talk. Plsql programming (1) Fun combat tutorial oracle (DAY) Introduction 1. On the section reviews 2.pl/sql introduction √ 3.pl/sql foundation √ Desired goals 1. Understand the oracle of the pl / sql concepts 2. Master pl

  • Oracle study notes 4 PL / SQL 2010-07-05

    PL / SQL blocks that form PL / SQL procedure as the basic unit will be logically related declarations and statements together PL / SQL is divided into three parts, a statement section, executable section and exception handling part [DECLARE declarati

  • PL / SQL to import data (cvs and excel) 2010-09-27

    PL / SQL supports multiple formats of data import, excel here and explain the format of cvs import. 1, cvs import format 1) into the PL / SQL, select: Tools -> text importing device -> the data from text file, then the top left corner of the "O

  • Oracle PL / SQL study 2010-11-30

    Write a stored procedure that you can add data to a table. 1. To create a simple table create table mytest( name varchar2(30), passwd varchar2(30) ); 2. Creation process. --replace Indicates if any sp_pro, replaces create or replace procedure sp_pro

  • PL / SQL Review four types of complex data 2011-02-17

    A, PL / SQL records 1 simple to use: declare type emp_record_type is record ( v_empno emp.empno% type, v_name emp.ename% type, v_sal emp.sal% type ); emp_record emp_record_type; begin select empno, ename, sal into emp_record from emp where empno = &n

  • PL / SQL based and uses the variables defined 2011-04-12

    • Introduction In writing PL / SQL procedure, you can define variables and constants; in PL / SQL program include: ① scalar type ② composite type ③ Reference Type ④ lob --- `Scalar type used In writing PL / SQL block, if you want to use variables, va

  • record & & pl / sql table & & varray & & nested table (nested nested) 2010-09-02

    record: the collection can be stored one to many of the scalar property. pl / sql table: a pl / sql code in the "table", there is only application running during the (memory), similar to the "array", sparse, dynamically increase the si

  • oracle pl / sql Nested Table 2010-09-06

    Looked at PL / SQL User's Guide and Reference, found nested table is really good http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type / CREATE TYPE Student AS OBJEC

  • Composite data types Oracle 10g pl / sql collection of learning six - the index table. Nested tables. Variable-length array 2010-11-12

    --Oracle 10g A composite data type pl/sql collection , The collection can be divided into the nested table index table. . Variable-length arrays --1. Index table ( Subscript is no limit on the length, and can also be negative ) -- Before the 9i , Whe

  • Complex data types Oracle 10g pl / sql collection of learning six - the index table. Nested table. Variable-length array 2010-11-12

    - Oracle 10g complex data types pl / sql collection, the collection can be divided into index tables, nested tables, variable-length array - 1, the index table (subscript no length limit, and can be negative) - In 9i before the definition of the inde

  • Oracle PL/SQL之嵌套表(Nested Table) 2012-03-09

    Test Code: Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 Connected as xxpo SQL> CREATE OR REPLACE TYPE color_tab_t AS TABLE OF VARCHAR2(30); 2 / Type created SQL> SQL> CREATE TABLE color_models ( 2 model_type VARCHAR2(12)