-- credit_rating.sql

DROP TABLE credit_rating
/

CREATE TABLE credit_rating (
  account_number NUMBER,
  credit_rating NUMBER
)
/

DECLARE
  TYPE num_col_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  accounts num_col_type;
  amounts_due num_col_type;
  balances num_col_type;
  limits num_col_type;
BEGIN
  SELECT account_number, SUM(amount_due),
         balance, credit_limit
    BULK COLLECT INTO accounts, amounts_due, balances, limits
    FROM order_header oh JOIN account a USING (account_number)
   WHERE amount_due IS NOT NULL
     AND amount_due > 0
     AND account_number IS NOT NULL
   GROUP BY account_number, balance, credit_limit;

  FORALL i IN INDICES OF accounts
    INSERT INTO credit_rating
      VALUES (accounts(i), 
              ROUND(amounts_due(i) / (limits(i) - balances(i)) * 100));

END;
/
