Using set transaction, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Using set transaction

Table comparison - sql, Table Comparison - SQL The following definitio...

Table Comparison - SQL The following definitions for relation comparisons: Let r1 and r2 be relations having the same heading. Then: r1 ⊆ r2 is true if every tuple of r1

Opening a cursor variable, Opening a Cursor Variable The OPEN-FOR stat...

Opening a Cursor Variable The OPEN-FOR statement relates a cursor variable with the multi-row query, executes the query, and then identifies the result set. The syntax for ope

Execute your documentation in oracle, Using research notes and Oracle docum...

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

Laws / rules associated with evolution, LAWS / RULES - Dollo's Law    ...

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

Components of an object type - attributes in pl/sql, Attributes: Just ...

Attributes: Just similar to variable, an attribute is declared with a name and datatype. The name should be exclusive within the object type. The datatype can be any Oracle ty

Iterative control:exit statements, EXIT The EXIT statement forces a loop...

EXIT The EXIT statement forces a loop to done unconditionally. Whenever an EXIT statement is encountered, the loop is done immediately and controls the passes to the next statem

Declaring and initializing objects in pl/sql, Declaring and Initializing Ob...

Declaring and Initializing Objects: An object type is once defined and installed in the schema; you can use it to declare the objects in any PL/SQL, subprogram, block or packa

Using forall and bulk collect - bulk bind performance, Using FORALL and BUL...

Using FORALL and BULK COLLECT Together You can unite the BULK COLLECT clause with the FORALL statement, in that case, the SQL engine bulk-binds column values incrementally. In

Comparison operators- pl/sql, Comparison Operators The Comparison operat...

Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

Package - pl/sql programming, What Is a Package? The package is a sch...

What Is a Package? The package is a schema object that group logically related PL/SQL items, types, and subprograms. The Packages usually have 2 parts, a specification & a bo

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd