Oracle PL/SQL - COMMIT Statement with COMMENT and WRITE Clauses

Introduction

The COMMIT statement ends the current transaction, making the changes permanent and visible to other users.

The WRITE clause of the COMMIT statement sets the priority.

Demo

SQL>
SQL> DROP TABLE accounts;

Table dropped.-- from www  . j  a  v a 2  s  .  c  o m

SQL> CREATE TABLE accounts (
  2    account_id  NUMBER(6),
  3    balance     NUMBER (10,2)
  4  );
SQL>
SQL> INSERT INTO accounts (account_id, balance)
  2  VALUES (1001, 6350.00);
SQL>
SQL> INSERT INTO accounts (account_id, balance)
  2  VALUES (1002, 5100.50);
SQL>
SQL> CREATE OR REPLACE PROCEDURE transfer (
  2    from_acct  NUMBER,
  3    to_acct    NUMBER,
  4    amount     NUMBER
  5  ) AUTHID DEFINER AS
  6  BEGIN
  7    UPDATE accounts
  8    SET balance = balance - amount
  9    WHERE account_id = from_acct;
 10
 11    UPDATE accounts
 12    SET balance = balance + amount
 13    WHERE account_id = to_acct;
 14    COMMIT WRITE IMMEDIATE NOWAIT;
 15  END;
 16  /

Procedure created.

SQL>
SQL> Query before transfer:
SP2-0734: unknown command beginning "Query befo..." - rest of line ignored.
SQL>
SQL> SELECT * FROM accounts;

ACCOUNT_ID    BALANCE
---------- ----------
      1001       6350
      1002     5100.5

2 rows selected.

SQL>
SQL> BEGIN
  2    transfer(1001, 1002, 250);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM accounts;

ACCOUNT_ID    BALANCE
---------- ----------
      1001       6100
      1002     5350.5

2 rows selected.

SQL>

Related Topics