[ Pobierz całość w formacie PDF ]
.In the last example, the fetched rows are not locked because noFORUPDATE clause is used.So, other users might unintentionally overwrite yourchanges.Also, the cursor must have a read-consistent view of the data, so rollbacksegments used in the update are not released until the cursor is closed.This canslow down processing when many rows are updated.The next example shows that you can use the%ROWTYPE attribute with cursors thatreference theROWID pseudocolumn:DECLARECURSOR c1 IS SELECT ename, sal, rowid FROM emp;emp_rec c1%ROWTYPE;BEGINOPEN c1;LOOPFETCH c1 INTO emp_rec;EXIT WHEN c1%NOTFOUND;.IF.THENDELETE FROM emp WHERE rowid = emp_rec.rowid;END IF;END LOOP;CLOSE c1;END;Interaction with Oracle 5-51 Using Autonomous TransactionsUsing Autonomous TransactionsA transaction is a series of SQL statements that does a logical unit of work.Often,one transaction starts another.In some applications, a transaction must operateoutside the scope of the transaction that started it.This can happen, for example,when a transaction calls out to a data cartridge.An autonomous transaction is an independent transaction started by anothertransaction, the main transaction.Autonomous transactions let you suspend themain transaction, do SQL operations, commit or roll back those operations, thenresume the main transaction.Figure 5 1 shows how control flows from the maintransaction (MT) to an autonomous transaction (AT) and back again.Figure 5 1 Transaction Control FlowMain Transaction Autonomous TransactionPROCEDURE proc1 ISPROCEDURE proc2 ISemp_id NUMBER;PRAGMA AUTON.BEGINdept_id NUMBER;emp_id := 7788;BEGIN MT suspendsINSERT.MT beginsdept_id := 20;SELECT.UPDATE.AT beginsproc2;INSERT.DELETE.UPDATE.COMMIT; MT endsCOMMIT; AT endsEND;END; MT resumesAdvantages of Autonomous TransactionsOnce started, an autonomous transaction is fully independent.It shares no locks,resources, or commit-dependencies with the main transaction.So, you can logevents, increment retry counters, and so on, even if the main transaction rolls back.More important, autonomous transactions help you build modular, reusablesoftware components.For example, stored procedures can start and finishautonomous transactions on their own.A calling application need not know about aprocedure s autonomous operations, and the procedure need not know about theapplication s transaction context.That makes autonomous transactions lesserror-prone than regular transactions and easier to use.Furthermore, autonomous transactions have all the functionality of regulartransactions.They allow parallel queries, distributed processing, and all thetransaction control statements includingSETTRANSACTION.5-52 PL/SQL User s Guide and Reference Using Autonomous TransactionsDefining Autonomous TransactionsTo define autonomous transactions, you use the pragma (compiler directive)AUTONOMOUS_TRANSACTION.The pragma instructs the PL/SQL compiler to marka routine as autonomous (independent).In this context, the term routine includestop-level (not nested) anonymous PL/SQL blockslocal, stand-alone, and packaged functions and proceduresmethods of a SQL object typedatabase triggersYou can code the pragma anywhere in the declarative section of a routine.But, forreadability, code the pragma at the top of the section.The syntax follows:PRAGMA AUTONOMOUS_TRANSACTION;In the following example, you mark a packaged function as autonomous:CREATE PACKAGE banking AS.FUNCTION balance (acct_id INTEGER) RETURN REAL;END banking;CREATE PACKAGE BODY banking AS.FUNCTION balance (acct_id INTEGER) RETURN REAL ISPRAGMA AUTONOMOUS_TRANSACTION;my_bal REAL;BEGIN.END;END banking;In the next example, you mark a stand-alone procedure as autonomous:CREATE PROCEDURE close_account (acct_id INTEGER, OUT balance) ASPRAGMA AUTONOMOUS_TRANSACTION;my_bal REAL;BEGIN.END;Interaction with Oracle 5-53 Using Autonomous TransactionsIn the following example, you mark a PL/SQL block as autonomous:DECLAREPRAGMA AUTONOMOUS_TRANSACTION;my_empno NUMBER(4);In the example below, you mark a database trigger as autonomous.Unlike regulartriggers, autonomous triggers can contain transaction control statements such asCOMMIT andROLLBACK.CREATE TRIGGER parts_triggerBEFORE INSERT ON parts FOR EACH ROWDECLAREPRAGMA AUTONOMOUS_TRANSACTION;BEGININSERT INTO parts_log VALUES(:new.pnum, :new.pname);COMMIT; -- allowed only in autonomous triggersEND;Restrictions on Pragma AUTONOMOUS_TRANSACTIONYou cannot use the pragma to mark all subprograms in a package (or all methods inan object type) as autonomous.Only individual routines can be markedautonomous.For example, the following pragma is illegal:CREATE PACKAGE banking ASPRAGMA AUTONOMOUS_TRANSACTION; -- illegalAlso, you cannot mark a nested PL/SQL block as autonomous.Autonomous versus Nested TransactionsAlthough an autonomous transaction is started by another transaction, it is not anested transaction for the following reasons:It does not share transactional resources (such as locks) with the maintransaction.It does not depend on the main transaction.For example, if the main transactionrolls back, nested transactions roll back, but autonomous transactions do not.Its committed changes are visible to other transactions immediately.(A nestedtransaction s committed changes are not visible to other transactions until themain transaction commits.)Exceptions raised in an autonomous transaction cause a transaction-levelrollback, not a statement-level rollback.5-54 PL/SQL User s Guide and Reference Using Autonomous TransactionsTransaction contextAs Figure 5 2 shows, the main transaction shares its context with nested routines,but not with autonomous transactions.Likewise, when one autonomous routinecalls another (or itself recursively), the routines share no transaction context.However, when an autonomous routine calls a non-autonomous routine, theroutines share the same transaction context.Figure 5 2 Transaction ContextTransaction Context Different Transaction ContextMain Transaction Nested RoutinePROCEDURE proc1 IS PROCEDURE proc2 ISemp_id NUMBER; bonus NUMBER;Autonomous TransactionBEGIN BEGINemp_id := 7788; bonus := 500;proc2; SELECT.PROCEDURE proc3 ISSELECT.INSERT.PRAGMA AUTON.INSERT.DELETE.dept_id NUMBER;INSERT.COMMIT; BEGINemp_id := 7566; END; dept_id := 20;UPDATE.UPDATE.DELETE.INSERT.proc3; UPDATE.COMMIT; COMMIT;END; END;Transaction VisibilityAs Figure 5 3 on page 5-56 shows, changes made by an autonomous transactionbecome visible to other transactions when the autonomous transaction commits.The changes also become visible to the main transaction when it resumes, but onlyif its isolation level is set toREADCOMMITTED (the default)
[ Pobierz całość w formacie PDF ]