-- order_for.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;
  bal account.balance%TYPE;
  limit account.credit_limit%TYPE;
  remaining NUMBER;
BEGIN
  FOR cur_order IN order_curs 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;
/
