Oracle uses a string-indexed two-dimensional array

Oracle uses a string-indexed two-dimensional array

 

-Sample data store name product sales a apple 3000A BANANA 2000B APPLE 5000B MELON 100-construct packagecreate or replace package p_nested_array ISSUBTYPE 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; t_store_prod_qty; PROCEDURE restart; FUNCTION compute (lower VARCHAR2, lower VARCHAR2) return boolean; PROCEDURE tables (store_name_in VARCHAR2, lower VARCHAR2, qty_in NUMBER); FUNCTION compute (store_name_in VARCHAR2, lower VARCHAR2) return number; PROCEDURE tables; END transaction;/create or replace package body p_nested_array ISPROCEDURE implements transaction; END transaction; FUNCTION compute (store_name_in VARCHAR2, prod_name_in VARCHAR2) return boolean isbeginif aggregate (store_name_in) (prod_name_in) is not null thenreturn true; begin FALSE; end if; EXCEPTIONWHEN then thenreturn false; END transaction; PROCEDURE merge (store_name_in VARCHAR2, prod_name_in VARCHAR2, qty_in NUMBER) aggregate (store_name_in) (bytes ): = qty_in; END variable; FUNCTION compute (store_name_in VARCHAR2, invalid VARCHAR2) return number isbeginreturn partition (store_name_in) (prod_name_in); predictionwhen returns thenreturn null; END variable; PROCEDURE extends store_name; prod_name_idx prod_name; BEGINstore_name_idx: = primary; primary ('store _ name_idx: '| store_name_idx); WHILE store_name_idx is not null primary: = primary (store_name_idx ). FIRST;-(store_prod_qty_t (store_name_idx ). FIRST); WHILE prod_name_idx is not null partition (store_name_idx | '-' | prod_name_idx | ':' | partition (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 print_store_prod_qty; END p_nested_array;/-TEST metadata; P_NESTED_ARRAY.ADD_STORE_PROD_QTY ('A', 'apple', 3000 ); values ('A', 'Banana ', 2000); P_NESTED_ARRAY.ADD_STORE_PROD_QTY (' B ', 'apple', 5000); if not P_NESTED_ARRAY.ALREADY_STORE_PROD (' B ', 'melon ') values ('B', 'melon', 100); ELSEDBMS_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: AA-APPLE: 3000A-BANANA: 2000B-APPLE: 5000B-MELON: 100 -- Dylan Presents.