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
<source lang="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 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></source>
Assign value to VARRAY
<source lang="sql">
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></source>
Column_value is a built-in function/pseudo-variable that is held over from the DBMS_SQL package
<source lang="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> 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></source>
CREATE TABLE with a VARRAY
<source lang="sql">
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></source>
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:
<source lang="sql">
CREATE OR REPLACE TYPE name-of-type IS VARRAY(nn)of type</source>
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.
<source lang="sql">
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></source>
Defining our type to be a VARRAY with 10 elements, where each element is a varying character string of up to 15 characters.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15)
2 /
SQL> SQL> drop type mem_type;</source>
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.
<source lang="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> 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.</source>
Extracting individual members of a VARRAY may be accomplished using two other functions: THE and VALUE
<source lang="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 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></source>
Getting Information on Varrays
You can use the DESCRIBE command to get information on your varray types.
<source lang="sql">
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></source>
If aliases are used, they must be used consistently
<source lang="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 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></source>
Inside the loop, you are accessing array elements by their subscripts.
<source lang="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("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.</source>
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.
<source lang="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></source>
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.
<source lang="sql">
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></source>
Loop through by using the built-in NEXT, PRIOR.
If there are no more elements, the built-in method will return NULL.
<source lang="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("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.</source>
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:
<source lang="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 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></source>
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
<source lang="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 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></source>
One way to make the "members" behave like an array is first to include the row number in the result set like this:
<source lang="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> 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></source>
Query table with VARRAY type column by column name
<source lang="sql">
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></source>
Query VARRAY column
<source lang="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 *
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></source>
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:
<source lang="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> 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.</source>
The COUNT Function
The COUNT function returns the number of members in a VARRAY.
<source lang="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 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></source>
Then, the individual array element can be extracted with a WHERE filter:
<source lang="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> 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></source>
The subscript of the last element is always equal to the size of the array.
<source lang="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("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.</source>
Using PL/SQL to Create Functions to Access Elements
<source lang="sql">
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></source>
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.
<source lang="sql">
declare
type VarrayType is varray(size) of ElementType;
... create or replace type VarrayType is varray(size) of ElementType;</source>
VARRAY in action
<source lang="sql">
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.</source>
VARRAY of Cursor
<source lang="sql">
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></source>
