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
Components of an Object Type: An object type encapsulates the operations and data. Therefore, you can declare the methods and attributes in an object type specification, but no

Selecting Objects: Suppose that you have run the SQL*Plus script below that creates object type Person and object table persons, and that you have settled the table: CREATE

Find the account numbers of all customers whose balance is more than 10,000 $

Example of WHEN or THEN Constraints A concrete example showing how SQL supports WHEN/THEN constraints CREATE TABLE SAL_HISTORY (EmpNo CHAR (6), Salary INTEGER NOT NULL,

Relational Operators and Logical Operators It prepares the ground for subsequent sections in which each specific relational operator is paired with its logical counterpart, su

Use External Routines The PL/SQL is particular for the SQL transaction processing. Therefore, several tasks are more quickly completed in a lower-level language like C that is

Control Structures The Control structures are the most important PL/SQL extension to the SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data

Application to Export Excel Data to MSSQL Server table I am having a table available in excel format and features the subsequent: - Some text is in Arabic (e.g. UTF-8 encodin

Write a query to find academics that are authors and that have only ever coauthored papers with authors from institutes in the same state as their own. List their academic number,

Effects of NULL for union - SQL The treatment of NULL in invocations of EXCEPT is as for UNION. This is different from its treatment in those of NOT IN and quantified compari