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
Negation (NOT, ¬) - SQL There are three rows instead of just two. As you can see, ¬ p is defined as in two-valued logic (2VL) when p is either true or false, but ¬ (unknown) i

Using SAVEPOINT The SAVEPOINT names and marks the present point in the processing of a transaction. Used with the ROLLBACK TO statement, the savepoints undo parts of a transac

Transactions in SQL BEGIN TRANSACTION, COMMIT, and ROLLBACK, SQL has the same syntax except for START in place of BEGIN. However, START TRANSACTION is used only for outermost

Primary Key - SQL A PRIMARY KEY specification carries an implicit NOT NULL constraint on each column of the specified key. When more than one key constraint is required, the k

What is Cursors how to use it in Real time application ?

Type versus Representation Confusion in SQL This describes how a value might have two or more distinct representations. For example, user-defined type POINT might have a decla

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

Using research notes and Oracle documentation plan and execute an upgrade of an installation of Oracle 10g to Oracle 11g release 1. To do this you must show in screen shots and wri

Recursive Subprograms The recursive subprogram is the one that calls itself. Think of a recursive call as a call to a few other subprograms that does the similar task as your

Transaction Control The Oracle is transaction oriented; that is, Oracle uses the transactions to make sure the data integrity. The transaction is a sequence of SQL data manip