Oracle PL/SQL Tutorial/Collections/Table of Number

Материал из SQL эксперт
Перейти к: навигация, поиск

Accessing an entire Index-by table

   <source lang="sql">

SQL> DECLARE 

 2    TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3    myTable1 num_tab;
 4    myTable2 num_tab;
 5    v_num NUMBER :=13;
 6  BEGIN
 7    myTable1(1):=1001;
 8
 9    myTable1(10):=1002;
10
11    myTable1(-10):=1003;
12
13    myTable1(v_num):=1004;
14
15    myTable2 :=myTable1;
16
17    dbms_output.put_line(to_char(myTable2(1))||" "||
18    to_char(myTable2(10))||" "||
19    to_char(myTable2(-10))||" "||
20    to_char(myTable2(v_num)));
21  END;
22  /

1001 1002 1003 1004 PL/SQL procedure successfully completed.</source>


Accessing an Index-by table

   <source lang="sql">

SQL> DECLARE 

 2    TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3    myTable num_tab;
 4    v_num NUMBER :=13;
 5  BEGIN
 6    myTable(1):=1001;
 7    myTable(10):=1002;
 8    myTable(-10):=1003;
 9    myTable(v_num):=1004;
10
11    dbms_output.put_line(to_char(myTable(1))||" "||
12    to_char(myTable(10))||" "||
13    to_char(myTable(-10))||" "||
14    to_char(myTable(v_num)));
15  END;
16  /

1001 1002 1003 1004 PL/SQL procedure successfully completed.</source>


Accessing an undefined row of an Index-by table

   <source lang="sql">

SQL> DECLARE 

 2    TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3    myTable num_tab;
 4    v_num NUMBER :=13;
 5  BEGIN
 6    myTable(1):=1001;
 7    myTable(10):=1002;
 8    myTable(-10):=1003;
 9    myTable(v_num):=1004;
10    dbms_output.put_line(to_char(myTable(100)));
11  END;
12  /

DECLARE

ERROR at line 1: ORA-01403: no data found ORA-06512: at line 10 <topic title="Sample code using exception while accessing an undefined row of an Index-by table"> 

<![CDATA[

SQL> -- Sample code using exception while accessing an undefined row of an Index-by table SQL> DECLARE 

 2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3    myTable numberTabletype;
 4    v_num NUMBER :=13;
 5  BEGIN
 6    myTable(1):=1001;
 7    myTable(10):=1002;
 8    myTable(-10):=1003;
 9    myTable(v_num):=1004;
10    dbms_output.put_line(to_char(myTable(100)));
11  EXCEPTION WHEN NO_DATA_FOUND THEN
12    dbms_output.put_line ("Invalid array element ");
13  END;
14  /

Invalid array element PL/SQL procedure successfully completed.</source>


Accessing Nested Table elements

   <source lang="sql">

SQL> declare 

 2    Type numberTableType is TABLE OF NUMBER;
 3    v_numarray numberTableType;
 4  begin
 5    v_numarray :=numberTableType(10,20,30,40);
 6
 7
 8    dbms_output.put_line(to_char(v_numarray(1))||", "||to_char(v_numarray(2))||", "||to_char(v_numarray(3))||", "||to_char(v_numarray(4)));
 9
10
11    v_numarray(4):=50;
12    dbms_output.put_line(to_char(v_numarray(1))||", "||to_char(v_numarray(2))||", "||to_char(v_numarray(3))||", "||to_char(v_numarray(4)));
13  end;
14  /

10, 20, 30, 40 10, 20, 30, 50 PL/SQL procedure successfully completed. SQL></source>


An example of COUNT method

   <source lang="sql">

SQL> DECLARE 

 2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3    myTable numberTabletype;
 4  BEGIN
 5    FOR idx IN 1..10 LOOP
 6      myTable(idx):=(2**idx)+1;
 7    END LOOP;
 8
 9    FOR idx IN 1..myTable.COUNT LOOP
10      dbms_output.put_line(to_char(myTable(idx)));
11    END LOOP;
12
13  END;
14  /

3 5 9 17 33 65 129 257 513 1025 PL/SQL procedure successfully completed.</source>


An example of declaring an Index-by table

   <source lang="sql">

SQL> DECLARE 

 2    TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3    myTable num_tab;
 4  BEGIN
 5    /*Do some processing */
 6    null;
 7  END;
 8  /

PL/SQL procedure successfully completed. SQL></source>


An example of the DELETE method

   <source lang="sql">

SQL> DECLARE 

 2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3    myTable numberTabletype;
 4  BEGIN
 5    FOR idx IN 1..10 LOOP
 6      myTable(idx):=(2**idx)+1;
 7    END LOOP;
 8
 9    myTable.DELETE(1);
10
11    myTable.DELETE(2,5);
12
13    myTable.DELETE;
14  END;
15  /

PL/SQL procedure successfully completed.</source>


An example of the FIRST, LAST and NEXT methods

   <source lang="sql">

SQL> DECLARE 

 2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3    myTable numberTabletype;
 4    idx BINARY_INTEGER;
 5  BEGIN
 6    FOR idx IN 1..10 LOOP
 7      myTable(idx):=(2**idx)+1;
 8    END LOOP;
 9
10    idx :=myTable.FIRST;
11
12    LOOP
13      dbms_output.put_line(to_char(myTable(idx)));
14      EXIT WHEN idx =myTable.LAST;
15      idx :=myTable.NEXT(idx);
16    END LOOP;
17  END;
18  /

3 5 9 17 33 65 129 257 513 1025 PL/SQL procedure successfully completed.</source>


Assigning rows of an Index-By table by means of a LOOP

   <source lang="sql">

SQL> DECLARE 

 2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3    myTable numberTabletype;
 4  BEGIN
 5    FOR idx IN 1..10 LOOP
 6      myTable(idx):=(2**idx)+1;
 7    END LOOP;
 8
 9    FOR idx IN 1..10 LOOP
10      dbms_output.put_line(to_char(myTable(idx)));
11    END LOOP;
12  END;
13  /

3 5 9 17 33 65 129 257 513 1025 PL/SQL procedure successfully completed.</source>


Associative arrays

   <source lang="sql">

SQL> DECLARE 

 2    TYPE site_array IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
 3    v_example_array site_array;
 4  BEGIN
 5    v_example_array("E"):=10;
 6    v_example_array("B"):=11;
 7    v_example_array("S"):=12;
 8    dbms_output.put_line(v_example_array.FIRST);
 9    dbms_output.put_line(v_example_array.LAST);
10    dbms_output.put_line(TO_CHAR(v_example_array(v_example_array.FIRST)));
11  END;
12  /

B S 11 PL/SQL procedure successfully completed. SQL> SQL></source>


compare two tables of integers

   <source lang="sql">

SQL> SQL> DECLARE 

 2     TYPE nested_tab_t IS TABLE OF INTEGER;
 3
 4     tab_1   nested_tab_t := nested_tab_t (1, 2, 3, 4, 5, 6, 7);
 5     tab_2   nested_tab_t := nested_tab_t (7, 6, 5, 4, 3, 2, 1);
 6
 7     PROCEDURE tabs_equal (i_tab_1 IN nested_tab_t, i_tab_2 IN nested_tab_t)
 8     IS
 9        v_equal   BOOLEAN := i_tab_1 = i_tab_2;
10     BEGIN
11        IF v_equal IS NULL
12        THEN
13           DBMS_OUTPUT.put_line ("null");
14        ELSIF v_equal
15        THEN
16           DBMS_OUTPUT.put_line ("equal");
17        ELSE
18           DBMS_OUTPUT.put_line ("not equal");
19        END IF;
20     END tabs_equal;
21  BEGIN
22     tabs_equal (tab_1, tab_2);
23     tab_1.EXTEND (1);
24     tabs_equal (tab_1, tab_2);
25     tab_2.EXTEND (1);
26     tabs_equal (tab_1, tab_2);
27  END;
28  /

PL/SQL procedure successfully completed. SQL></source>


Deleting an Index-by table using an empty Index-by table

   <source lang="sql">

SQL> DECLARE 

 2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3    myTable1 numberTabletype;
 4    myTable2 numberTabletype;
 5    v_num NUMBER :=13;
 6  BEGIN
 7    myTable1(1):=1001;
 8    myTable1(10):=1002;
 9    myTable1(-10):=1003;
10    myTable1(v_num):=1004;
11    myTable1:=myTable2;
12  END;
13  /

PL/SQL procedure successfully completed.</source>


Initialize table collection with null value

   <source lang="sql">

SQL> SQL> declare 

 2    Type numberTableType is TABLE OF NUMBER;
 3    v_numarray2 numberTableType;
 4  begin
 5    v_numarray2 :=numberTableType(NULL);
 6
 7    if v_numarray2 IS NULL then
 8      dbms_output.put_line("v_numarray2 is null");
 9    else
10      dbms_output.put_line("v_numarray2 is not null");
11    end if;
12    if v_numarray2(1) IS NULL then
13      dbms_output.put_line("The first element of v_numarray2 is null");
14    end if;
15  end;
16  /

v_numarray2 is not null The first element of v_numarray2 is null PL/SQL procedure successfully completed. SQL> SQL></source>


Initializing a Nested Table

   <source lang="sql">

SQL> DECLARE 

 2    TYPE numberTableType IS TABLE OF NUMBER;
 3
 4    v_numarray numberTableType :=numberTableType(10,20,30,40);
 5
 6  BEGIN
 7
 8    dbms_output.put_line("The first element of v_numarray is "||
 9
10    TO_CHAR(v_numarray(1)));
11  END;
12  /

The first element of v_numarray is 10 PL/SQL procedure successfully completed. SQL></source>


Is a table collection a null value

   <source lang="sql">

SQL> SQL> declare 

 2    Type numberTableType is TABLE OF NUMBER;
 3    v_emptyarray numberTableType :=numberTableType();
 4  begin
 5    if v_emptyarray IS NULL then
 6      dbms_output.put_line("v_emptyarray is null");
 7    else
 8      dbms_output.put_line("v_emptyarray is NOT null");
 9    end if;
10  end;
11  /

v_emptyarray is NOT null PL/SQL procedure successfully completed. SQL></source>


Loop through table of number by index

   <source lang="sql">

SQL> SQL> SQL> create table employee 

 2          (
 3           emp_no                 integer     primary key
 4          ,lastname               varchar2(20)    not null
 5          ,firstname              varchar2(15)    not null
 6          ,midinit                varchar2(1)
 7          ,street                 varchar2(30)
 8          ,city                   varchar2(20)
 9          ,state                  varchar2(2)
10          ,zip                    varchar2(5)
11          ,zip_4                  varchar2(4)
12          ,area_code              varchar2(3)
13          ,phone                  varchar2(8)
14          ,salary                 number(5,2)
15          ,birthdate              date
16          ,hiredate               date
17          ,title                  varchar2(20)
18          ,dept_no                integer
19        ,mgr              integer
20        ,region           number
21        ,division         number
22        ,total_sales          number
23         );

Table created. SQL> SQL> SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, birthdate, title) 

 2  values (1,"Gardinia","Joy","R","688 Ave","New York","NY","12122","2333","212","200-3393","12-nov-1956","President");

1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales) 

 2  values (2,"Anderson","Lucy","J","33 Ave","New York","NY","43552","6633","212","234-4444",7.75,"21-mar-1951","1-feb-1994","Sales Manager",2,1,100,10,40000);

1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales) 

 2  values (3,"Somers","Ingrid","E","12 Ave","New York","NY","76822","8763","212","867-6893",7.75,"14-feb-1963","15-mar-1995","Sales Clerk",2,2,100,10,10000);

1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales) 

 2  values (4,"Washington","Georgia","J","13th Street","New York","NY","43122","4333","212","340-4365",11.50,"2-jul-1963","21-apr-1994","Designer",1,1,100,10,40000);

1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales) 

 2  values (5,"Doright","Dudley","J","56 Langer Street","Staten Island","NY","23332","4983","718","777-4365",21.65,"15-may-1958","2-aug-1994","Designer",1,1,100,10,40000);

1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales) 

 2  values ( 6,"Doright","Dorothy","R","56 Langer Street","Staten Island","NY","23332","4983","718","777-4365",24.65,"10-dec-1968","2-aug-1994","Designer",1,1,100,10,40000);

1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales) 

 2  values ( 7,"Perry","Donna","R","1st Ave","New York","NY","44444","3444","212","111-6893",7.75,"14-feb-1967","15-mar-1995","Sales Clerk",2,1,100,10,40000);

1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales) 

 2  values ( 8,"Roger","John","E","67 H Ave","New York","NY","33822","1163","212","122-6893",10.00,"14-jun-1956","15-mar-1995","Accountant",3,1,100,10,40000);

1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales) 

 2  values ( 9,"Hall","Ted","R","1236 Lane","New York","NY","33823","1164","212","222-4393",13.00,"10-jun-1959","15-aug-1997","Sales Representative",3,1,100,10,50000);

1 row created. SQL> SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales) 

 2  values ( 10,"Barbee","Candice","L","400 Street","New York","NY","33811","2009","212","321-6873",12.00,"10-oct-1964","15-jan-1999","Sales Representative",3,1,100,10,35000);

1 row created. SQL> SQL> SQL> SQL> declare 

 2    type num_type is    table of number;
 3    l_manager_array     num_type := num_type(1,2,3);
 4    child_record_found  exception;
 5    pragma    exception_init(child_record_found, -2292);
 6    v_index             number;
 7  begin
 8    forall i in l_manager_array.FIRST..l_manager_array.LAST
 9       delete from employee WHERE mgr = l_manager_array(i);
10       dbms_output.put_line(sql%rowcount);
11    exception
12       when child_record_found then
13           v_index := l_manager_array.first;
14           dbms_output.put_line("child record found");
15           for i in l_manager_array.FIRST..l_manager_array.LAST
16           loop
17             dbms_output.put_line("delete for manager "
18                                  ||l_manager_array(v_index)
19                                  ||" deleted "
20                                  ||SQL%BULK_ROWCOUNT(v_index)
21                                  ||" rows.");
22             v_index := l_manager_Array.NEXT(v_index);
23           end loop;
24           commit;
25  end;
26  /

9 PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table employee; Table dropped. SQL> SQL></source>


NULL and Empty Nested Tables and NULL elements

   <source lang="sql">

SQL> declare 

 2
 3    Type numberTableType is TABLE OF NUMBER;
 4
 5    v_numarray numberTableType;
 6
 7  begin
 8
 9    if v_numarray IS NULL then
10
11      dbms_output.put_line("v_numarray is null");
12
13    end if;
14
15    v_numarray :=NULL;
16
17    if v_numarray IS NULL then
18
19      dbms_output.put_line("v_numarray is null after assignment");
20
21    end if;
22  end;
23  /

v_numarray is null v_numarray is null after assignment PL/SQL procedure successfully completed. SQL></source>


number_list.EXTEND(2): Add two null value members at the end of the list.

   <source lang="sql">

SQL> create or replace TYPE number_table IS TABLE OF INTEGER; 

 2  /

Type created. SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS 

 2   BEGIN
 3       FOR i IN list_in.FIRST..list_in.LAST LOOP
 4         IF list_in.EXISTS(i) THEN
 5           DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
 6         END IF;
 7       END LOOP;
 8   END print_list;
 9   /

Procedure created. SQL> SQL> SQL> SQL> DECLARE 

 2     number_list NUMBER_TABLE;
 3
 4   BEGIN
 5     IF NOT number_list.EXISTS(1) THEN
 6       number_list := number_table(1,2,3,4,5);
 7     END IF;
 8
 9     print_list(number_list);
10
11     -- Add two null value members at the end of the list.
12     number_list.EXTEND(2);
13
14     -- Print revised contents.
15     DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
16     print_list(number_list);
17   END;
18   /

List 1 List 2 List 3 List 4 List 5 Nested table after a deletion List 1 List 2 List 3 List 4 List 5 List List PL/SQL procedure successfully completed. SQL> SQL></source>


number_list.EXTEND(3,4): Add three members at the end of the list and copy the contents of item 4

   <source lang="sql">

SQL> create or replace TYPE number_table IS TABLE OF INTEGER; 

 2  /

Type created. SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS 

 2   BEGIN
 3       FOR i IN list_in.FIRST..list_in.LAST LOOP
 4         IF list_in.EXISTS(i) THEN
 5           DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
 6         END IF;
 7       END LOOP;
 8   END print_list;
 9   /

Procedure created. SQL> SQL> SQL> SQL> DECLARE 

 2     number_list NUMBER_TABLE;
 3
 4   BEGIN
 5     IF NOT number_list.EXISTS(1) THEN
 6       number_list := number_table(1,2,3,4,5);
 7     END IF;
 8
 9     print_list(number_list);
10
11     
12     number_list.EXTEND(3,4);
13
14     
15     DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
16     print_list(number_list);
17   END;
18   /

List 1 List 2 List 3 List 4 List 5 Nested table after a deletion List 1 List 2 List 3 List 4 List 5 List 4 List 4 List 4 PL/SQL procedure successfully completed. SQL></source>


ORA-06531: Reference to uninitialized collection

   <source lang="sql">

SQL> SQL> declare 

 2    Type numberTableType is TABLE OF NUMBER;
 3    v_numarray1 numberTableType;
 4  begin
 5    if v_numarray1 IS NULL then
 6      dbms_output.put_line("v_numarray1 is null");
 7    end if;
 8    if v_numarray1(1) IS NULL then
 9      dbms_output.put_line("The first element of v_numarray1 is null");
10    end if;
11  end;
12  /

v_numarray1 is null declare

ERROR at line 1: ORA-06531: Reference to uninitialized collection ORA-06512: at line 8

SQL></source>


ORA-06533: Subscript beyond count

   <source lang="sql">

SQL> SQL> SQL> declare 

 2    Type numberTableType is TABLE OF NUMBER;
 3
 4    v_numarray numberTableType;
 5
 6  begin
 7
 8    v_numarray :=numberTableType(10,20,30,40);
 9
10    dbms_output.put_line(to_char(v_numarray(1))||", "||to_char(v_numarray(2))||", "||to_char(v_numarray(3))||", "||to_char(v_numarray(4)));
11
12    dbms_output.put_line(to_char(v_numarray(5)));
13  end;
14  /

10, 20, 30, 40 declare

ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 12

SQL> SQL></source>


Using the EXISTS method

   <source lang="sql">

SQL> DECLARE 

 2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 3    myTable numberTabletype;
 4    v_num NUMBER :=13;
 5  BEGIN
 6    myTable(1):=1001;
 7
 8    myTable(10):=1002;
 9
10    myTable(-10):=1003;
11
12    myTable(v_num):=1004;
13
14    IF myTable.EXISTS(100) THEN
15      dbms_output.put_line(to_char(myTable(100)));
16    END IF;
17
18  END;
19  /

PL/SQL procedure successfully completed.</source>

Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/Collections/Table_of_Number&oldid=2922»