[bbk5141]第14集 - Chapter 06- Working with Composite Data Types(Collection)
DECLARE SUBTYPE location_t IS VARCHAR2(64); TYPE population_type IS TABLE OF NUMBER INDEX BY location_t; l_country_population population_type; l_count PLS_INTEGER; l_location location_t; BEGIN l_country_population('Greenland') := 100000; l_country_population('USA') := 300000000000; l_country_population('Icland') := 750000; l_country_population('Australia') := 23000000; l_country_population('usa') := 40000000; l_count := l_country_population.COUNT; DBMS_OUTPUT.PUT_LINE('COUNT = ' || l_count); l_location := l_country_population.FIRST; DBMS_OUTPUT.PUT_LINE('First Row = ' || l_location); DBMS_OUTPUT.PUT_LINE('First Value =' || l_country_population(l_location)); l_location := l_country_population.LAST; DBMS_OUTPUT.PUT_LINE('Last Row = ' || l_location); DBMS_OUTPUT.PUT_LINE('Last Value =' || l_country_population(l_location)); END; /
DECLARE TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64); city_population population_type; i VARCHAR2(64); BEGIN city_population('Smallville') := 2000; city_population('Midland') := 750000; city_population('Megalopolis') := 1000000000; city_population('Smallville') := 2001; i := city_population.FIRST; DBMS_OUTPUT.PUT_LINE(i); WHILE(i IS NOT NULL) LOOP DBMS_OUTPUT.PUT_LINE('Key->' || i || ',Value ->' || city_population(i)); i := city_population.NEXT(i); END LOOP; END; /
demo : lookup
DECLARE SUBTYPE name_t IS VARCHAR2(32); TYPE lookup_t IS TABLE OF name_t INDEX BY PLS_INTEGER; name_tab lookup_t; name name_t := 'Frank'; l_index PLS_INTEGER; l_found BOOLEAN := FALSE; BEGIN --Populate the lookup table first name_tab(0) := 'Wilson'; name_tab(1) := 'Jim'; name_tab(2) := 'Richard'; name_tab(3) := 'Michael'; name_tab(4) := 'Frank'; name_tab(5) := 'Chris'; name_tab(6) := 'Walnut'; name_tab(7) := 'Patrick'; name_tab(8) := 'Arcer'; --Lookup the name in the lookup table l_index := name_tab.FIRST; WHILE l_index IS NOT NULL LOOP IF l_index IS NULL THEN EXIT; END IF; IF name = name_tab(l_index) THEN l_found := TRUE; EXIT; END IF; l_index := name_tab.NEXT(l_index); END LOOP; IF l_found THEN DBMS_OUTPUT.PUT_LINE('I found it :' || l_index || '-->' || name); ELSE DBMS_OUTPUT.PUT_LINE('I cannot find it.Sorry!'); END IF; END; /
改进版
DECLARE SUBTYPE name_t IS VARCHAR2(32); SUBTYPE value_t IS VARCHAR2(32); TYPE lookup_t IS TABLE OF value_t INDEX BY name_t; name_tab lookup_t; name name_t := 'Frank'; BEGIN --Populate the lookup table first name_tab('Wilson') := 'a_Wilson'; name_tab('Jim') := 'b_Jim'; name_tab('Richard') := 'c_Richard'; name_tab('Michael') := 'd_Michael'; name_tab('Frank') := 'e_Frank'; name_tab('Chris') := 'f_Chris'; name_tab('Walnut') := 'g_Walnut'; name_tab('Patrick') := 'h_Patrick'; name_tab('Arcer') := 'i_Arcer'; --Lookup the name in the lookup table IF name_tab.EXISTS(name) THEN DBMS_OUTPUT.PUT_LINE('I found it ! =>' || name || '->' || name_tab(name)); ELSE DBMS_OUTPUT.PUT_LINE('Sorry,I cannot find it!'); END IF; END; /
Hash Functions
Index By Integer or String ?
Performance about Index by String