Using set transaction, PL-SQL Programming

Using SET TRANSACTION

You use the SET TRANSACTION statement to begin the read-only or read-write transaction, start an isolation level, or assign your present transaction to a specified rollback segment. The Read-only transactions are helpful for running the multiple queries against one or more tables whereas other users update the same tables.

During the read-only transaction, all the queries refer to similar snapshot of the database, providing the multi-query, multi-table, read-consistent view. The Other users can carry on to query or update data as usual. The commit or rollback ends the transaction. In the illustration below, like the store manager, you use the read-only transaction to meet the sales figures for the day, the past week & the past month.

The figures are unaffected by the other users updating the database throughout the transaction.

DECLARE

daily_sales REAL;

weekly_sales REAL;

monthly_sales REAL;

BEGIN

...

COMMIT; -- ends previous transaction

SET TRANSACTION READ ONLY;

SELECT SUM(amt) INTO daily_sales FROM sales

WHERE dte = SYSDATE;

SELECT SUM(amt) INTO weekly_sales FROM sales

WHERE dte > SYSDATE - 7;

SELECT SUM(amt) INTO monthly_sales FROM sales

WHERE dte > SYSDATE - 30;

COMMIT; -- ends read-only transaction

...

END;

The SET TRANSACTION statement should be the first SQL statement in the read-only transaction and can only come out once in the transaction. When you set a transaction to the READ ONLY, the following queries see only changes committed before the transaction began. The use of READ ONLY does not affect the other users or transactions.

Posted Date: 10/4/2012 5:19:36 AM | Location : United States







Related Discussions:- Using set transaction, Assignment Help, Ask Question on Using set transaction, Get Answer, Expert's Help, Using set transaction Discussions

Write discussion on Using set transaction
Your posts are moderated
Related Questions
Fetching with a Cursor The FETCH statements retrieve the rows in the result set one at a time. After each and every fetch, the cursor advance to the next row in the result set

LAWS / RULES - Dollo's Law                     :           Living organisms do exhibit evolutionary irreversibility or evolution is irreversible. Williston's Law

Lightweight system to provide and take info from workers in the field and office, have basic design outlined already just require build and implementation Desired Skills CSS,

Collections:   The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subsc

Transaction context As the figure shows, the major transaction shares its context with the nested transactions, but not with the autonomous transactions. Similarly, If one aut

Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t

Third Step at defining type SID in SQL CREATE DOMAIN SID AS VARCHAR(5) CHECK ( VALUE IS NOT NULL AND SUBSTRING(VALUE FROM 1 FOR 1) = 'S' AND CAST('+'||SUBSTRING(VALUE

Need Windows and Linux system Administrator We are seeking a part time system administrator to take care of our servers. Your things to do would add, but not limited to: -

Processing Transactions This part describes how to do the transaction processing. You learn the fundamental techniques that safeguard the consistency of your database, involvin

Implicit Cursor is declared and used by the oracle environment internally. while the explicit cursor is declared and used by the external user. more over implicitly cursors are no