Oracle PL/SQL Tutorial/Collections/Varray
Содержание
- 1 A procedure that uses EXISTS and LAST
- 2 Assign value to VARRAY
- 3 Column_value is a built-in function/pseudo-variable that is held over from the DBMS_SQL package
- 4 CREATE TABLE with a VARRAY
- 5 Creating a Varray Type
- 6 Decreasing the Size of an Array
- 7 Defining our type to be a VARRAY with 10 elements, where each element is a varying character string of up to 15 characters.
- 8 EXISTS and LAST
- 9 Extracting individual members of a VARRAY may be accomplished using two other functions: THE and VALUE
- 10 Getting Information on Varrays
- 11 If aliases are used, they must be used consistently
- 12 Inside the loop, you are accessing array elements by their subscripts.
- 13 LAST and COUNT give the same result for VARRAYs.
- 14 Loading a Table with a VARRAY in It: INSERT VALUEs with Constants
- 15 Loop through by using the built-in NEXT, PRIOR.
- 16 Manipulating the VARRAY with The TABLE Function
- 17 Manipulating the VARRAY with The VARRAY Self-join
- 18 One way to make the "members" behave like an array is first to include the row number in the result set like this:
- 19 Query table with VARRAY type column by column name
- 20 Query VARRAY column
- 21 The CAST function converts an object type (such as a VARRAY) into a common type that can be queried. Oracle 10g automatically converts the VARRAY without the CAST.
- 22 The COUNT Function
- 23 Then, the individual array element can be extracted with a WHERE filter:
- 24 The subscript of the last element is always equal to the size of the array.
- 25 Using PL/SQL to Create Functions to Access Elements
- 26 Using VARRAYs
- 27 VARRAY in action
- 28 VARRAY of Cursor
A procedure that uses EXISTS and LAST
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE vs3 (sub integer)
2 IS
3 CURSOR vcur IS
4 SELECT name, members FROM club;
5 x varchar2(30);
6 BEGIN
7 FOR j IN vcur LOOP
8 x := j.name||" No Members";
9 IF j.members.exists(1) THEN -- exists
10 IF sub <= j.members.last THEN -- last
11 x := j.name||" "||j.members(sub);
12 -- access array element
13 ELSE
14 x := j.name||" Less than "||sub||" members";
15 END IF;
16 END IF;
17 dbms_output.put_line(x);
18 END LOOP;
19 END vs3;
20 /
Procedure created.
SQL>
SQL> exec vs3(1);
AL Brenda
FL Gen
PL/SQL procedure successfully completed.
SQL> exec vs3(2);
AL Richard
FL John
PL/SQL procedure successfully completed.
SQL> exec vs3(3);
AL Less than 3 members
FL Steph
PL/SQL procedure successfully completed.
SQL> exec vs3(4);
AL Less than 4 members
FL JJ
PL/SQL procedure successfully completed.
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
Assign value to VARRAY
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 TYPE emp_array IS VARRAY(100) OF VARCHAR2(30);
3
4 emps emp_array;
5 inx1 PLS_INTEGER;
6
7 BEGIN
8 emps := emp_array ("emp One","emp Two");
9
10 FOR inx1 IN 1..2 LOOP
11 DBMS_OUTPUT.PUT_LINE(emps(inx1));
12 END LOOP;
13 END;
14 /
emp One
emp Two
PL/SQL procedure successfully completed.
SQL>
SQL>
Column_value is a built-in function/pseudo-variable that is held over from the DBMS_SQL package
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> SELECT c.name, p.column_value, COUNT(p.column_value)
2 FROM club c, TABLE(c.members) p
3 -- WHERE c.name = "AL"
4 GROUP by c.name, p.column_value
5
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
CREATE TABLE with a VARRAY
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
SQL>
SQL> DESC club;
Name Null? Type
-----------
NAME VARCHAR2(10)
ADDRESS VARCHAR2(20)
CITY VARCHAR2(20)
PHONE VARCHAR2(8)
MEMBERS MEM_TYPE
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
SQL>
Creating a Varray Type
- A varray stores an ordered set of elements.
- Each element has an index associated with it.
- A varray has a maximum size that you can change dynamically.
You create a varray type using the SQL DDL CREATE TYPE statement.
You specify the maximum size and the type of elements stored in the varray when creating the
The basic Oracle syntax for the CREATE TYPE statement for a VARRAY type definition would be:
CREATE OR REPLACE TYPE name-of-type IS VARRAY(nn)of type
Decreasing the Size of an Array
You cannot delete an element inside the array.
Even if you set it to NULL, it still exists.
The only thing you can do is decrease the size of the array.
SQL>
SQL> declare
2 type month_va is varray(13) of VARCHAR2(20);
3 v_month_va month_va:=month_va();
4 v_count_nr number;
5 begin
6 v_month_va.extend(3);
7 v_month_va(1):="January";
8 v_month_va(2):="February";
9 v_month_va(3):="March";
10
11 v_month_va(2):=null;
12 if v_month_va.exists(2)
13 then
14 DBMS_OUTPUT.put_line("Object Exists");
15 end if;
16
17 v_month_va(3):=v_month_va(2);
18 v_month_va.trim(1);
19 DBMS_OUTPUT.put_line("Count:"||v_month_va.count);
20 DBMS_OUTPUT.put_line("Last:"||v_month_va.last);
21 end;
22 /
Object Exists
Count:2
Last:2
PL/SQL procedure successfully completed.
SQL>
SQL>
Defining our type to be a VARRAY with 10 elements, where each element is a varying character string of up to 15 characters.
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
SQL>
SQL> drop type mem_type;
EXISTS and LAST
Suppose we add a row with no members to the Club table:
EXISTS returns a Boolean that acknowledges the presence (T) or absence (F) of a member of a VARRAY.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club values ("NY","55 Fifth Ave.","NYC",
2 "999-9999",null)
3 SELECT *
4 FROM club
5
SQL>
SQL> CREATE OR REPLACE FUNCTION vs (vlist club.members%type, sub integer)
2 RETURN VARCHAR2
3 IS
4 BEGIN
5 IF vlist.exists(1) THEN
6 IF sub <= vlist.last THEN
7 RETURN vlist(sub);
8 ELSE
9 RETURN "Less than "||sub||" members";
10 END IF;
11 ELSE
12 RETURN "No members";
13 END IF;
14 END vs;
15 /
Function created.
SQL> ---The EXISTS function requires an argument to tell which element of the VARRAY is referred to. In the above function we are saying in the coded if-statement that if there is no first element, then return "No members." If a first member of the array is present, then the array is not null and we can look for whichever member is sought (per the value of sub). If the value of sub is less than the value of the last subscript, then the return of ""Less than "||sub||" members"" is effected.
SQL>
SQL> SELECT c.name, vs(members,3) member_name
2 FROM club c;
NAME
----------
MEMBER_NAME
------------------------
AL
Less than 3 members
FL
Steph
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
Extracting individual members of a VARRAY may be accomplished using two other functions: THE and VALUE
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT VALUE(x) FROM
2 THE(SELECT c.members FROM club c
3 WHERE c.name = "FL") x
4 WHERE VALUE(x) is not null;
VALUE(X)
---------------
Gen
John
Steph
JJ
SQL>
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
SQL>
Getting Information on Varrays
You can use the DESCRIBE command to get information on your varray types.
SQL> CREATE or replace TYPE addressVarray AS VARRAY(2) OF VARCHAR2(50);
2 /
Type created.
SQL>
SQL> DESCRIBE addressVarray;
addressVarray VARRAY(2) OF VARCHAR2(50)
SQL>
If aliases are used, they must be used consistently
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> SELECT c.name "Clubname"
2 FROM club c
3 WHERE "Gen" IN
4 (SELECT * FROM TABLE(c.members));
Clubname
----------
FL
SQL>
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
SQL>
Inside the loop, you are accessing array elements by their subscripts.
SQL>
SQL> declare
2 type month_va is varray(13) of VARCHAR2(20);
3 v_month_va month_va;
4 v_count_nr number;
5 begin
6 v_month_va:=month_va("A","B","C","D","E","F","G");
7 DBMS_OUTPUT.put_line("Length:"||v_month_va.count);
8
9 for i in v_month_va.first..v_month_va.last
10 loop
11 DBMS_OUTPUT.put_line("v_month_va(i): "||v_month_va(i));
12 end loop;
13 end;
14 /
Length:7
v_month_va(i): A
v_month_va(i): B
v_month_va(i): C
v_month_va(i): D
v_month_va(i): E
v_month_va(i): F
v_month_va(i): G
PL/SQL procedure successfully completed.
LAST and COUNT give the same result for VARRAYs.
The functions FIRST and LAST may be used to set the upper and lower limit of a for-loop to access members of the array one at a time in PL/SQL.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE vartest1 IS
2 CURSOR fcur IS SELECT name, members FROM club;
3 BEGIN
4 FOR j IN fcur LOOP
5 dbms_output.put_line("For the "||j.name||" club ...");
6 IF j.members.exists(1) THEN
7 FOR k IN j.members.first..j.members.last LOOP
8 dbms_output.put_line("** "||j.members(k));
9 END LOOP;
10 ELSE
11 dbms_output.put_line("** There are no members on file");
12 END IF;
13 END LOOP; /* end for j in fcur loop */
14 END vartest1;
15 /
Procedure created.
SQL> exec vartest1;
For the AL club ...
** Brenda
** Richard
For the FL club ...
** Gen
** John
** Steph
** JJ
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
SQL>
SQL>
Loading a Table with a VARRAY in It: INSERT VALUEs with Constants
Oracle"s VARRAYs behave like classes in object-oriented programming. Classes are instantiated into objects using constructors.
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL> SELECT *
2 FROM club;
NAME ADDRESS CITY PHONE
---------- -------------------------------------------------- -------------------- --------
MEMBERS
-------------------------------------------------------------------------------------------
AL 111 First St. Mobile 222-2222
MEM_TYPE("Brenda", "Richard")
FL 222 Second St. Orlando 333-3333
MEM_TYPE("Gen", "John", "Steph", "JJ")
SQL>
SQL>
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
SQL>
Loop through by using the built-in NEXT, PRIOR.
If there are no more elements, the built-in method will return NULL.
SQL>
SQL> declare
2 type month_va is varray(13) of VARCHAR2(20);
3 v_month_va month_va;
4 v_count_nr number;
5 begin
6 v_month_va:=month_va("A","B","C","D","E","F","G");
7 DBMS_OUTPUT.put_line("Length:"||v_month_va.count);
8
9 for i in v_month_va.first..v_month_va.last
10 loop
11 DBMS_OUTPUT.put_line("v_month_va(i): "||v_month_va(i));
12 end loop;
13 end;
14 /
Length:7
v_month_va(i): A
v_month_va(i): B
v_month_va(i): C
v_month_va(i): D
v_month_va(i): E
v_month_va(i): F
v_month_va(i): G
PL/SQL procedure successfully completed.
Manipulating the VARRAY with The TABLE Function
The TABLE function can be used to indirectly access data in the VARRAY by using an IN predicate:
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL> SELECT name "Clubname"
2 FROM club
3 WHERE "Gen" IN
4 (SELECT * FROM TABLE(club.members))
5
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
SQL>
Manipulating the VARRAY with The VARRAY Self-join
A statement can be created that joins the values of the virtual table (created with the TABLE function) to the rest of the values in the table
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> SELECT c.name, c.address, p.column_value
2 FROM club c, TABLE(c.members) p;
NAME ADDRESS COLUMN_VALUE
---------- -------------------------------------------------- ---------------
AL 111 First St. Brenda
AL 111 First St. Richard
FL 222 Second St. Gen
FL 222 Second St. John
FL 222 Second St. Steph
FL 222 Second St. JJ
6 rows selected.
SQL>
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
SQL>
One way to make the "members" behave like an array is first to include the row number in the result set like this:
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL> SELECT n, val
2 FROM
3 (SELECT rownum n, COLUMN_VALUE val FROM
4 THE(SELECT c.members FROM club c
5 WHERE c.name = "FL") x
6 WHERE COLUMN_VALUE IS NOT NULL);
N VAL
---------- ---------------
1 Gen
2 John
3 Steph
4 JJ
SQL>
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
Query table with VARRAY type column by column name
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL> SELECT name, city, members
2 FROM club
3
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
Query VARRAY column
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL> SELECT *
2 FROM club;
NAME ADDRESS CITY PHONE
---------- -------------------------------------------------- -------------------- --------
MEMBERS
--------------------------------------------------------------------------------------------
AL 111 First St. Mobile 222-2222
MEM_TYPE("Brenda", "Richard")
FL 222 Second St. Orlando 333-3333
MEM_TYPE("Gen", "John", "Steph", "JJ")
SQL>
SQL>
SQL>
SQL> drop table club;
Table dropped.
SQL> drop TYPE mem_type;
Type dropped.
SQL>
The CAST function converts an object type (such as a VARRAY) into a common type that can be queried. Oracle 10g automatically converts the VARRAY without the CAST.
The CAST function may also be used with the MULTISET function to perform DML operations on VARRAYs. MULTISET is the "reverse" of CAST in that MULTISET converts a nonobject set of data to an object set. Suppose we create a new table of names:
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> SELECT COLUMN_VALUE FROM
2 THE(SELECT CAST(c.members as mem_type)
3 FROM club c
4 WHERE c.name = "FL");
COLUMN_VALUE
---------------
Gen
John
Steph
JJ
SQL>
SQL>
SQL>
SQL> CREATE TABLE newnames (n varchar2(20))
2 /
Table created.
SQL> INSERT INTO newnames VALUES ("Beryl")
2 /
1 row created.
SQL> INSERT INTO newnames VALUES ("Fred")
2 /
1 row created.
SQL> SELECT *
2 FROM newnames
3 /
N
--------------------
Beryl
Fred
SQL>
SQL> INSERT INTO club VALUES ("VA",null,null,null,null)
2 /
1 row created.
SQL> UPDATE club SET members =
2 CAST(MULTISET(SELECT n FROM newnames) as mem_type)
3 WHERE name = "VA"
4 /
1 row updated.
SQL>
SQL> select * from club;
NAME ADDRESS CITY PHONE
---------- -------------------------------------------------- -------------------- --------
MEMBERS
-------------------------------------------------------------------------------------------
AL 111 First St. Mobile 222-2222
MEM_TYPE("Brenda", "Richard")
FL 222 Second St. Orlando 333-3333
MEM_TYPE("Gen", "John", "Steph", "JJ")
VA
MEM_TYPE("Beryl", "Fred")
SQL>
SQL> INSERT INTO club VALUES("MD",null, null,null,
2 CAST(MULTISET(SELECT * FROM newnames) as mem_type))
3 /
1 row created.
SQL>
SQL> select * from club;
NAME ADDRESS CITY PHONE
---------- -------------------------------------------------- -------------------- --------
MEMBERS
-------------------------------------------------------------------------------------------
AL 111 First St. Mobile 222-2222
MEM_TYPE("Brenda", "Richard")
FL 222 Second St. Orlando 333-3333
MEM_TYPE("Gen", "John", "Steph", "JJ")
VA
MEM_TYPE("Beryl", "Fred")
MD
MEM_TYPE("Beryl", "Fred")
SQL>
SQL>
SQL> drop table newnames;
Table dropped.
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
The COUNT Function
The COUNT function returns the number of members in a VARRAY.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE vartest IS
2 CURSOR fcur IS
3 SELECT city, members FROM club;
4 BEGIN
5 FOR j IN fcur LOOP
6 IF j.members.exists(1) THEN
7 dbms_output.put_line(j.City||" has "|| j.members.count||" members");
8
9 END IF;
10 END LOOP; /* end for j in fcur loop */
11 END vartest;
12 /
Procedure created.
SQL> exec vartest;
Mobile has 2 members
Orlando has 4 members
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
Then, the individual array element can be extracted with a WHERE filter:
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT n, val
2 FROM
3 (SELECT rownum n, COLUMN_VALUE val FROM
4 THE(SELECT c.members FROM club c
5 WHERE c.name = "FL") x
6 WHERE COLUMN_VALUE IS NOT NULL)
7 WHERE n=3;
N VAL
---------- ---------------
3 Steph
SQL>
SQL>
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
SQL>
The subscript of the last element is always equal to the size of the array.
SQL>
SQL> declare
2 type month_va is varray(13) of VARCHAR2(20);
3 v_month_va month_va;
4 v_count_nr number;
5 begin
6 v_month_va:=month_va("A","B","C","D","E","F","G");
7 DBMS_OUTPUT.put_line("Length:"||v_month_va.count);
8
9 for i in v_month_va.first..v_month_va.last
10 loop
11 DBMS_OUTPUT.put_line("v_month_va(i): "||v_month_va(i));
12 end loop;
13 end;
14 /
Length:7
v_month_va(i): A
v_month_va(i): B
v_month_va(i): C
v_month_va(i): D
v_month_va(i): E
v_month_va(i): F
v_month_va(i): G
PL/SQL procedure successfully completed.
Using PL/SQL to Create Functions to Access Elements
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
Type created.
SQL>
SQL>
SQL> CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
6 /
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO club VALUES ("AL","111 First St.","Mobile",
2 "222-2222", mem_type("Brenda","Richard"));
1 row created.
SQL>
SQL> INSERT INTO club VALUES ("FL","222 Second St.","Orlando",
2 "333-3333", mem_type("Gen","John","Steph","JJ"));
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION vs (vlist club.members%type, sub integer)
2 RETURN VARCHAR2
3 IS
4 BEGIN
5 IF sub <= vlist.last THEN
6 RETURN vlist(sub);
7 END IF;
8 RETURN NULL;
9 END vs;
10 /
Function created.
SQL>
SQL> SELECT vs(members,2)
2 FROM club
3 /
VS(MEMBERS,2)
-----------------
Richard
John
SQL> SELECT DECODE(vs(members,3),null,"No members",vs(members,3))
2 FROM club
3 WHERE name IN ("FL", "MD")
4 /
DECODE(VS(MEMBERS,3),NULL,"NOMEMBERS",VS(MEMBERS,3))
----------------------------------------------------
Steph
SQL>
SQL>
SQL> drop table club;
Table dropped.
SQL> drop type mem_type;
Type dropped.
SQL>
Using VARRAYs
In the Oracle environment, array subscripts start from 1, and not from 0 (as in C and Java).
VARRAYs are of fixed length.
You specify the length of the array when you define it.
Arrays of elements of the same type use sequential numbers as a subscript.
VARRAYS can be used both in PL/SQL and SQL.
You should use VARRAYs when you know the size of your data set and that size is very stable.
declare
type VarrayType is varray(size) of ElementType;
...
create or replace type VarrayType is varray(size) of ElementType;
VARRAY in action
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> declare
2 type month_va is varray(13) of VARCHAR2(20);
3 v_month_va month_va;
4 v_count_nr number;
5 begin
6 v_month_va:=month_va("January","February","March","April","May","June","July","August","September","October","November","December");
7 DBMS_OUTPUT.put_line("Length:"||v_month_va.count);
8
9 v_month_va.extend;
10 v_month_va(v_month_va.last):="Null";
11 DBMS_OUTPUT.put_line("Length:"||v_month_va.count);
12
13 for i in v_month_va.first..v_month_va.last
14 loop
15 select count(*) into v_count_nr from employee
16 where nvl(replace(to_char(start_date,"Month")," "), "Null")=v_month_va(i);
17
18 DBMS_OUTPUT.put_line(v_month_va(i)||": "||v_count_nr);
19 end loop;
20 end;
21 /
Length:12
Length:13
January: 1
February: 0
March: 1
April: 0
May: 0
June: 0
July: 2
August: 0
September: 1
October: 1
November: 0
December: 2
Null: 0
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
VARRAY of Cursor
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 CURSOR all_emps IS
3 SELECT *
4 FROM employee
5 ORDER BY first_name;
6
7 TYPE emp_array IS VARRAY(100) OF employee%ROWTYPE;
8
9 emps emp_array;
10 inx1 PLS_INTEGER;
11 inx2 PLS_INTEGER;
12 BEGIN
13 inx1 := 0;
14
15 emps := emp_array ();
16
17 FOR emp IN all_emps LOOP
18 inx1 := inx1 + 1;
19 emps.extend();
20 emps(inx1).id := emp.id;
21 emps(inx1).first_name := emp.first_name;
22 emps(inx1).salary := emp.salary;
23 END LOOP;
24
25 FOR inx2 IN 1..emps.count LOOP
26 DBMS_OUTPUT.PUT_LINE (emps(inx2).id ||" " || emps(inx2).first_name);
27 END LOOP;
28 END;
29 /
02 Alison
04 Celia
07 David
03 James
08 James
01 Jason
06 Linda
05 Robert
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>