Live ORACLE

-- Create empty tables to hold order details
DROP TABLE valid_orders CASCADE CONSTRAINTS PURGE;

CREATE TABLE valid_orders (
cust_name VARCHAR2(32),
amount    NUMBER(10,2));

DROP TABLE big_orders CASCADE CONSTRAINTS PURGE;

CREATE TABLE big_orders
AS
SELECT *
FROM   valid_orders
WHERE  1 = 0;

DROP TABLE rejected_orders CASCADE CONSTRAINTS PURGE;

CREATE TABLE rejected_orders
AS
SELECT *
FROM   valid_orders
WHERE  1 = 0;

DECLARE
-- Make collections to hold a set of customer names and order amounts.
SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
TYPE cust_typ IS TABLE OF CUST_NAME;
cust_tab            CUST_TYP;
SUBTYPE order_amount IS valid_orders.amount%TYPE;
TYPE amount_typ IS TABLE OF NUMBER;
amount_tab          AMOUNT_TYP;
-- Make other collections to point into the CUST_TAB collection.
TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
big_order_tab       INDEX_POINTER_T := Index_pointer_t();
rejected_order_tab  INDEX_POINTER_T := Index_pointer_t();
PROCEDURE Setup_data
IS
BEGIN
  -- Set up sample order data, including some invalid orders and some 'big' orders.
  cust_tab := Cust_typ('Company1','Company2','Company3','Company4',
                       'Company5');
  amount_tab := Amount_typ(5000.01,0,150.25,4000.00,NULL);
END;
BEGIN
Setup_data();
dbms_output.Put_line('--- Original order data ---');
FOR i IN 1.. cust_tab.LAST LOOP
  dbms_output.Put_line('Customer #'
                       ||i
                       ||', '
                       ||Cust_tab(i)
                       ||': $'
                       ||Amount_tab(i));
END LOOP;
-- Delete invalid orders (where amount is null or 0).
FOR i IN 1.. cust_tab.LAST LOOP
  IF Amount_tab(i) IS NULL
      OR Amount_tab(i) = 0 THEN
    cust_tab.Delete(i);
    amount_tab.Delete(i);
  END IF;
END LOOP;
dbms_output.Put_line('--- Data with invalid orders deleted ---');
FOR i IN 1.. cust_tab.LAST LOOP
  IF cust_tab.Exists(i) THEN
    dbms_output.Put_line('Customer #'
                         ||i
                         ||', '
                         ||Cust_tab(i)
                         ||': $'
                         ||Amount_tab(i));
  END IF;
END LOOP;
-- Because the subscripts of the collections are not consecutive, use
-- FORALL...INDICES OF to iterate through the actual subscripts,
-- rather than 1..COUNT
FORALL i IN INDICES OF cust_tab
  INSERT INTO valid_orders
             (cust_name,
              amount)
  VALUES     (Cust_tab(i),
              Amount_tab(i));
-- Now process the order data differently
-- Extract 2 subsets and store each subset in a different table
Setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.
FOR i IN cust_tab.FIRST.. cust_tab.LAST LOOP
  IF Amount_tab(i) IS NULL
      OR Amount_tab(i) = 0 THEN
    rejected_order_tab.extend; -- Add a new element to this collection
    -- Record the subscript from the original collection
    Rejected_order_tab(rejected_order_tab.LAST) := i;
  END IF;
  IF Amount_tab(i) > 2000 THEN
    big_order_tab.extend; -- Add a new element to this collection
    -- Record the subscript from the original collection
    Big_order_tab(big_order_tab.LAST) := i;
  END IF;
END LOOP;
-- Now it's easy to run one DML statement on one subset of elements,
-- and another DML statement on a different subset.
FORALL i IN VALUES OF rejected_order_tab
  INSERT INTO rejected_orders
  VALUES     (Cust_tab(i),
              Amount_tab(i));
FORALL i IN VALUES OF big_order_tab
  INSERT INTO big_orders
  VALUES     (Cust_tab(i),
              Amount_tab(i));
COMMIT;
END;
/
 

OUTPUT:-

--- Original order data ---
Customer #1, Company1: $5000.01
Customer #2, Company2: $0
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
Customer #5, Company5: $
--- Data with invalid orders deleted ---
Customer #1, Company1: $5000.01
Customer #3, Company3: $150.25
Customer #4, Company4: $4000

PL/SQL procedure successfully completed.

Customer                         Valid order amount
-------------------------------- ------------------
Company1                                    5000.01
Company3                                     150.25
Company4                                       4000

Customer                         Big order amount
-------------------------------- ----------------
Company1                                  5000.01
Company4                                     4000

Customer                         Rejected order amount
-------------------------------- ---------------------
Company2                                             0
Company5

Using FORALL with Non-Consecutive Index Values