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

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



本文出自 “重剑无锋 大巧不工” 博客,请务必保留此出处http://wuyelan.blog.51cto.com/6118147/1550345

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。