 |
|
EnterpriseDB: SQL Collections
Oracle Tips by
Burleson
|
I
covered SQL collections in the last chapter in the section about
Arrays. Oracle PL/SQL provides three types of collections:
Associative Table (Indexed by Binary_Integer or Varchar2), Nested
Table and Varray. While SPL does not provide these three data types,
it does provide an Index By Binary_Integer table.
SPL also
offers the Array[] type, i.e. Integer[] or text[], which can mimic
both nested tables and Varrays. The downside to using the native
Array type is that you break compatibility with Oracle. As
EnterpriseDB works to make SPL increasingly compatible, the ability to
use non-standard functionality may go away. I will explain how to use
the Oracle compatible methods as well as native and you can decide
when and where they are appropriate.
The
syntax for creating an SPL table is:
TYPE <type name> IS TABLE OF <scalar data type OR
record type OR
%TYPE OR
%ROWTYPE>
INDEX BY BINARY_INTEGER;
I
explained anchored variables and records above. A scalar data type is
a stand-alone type like Date, Varchar2, etc. You cannot use a
composite type in an index by table unless it follows the syntax for a
RECORD as described below.
DECLARE
TYPE t_tab IS
TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER; -- Step 1
a_tab t_tab; -- Step 2
n_tab text[]; -- Step 3
v_tab text[5]; -- Step 4
BEGIN
a_tab(1) := 'Lewis'; -- Step 5a
a_tab(2) := 'Lewis'; -- Step 5b
n_tab := Array['Louis', 'Louis']; -- Step 6
v_tab := Array['Luis', 'Luis']; -- Step 7
DBMS_OUTPUT.PUT_LINE( -- Step 8
'A_TAB1 = ' || a_tab(1) ||
', A_TAB2 = ' || a_tab(2) ||
', N_TAB1 = ' || n_tab[1] ||
', N_TAB2 = ' || n_tab[2] ||
', V_TAB1 = ' || v_tab[1] ||
', V_TAB2 = ' || v_tab[2] );
END;
From the
point on, I will no longer show you the output of both the executable
code and the output. To save space, I will display the output only.
The above code produces:
INFO: A_TAB1 = Lewis, A_TAB2 =
Lewis, N_TAB1 = Louis, N_TAB2 = Louis, V_TAB1 = Luis, V_TAB2 = Luis
SPL
Procedure successfully complete
Now I
will walk through the code step by step.
In Step
1, I created the Oracle style, INDEX BY, or associative table. This
creates the TABLE data type, not the variable.
Step 2
is declaring a variable using the data type of the table I just
created. When using the TYPE keyword, I am actually creating a type,
not a variable. Step 2 creates the variables of the new type.
Step 3
is the declaration of an EnterpriseDB Advanced Server native array
variable. The syntax is exactly like that of the array types we
covered in Chapter 2. This type of array, without a limit on the
size, is most like an Oracle Nested table. Like a nested table, this
type of array can be stored in the database.
Step 4
is also the declaration of a native array variable. This one is more
like an Oracle Varray. This type of collection has a size limit, in
this case 5.
In Steps
5a and 5b, I assign the value of "Lewis" to the first and second row
of the a_tab array. I refer to the value by use of a subscript. The
subscript is the position of the element in the array. A subscript is
like a row number in a database table.
In Step
6, I assign the value of "Louis" to the first and second row in the
n_tab array.
In Step
7, I assign the value of "Luis" to the first and second row in the
v_tab array.
Growing
native arrays (n_tab and v_tab) is a bit more complicated than growing
an index by table. It is easy to size them on assignment (as I did
above in steps 6 and 7). I will show you how you can use the native
arrays, and grow them dynamically, in the section on coding for
compatibility.
In Step
8, I am using DBMS_OUTPUT to display the values of each table. In
this instance, I am displaying each of the array names followed by the
data we just assigned to the arrays.
If you
are familiar with almost any other programming language, the table
type is simply an array. The case shown above is a single dimension
array.
You can
also create multi-dimensional arrays. You can do that by using a
RECORD type as the data type for your SPL table:
DECLARE
TYPE r_user_record IS RECORD (
emp_name emp.ename%TYPE,
emp_dept emp.deptno%TYPE,
emp_length_in_service INTERVAL ); -- Step 1
TYPE t_tab IS TABLE OF r_user_record
INDEX BY BINARY_INTEGER; -- Step 2
a_tab t_tab; -- Step 3
BEGIN
a_tab(1).emp_name := 'Lewis'; -- Step 4
a_tab(2).emp_name := 'George'; -- Step 5
DBMS_OUTPUT.PUT_LINE( -- Step 6
'A_TAB1 = ' || a_tab(1).emp_name ||
', A_TAB2 = ' || a_tab(2).emp_name );
END;
INFO: A_TAB1 = Lewis, A_TAB2 = Lewis
When you
use a table of records, you combine the syntax of tables and records.
You put your subscript for the table on your table variable, a_tab(1),
and you append the record column you would like to access,
a_tab(1).emp_name. So the syntax to access an element in a table of
records is:
<table name(<array row subscript>)>.<record column
name>
In the
example above, I declare the record type in step 1, declare the table
type using that record type in step 2 and declare the variable I will
use in my code in step 3. In steps 4 and 5, I assign names to rows
one and two (using subscripts) to my table(subscript).record_variable,
in this case, emp_name.
In SPL,
we can get sneaky and create an even more complex multi-dimensional
array. I want to point out that this syntax is not compatible with
Oracle (although with minor changes you can make it work).
You can
declare an array as part of your record type by anchoring to an array
column in a table. You would access the values of that array just as
you would normally.
DECLARE
TYPE r_user_record IS RECORD (
emp_name emp.ename%TYPE,
emp_dept emp.deptno%TYPE,
emp_length_in_service INTERVAL,
e_text array_tab.names%TYPE ); -- Step 1
TYPE t_tab IS TABLE OF r_user_record
INDEX BY BINARY_INTEGER; -- Step 2
a_tab
t_tab; -- Step 3
BEGIN
a_tab(1).emp_name := 'Lewis'; -- Step 4
a_tab(2).emp_name := 'George'; -- Step 5
a_tab(1).e_text := ARRAY['First Row', 'Second Row',
'Etc.']; -- Step 6
DBMS_OUTPUT.PUT_LINE( -- Step 7
'A_TAB1 = ' || a_tab(1).emp_name ||
', A_TAB1.e_text[1]= ' || a_tab(1).e_text[1] );
END;
INFO: A_TAB1 = Lewis, A_TAB1.e_text[1]= First Row
That
is a pretty neat side effect of combining SPL with native data types
but remember that this syntax is not compatible with Oracle or
PL/SQL. If compatibility is important to you, you may want to be
careful with this syntax. In the chapter on maintaining compatibility
between Oracle and EnterpriseDB Advanced Server, I cover compatibility
issues in greater detail.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
