–SAMPLE DATA 店名 商品 銷量 A APPLE 3000 A BANANA 2000 B APPLE 5000 B MELON 100 –CONSTRUCT PACKAGE CREATE OR REPLACE PACKAGE p_nested_array IS SUBTYPE prod_name IS VARCHAR2(200); SUBTYPE store_name IS VARCHAR2(200); c_was_processed CONSTANT BOOLEAN := TRUE; TYPE t_prod_qty IS TABLE OF NUMBER(10) INDEX BY prod_name; TYPE t_store_prod_qty IS TABLE OF t_prod_qty INDEX BY store_name; store_prod_qty_t t_store_prod_qty; PROCEDURE reset_store_prod_qty; FUNCTION already_store_prod(store_name_in VARCHAR2, prod_name_in VARCHAR2) RETURN BOOLEAN; PROCEDURE add_store_prod_qty(store_name_in VARCHAR2, prod_name_in VARCHAR2, qty_in NUMBER); FUNCTION find_store_prod_qty(store_name_in VARCHAR2, prod_name_in VARCHAR2) RETURN NUMBER; PROCEDURE print_store_prod_qty; END p_nested_array; / CREATE OR REPLACE PACKAGE BODY p_nested_array IS PROCEDURE reset_store_prod_qty IS BEGIN store_prod_qty_t.DELETE; END reset_store_prod_qty; FUNCTION already_store_prod(store_name_in VARCHAR2, prod_name_in VARCHAR2) RETURN BOOLEAN IS BEGIN IF store_prod_qty_t(store_name_in) (prod_name_in) IS NOT NULL THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION WHEN no_data_found THEN RETURN FALSE; END already_store_prod; PROCEDURE add_store_prod_qty(store_name_in VARCHAR2, prod_name_in VARCHAR2, qty_in NUMBER) IS BEGIN store_prod_qty_t(store_name_in)(prod_name_in) := qty_in; END add_store_prod_qty; FUNCTION find_store_prod_qty(store_name_in VARCHAR2, prod_name_in VARCHAR2) RETURN NUMBER IS BEGIN RETURN store_prod_qty_t(store_name_in)(prod_name_in); EXCEPTION WHEN no_data_found THEN RETURN NULL; END find_store_prod_qty; PROCEDURE print_store_prod_qty IS store_name_idx store_name; prod_name_idx prod_name; BEGIN store_name_idx := store_prod_qty_t.FIRST; dbms_output.put_line(‘store_name_idx: ’ || store_name_idx); WHILE store_name_idx IS NOT NULL LOOP prod_name_idx := store_prod_qty_t(store_name_idx).FIRST; –(store_prod_qty_t(store_name_idx).FIRST); WHILE prod_name_idx IS NOT NULL LOOP dbms_output.put_line(store_name_idx || ‘–’ || prod_name_idx || ‘: ’ || store_prod_qty_t(store_name_idx) (prod_name_idx)); prod_name_idx := store_prod_qty_t(store_name_idx).NEXT(prod_name_idx); –(store_prod_qty_t(store_name_idx).NEXT(prod_name_idx)); END LOOP; store_name_idx := store_prod_qty_t.NEXT(store_name_idx); END LOOP; /* error raised FOR i IN store_prod_qty_t.FIRST .. store_prod_qty_t.LAST LOOP FOR j IN store_prod_qty_t(i).FIRST .. store_prod_qty_t(i).LAST LOOP dbms_output.put_line('Store ' || i || ' saled ' || store_prod_qty_t(i) (j) || ' ' || j || '.'); END LOOP; END LOOP;*/ END print_store_prod_qty; END p_nested_array; / –TEST SCRIPT DECLARE BEGIN P_NESTED_ARRAY.RESET_STORE_PROD_QTY; P_NESTED_ARRAY.ADD_STORE_PROD_QTY(‘A’,’APPLE’,3000); P_NESTED_ARRAY.ADD_STORE_PROD_QTY(‘A’,’BANANA’,2000); P_NESTED_ARRAY.ADD_STORE_PROD_QTY(‘B’,’APPLE’,5000); IF NOT P_NESTED_ARRAY.ALREADY_STORE_PROD(‘B’,’MELON’) THEN P_NESTED_ARRAY.ADD_STORE_PROD_QTY(‘B’,’MELON’,100); ELSE DBMS_OUTPUT.PUT_LINE(‘Store B”s MELON saled ‘||P_NESTED_ARRAY.FIND_STORE_PROD_QTY(‘B’,’MELON’)||’.’); END IF; DBMS_OUTPUT.PUT_LINE(‘Store B”s MELON saled ‘||P_NESTED_ARRAY.FIND_STORE_PROD_QTY(‘B’,’MELON’)||’.’); P_NESTED_ARRAY.print_store_prod_qty; END; / –OUTPUT: Store B’s MELON saled 100. store_name_idx: A A–APPLE: 3000 A–BANANA: 2000 B–APPLE: 5000 B–MELON: 100 —-Dylan Presents.