– Reference article: http://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54plsql-2245345.html
–sample DATA
Store Name Merchandise Sales
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 (Ten) 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 no 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 are 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 are 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.
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Oracle uses a two-dimensional array indexed by a string