-- order_loop.sql

DROP TABLE bad_credit
/

CREATE TABLE bad_credit (
  account_number NUMBER,
  over_limit NUMBER(9,2)
)
/

DECLARE
  CURSOR order_curs IS
    SELECT account_number, SUM(amount_due) AS total_due 
      FROM order_header
     WHERE amount_due IS NOT NULL
       AND amount_due > 0
       AND account_number IS NOT NULL
     GROUP BY account_number;

  cur_order order_curs%ROWTYPE;
  bal account.balance%TYPE;
  limit account.credit_limit%TYPE;
  remaining NUMBER;
BEGIN
  OPEN order_curs;
  FETCH order_curs
   INTO cur_order;
  WHILE order_curs%FOUND LOOP

    -- process records
    SELECT balance, credit_limit
      INTO bal, limit
      FROM account
     WHERE account_number = cur_order.account_number;

    remaining := limit - bal;
    IF remaining < cur_order.total_due THEN
      INSERT INTO bad_credit
        VALUES (cur_order.account_number, 
                cur_order.total_due - remaining);
    END IF;
    
    FETCH order_curs
     INTO cur_order;
  END LOOP;

  CLOSE order_curs;

END;
/
