Adsense Ad

Tuesday 9 January 2018

Oracle PL/SQL associative array examples

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 7thversion 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 FORloop 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 boundvariable. 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
By at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)