1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Associative Arrays in PL/SQL

Discussion in 'SQL PL/SQL' started by SBH, Dec 23, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    A collection is a group of elements arranged in a defined order, where each one of them can be accessed through its index. The most common forms of collections are arrays, maps or lists. In current version of programming languages, almost all of them support the use of collections.

    2. Introduction

    Oracle supports Collections in three forms, namely, Associative Arrays, Nested Tables and Varrays. The article explains the use of Associative Arrays in PL/SQL.
    [​IMG]

    Associative arrays alias Index By tables are non persistent form of collections in PL/SQL, where their life is a single database session. Note than non persistent objects are not physically stored in the database, therefore they can only be used within PL/SQL. It can be defined as a one dimensional array variable fragmented in the “Key-Value” format, which can hold more than one element (value) each acquiring a memory location. The key or element index can belong to number family or can be a string also in Oracle versions 9iR2 and above. For numeric index keys, the range is -2147483647 to 2147483647.

    Data in the elements does not have to be stored in consecutive rows. It can exist in sparse.

    3. Syntax

    Code (Text):
    TYPE  type_name  IS  TABLE  OF  element_type  [NOT  NULL] INDEX  BY  [BINARY_INTEGER  |  PLS_INTEGER  |  VARCHAR2(size_limit)];
    Prior to Oracle 9iR2, only BINARY_INTEGER was the admissible index type. In later versions of Oracle, index key can be of BINARY_INTEGER, PLS_INTEGER, VARCHAR2 or their subtype’s type.

    Restrictions: RAW, LONG RAW, ROWID, and CHAR are not allowed as key types for an associative array.

    Example 1: Index-by tables (Associative arrays)

    Associative arrays can be initialized by assigning values corresponding to an index. In the below PL/SQL block, elements corresponding to cell index 1,20,12of associative array DTAB are initialized with numeric values.

    Code (SQL):
    DECLARE
     TYPE CLUB_TAB IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
     DTAB CLUB_TAB;
     RESULT NUMBER;
    BEGIN
     DTAB(1) := 5 ;
     DTAB(20) := 10 ;
     DTAB(12) := 15 ;
     RESULT := DTAB(1) + DTAB(20) + DTAB(12);
     DBMS_OUTPUT.PUT_LINE(RESULT);
    END;
    /

    30

    PL/SQL PROCEDURE successfully completed.
    Example 2: The PL/SQL block below uses string as the index and assigns their quarter sales as element value.

    Code (SQL):
    DECLARE
     TYPE CLUB_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
     DTAB CLUB_TAB;
     L_COMP VARCHAR2(100) := 'TCS';
    BEGIN
     DTAB('INFOSYS') := 5000;
     DTAB('TCS') := 84020 ;
     DTAB('CTS') := 18300;
     DTAB('ORACLE') := 90010;
     DTAB('SUN') := 43000 ;
     DTAB('ICICI') := 2000;
     DBMS_OUTPUT.PUT_LINE(DTAB(L_COMP));
    END;
    /

    84020

    PL/SQL PROCEDURE successfully completed.

    4. Associative Array Methods

    Below table shows the methods which can be used with the associative arrays to access index and values.

    [TABLE]Method Description
    EXISTS(n) Returns TRUE if the nth element in a PL/SQL table exists
    COUNT Returns the number of elements that a PL/SQL table currently contains
    FIRST | LAST Returns the first and last (smallest and largest) index number in a PL/SQL table. Returns NULL , if it is empty
    PRIOR (n) Returns the index number that precedes index in a PL/SQL table
    NEXT (n) Returns the index number that succeeds index in a PL/SQL table
    DELETE DELETE removes all elements from the table. DELETE (n) removes n elements from the table. DELETE (m,n) removes all elements in range from m..n from the table[/TABLE]

    Syntax for using a collection method:

    Code (Text):
    collection_name.method_name[(parameters)]
    Restrictions

    1. Collection methods are not callable from SQL statements.
    2. No method can be used with a NULL collection except EXISTS. Oracle raises COLLECTION_IS_NULL exception if any method is used with NULL collection

    4.1. Examples

    The PL/SQL block below shows the use of FIRST and LAST collection methods.

    Code (SQL):
    DECLARE
       TYPE CLUB_NEST IS TABLE OF NUMBER;
         TYPE CLUB_TAB IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(100);
       DTAB1 CLUB_NEST := CLUB_NEST(1986,2010);
       DTAB2 CLUB_TAB;
     BEGIN
       DTAB2('Club') := 5000;
       DTAB2('Oracle') := 1000;
       DBMS_OUTPUT.PUT_LINE( 'Index for First element in CLUB_TAB: '||DTAB2.FIRST)  ;
       DBMS_OUTPUT.PUT_LINE( 'Index for Last element in CLUB_TAB: '||DTAB2.LAST)  ;
       FOR I IN DTAB1.FIRST..DTAB1.LAST  LOOP
         DBMS_OUTPUT.PUT_LINE('Index for element '||to_char(i)||' is:'|| to_char(DTAB1(I)) )  ;
       END LOOP;
     END;
    /

    INDEX FOR FIRST element IN CLUB_TAB: Club
    INDEX FOR LAST element IN CLUB_TAB: Oracle
    INDEX FOR element 1 IS:1986
    INDEX FOR element 2 IS:2010

    PL/SQL PROCEDURE successfully completed.
     

    Attached Files: