rem 
rem $Header: sample4.sql 28-may-99.14:16:48 jmuller Exp $ 
rem 
Rem  Copyright (c) 1991, 1999 by Oracle Corporation 
Rem    NAME
Rem      sample4.sql - <one-line expansion of the name>
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem    RETURNS
Rem 
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    MODIFIED   (MM/DD/YY)
Rem     jmuller    05/28/99 -  Fix bug 708690: TAB -> blank
Rem     rvasired   05/12/92 -  Creation 
/*
** This program modifies the ACCOUNTS table based on instructions
** stored in the ACTION table.  Each row of the ACTION table 
** contains an account number to act upon, an action to be taken
** (insert, update, or delete), an amount  by which to update the
** account, and a time tag.
**
** On an insert, if the account already exists, an update is
** performed 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.
**
** Copyright (c) 1989,1992, 1999 by Oracle Corporation
*/

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;
/
