в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:25Z

1 версия

Новая страница

== 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">
<code><![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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Collections/Table of Number - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия