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

I need sql data base, I need SQL Data Base Project Description: Netwo...

I need SQL Data Base Project Description: Network SQL database and SQL Setup two 4 workstation Skills required are Data Entry, MySQL, SQL

Using exception_init - user-defined exceptions, Using EXCEPTION_INIT T...

Using EXCEPTION_INIT To handle unnamed internal exceptions, you should use the OTHERS handler or the pragma EXCEPTION_INIT. The pragma is a compiler directive that can be th

Indeterminacy in sql, Indeterminacy in SQL Some SQL expressions are ac...

Indeterminacy in SQL Some SQL expressions are actually not function invocations at all in the mathematical sense, being indeterminate-invocations operating on identical input

Origin of earth - big bang hypothesis, ORIGIN OF EARTH - BIG-BAN G HY...

ORIGIN OF EARTH - BIG-BAN G HYPOTHESIS - Origin of life is linked to origin of earth. Cosmos, the Universe originated 10-20 billion years ago by Big Bang (thermonu

Exit-when - iterative control, EXIT-WHEN The EXIT-WHEN statement permits...

EXIT-WHEN The EXIT-WHEN statement permits a loop to complete conditionally. Whenever the EXIT statement is encountered, the condition in the WHEN clause is computed. When the co

Data types in sql - character, Data Types in SQL - Character CHARACTER...

Data Types in SQL - Character CHARACTER or, synonymously, CHAR, for character strings. When this type is to be the declared type of something (e.g., a column), the permissible

Positional notation, Positional Notation The first procedure call uses...

Positional Notation The first procedure call uses the positional notation. The PL/SQL compiler relates the first actual parameter, account, with the first proper parameter, ac

Closing a cursor - explicit cursor, Closing a Cursor The CLOSE stateme...

Closing a Cursor The CLOSE statements disable the cursor, and the result set becomes undefined. An illustration of the CLOSE statement as shown: CLOSE c1;

Use native dynamic sql - improve performance of application, Use Native Dyn...

Use Native Dynamic SQL A few programs (a normal-purpose report writer for illustration) should build and process a variety of SQL statements at run time. Therefore, their full

I want online credit application website, Project Description: We organi...

Project Description: We organize an online system called ACPAS we have created a project called EVO that can be use by our customers to integrate their web sites with the Acpas

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