Question: What are some working examples
of a associative array in PL/SQL?
Answer: associative arrays in PL/SQL are far
faster than explicit PL/SQL cursors. Here are some working examples
of
PL/SQL associative array constructs:
PL/SQL associative array example, non Oracle::
accept cc prompt 'Enter country code: '
DECLARE
TYPE country_tab IS TABLE OF VARCHAR2(50)
INDEX BY
VARCHAR2(5);
t_country country_tab;
BEGIN
--
Populate lookup
t_country('UK') := 'United Kingdom';
t_country('US')
:= 'United States of America';
t_country('FR') := 'France';
t_country('DE') := 'Germany';
-- Find country name for ISO
code "&cc"
DBMS_OUTPUT.PUT_LINE('ISO code "&cc" = ' || t_country(upper('&cc')));
END;
/
PL/SQL Associative array example (index by
binary integer):
SET SERVEROUTPUT ON
set verify
off
accept cc prompt 'Enter the country code: '
DECLARE
TYPE
country_type
IS RECORD (
iso_code
VARCHAR2(5),
name VARCHAR2(50)
);
TYPE
country_tab
IS TABLE OF
country_type
INDEX BY BINARY_INTEGER;
t_country country_tab;
BEGIN
-- Populate lookup
t_country(1).iso_code := 'UK';
t_country(1).name := 'United
Kingdom';
t_country(2).iso_code := 'US';
t_country(2).name :=
'United States of America';
t_country(3).iso_code := 'FR';
t_country(3).name := 'France';
t_country(4).iso_code := 'DE';
t_country(4).name := 'Germany';
-- Find country name for ISO
code "DE"
<< lookup >>
FOR i IN 1 .. 4 LOOP
IF t_country(i).iso_code
= upper('&cc') THEN
DBMS_OUTPUT.PUT_LINE('ISO code "&cc" = ' ||
t_country(i).name);
EXIT lookup;
END IF;
END LOOP;
END;
/
====================================================
The Associative arrays were the first ever
collection type to be created in Oracle in its 7th
version by the name, PL/SQL tables. Ironically, they have never been
behaving anything like a traditional heap table back then. From the
Oracle version 8, they were given a new name as Index-by tables,
meaning that these are tables with index values. In this version,
Oracle also introduced two new types, Nested
table, and VARRAYs, grouping
them all under a single section called as the Collections. With the
release 9iR2, Oracle changed the name of the
index by tables into associative arrays, as they were more like an
array in structure and also allowed them to be indexed by either
PLS_INTEGER, BINARY_INTEGER or VARCHAR2 data types. By allowing us
to index by string, the associative arrays are far more flexible
than the other two types with more real-time use in our
applications.
These are sparsely populated, meaning that
the index value does not have to be sequential, but needs to be
unique. This type supports any integer to be its index value i.e.,
positive, negative, zero and even accepts mathematical expressions.
As already discussed, the associative arrays are not capable of
acting as data types for the table columns in the
database, unlike the nested
tables or VARRAYS. Thus, these can only be transient in nature.
Since 9i, there were not much of a change in
the behavior of the associative arrays until 12c. The SQL support
for the associative arrays is
made available from the release R12.1 and higher by allowing the
associative arrays to be accessed through the TABLE function and as
bind variables during dynamic processing.
The prototype for defining an associative
array is shown below,
TYPE <Type_name>
IS
TABLE OF <Scalar_datatype | Composite_datatype> [NOT
NULL] INDEX BY
<PLS_INTEGER | BINARY_INTEGER | VARCHAR2(<Precision_value>)
|
STRING (<Precision_value>) | LONG>;
Precision_value: Between the range of 1 to 32,767.
The associative arrays do not require an
initialization before element insertion as they do not own a
constructor. Thus, there is no need for space allocation prior to
assigning the values using the collection API
extend.
In the below listing, an associative array is
declared with VARCHAR2(50) as its element’s data type and
PLS_INTEGER as its index data type. The execution section of the
block assigns the elements to some random index values and it is
looped for the final display.
DECLARE
type type_aa
IS
TABLE OF VARCHAR2(50) INDEX BY pls_integer;
l_aa_var1 type_aa;
BEGIN
l_aa_var1(-5):='APPEND';
l_aa_var1(-3):='BIND';
l_aa_var1(0) :='CONSTRUCT';
l_aa_var1(3) :='DYNAMIC';
l_aa_var1(5) :='EXTEND';
FOR loop_aa IN l_aa_var1.first..l_aa_var1.last
LOOP
dbms_output.put_line(l_aa_var1(loop_aa));
END LOOP loop_aa;
END;
/
Error
report –
ORA-01403: no data found
ORA-06512: at line 14
01403. 00000 -
"no data found"
*Cause:
No data was found from the objects.
The FOR loop’s index range is defined between
L_AA_VAR1.FIRST, which is the least index value of the elements list
[-5] and L_AA_VAR1.LAST, which is the greatest index value of the
elements list [5]. Thus, the loop runs itself for the index values
[-5], [-4], [-3], [-2], [-1], [0], [1], [2], [3], [4], [5]. As we
have not assigned any elements for the indexes [-4], [-2], [-1],
[1], [2], [4], the above block fails with the NO_DATA_FOUND error
right at the index [-4] it.
To avoid this scenario, the collection API
method exists is used in
the FOR loop to avoid the exception and proceeds the elements for
the next loop’s index value as shown below,
DECLARE
type type_aa
IS
TABLE OF VARCHAR2(50) INDEX BY pls_integer;
l_aa_var1 type_aa;
BEGIN
l_aa_var1(-5):='APPEND';
l_aa_var1(-3):='BIND';
l_aa_var1(0) :='CONSTRUCT';
l_aa_var1(3) :='DYNAMIC';
l_aa_var1(5) :='EXTEND';
FOR loop_aa IN l_aa_var1.first..l_aa_var1.last
LOOP
IF l_aa_var1.exists(loop_aa) THEN
dbms_output.put_line('Index value: '||loop_aa||'. Element
value: '||
l_aa_var1(loop_aa));
END IF;
END LOOP loop_aa;
END;
/
Result:
Index value: -5. Element value: APPEND
Index value: -3. Element value: BIND
Index value: 0. Element value: CONSTRUCT
Index value: 3. Element value: DYNAMIC
Index value: 5. Element value: EXTEND
In the below snippet, the index value is an
arithmetic calculation, where its element can be accessed from a
different arithmetic calculation providing that their resultant is
equal. The different ways of fetching the element for the index [30]
is shown in the below example.
DECLARE
type type_aa
IS
TABLE OF VARCHAR2(50) INDEX BY pls_integer;
l_aa_var1 type_aa;
BEGIN
l_aa_var1(10+20):='ASSOCIATIVE ARRAY';
dbms_output.put_line('The element value:
'||l_aa_var1(10+20));
dbms_output.put_line('The element value:
'||l_aa_var1(20+10));
dbms_output.put_line('The element value:
'||l_aa_var1(30));
dbms_output.put_line('The element value:
'||l_aa_var1(50-20));
dbms_output.put_line('The element value:
'||l_aa_var1(60/2));
END;
/
Result:
The element value: ASSOCIATIVE ARRAY
The element value: ASSOCIATIVE ARRAY
The element value: ASSOCIATIVE ARRAY
The element value: ASSOCIATIVE ARRAY
The element value: ASSOCIATIVE ARRAY
In the below script, the associative array’s
element data type is declared as PLS_INTEGER and its index data type
is defined as VARCHAR2(50). This array is used for saving the test
scores of individual students from the STUDENTS table with their
name as index value and their score value as the element. A cursor
FOR loop is constructed with the STUDENTS table to display the
scores of each student. The
exists method is used here to take out the students, whose
results are not available and handle them separately.
DECLARE
type type_aa
IS
TABLE OF pls_integer INDEX BY VARCHAR2(50);
l_aa_test_scores type_aa;
BEGIN
l_aa_test_scores('Adam')
:=59;
l_aa_test_scores('Samantha'):=70;
l_aa_test_scores('Patrick') :=45;
l_aa_test_scores('Manickam'):=75;
l_aa_test_scores('Johnson') :=90;
FOR loop_aa IN
(
SELECT
stud_name
FROM
students
)
LOOP
IF l_aa_test_scores.exists(loop_aa.stud_name) THEN
dbms_output.put_line('Student name: '||loop_aa.stud_name||',
Score: '||l_aa_test_scores(loop_aa.stud_name));
ELSE
dbms_output.put_line('Student name: '||loop_aa.stud_name||
', Score: Not Available');
END IF;
END LOOP loop_aa;
END;
/
Result:
Student name: Adam, Score: 59
Student name: Felicia, Score: Not Available
Student name: Gerald, Score: Not Available
Student name: Johnson, Score: 75
Student name: Manickam, Score: 75
Student name: Nathen, Score: Not Available
Student name: Patrick, Score: 45
Student name: Samantha, Score: 70
Associative
Array Enhancement in 12c
From the Oracle version 12c and above, the
option for using the associative array in the TABLE function and as
a bind variable in the dynamic query has been enabled. The below
sections shows the detailed explanation of their enhancements.
Associative Array with the TABLE
Function
Prior to 12c, Oracle allowed us to use the
TABLE function only for the nested tables and VARRAYs that are
created in the database. From 12c and above, all the collection
types can enjoy the use of TABLE function even if they are locally
declared with certain limitations.
An associative array must be declared in a
package separately and not in the same PL/SQL unit for it to use the
TABLE function. In the below example, the package PKG_AA is created
with an associative array having a record as its element’s data type
and PLS_INTEGER as its index’s data type. This package also sports a
function with the above said associative array as its return type.
This function returns the employee ID and the name of the first 10
employees as shown below.
CREATE OR REPLACE PACKAGE pkg_aa
IS
type type_rec
IS
record
(
Employee_id
NUMBER,
Employee_name VARCHAR2(30));
type type_aa
IS
TABLE OF type_rec INDEX BY pls_integer;
FUNCTION func_aa RETURN type_aa;
END;
/
CREATE OR REPLACE PACKAGE body pkg_aa
IS
FUNCTION func_aa
RETURN type_aa
IS
l_aa_var1 pkg_aa.type_aa;
BEGIN
FOR loop_aa IN
(
SELECT rownum rn, employee_id, last_name||', '||first_name
employee_name
FROM employees
FETCH FIRST 10 rows only
)
LOOP
l_aa_var1(loop_aa.rn).employee_id
:=loop_aa.employee_id;
l_aa_var1(loop_aa.rn).employee_name:=loop_aa.employee_name;
END LOOP loop_aa;
RETURN l_aa_var1;
END func_aa;
END pkg_aa;
/
The below anonymous block creates an instance
of the associative array from the package PKG_AA and it is assigned
to the function FUNC_AA’s return value, which is then mimicked to a
classic heap table using the TABLE function in
an FOR loop for it to display
the employee details as shown.
%
Note: The function
with its return type as an associative array cannot
be directly used in the TABLE function and it has to
be assigned to a local variable for it to use the
TABLE function as shown in the below example.
|
DECLARE
l_aa_var1 pkg_aa.type_aa;
BEGIN
l_aa_var1:=pkg_aa.func_aa;
FOR loop_aa IN
(
SELECT
*
FROM
TABLE(l_aa_var1)
)
LOOP
dbms_output.put_line('Employee ID: '||loop_aa.employee_id||
', Employee Name: '||loop_aa.employee_name);
END LOOP loop_aa;
END;
/
Result:
Employee ID: 100, Employee Name: King, Steven
Employee ID: 101, Employee Name: Williams,
Neena
Employee ID: 102, Employee Name: De Haan, Lex
Employee ID: 103, Employee Name: Hunold,
Alexander
Employee ID: 104, Employee Name: Ernst, Bruce
Employee ID: 105, Employee Name: Austin,
David
Employee ID: 106, Employee Name: Pataballa,
Valli
Employee ID: 107, Employee Name: Lorentz,
Diana
Employee ID: 108, Employee Name: Greenberg,
Nancy
Employee ID: 109, Employee Name: Faviet,
Daniel
Associative Array as Bind Variable
Prior to 12c, Oracle prohibited associative
arrays from acting as bind variables in the dynamic queries. From
12c and later releases, associative arrays can be used as bind
variables of IN and OUT types.
For this scenario, the package PKG_AA is
created with an associative array of type VARCHAR2(500) for the
elements and PLS_INTEGER for its indexes. This package also has a
procedure PROC_AA, which has the package declared the
associative array as its OUT
parameter. This procedure calculates the experience for a list of
employees and assigns it to the OUT parameter as shown below,
CREATE OR REPLACE PACKAGE pkg_aa
IS
type type_aa
IS
TABLE OF VARCHAR2(500) INDEX BY pls_integer;
PROCEDURE proc_aa(op_aa_var1 OUT type_aa);
END;
/
CREATE OR REPLACE PACKAGE body pkg_aa
IS
PROCEDURE proc_aa(op_aa_var1 OUT type_aa)
IS
BEGIN
FOR loop_aa IN
(
SELECT
rownum rn,
last_name
||', '
||first_name employee_name,
hire_date
FROM
employees
FETCH
FIRST 9 percent rows only
)
LOOP
op_aa_var1(loop_aa.rn):='Total experience of '||loop_aa.employee_name
||' is '||REPLACE(ltrim(numtoyminterval(ROUND(sysdate-loop_aa.hire_date)/
30,'Month'),'+0'),'-',' Years and ')||' Months';
END LOOP loop_aa;
END proc_aa;
END pkg_aa;
/
The below anonymous block depicts usage of
the associative array as an OUT bound
variable. The OUT parameter of the procedure
PKG_AA.PROC_AA is bounded
to the local variable L_AA_VAR1, which is then mimicked to a
traditional heap table using the TABLE function and printed using
the cursor FOR loop as shown below,
DECLARE
l_aa_var1 pkg_aa.type_aa;
BEGIN
EXECUTE immediate 'BEGIN pkg_aa.proc_aa(:l_aa_var1);
END;' USING OUT
l_aa_var1;
FOR loop_aa IN
(
SELECT
*
FROM
TABLE(l_aa_var1)
)
LOOP
dbms_output.put_line(loop_aa.column_value);
END LOOP loop_aa;
END;
/
Result:
Total experience of King, Steven is 13 Years
and 02 Months
Total experience of Williams, Neena is 10
Years and 11 Months
Total experience of De Haan, Lex is 15 Years
and 08 Months
Total experience of Hunold, Alexander is 10
Years and 07 Months
Total experience of Ernst, Bruce is 9 Years
and 03 Months
Total experience of Austin, David is 11 Years
and 02 Months
Total experience of
Pataballa, Valli is 10 Years
and 06 Months
Total experience of Lorentz, Diana is 9 Years
and 06 Months
Total experience of Greenberg, Nancy is 14
Years and 00 Months
Total experience of Faviet,
Daniel is 14 Years and 00 Months
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
