Oracle PL/SQL - SET TRANSACTION Statement

Introduction

You use the SET TRANSACTION statement to begin a read-only or read-write transaction.

Read-only transactions are useful for running multiple queries while other users update the same tables.

During a read-only transaction, all queries refer to the same snapshot of the database.

Other users can continue to query or update data as usual.

The SET TRANSACTION statement must be the first statement in a read-only transaction and can appear only once.

Only the SELECT, OPEN, FETCH, CLOSE, LOCK TABLE, COMMIT, and ROLLBACK statements are allowed in a read-only transaction.

A commit or rollback ends the transaction.


DECLARE 
  daily_order_total    NUMBER(12,2); 
  weekly_order_total   NUMBER(12,2);  
  monthly_order_total  NUMBER(12,2); 
BEGIN 
   COMMIT; -- end previous transaction 
   SET TRANSACTION READ ONLY NAME 'Calculate Order Totals'; 

   SELECT SUM (order_total) 
   INTO daily_order_total 
   FROM orders 
   WHERE order_date = SYSDATE; 

   SELECT SUM (order_total) 
   INTO weekly_order_total 
   FROM orders 
   WHERE order_date = SYSDATE - 7; 

   SELECT SUM (order_total) 
   INTO monthly_order_total 
   FROM orders 
   WHERE order_date = SYSDATE - 30; 

   COMMIT; -- ends read-only transaction 
END; 
 /

Oracle PL/SQL - SET TRANSACTION Statement

Introduction

Related Topic