Live ORACLE

DECLARE

  daily_order_total    NUMBER(12,2);

   weekly_order_total   NUMBER(12,2);

   monthly_order_total  NUMBER(12,2);

BEGIN

  COMMIT; -- ends 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;

/

Using SET TRANSACTION to Begin a Read-only Transaction