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

Introduction to SQl and DQL, which operation is used if we are interested i...

which operation is used if we are interested in only certain columns of a table?

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

Need to change mysql query to pdo, Need to change mysql query to PDO Pro...

Need to change mysql query to PDO Project Description: I want someone to convert me 1 .php file that includes some sql/mysql stuff to PDO is a very small file. Skills requ

Using invoker rights in pl sql, Using Invoker Rights: By default, the ...

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

Group and having query, Using a join on 2 tables, select all columns and 10...

Using a join on 2 tables, select all columns and 10 rows from the 2 tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 E JOIN STAFF S ON E.EMP_

Predefined exceptions, Predefined Exceptions The internal exception is ...

Predefined Exceptions The internal exception is raised implicitly whenever your PL/SQL program exceeds a system-dependent limit or violates an Oracle rule. Each & every Oracle

Rephrase conditional control statements, Rephrase Conditional Control State...

Rephrase Conditional Control Statements When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops evaluating the expression as s

Create a procedure that update the status, Create a procedure named STATUS_...

Create a procedure named STATUS_SHIP_SP that allows a company to employee in the Shipping Department to update the status of an order to add shipping information. The BB_BASKETSTAT

Selecting objects in pl sql, Selecting Objects: Suppose that you have ...

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

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