PL/SQL Associative Array

  • Similar to hash tables in other programming languages. Dictionary in Python, like Key-Value pair.
  • Index-by tables, also known as associative arrays. Associative arrays are set of Key-Value pair.
  • Each key is unique & locate to use corresponding value in the array.
  • Key – String/PLS_Integer.  Value can be any PL/SQL datatype like Varchar2, Integer. (Check Oracle Documentation for restrictions if any )
  • It can be dense or sparse – i.e. no consecutive index order.
SET SERVEROUTPUT ON;
SET DEFINE OFF

DECLARE
        TYPE direction IS
        TABLE OF VARCHAR2(15) INDEX BY VARCHAR2(2);
    TYPE error_code IS
        TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;

    TYPE state_info IS RECORD (
        state_code  CHAR(2),
        state_name  VARCHAR2(20)
    );
    TYPE states IS
        TABLE OF state_info INDEX BY PLS_INTEGER;
    lv_direction_dict   direction;
    lv_error_code_dict  error_code;
    lv_states           states;
    ln_index            VARCHAR2(2);
BEGIN
/* TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(2) */
        lv_direction_dict('N') := 'North';
    lv_direction_dict('S') := 'South';
    lv_direction_dict('E') := 'East';
    lv_direction_dict('W') := 'West';
    lv_direction_dict('NE') := 'North-East';
    dbms_output.put_line(lv_direction_dict('N')); -- Accessing Single Value
  --dbms_output.put_line(lv_direction_dict('E')); -- Accessing Single Value
    
        ln_index := lv_direction_dict.first;

/*Iteration*/
    WHILE ln_index IS NOT NULL LOOP
        dbms_output.put_line(ln_index
                             || ':'
                             || lv_direction_dict(ln_index));
        ln_index := lv_direction_dict.next(ln_index);
    END LOOP;

    dbms_output.put_line('*************************************************'); 
    /* TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER */
    
        lv_error_code_dict(100) := 'Continue';
    lv_error_code_dict(200) := 'Ok';
    lv_error_code_dict(400) := 'Non-Authorized';
    lv_error_code_dict(203) := 'Bad Request';
    dbms_output.put_line('Error Code 200 Description :' || lv_error_code_dict(200)); -- Accessing Signle Value
        dbms_output.put_line('Error Code 400 Description :' ||
    lv_error_code_dict(400)); -- Accessing Signle Value
    
    /* TABLE OF Composite Datatype INDEX BY PLS_INTEGER */
        dbms_output.put_line('*************************************************');
    lv_states(1).state_code := 'JK';
    lv_states(1).state_name := 'Jammu & Kashmir';
    lv_states(2).state_code := 'HP';
    lv_states(2).state_name := 'Himachal Pradesh';
    lv_states(3).state_code := 'PB';
    lv_states(3).state_name := 'Punjab';
    lv_states(7).state_code := 'DL';
    lv_states(7).state_name := 'Delhi';
    lv_states(9).state_code := 'UP';
    lv_states(9).state_name := 'Uttar Pradesh';
    lv_states(29).state_code := 'KA';
    lv_states(29).state_name := 'Karnataka';
    lv_states(32).state_code := 'KL';
    lv_states(32).state_name := 'Kerala';

    FOR i IN 1..37 LOOP
        BEGIN
            dbms_output.put_line(lv_states(i).state_code
                                 || ':'
                                 || lv_states(i).state_name);

        EXCEPTION
            WHEN OTHERS THEN
                NULL;--key not available
            END;
    END LOOP;

END;
/

Output:

GitHub Link : https://github.com/shobhit-singh/PLSQL-Programs/blob/master/README.md

Referencehttps://docs.oracle.com/cd/A97630_01/appdev.920/a96624/05_colls.htm

Thanks!

Happy Learning! Your feedback would be appreciated!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s