Oracle PL/SQL Tutorial/Collections/Table of Varchar2
Содержание
- 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.
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>
compare table of varchar2
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.
Constructor of table collection
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>
Demonstrate Associative Arrays
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>
for index between first and last
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>
Initialize table collection of varchar2
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>
Loading and accessing an array of varchar2
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>
Table collection variable assignment
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>