PL/SQL User's Guide and Reference

Sample 4. Batch Transaction Processing

In the next example the accounts table is modified according to instructions stored in the action table. Each row in the action table contains an account number, an action to be taken (I, U, or D for insert, update, or delete), an amount by which to update the account, and a time tag used to sequence the transactions.

On an insert, if the account already exists, an update is done instead. On an update, if the account does not exist, it is created by an insert. On a delete, if the row does not exist, no action is taken.

Input Tables

SQL> SELECT * FROM accounts ORDER BY account_id;


ACCOUNT_ID         BAL
----------   ---------
         1        1000
         2        2000
         3        1500
         4        6500
         5         500

SQL> SELECT * FROM action ORDER BY time_tag;
 
ACCOUNT_ID  O  NEW_VALUE STATUS                   TIME_TAG
----------  - ---------- ------------------------ ---------
         3  u        599                          18-NOV-88
         6  i      20099                          18-NOV-88
         5  d                                     18-NOV-88
         7  u       1599                          18-NOV-88
         1  i        399                          18-NOV-88
         9  d                                     18-NOV-88
        10  x                                     18-NOV-88
 
7 records selected.

PL/SQL Block

-- available online in file SAMPLE4

DECLARE
   CURSOR c1 IS
      SELECT account_id, oper_type, new_value FROM action
      ORDER BY time_tag
      FOR UPDATE OF status;
 
BEGIN
   FOR acct IN c1 LOOP  -- process each row one at a time
 
   acct.oper_type := upper(acct.oper_type);
 
   /*----------------------------------------*/
   /* Process an UPDATE.  If the account to  */
   /* be updated doesn't exist, create a new */
   /* account.                               */
   /*----------------------------------------*/
   IF acct.oper_type = 'U' THEN
      UPDATE accounts SET bal = acct.new_value
         WHERE account_id = acct.account_id;

      IF SQL%NOTFOUND THEN  -- account didn't exist. Create it.
         INSERT INTO accounts
            VALUES (acct.account_id, acct.new_value);
         UPDATE action SET status =
            'Update: ID not found. Value inserted.'
            WHERE CURRENT OF c1;
      ELSE
         UPDATE action SET status = 'Update: Success.'
            WHERE CURRENT OF c1;
      END IF;
 
   /*--------------------------------------------*/
   /* Process an INSERT.  If the account already */
   /* exists, do an update of the account        */
   /* instead.                                   */
   /*--------------------------------------------*/
   ELSIF acct.oper_type = 'I' THEN
      BEGIN
         INSERT INTO accounts
            VALUES (acct.account_id, acct.new_value);
         UPDATE action set status = 'Insert: Success.'
            WHERE CURRENT OF c1;
         EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN   -- account already exists
               UPDATE accounts SET bal = acct.new_value
                  WHERE account_id = acct.account_id;
               UPDATE action SET status =
                  'Insert: Acct exists. Updated instead.'
                  WHERE CURRENT OF c1;
       END;
 
   /*--------------------------------------------*/
   /* Process a DELETE.  If the account doesn't  */
   /* exist, set the status field to say that    */
   /* the account wasn't found.                  */
   /*--------------------------------------------*/
   ELSIF acct.oper_type = 'D' THEN
      DELETE FROM accounts
         WHERE account_id = acct.account_id;
 
      IF SQL%NOTFOUND THEN   -- account didn't exist.
         UPDATE action SET status = 'Delete: ID not found.'
            WHERE CURRENT OF c1;
      ELSE
         UPDATE action SET status = 'Delete: Success.'
            WHERE CURRENT OF c1;
      END IF;

   /*--------------------------------------------*/
   /* The requested operation is invalid.        */
   /*--------------------------------------------*/
   ELSE  -- oper_type is invalid
      UPDATE action SET status =
         'Invalid operation. No action taken.'
         WHERE CURRENT OF c1;
 
   END IF;
 
   END LOOP;
   COMMIT;
END;

Output Tables

SQL> SELECT * FROM accounts ORDER BY account_id;


ACCOUNT_ID         BAL
----------   ---------
         1         399
         2        2000
         3         599
         4        6500
         6       20099
         7        1599
 
6 records selected.


SQL> SELECT * FROM action ORDER BY time_tag;
 
ACCOUNT_ID  O  NEW_VALUE STATUS                    TIME_TAG
----------  - ---------- ------------------------  ---------
         3  u        599 Update: Success.          18-NOV-88
         6  i      20099 Insert: Success.          18-NOV-88
         5  d            Delete: Success.          18-NOV-88
         7  u       1599 Update: ID not found.     18-NOV-88
                         Value inserted.
         1  i        399 Insert: Acct exists.      18-NOV-88
                         Updated instead.
         9  d            Delete: ID not found.     18-NOV-88
        10  x            Invalid operation.        18-NOV-88
                         No action taken.
 
7 records selected.