Wednesday, March 2, 2011

FORALL with Non-Consecutive Index Values

CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 2;
CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 2 = 3;

DECLARE
-- Make collection to hold the customer name and order amount
   TYPE cust_name_t IS TABLE OF valid_orders.cust_name%TYPE;
   TYPE order_amount_t is TABLE OF valid_orders.amount%TYPE;
  
   cust_name_tab       cust_name_t;
   order_amount_tab    order_amount_t;
-- Make another collection to point into cust_name_tab collection
   TYPE pointer_t IS TABLE OF PLS_INTEGER;
   rejected_order_tab  pointer_t := pointer_t();
   big_order_tab       pointer_t := pointer_t();   
  
   PROCEDURE setup_data is
   BEGIN
         cust_name_tab    :=  cust_name_t('Customer1','Customer2','Customer3','Customer4','Customer5');
         order_amount_tab :=  order_amount_t(500.0,0,1000,2000,NULL);
   END;
BEGIN
   setup_data();
   --  Point all big orders from main collection
   --  Delete invalid records from the main collection
   --  Point all invalid orders from main collection
   FOR i IN cust_name_tab.FIRST..cust_name_tab.LAST
   LOOP
     IF order_amount_tab(i) > 1000 THEN
         big_order_tab.EXTEND;
         big_order_tab(big_order_tab.LAST) := i;
      END IF;
     IF order_amount_tab(i) = 0 OR order_amount_tab(i) IS NULL THEN
         cust_name_tab.delete(i);
         order_amount_tab.delete(i);
         rejected_order_tab.EXTEND;
         rejected_order_tab(rejected_order_tab.LAST) := i;
      END IF; 
    
   END LOOP;  

   FORALL i IN INDICES OF cust_name_tab
       INSERT INTO valid_orders VALUES(cust_name_tab(i),order_amount_tab(i));
   setup_data();
  
   FORALL i in VALUES OF rejected_order_tab
         INSERT INTO rejected_orders VALUES(cust_name_tab(i),order_amount_tab(i));
 
   FORALL i in VALUES OF big_order_tab
         INSERT INTO big_orders VALUES(cust_name_tab(i),order_amount_tab(i));
    COMMIT;
END;

No comments:

Post a Comment

External Table

Oracle External Table External tables are defined as tables that do not resides in the database allows you to access data that is stor...