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