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

  TYPE order_tab_type IS TABLE OF order_curs%ROWTYPE 
    INDEX BY PLS_INTEGER;
  cur_orders order_tab_type;
  bal account.balance%TYPE;
  limit account.credit_limit%TYPE;
  remaining NUMBER;
BEGIN
  OPEN order_curs;
  FETCH order_curs
   BULK COLLECT INTO cur_orders LIMIT 10;
  WHILE cur_orders.COUNT != 0 LOOP   -- can't use %FOUND

    FOR i IN 1..cur_orders.COUNT LOOP
      -- process records
      SELECT balance, credit_limit
        INTO bal, limit
        FROM account
       WHERE account_number = cur_orders(i).account_number;
  
      remaining := limit - bal;
      IF remaining < cur_orders(i).total_due THEN
        INSERT INTO bad_credit
          VALUES (cur_orders(i).account_number, 
                  cur_orders(i).total_due - remaining);
      END IF;
    END LOOP;
    
    FETCH order_curs
     BULK COLLECT INTO cur_orders LIMIT 10;
  END LOOP;

  CLOSE order_curs;

END;
/
