 |
|
Oracle Tips by Burleson |
The PL/SQL VARRAY Collection
The VARRAY
is one of the easier collections
to understand and use, so that is where we will start. The VARRAY or
variable array is a set of objects very much like a VARCHAR2 character
string. A VARCHAR2 is defined with a maximum limit on the number of
characters it can hold. A VARRAY is also defined with a maximum
limit but instead of just holding characters, a VARRAY can hold any
defined datatype. A VARRAY is a dense collection, meaning that you
can only add or remove objects from the end. You define a VARRAY
first as a type, and then define a variable that is the actual
collection.
declare
type auth_var is varray(1000) of author%rowtype;
type authname_var is varray(300) of
author.auth_last_name%type;
a_auth
auth_var;
a_aname authname_var;
begin …
If the VARRAYs and/or nested tables are stored as columns in
the database, you must make the declaration persistent by declaring
the type as a database object, shown in the next example.
create or
replace type book_title_var as
varray(20) of book.book_key%type;
Once the VARRAY is defined, elements
can easily be inserted into the VARRAY.
SQL> declare
2 type auth_var is varray(10) of
3 author.author_last_name%type;
4 a_auth auth_var := auth_var();
5 begin
6 a_auth.extend;
7 a_auth(1) := 'Smith';
8 a_auth.extend;
9 a_auth(2) := 'Jones';
10 dbms_output.put_line(
a_auth(1) ||' and '|| a_auth(2));
11 end; /
Smith and
Jones
Lines 2 and 3 define the VARRAY type with
a maximum of 10 elements. Line 4 defines the variable a_auth
of auth_var type and it also initializes the array. A VARRAY
can not be used until it is initialized. In line 4 the auth_var()
function actually does the initialization.
Once the array is initialized you can extend it
and add elements, which we do in lines 6 through 9. We access each
element using the VARRAY
variable and the index number.
When the PL/SQL block ends (or the array variable goes out of scope),
the memory used by the array is recovered automatically by the PL/SQL
engine. Unlike a cursor, you do not close a collection.
A cursor can also be used to load the VARRAY. In this
case you need to ensure that you do not exceed the array size.
SQL> declare
2 type auth_var is varray(20) of
3 author.author_last_name%type;
4 a_auth auth_var := auth_var();
5 begin
6 for r_c1 in
(select author_last_name from author) loop
7 a_auth(a_auth.last+1) := r_c1.author_last_name;
8 end loop;
9 end;
10 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index
table key value
ORA-06512: at line 7
Oops, it appears that we forgot that
a_auth.last does not exist until we have at least one element.
If the cursor is rewritten to first extend the array before loading
the value we will not have this problem. In the example shown below,
we load the array after checking for the array boundary.
SQL> declare
2 type auth_var is varray(20) of
3 author.author_last_name%type;
4 a_auth auth_var := auth_var();
5 indx number := 1;
6 begin
7 for r_c1 in
(select author_last_name from author) loop
8 if indx < a_auth.limit then
9 a_auth.extend;
10 a_auth(a_auth.last) := r_c1.author_last_name;
11 dbms_output.put_line(a_auth(indx));
12 indx := indx +1;
13 end if;
14 end loop;
15 end;
16 /
jones
hester
weaton
jeckle
withers
petty
clark
mee
shagger
smith
So what makes this collection so great? The next
example is rather simple but it demonstrates the ability to perform
multiple tasks on the collection. The listing is a bit long and is in
the code depot under VARRAY
.
First let’s create an “included function” that
returns the array. The function creates an array of sales
table row types that match the passed book_key and returns the
VARRAY. The included function is contained in lines 6 through
23.
SQL> declare
2 type sales_array is varray(1000) of sales%rowtype;
3 a_sal sales_array;
4 n_avg number := 0;
5
6 function get_array (b_key in sales.book_key%type)
7 return sales_array
8 as
9 a_sales sales_array := sales_array();
10 indx number := 1;
11 cursor c1(v_key varchar2) is
12 select * from sales where book_key = v_key
13 order by order_number;
14 begin
15 for r_c1 in c1(b_key) loop
16 if indx < a_sales.limit then
17 a_sales.extend;
18 a_sales(a_sales.last) := r_c1;
19 indx := indx +1;
20 end if;
21 end loop;
22 return a_sales;
23 end; -- function
Please note that line 2 and 3 define the VARRAY and the
array variable, and notice that line 3 does not initialize the array.
Instead, the function creates an array called s_sales and
initializes it on line 9. The function used the same technique to
load the array as the previous example. The VARRAY is returned to the
calling procedure in line 22. Now we can simply call the function to
get a fully loaded array, as shown below:
25 begin
26 for r_c2 in (select distinct book_key from sales
27 order by book_key) loop
28 a_sal := get_array(r_c2.book_key);
29 --number of orders
30 dbms_output.put_line(
31 'Orders for Key '||r_c2.book_key||
': '||a_sal.count);
32 --total sales
33 for indx in a_sal.first..a_sal.last loop
34 n_avg := n_avg + a_sal(indx).quantity;
35 end loop;
36 n_avg := n_avg/a_sal.count;
37 dbms_output.put_line(
38 'Average Sales are
'||to_char(n_avg,99999.00));
39 -- the third order for that book
40 dbms_output.put_line(
41 'The third order is
'||a_sal(3).order_number);
42 -- the prior and next order
43 dbms_output.put_line(
44 'The second order is
'||a_sal(a_sal.prior(3)).order_number);
45 dbms_output.put_line(
46 'The fourth order is
'||a_sal(a_sal.next(3)).order_number);
47 end loop;
48 end;
49 /
Line 28 calls the function which first creates and
then passes back the loaded VARRAY
for each book_key in the cursor
loop. Next a number of operations are performed on elements in the
VARRAY.
Lines 33 through 35 of the above listing are very
important because they demonstrate using a FOR loop along with the
FIRST and LAST methods to efficiently move through the collection, one
element at a time. What makes this very efficient is that the FOR
loop will take care of checking that each element is valid.
If the loop finds a deleted element (which is not
possible in a VARRAY) it will skip it and move to the next valid element. The
example above performs four operations on each VARRAY. Compared to
using a cursor to retrieve the data for each operation from the
database, the VARRAY is much more efficient. A partial example of the
output is provided below.
Orders for
Key B101: 5
Average Sales are 3640
The third order is O168
The second order is O129
The fourth order is O196
Orders for Key B102: 11
Average Sales are 1812
The third order is O104
The second order is O103
The fourth order is O105
Orders for Key B103: 9
Remember, a VARRAY is a
collection of elements (all of which are the same data type).
Elements can be added to the end of the VARRAY as long as the array
limit set in the definition is not exceeded. You can not delete
elements except at the end of a VARRAY.
SQL> declare
2 type auth_var is varray(20) of
3 author.author_last_name%type;
4 a_auth auth_var := auth_var();
5 indx number := 1;
6 begin
7 for r_c1 in
(select author_last_name from author) loop
8 if indx < a_auth.limit then
9 a_auth.extend;
10 a_auth(a_auth.last) := r_c1.author_last_name;
11 indx := indx +1;
12 end if;
13 end loop;
14 dbms_output.put_line(a_auth.count);
15 a_auth.trim(5);
16 dbms_output.put_line(a_auth.count);
17 a_auth.delete;
18 dbms_output.put_line(a_auth.count);
19 end; /
10
5
0
Notice from the output that line 15 removed the
last 5 elements while line 17 removed all the elements. If we attempt
to delete the fifth element we get an error because you can only add
or delete from the end of the VARRAY.
14
dbms_output.put_line(a_auth.count);
15 a_auth.delete(5);
16 dbms_output.put_line(a_auth.count);
17 end;
18 /
a_auth.delete(5);
*
ERROR at line 15:
ORA-06550: line 15, column 3:
PLS-00306: wrong number or types of arguments in call to 'DELETE'
ORA-06550: line 15, column 3:
PL/SQL: Statement ignored
A nested table is similar to a
VARRAY except that it is a sparse collection, meaning that it
can have deleted elements contained in the collection.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
