Oracle PL/SQL Tutorial/Collections/Table of Varchar2 — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 13:04, 26 мая 2010
Содержание
- 1 A procedure that uses an implicit cursor loop to load an array with employee number and name.
- 2 compare table of varchar2
- 3 Constructor of table collection
- 4 Demonstrate Associative Arrays
- 5 for index between first and last
- 6 Initialize table collection of varchar2
- 7 Loading and accessing an array of varchar2
- 8 Table collection variable assignment
A procedure that uses an implicit cursor loop to load an array with employee number and name.
<source lang="sql">
SQL> SQL> SQL> SQL> SQL> create table employee
2 ( 3 empl_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 ,company_name varchar2(50));
Table created. SQL> SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE loadarray IS
2 TYPE cust_table_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; 3 cust_table cust_table_type; 4 BEGIN 5 FOR crec IN (select empl_no, firstname ||" "|| lastname AS name from employee) LOOP 6 cust_table(crec.empl_no) := crec.name; 7 dbms_output.put_line(cust_table(crec.empl_no)); 8 END LOOP; 9 END; 10 /
Procedure created. SQL> SQL> show errors No errors. SQL> SQL> exec loadarray PL/SQL procedure successfully completed. SQL> SQL> drop table employee; Table dropped. SQL></source>
compare table of varchar2
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE clientList IS TABLE OF VARCHAR2 (64);
3
4 group1 clientList := clientList ("Customer 1", "Customer 2");
5 group2 clientList := clientList ("Customer 1", "Customer 3", NULL);
6 group3 clientList := clientList ("Customer 3", NULL, "Customer 1");
7 BEGIN
8 IF group1 = group2
9 THEN
10 DBMS_OUTPUT.put_line ("Group 1 = Group 2");
11 ELSIF group1 != group2
12 THEN
13 DBMS_OUTPUT.put_line ("Group 1 != Group 2");
14 END IF;
15
16 IF group2 != group3
17 THEN
18 DBMS_OUTPUT.put_line ("Group 2 != Group 3");
19 ELSIF group2 = group3
20 THEN
21 DBMS_OUTPUT.put_line ("Group 2 = Group 3");
22 END IF;
23 END;
24 /
PL/SQL procedure successfully completed.</source>
Constructor of table collection
<source lang="sql">
SQL> SQL> CREATE OR REPLACE TYPE strings_nt IS TABLE OF VARCHAR2(100);
2 /
Type created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE employees_pkg
2 IS
3 vancouver_employees strings_nt := strings_nt ("R", "H", "D", "S", "C");
4 newyork_employees strings_nt := strings_nt ("H", "S", "A");
5 boston_employees strings_nt := strings_nt ("S", "D");
6
7 PROCEDURE show_employees(title_in IN VARCHAR2,employees_in IN strings_nt);
8 END;
9 /
Package created. SQL> SHO ERR No errors. SQL> SQL> CREATE OR REPLACE PACKAGE BODY employees_pkg
2 IS 3 PROCEDURE show_employees(title_in IN VARCHAR2,employees_in IN strings_nt) 4 IS 5 BEGIN 6 DBMS_OUTPUT.put_line (title_in); 7 8 FOR indx IN employees_in.FIRST .. employees_in.LAST 9 LOOP 10 DBMS_OUTPUT.put_line (indx || " = " || employees_in (indx)); 11 END LOOP; 12 13 END show_employees; 14 END; 15 /
Package body created. SQL> SQL> SQL> SHOw error No errors. SQL> SQL> SQL> DECLARE
2 our_favorites strings_nt := strings_nt ();
3 BEGIN
4 our_favorites := employees_pkg.newyork_employees
5 MULTISET UNION
6 employees_pkg.vancouver_employees;
7
8 employees_pkg.show_employees("VEVA THEN STEVEN", our_favorites);
9 END;
10 /
PL/SQL procedure successfully completed. SQL></source>
Demonstrate Associative Arrays
<source lang="sql">
SQL> declare
2 type varchar_array is table of varchar2(100) index by binary_integer; 3 array1 varchar_array; 4 v_index number; 5 begin 6 array1(1) := "T"; 7 array1(2) := "R"; 8 array1(5) := "B"; 9 10 v_index := array1.first; 11 12 for x in 1..array1.count loop 13 dbms_output.put_line(array1(v_index) ); 14 v_index := array1.next(v_index); 15 end loop; 16 end; 17 /
PL/SQL procedure successfully completed. SQL> SQL></source>
for index between first and last
<source lang="sql">
SQL> SQL> CREATE OR REPLACE TYPE strings_nt IS TABLE OF VARCHAR2(100);
2 /
Type created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE employees_pkg
2 IS
3 vancouver_employees strings_nt := strings_nt ("R", "H", "D", "S", "C");
4 newyork_employees strings_nt := strings_nt ("H", "S", "A");
5 boston_employees strings_nt := strings_nt ("S", "D");
6
7 PROCEDURE show_employees (
8 title_in IN VARCHAR2
9 , employees_in IN strings_nt
10 );
11 END;
12 /
Package created. SQL> SHO ERR No errors. SQL> SQL> CREATE OR REPLACE PACKAGE BODY employees_pkg
2 IS
3 PROCEDURE show_employees (
4 title_in IN VARCHAR2
5 , employees_in IN strings_nt
6 )
7 IS
8 BEGIN
9 DBMS_OUTPUT.put_line (title_in);
10
11 FOR indx IN employees_in.FIRST .. employees_in.LAST
12 LOOP
13 DBMS_OUTPUT.put_line (indx || " = " || employees_in (indx));
14 END LOOP;
15
16 DBMS_OUTPUT.put_line ("_");
17 END show_employees;
18 END;
19 /
Package body created. SQL> SHOw error No errors. SQL> SQL> SQL> DECLARE
2 distinct_employees strings_nt := strings_nt ();
3
4 PROCEDURE bpl (val IN BOOLEAN, str IN VARCHAR2)
5 IS
6 BEGIN
7 IF val
8 THEN
9 DBMS_OUTPUT.put_line (str || "-TRUE");
10 ELSIF NOT val
11 THEN
12 DBMS_OUTPUT.put_line (str || "-FALSE");
13 ELSE
14 DBMS_OUTPUT.put_line (str || "-NULL");
15 END IF;
16 END;
17 BEGIN
18
19 employees_pkg.vancouver_employees.EXTEND;
20 employees_pkg.vancouver_employees(employees_pkg.vancouver_employees.LAST) := "ROBERT HARRIS";
21
22 distinct_employees := SET (employees_pkg.vancouver_employees);
23
24 employees_pkg.show_employees ("FULL SET", employees_pkg.vancouver_employees);
25
26 bpl (employees_pkg.vancouver_employees IS A SET, "My employees distinct?");
27 bpl (employees_pkg.vancouver_employees IS NOT A SET, "My employees NOT distinct?");
28
29 employees_pkg.show_employees ("DISTINCT SET", distinct_employees);
30
31 bpl (distinct_employees IS A SET, "SET of employees distinct?");
32 bpl (distinct_employees IS NOT A SET, "SET of employees NOT distinct?");
33 DBMS_OUTPUT.PUT_LINE ("");
34
35 END;
36 /
PL/SQL procedure successfully completed. SQL></source>
Initialize table collection of varchar2
<source lang="sql">
SQL> SQL> CREATE TYPE names_t AS TABLE OF VARCHAR2 (100);
2 /
Type created. SQL> SQL> CREATE TYPE authors_t AS TABLE OF VARCHAR2 (100);
2 /
Type created. SQL> SQL> CREATE TABLE favorite_authors (name varchar2(200))
2 /
Table created. SQL> SQL> BEGIN
2 INSERT INTO favorite_authors VALUES ("R");
3
4 INSERT INTO favorite_authors VALUES ("T");
5
6 INSERT INTO favorite_authors VALUES ("T");
7
8 COMMIT;
9 END;
10 /
PL/SQL procedure successfully completed. SQL> SQL> DECLARE
2 scifi_favorites authors_t := authors_t ("S", "O", "G");
3 BEGIN
4 FOR rec IN (SELECT column_value favs FROM TABLE (CAST (scifi_favorites AS names_t))
5 UNION
6 SELECT NAME FROM favorite_authors)
7 LOOP
8 DBMS_OUTPUT.put_line (rec.favs);
9 END LOOP;
10 END;
11 /
PL/SQL procedure successfully completed. SQL> SQL> DROP TYPE authors_t force; Type dropped. SQL> DROP TYPE names_t force; Type dropped. SQL> DROP table favorite_authors; Table dropped. SQL> SQL> SQL></source>
Loading and accessing an array of varchar2
<source lang="sql">
SQL> SQL> create table employee
2 ( 3 empl_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 ,company_name varchar2(50));
Table created. SQL> SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE loadarray IS
2 TYPE cust_table_type IS TABLE OF VARCHAR2(100) 3 INDEX BY BINARY_INTEGER; 4 cust_table cust_table_type; 5 BEGIN 6 FOR crec IN (select empl_no, firstname ||" "|| lastname AS name from employee) LOOP 7 cust_table(crec.empl_no) := crec.name; 8 END LOOP; 9 dbms_output.put_line(cust_table(1)); 10 END; 11 /
Procedure created. SQL> exec loadarray Joe Jones PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> drop table employee; Table dropped. SQL></source>
Table collection variable assignment
<source lang="sql">
SQL> SQL> create table employee
2 ( 3 empl_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 ,company_name varchar2(50));
Table created. SQL> SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created. SQL> SQL> SQL> SQL> SQL> DECLARE
2 3 TYPE varchar2_array_t IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; 4 v_array1 varchar2_array_t; 5 6 TYPE cust_record_array_t IS TABLE OF employee%rowtype INDEX BY BINARY_INTEGER; 7 v_array2 cust_record_array_t; 8 9 v_array3 cust_record_array_t; 10 11 12 BEGIN 13 v_array2 := v_array3; 14 end; 15 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table employee; Table dropped. SQL> SQL></source>
