Revising program to pass original value

manpreet Best Answer 2 years ago

My task is to revise this program so that before updating the current balance in an account which was the current balance becomes the previous balance.**So for example, if the column "Current Bal" holds a value of 100 and the column "Previous Bal" holds a value of 50...when updated the "previous bal" column will then need to take the value of 100 while a new value is updated into the "current bal" column. When an update is done in place of an insertion, the former current balance should become the previous balance. In the case of an insertion of a new account, the previous balance should be set to zero and the current balance becomes the new balance. This is my code... Can someone point me in the right direction??? Thanks in advance!

/*
**  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), and an amount to update the
**  account to if the action is not a delete.
**      On an insert, if the account already exists, an update is
**  performed instead.  On an update, if the account does not exist,
**  then the account is created via an insert.  On a delete, if the
**  row does not exist, no alternative action is taken.
**
**
*/
DECLARE
  CURSOR c1 IS
    SELECT account_id, oper_type, new_value FROM action
    ORDER BY time_tag
  FOR UPDATE OF status;
  e_primary_key_error exception;
  PRAGMA EXCEPTION_INIT (e_primary_key_error, -00001);
BEGIN
  FOR acct IN c1 LOOP      -- process each row one at a time
  DBMS_OUTPUT.PUT_LINE (ACCT.ACCOUNT_ID);
  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 e_primary_key_error THEN      -- account already exists
          UPDATE accounts SET bal = acct.new_value
               WHERE account_id = acct.account_id;
               UPDATE action SET status =
                 'Insert: Acct exists, Primary Key Error. Update 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
                                                

                                                                                                        REPLY
                                                    
                                                
                                                


                                                    

                                                        
                                                            0
                                                            views 

                                                            0
                                                                likes 
                                                            
                                                            

                                                            0
                                                            shares

                                                            


    

         Facebook
        
         Twitter
        
        
         Linked In 
        
          WhatsApp