| 26. 2. 2. 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;
|
|
The size of a VARRAY must be a positive integer and cannot be null. |
You cannot create an array of REF CURSORs. |
|
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 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 DBMS_OUTPUT.put_line('v_month_va(i): '||v_month_va(i));
16 end loop;
17 end;
18 /
Length:7
Length:8
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
v_month_va(i): Null
PL/SQL procedure successfully completed.
|
|