Oracle PL/SQL - Associative Array Constants

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Associative Arrays

Introduction

Create a function to populate the associative array with its initial value and then invoke the function in the constant declaration.

Demo

SQL>
SQL> CREATE OR REPLACE PACKAGE My_Types AUTHID DEFINER IS
  2    TYPE My_Type IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  3    FUNCTION Init_My_Type RETURN My_Type;
  4  END My_Types;
  5  /-- from   www .j a  va 2 s . co  m

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY My_Types IS
  2    FUNCTION Init_My_Type RETURN My_Type IS
  3      Ret My_Type;
  4    BEGIN
  5      Ret(-10) := '-ten';
  6      Ret(0) := 'zero';
  7      Ret(1) := 'one';
  8      Ret(2) := 'two';
  9      Ret(3) := 'three';
 10      Ret(4) := 'four';
 11      Ret(9) := 'nine';
 12      RETURN Ret;
 13    END Init_My_Type;
 14  END My_Types;
 15  /

Package body created.

SQL>
SQL> DECLARE
  2    v CONSTANT My_Types.My_Type := My_Types.Init_My_Type();
  3  BEGIN
  4    DECLARE
  5      Idx PLS_INTEGER := v.FIRST();
  6    BEGIN
  7      WHILE Idx IS NOT NULL LOOP
  8        DBMS_OUTPUT.PUT_LINE(TO_CHAR(Idx, '999')||LPAD(v(Idx), 7));
  9        Idx := v.NEXT(Idx);
 10      END LOOP;
 11    END;
 12  END;
 13  /
-10   -ten
0   zero
1    one
2    two
3  three
4   four
9   nine

PL/SQL procedure successfully completed.

SQL>

Related Topic