Oracle uses a string-indexed two-dimensional array

Source: Internet
Author: User

Oracle uses a string-indexed two-dimensional array

Oracle uses a string-indexed two-dimensional array

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.