-- order_simple.sql

DROP TABLE bad_credit
/
CREATE TABLE bad_credit (
  account_number NUMBER,
  over_limit NUMBER(9,2)
)
/
DECLARE
  bal account.balance%TYPE;
  limit account.credit_limit%TYPE;
  remaining NUMBER;
BEGIN
  FOR cur_order IN ( 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 )
  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;
  END LOOP;
END;
/
