Oracle uses a string-indexed two-dimensional array and an oracle two-dimensional array
-Reference: http://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54plsql-2245345.html
-SAMPLE DATA
Store name product sales
A apple 3000
A banana 2000
B APPLE 5000
Bmelon 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-APPLE: 3000
A-BANANA: 2000
B-APPLE: 5000
B-MELON: 100
-- Dylan Presents.
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.