11.PL_SQL——PL_SQL中的复合数据类型之COLLECTION（联合数组（Associative Arrays））

ph033378

　　SQL> edit
　　DECLARE
　　TYPE list_of_names_t IS TABLEOF employees.first_name%TYPE
　　INDEX BY PLS_INTEGER;
　　happyfamily list_of_names_t;
　　l_row PLS_INTEGER;
　　BEGIN
　　happyfamily(2020202020) :='Eli';
　　happyfamily(-15070) :='Steven';
　　happyfamily(-90900) :='Chris';
　　happyfamily(88) := 'Veva';
　　l_row := happyfamily.FIRST;
　　WHILE (l_row IS NOT NULL)
　　LOOP
　　DBMS_OUTPUT.PUT_LINE(l_row|| '-->' || happyfamily(l_row));
　　l_row :=happyfamily.NEXT(l_row);
　　END LOOP;
　　l_row := 88;
　　IF happyfamily.EXISTS(l_row)THEN
　　DBMS_OUTPUT.PUT_LINE(' Itis here! --> ' || happyfamily(l_row));
　　ELSE
　　DBMS_OUTPUT.PUT_LINE(' Itis not here! --> ' || happyfamily(l_row));
　　END IF;
　　END;
　　/
　　SQL> @notes/s44.sql
　　-90900-->Chris
　　-15070-->Steven
　　88-->Veva
　　2020202020-->Eli
　　It is here! --> Veva
　　PL/SQLprocedure successfully completed

　　================Example1=================
　　SQL> edit
　　DECLARE
　　TYPE dept_table_type IS TABLEOF
　　departments%ROWTYPE INDEXBY PLS_INTEGER;
　　dept_tabledept_table_type;
　　-- Each element ofdept_table is a record
　　BEGIN
　　SELECT * INTO dept_table(1)
　　FROM departments
　　WHERE department_id = 10;
　　DBMS_OUTPUT.PUT_LINE(dept_table(1).department_id ||' ' ||dept_table(1).department_name ||' '||
　　dept_table(1).manager_id);
　　END;
　　/
　　SQL> @notes/s45.sql
　　10 Administration 200
　　PL/SQL procedure successfully completed.
　　================Example2=================
　　SQL> edit
　　DECLARE
　　TYPE emp_table_type IS TABLEOF
　　employees%ROWTYPE INDEX BYPLS_INTEGER;
　　my_emp_tableemp_table_type;
　　max_count NUMBER(3) :=104;
　　BEGIN
　　FOR i IN 100..max_count
　　LOOP
　　SELECT * INTOmy_emp_table(i)
　　FROM employees
　　WHERE employee_id = i;
　　END LOOP;
　　FOR i INmy_emp_table.FIRST..my_emp_table.LAST
　　LOOP
　　DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
　　END LOOP;
　　END;
　　/
　　SQL> @notes/s46.sql
　　King
　　Kochhar
　　De Haan
　　Hunold
　　Ernst
　　PL/SQLprocedure successfully completed

　　SQL> edit
　　DECLARE
　　SUBTYPE location_t ISVARCHAR2(64);
　　TYPE population_type IS TABLEOF NUMBER
　　INDEX BY location_t;
　　l_country_populationpopulation_type;
　　l_count PLS_INTEGER;
　　l_location location_t;
　　BEGIN
　　l_country_population('GreenLand') := 100000;
　　l_country_population('USA') :=300000000;
　　l_country_population('IceLand') := 750000;
　　l_country_population('Australia') := 23000000;
　　l_country_population('usa') :=400000000;
　　l_count :=l_country_population.COUNT;
　　DBMS_OUTPUT.PUT_LINE('COUNT =' || l_count);
　　l_location :=l_country_population.FIRST;
　　DBMS_OUTPUT.PUT_LINE('FirstRow = ' || l_location);
　　DBMS_OUTPUT.PUT_LINE('Firstvalue = ' || l_country_population(l_location));
　　l_location :=l_country_population.LAST;
　　DBMS_OUTPUT.PUT_LINE('LastRow = ' || l_location);
　　DBMS_OUTPUT.PUT_LINE('Last Value = ' ||l_country_population(l_location));
　　END;
　　/
　　SQL> @notes/s47.sql
　　COUNT = 5
　　First Row = Australia
　　First value = 23000000
　　Last Row = usa
　　Last Value = 400000000
　　PL/SQLprocedure successfully completed

　　SQL> edit
　　DECLARE
　　TYPEpopulation_type IS TABLE OF NUMBER
　　INDEX BYVARCHAR2(64);
　　city_populationpopulation_type;
　　i VARCHAR2(64);
　　BEGIN
　　city_population('Smallville') := 2000;
　　city_population('Midland') := 75000;
　　city_population('Megalopolis') := 1000000;
　　-- Change valueassocaited with key 'Samllville':
　　city_population('Smallville'):= 2001;
　　-- Print associatearray:
　　i :=city_population.FIRST;
　　-- Get the firstelement of array
　　WHILE i IS NOTNULL
　　LOOP
　　DBMS_OUTPUT.PUT_LINE('Popultaion of ' || i || ' is ' ||city_population(i));
　　i := city_population.NEXT(i);
　　-- Get thenext element of array
　　END LOOP;
　　END;
　　/
　　SQL> @notes/s48.sql
　　Popultaion of Megalopolis is 1000000
　　Popultaion of Midland is 75000
　　Popultaion of Smallville is 2001
　　PL/SQLprocedure successfully completed

　　================Example3=================
　　SQL> edit
　　DECLARE
　　TYPEemp_table_type IS TABLE OF
　　employees%ROWTYPE INDEX BY PLS_INTEGER;
　　my_emp_tableemp_table_type;
　　max_countNUMBER(3) := 104;
　　BEGIN
　　FOR i IN100..max_count
　　LOOP
　　SELECT * INTOmy_emp_table(i)
　　FROM employees
　　WHEREemployee_id = i;
　　END LOOP;
　　FOR i INmy_emp_table.FIRST..my_emp_table.LAST
　　LOOP
　　DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
　　END LOOP;
　　END;
　　/
　　SQL> @notes/s49.sql
　　King
　　Kochhar
　　De Haan
　　Hunold
　　Ernst
　　PL/SQL procedure successfully completed.
　　================Example4=================
　　SQL> edit
　　DECLARE
　　SUBTYPE name_t ISVARCHAR2(32);
　　TYPE lookup_t IS TABLE OFname_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) := 'Wiley';
　　name_tab(1) := 'Mickey';
　　name_tab(2) := 'Simon';
　　name_tab(3) := 'Neal';
　　name_tab(4) := 'Frank';
　　name_tab(5) := 'Darius';
　　name_tab(6) := 'Patrick';
　　-- Lookup the name in the lookup table
　　l_index := name_tab.FIRST;
　　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;
　　/
　　SQL> @notes/s50.sql
　　I found it: 4 --> Frank
　　PL/SQL procedure successfully completed.
　　================Example5=================
　　SQL> edit
　　DECLARE
　　SUBTYPE name_t ISVARCHAR2(32);
　　SUBTYPE value_t IS VARCHAR2(32);
　　TYPE lookup_t IS TABLE OFvalue_t INDEX BY name_t;
　　name_tab        lookup_t;
　　name    name_t := 'Frank';
　　BEGIN
　　-- Populate the lookup table first
　　name_tab('Wiley') := 'Wu';
　　name_tab('Mickey') := 'Wu';
　　name_tab('Simon') :='Beaker';
　　name_tab('Neal') :='Caffery';
　　name_tab('Frank') :='Zhou';
　　name_tab('Darius') :='Florizk';
　　name_tab('Patrick') :='Jane';
　　-- Lookup the name in the lookup table
　　IFname_tab.EXISTS(name) THEN
　　DBMS_OUTPUT.PUT_LINE('I found it: ' || name || ' --> ' ||name_tab(name));
　　ELSE
　　DBMS_OUTPUT.PUT_LINE('I cannot find it. Sorry!');
　　END IF;
　　END;
　　/
　　SQL> @notes/s51.sql
　　I found it: Frank --> Zhou
　　PL/SQLprocedure successfully completed