oracle二维数组为查询条件,Oracle使用由字符串索引的二维数组

–参考文章：http://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54plsql-2245345.html

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

版权声明：本文为博主原创文章，未经博主允许不得转载。

Oracle使用由字符串索引的二维数组

标签：oralce   二维数组   关联数组   字符串索引集合

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://blog.csdn.net/indexman/article/details/46695449