Friday, October 2, 2015

Oracle PL/SQL Collections with Examples

Oracle Collections
Ø  Associative array(index-by table)
Ø  varray(variable-size arrray)
Ø  Nested table

Dense or sparse: A dense collection has no gap between elements-Every element in the list has a values and a  sparse collection has gap between  elements

Uninitialized Status: An empty collection exists but has no elements To add elements to an empty collection invoke the extend method.A null collection does not exists to change a null collection to an existing collection ,you must initialize it, either by making it  empty or assigning a non-null value to it

 

Associative Arrays:

An associative array (formerly called PL/SQL table or index-by table) is a set of key-value pairs. Each key is a unique index, used to locate the associated value with the syntax variable_name(index).
The data type of index can be either a string type or PLS_INTEGER. Indexes are stored in sort order
Ø  Like a database table, an associative array:
ü  is empty (but not null) until you populate it
ü  can hold an unspecified number of elements, which you can access without knowing their positions
Ø  Unlike a database table, an associative array:
ü  Does not need disk space or network operations
ü  Cannot be manipulated with DML statements

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;

Ex: defines a type of associative array indexed by string, declares a variable of that type, populates the variable with three elements, changes the value of one element, and prints the values (in sort order, not creation order). (FIRST and NEXT are collection methods
Associative array indexed by string::
DECLARE
   -- Associative array indexed by string:
   TYPE population IS TABLE OF NUMBER                -- Associative array type
      INDEX BY VARCHAR2 (64);                            --  indexed by string

   city_population   population;                --  Associative array variable
   i                 VARCHAR2 (64);                         -- Scalar variable
BEGIN
   -- Add elements (key-value pairs) to associative array:
   city_population ('Chennai') := 2000000;
   city_population ('Mubai') := 750000;
   city_population ('Ap') := 1000000;
   -- Change value associated with key 'Ap':
   city_population ('Ap') := 2001;
   -- Print associative array:
   i := city_population.FIRST;                   -- Get first element of array

   WHILE i IS NOT NULL
   LOOP
      DBMS_OUTPUT.PUT_LINE (
         'Population of ' || i || ' is ' || city_population (i));
      i := city_population.NEXT (i);              -- Get next element of array
   END LOOP;
END;

Example #2 Function Returns Associative Array Indexed by PLS_INTEGER:
DECLARE
   TYPE sum_multiples IS TABLE OF PLS_INTEGER
      INDEX BY PLS_INTEGER;

   n    PLS_INTEGER := 5;            -- number of multiples to sum for display
   sn   PLS_INTEGER := 10;                       -- number of multiples to sum
   m    PLS_INTEGER := 3;                                          -- multiple

   FUNCTION get_sum_multiples (multiple IN PLS_INTEGER, num IN PLS_INTEGER)
      RETURN sum_multiples
   IS
      s   sum_multiples;
   BEGIN
      FOR i IN 1 .. num
      LOOP
         s (i) := multiple * ( (i * (i + 1)) / 2);         -- sum of multiples
      END LOOP;

      RETURN s;
   END get_sum_multiples;
BEGIN
   DBMS_OUTPUT.PUT_LINE (
         'Sum of the first '
      || TO_CHAR (n)
      || ' multiples of '
      || TO_CHAR (m)
      || ' is '
      || TO_CHAR (get_sum_multiples (m, sn) (n)));
END;

/

Example#3   Declaring Associative Array Constant:

CREATE OR REPLACE PACKAGE My_Types
   AUTHID DEFINER
IS
   TYPE My_AA IS TABLE OF VARCHAR2 (20)
      INDEX BY PLS_INTEGER;

   FUNCTION Init_My_AA
      RETURN My_AA;
END My_Types;
/

CREATE OR REPLACE PACKAGE BODY My_Types
IS
   FUNCTION Init_My_AA
      RETURN My_AA
   IS
      Ret   My_AA;
   BEGIN
      Ret (-10) := '-ten';
      Ret (0) := 'zero';
      Ret (1) := 'one';
      Ret (2) := 'two';
      Ret (3) := 'three';
      Ret (4) := 'four';
      Ret (9) := 'nine';
      RETURN Ret;
   END Init_My_AA;
END My_Types;
/

DECLARE
   v   CONSTANT My_Types.My_AA := My_Types.Init_My_AA ();
BEGIN
   DECLARE
      Idx   PLS_INTEGER := v.FIRST ();
   BEGIN
      WHILE Idx IS NOT NULL
      LOOP
         DBMS_OUTPUT.PUT_LINE (TO_CHAR (Idx, '999') || LPAD (v (Idx), 7));
         Idx := v.NEXT (Idx);
      END LOOP;
   END;
END;
/

Varrays (Variable-Size Arrays):

A varray (variable-size array) is an array whose number of elements can vary from zero (empty) to the declared maximum size. To access an element of a varray variable, use the syntax variable_name(index). The lower bound of index is 1; the upper bound is the current number of elements. The upper bound changes as you add or delete elements, but it cannot exceed the maximum size. When you store and retrieve a varray from the database, its indexes and element order remain stable
Example#4 Varray (Variable-Size Array):
DECLARE
   TYPE Foursome IS VARRAY (4) OF VARCHAR2 (15);                -- VARRAY type

   -- varray variable initialized with constructor:
   team   Foursome
             := Foursome ('John',
                          'Mary',
                          'Alberto',
                          'Juanita');

   PROCEDURE print_team (heading VARCHAR2)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (heading);

      FOR i IN 1 .. 4
      LOOP
         DBMS_OUTPUT.PUT_LINE (i || '.' || team (i));
      END LOOP;

      DBMS_OUTPUT.PUT_LINE ('---');
   END;
BEGIN
   print_team ('2001 Team:');
   team (3) := 'Pierre';                      -- Change values of two elements
   team (4) := 'Yvonne';
   print_team ('2005 Team:');
   -- Invoke constructor to assign new values to varray variable:
   team :=
      Foursome ('Arun',
                'Amitha',
                'Allan',
                'Mae');
   print_team ('2009 Team:');
END;
/

Nested Tables:

In the database, a nested table is a column type that stores an unspecified number of rows in no particular order. When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable. The syntax is variable_name(index). The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.

Example#5  Nested Table of Local Type:

DECLARE
  TYPE Roster IS TABLE OF VARCHAR2(15);            -- nested table type
  -- nested table variable initialized with constructor:
  names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
  PROCEDURE print_names (heading VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
    FOR i IN names.FIRST .. names.LAST LOOP          -- For first to last element
      DBMS_OUTPUT.PUT_LINE(names(i));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('---');
  END;
BEGIN
  print_names('Initial Values:');
  names(3) := 'P Perez';                                                    -- Change value of one element
  print_names('Current Values:');
  names := Roster('A Jansen', 'B Gupta');  -- Change entire table
  print_names('Current Values:');
END;

Example#5  Nested Table of Standalone Stored Type:

CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;
/

CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) IS
  i  NUMBER;
BEGIN
  i := nt.FIRST;
  IF i IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('nt is empty');
  ELSE
    WHILE i IS NOT NULL LOOP
      DBMS_OUTPUT.PUT('nt.(' || i || ') = '); print(nt(i));
      i := nt.NEXT(i);
    END LOOP;
  END IF;
  DBMS_OUTPUT.PUT_LINE('---');
END print_nt;
/

DECLARE
  nt nt_type := nt_type(); -- nested table variable initialized to empty
BEGIN
  print_nt(nt);
  nt := nt_type(90, 9, 29, 58);
  print_nt(nt);
END;
/

 

Collection Constructors:

A collection constructor (constructor) is a system-defined function with the same name as a collection type, which returns a collection of that type. The syntax of a constructor invocation is:
collection_type ( [ value [, value ]... ] )
Note: This topic applies only to varrays and nested tables.
Associative arrays do not have constructors. In this topic, collection means varray or nested table

Example #5  Initializing Collection (Varray) Variable to Empty:

DECLARE
  TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
  team Foursome := Foursome();  -- initialize to empty
  PROCEDURE print_team (heading VARCHAR2)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
    IF team.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Empty');
    ELSE
      FOR i IN 1..4 LOOP
        DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
      END LOOP;
    END IF;
    DBMS_OUTPUT.PUT_LINE('---');
  END;
BEGIN
  print_team('Team:');
  team := Foursome('John', 'Mary', 'Alberto', 'Juanita');
  print_team('Team:');
END;
/

 

Assigning Values to Collection Variables:

You can assign a value to a collection variable in these ways:
Invoke a constructor to create a collection and assign it to the collection variable Use the assignment statement Pass it to a subprogram as an OUT or IN OUT parameter, and then assign the value inside the subprogram.
Data Type Compatibility:
You can assign a collection to a collection variable only if they have the same data type. Having the same element type is not enough.

Example #8    Data Type Compatibility for Collection Assignment:

DECLARE
  TYPE triplet IS VARRAY(3) OF VARCHAR2(15);
  TYPE trio    IS VARRAY(3) OF VARCHAR2(15);
  group1 triplet := triplet('Jones', 'Wong', 'Marceau');
  group2 triplet;
group3 trio;
BEGIN
  group2 := group1;  -- succeeds
group3 := group1;  -- fails
end;
/

In Example #8, VARRAY types triplet and trio have the same element type, VARCHAR(15). Collection variables group1 and group2 have the same data type, triplet, but collection variable group3 has the data type trio. The assignment of group1 to group2 succeeds, but the assignment of group1 to group3 fails Assigning Null Values to Varray or Nested Table Variables:
To a varray or nested table variable, you can assign the value NULL or a null collection of the same data type. Either assignment makes the variable null.
Example #9 Assigning Null Value to Nested Table Variable:
DECLARE
  TYPE dnames_tab IS TABLE OF VARCHAR2(30);
  dept_names dnames_tab := dnames_tab(
    'Shipping','Sales','Finance','Payroll');  -- Initialized to non-null value
  empty_set dnames_tab;  -- Not initialized, therefore null
  PROCEDURE print_dept_names_status IS
  BEGIN
    IF dept_names IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('dept_names is null.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('dept_names is not null.');
    END IF;
  END  print_dept_names_status;
BEGIN
  print_dept_names_status;
  dept_names := empty_set;  -- Assign null collection to dept_names.
  print_dept_names_status;
  dept_names := dnames_tab (
    'Shipping','Sales','Finance','Payroll');  -- Re-initialize dept_names
  print_dept_names_status;
END;
/

 

Example #9 initializes the nested table variable dname_tab to a non-null value;

assigns a null collection to it, making it null; and re-initializes it to a different non-null value.
Assigning Set Operation Results to Nested Table Variables:
To a nested table variable, you can assign the result of a SQL MULTISET operation or SQL SET function invocation.
The SQL MULTISET operators combine two nested tables into a single nested table.
The elements of the two nested tables must have comparable data types. For information about the MULTISET operators, see Oracle Database SQL Language Reference.
The SQL SET function takes a nested table argument and returns a nested table of the same data type whose elements are distinct (the function eliminates duplicate elements). For information about the SET function, see Oracle Database SQL Language Reference.

Example #10   Assigning Set Operation Results to Nested Table Variable:

DECLARE
  TYPE nested_typ IS TABLE OF NUMBER;
  nt1    nested_typ := nested_typ(1,2,3);
  nt2    nested_typ := nested_typ(3,2,1);
  nt3    nested_typ := nested_typ(2,3,1,3);
  nt4    nested_typ := nested_typ(1,2,4);
  answer nested_typ;
  PROCEDURE print_nested_table (nt nested_typ) IS
    output VARCHAR2(128);
  BEGIN
    IF nt IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Result: null set');
    ELSIF nt.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Result: empty set');
    ELSE
      FOR i IN nt.FIRST .. nt.LAST LOOP  -- For first to last element
        output := output || nt(i) || ' ';
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('Result: ' || output);
    END IF;
  END print_nested_table;
BEGIN
  answer := nt1 MULTISET UNION nt4;
  print_nested_table(answer);
answer := nt1 MULTISET UNION nt3;
  print_nested_table(answer);
answer := nt1 MULTISET UNION DISTINCT nt3;
  print_nested_table(answer);
answer := nt2 MULTISET INTERSECT nt3;
  print_nested_table(answer);
answer := nt2 MULTISET INTERSECT DISTINCT nt3;
  print_nested_table(answer);
answer := SET(nt3);
  print_nested_table(answer);
answer := nt3 MULTISET EXCEPT nt2;
  print_nested_table(answer);
answer := nt3 MULTISET EXCEPT DISTINCT nt2;
  print_nested_table(answer);
END;
/

 

Example #10 assigns the results of several MULTISET operations and one SET

Function invocation of the nested table variable answer, using the procedure print_ nested_table to print answer after each assignment. The procedure use the collection methods FIRST and LAST
Multidimensional Collections:
Although a collection has only one dimension, you can model a multidimensional collection with a collection whose elements are collections.

Example #11 Two-Dimensional Varray (Varray of Varrays):

DECLARE
  TYPE t1 IS VARRAY(10) OF INTEGER;  -- varray of integer
  va t1 := t1(2,3,5);
  TYPE nt1 IS VARRAY(10) OF t1;      -- varray of varray of integer
  nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va);
  i INTEGER;
  va1 t1;
BEGIN
  i := nva(2)(3);
  DBMS_OUTPUT.PUT_LINE('i = ' || i);
  nva.EXTEND;
  nva(5) := t1(56, 32);          -- replace inner varray elements
  nva(4) := t1(45,43,67,43345);  -- replace an inner integer element
  nva(4)(4) := 1;                -- replace 43345 with 1
  nva(4).EXTEND;    -- add element to 4th varray element
  nva(4)(5) := 89;  -- store integer 89 there
END;
/

In Example 11, nva is a two-dimensional varray  —a varray of varrays of integers.

Example #12 Nested Tables of Nested Tables and Varrays of Integers:

DECLARE
  TYPE tb1 IS TABLE OF VARCHAR2(20);  -- nested table of strings
  vtb1 tb1 := tb1('one', 'three');
  TYPE ntb1 IS TABLE OF tb1; -- nested table of nested tables of strings
  vntb1 ntb1 := ntb1(vtb1);
  TYPE tv1 IS VARRAY(10) OF INTEGER;  -- varray of integers
TYPE ntb2 IS TABLE OF tv1;          -- nested table of varrays of integers
  vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3));
BEGIN
  vntb1.EXTEND;
  vntb1(2) := vntb1(1);
  vntb1.DELETE(1);     -- delete first element of vntb1
  vntb1(2).DELETE(1);  -- delete first string from second table in nested table
END;
/

 

Example #13 Nested Tables of Associative Arrays and Varrays of Strings:

DECLARE
  TYPE tb1 IS TABLE OF INTEGER INDEX BY PLS_INTEGER;  -- associative arrays
  v4 tb1;
  v5 tb1;
  TYPE ntb1 IS TABLE OF tb1 INDEX BY PLS_INTEGER;  -- nested table of
  v2 ntb1;                                         --  associative arrays
  TYPE va1 IS VARRAY(10) OF VARCHAR2(20);  -- varray of strings
  v1 va1 := va1('hello', 'world');
  TYPE ntb2 IS TABLE OF va1 INDEX BY PLS_INTEGER;  -- nested table of varrays
  v3 ntb2;
BEGIN
  v4(1)   := 34;     -- populate associative array
  v4(2)   := 46456;
  v4(456) := 343;
  v2(23) := v4;  -- populate nested table of associative arrays
  v3(34) := va1(33, 456, 656, 343);  -- populate nested table of varrays
  v2(35) := v5;      -- assign empty associative array to v2(35)
  v2(35)(2) := 78;
END;
/

In Example 13, ntb1 is a nested table of associative arrays, and ntb2 is a nested table of varrays of strings

 

Collection Comparisons:

You cannot compare associative array variables to the value NULL or to each other. Except for Comparing Nested Tables for Equality and Inequality, you cannot natively compare two collection variables with relational operators
For example, a collection variable cannot appear in a DISTINCT, GROUP BY, or ORDER BY clause.
Comparing Varray and Nested Table Variables to NULL:
You can compare varray and nested table variables to the value NULL with the "IS [NOT] NULL Operator"
Example #14 Comparing Varray and Nested Table Variables to NULL:
DECLARE
  TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);  -- VARRAY type
  team Foursome;                               -- varray variable
TYPE Roster IS TABLE OF VARCHAR2(15);        -- nested table type
  names Roster := Roster('Adams', 'Patel');    -- nested table variable
BEGIN
IF team IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('team IS NULL');
  ELSE
    DBMS_OUTPUT.PUT_LINE('team IS NOT NULL');
  END IF;
  IF names IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE('names IS NOT NULL');
  ELSE
    DBMS_OUTPUT.PUT_LINE('names IS NULL');
  END IF;
END;
/

 

Comparing Nested Tables for Equality and Inequality:

If two nested table variables have the same nested table type, and that nested table type does not have elements of a record type, then you can compare the two variables for equality or inequality with the relational operators equal (=) and not equal (<>, !=, ~=, ^=). Two nested table variables are equal if and only if they have the same set of elements (in any order)
Example #15 compares nested table variables for equality and inequality with relational operators
Example #15 Comparing Nested Tables for Equality and Inequality:
DECLARE
  TYPE dnames_tab IS TABLE OF VARCHAR2(30); -- element type is not record type
  dept_names1 dnames_tab :=
    dnames_tab('Shipping','Sales','Finance','Payroll');
  dept_names2 dnames_tab :=
    dnames_tab('Sales','Finance','Shipping','Payroll');
  dept_names3 dnames_tab :=
    dnames_tab('Sales','Finance','Payroll');
BEGIN
  IF dept_names1 = dept_names2 THEN
    DBMS_OUTPUT.PUT_LINE('dept_names1 = dept_names2');
  END IF;
IF dept_names2 != dept_names3 THEN
    DBMS_OUTPUT.PUT_LINE('dept_names2 != dept_names3');
  END IF;
END;
/

 

Comparing Nested Tables with SQL Multiset Conditions:

You can compare nested table variables, and test some of their properties, with SQL multiset conditions (described in Oracle Database SQL Language Reference).
Example #16   uses the SQL multiset conditions and two SQL functions that take nested table variable arguments, CARDINALITY (described in Oracle Database SQL Language Reference) and SET (described in Oracle Database SQL Language Reference).
Example #16 Comparing Nested Tables with SQL Multiset Conditions:
DECLARE
  TYPE nested_typ IS TABLE OF NUMBER;
  nt1 nested_typ := nested_typ(1,2,3);
  nt2 nested_typ := nested_typ(3,2,1);
  nt3 nested_typ := nested_typ(2,3,1,3);
  nt4 nested_typ := nested_typ(1,2,4);
  PROCEDURE testify (
    truth BOOLEAN := NULL,
    quantity NUMBER := NULL
  ) IS
  BEGIN
    IF truth IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE (
        CASE truth
           WHEN TRUE THEN 'True'
           WHEN FALSE THEN 'False'
        END
      );
    END IF;
IF quantity IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE(quantity);
    END IF;
  END;
BEGIN
  testify(truth => (nt1 IN (nt2,nt3,nt4)));        -- condition
  testify(truth => (nt1 SUBMULTISET OF nt3));      -- condition
  testify(truth => (nt1 NOT SUBMULTISET OF nt4));  -- condition
  testify(truth => (4 MEMBER OF nt1));             -- condition
  testify(truth => (nt3 IS A SET));                -- condition
  testify(truth => (nt3 IS NOT A SET));            -- condition
  testify(truth => (nt1 IS EMPTY));                -- condition
  testify(quantity => (CARDINALITY(nt3)));         -- function
  testify(quantity => (CARDINALITY(SET(nt3))));    -- 2 functions
END;
/

 

Collection Methods:

A collection method is a PL/SQL subprogram—either a function that returns information about a collection or a procedure that operates on a collection. Collection methods make collections easier to use and your applications easier to maintain.
Note: With a null collection, EXISTS is the only collection method that does not raise the predefined exception COLLECTION_IS_NULL

Method        Type                  Description

DELETE     Procedure            Deletes elements from collection.
TRIM            Procedure            Deletes elements from end of varray or nested table.
EXTEND    Procedure            Adds elements to end of varray or nested table.
EXISTS      Function       Returns TRUE if and only if specified element of varray or nested table exists.
FIRST                     Function               Returns first index in collection.
LAST                      Function               Returns last index in collection.
COUNT                  Function               Returns number of elements in collection.
LIMIT                     Returns maximum number of elements that collection can have.
PRIOR                 Function               Returns index that precedes specified index.
NEXT                  Function               Returns index that succeeds specified index.
DELETE Collection Method:
DELETE is a procedure that deletes elements from a collection. This method has these forms:
DELETE deletes all elements from a collection of any type.
This operation immediately frees the memory allocated to the deleted elements.

From an associative array or nested table:
DELETE(n) deletes the element whose index is n, if that element exists; otherwise, it does nothing.
DELETE(m,n) deletes all elements whose indexes are in the range m..n, if both m and n exist and m <= n; otherwise, it does nothing.
Example #17 declares a nested table variable, initializing it with six elements; deletes and then restores the second element; deletes a range of elements and then restores one of them; and then deletes all elements. The restored elements occupy the same memory as the corresponding deleted elements. The procedure print_nt prints the nested table variable after initialization and after each DELETE operation. The type nt_type and procedure print_nt are defined inExample #6.

Example 17 DELETE Method with Nested Table:
DECLARE
  nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
BEGIN
  print_nt(nt);
  nt.DELETE(2);     -- Delete second element
  print_nt(nt);
nt(2) := 2222;    -- Restore second element
  print_nt(nt);
nt.DELETE(2, 4);  -- Delete range of elements
  print_nt(nt);
nt(3) := 3333;    -- Restore third element
  print_nt(nt);
nt.DELETE;        -- Delete all elements
  print_nt(nt);
END;

Example #18 populates an associative array indexed by string and deletes all elements, which frees the memory allocated to them. Next, the example replaces the deleted elements—that is, adds new elements that have the same indexes as the deleted elements. The new replacement elements do not occupy the same memory as the corresponding deleted elements. Finally, the example deletes one element and then a range of elements. The procedure print_aa_str shows the effects of the operations.
Example #18   DELETE Method with Associative Array Indexed by String::
DECLARE
  TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10);
  aa_str  aa_type_str;
  PROCEDURE print_aa_str IS
    i  VARCHAR2(10);
  BEGIN
    i := aa_str.FIRST;
    IF i IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('aa_str is empty');
    ELSE
      WHILE i IS NOT NULL LOOP
        DBMS_OUTPUT.PUT('aa_str.(' || i || ') = '); print(aa_str(i));
        i := aa_str.NEXT(i);
      END LOOP;
    END IF;
    DBMS_OUTPUT.PUT_LINE('---');
  END print_aa_str;
BEGIN
  aa_str('M') := 13;
  aa_str('Z') := 26;
  aa_str('C') := 3;
  print_aa_str;
  aa_str.DELETE;  -- Delete all elements
  print_aa_str;
aa_str('M') := 13;   -- Replace deleted element with same value
aa_str('Z') := 260;  -- Replace deleted element with new value
aa_str('C') := 30;   -- Replace deleted element with new value
  aa_str('W') := 23;   -- Add new element
  aa_str('J') := 10;   -- Add new element
  aa_str('N') := 14;   -- Add new element
  aa_str('P') := 16;   -- Add new element
  aa_str('W') := 23;   -- Add new element
  aa_str('J') := 10;   -- Add new element
  print_aa_str;
  aa_str.DELETE('C');      -- Delete one element
  print_aa_str;
aa_str.DELETE('N','W');  -- Delete range of elements
  print_aa_str;
aa_str.DELETE('Z','M');  -- Does nothing
  print_aa_str;
END;
/

Example #19 declares a nested table variable, initializing it with six elements; trims the last element; deletes the fourth element; and then trims the last two elements—one of which is the deleted fourth element. The procedure print_nt prints the nested table variable after initialization and after the TRIM and DELETE operations. The type nt_type and procedure print_nt are defined inExample #6.

Example #19 TRIM Method with Nested Table:

DECLARE
  nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
BEGIN
  print_nt(nt);
  nt.TRIM;       -- Trim last element
  print_nt(nt);
nt.DELETE(4);  -- Delete fourth element
  print_nt(nt);
nt.TRIM(2);    -- Trim last two elements
  print_nt(nt);
END;
/

 

Example #20 EXTEND Method with Nested Table:

DECLARE
  nt nt_type := nt_type(11, 22, 33);
BEGIN
  print_nt(nt);
  nt.EXTEND(2,1);  -- Append two copies of first element
  print_nt(nt);
nt.DELETE(5);    -- Delete fifth element
  print_nt(nt);
nt.EXTEND;       -- Append one null element
  print_nt(nt);
END;
/

 

Example #21 EXISTS Method with Nested Table:

DECLARE
  TYPE NumList IS TABLE OF INTEGER;
  n NumList := NumList(1,3,5,7);
BEGIN
  n.DELETE(2); -- Delete second element
  FOR i IN 1..6 LOOP
    IF n.EXISTS(i) THEN
      DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i));
    ELSE
      DBMS_OUTPUT.PUT_LINE('n(' || i || ') does not exist');
    END IF;
  END LOOP;
END;
/

 

Example #22 FIRST and LAST Values for Associative Array Indexed by PLS_INTEGER:::

DECLARE
  TYPE aa_type_int IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
  aa_int  aa_type_int;
  PROCEDURE print_first_and_last IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST);
    DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST);
  END print_first_and_last;
BEGIN
  aa_int(1) := 3;
  aa_int(2) := 6;
  aa_int(3) := 9;
  aa_int(4) := 12;
  DBMS_OUTPUT.PUT_LINE('Before deletions:');
  print_first_and_last;
  aa_int.DELETE(1);
aa_int.DELETE(4);
  DBMS_OUTPUT.PUT_LINE('After deletions:');
  print_first_and_last;
END;
/

 

COUNT Collection Method:

COUNT is a function that returns the number of elements in the collection (ignoring deleted elements, even if DELETE kept placeholders for them).
Example #26 COUNT and LAST Values for Varray:
DECLARE
  TYPE NumList IS VARRAY(10) OF INTEGER;
  n NumList := NumList(1,3,5,7);
  PROCEDURE print_count_and_last IS
  BEGIN
    DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', ');
    DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST);
  END  print_count_and_last;
BEGIN
  print_count_and_last;
  n.EXTEND(3);
  print_count_and_last;
n.TRIM(5);
  print_count_and_last;
END;
/

 

LIMIT Collection Method:

LIMIT is a function that returns the maximum number of elements that the collection can have. If the collection has no maximum number of elements, LIMIT returns NULL. Only a varray has a maximum size.
Example #28 LIMIT and COUNT Values for Different Collection Types:
DECLARE
  TYPE aa_type IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
  aa aa_type;                          -- associative array
  TYPE va_type IS VARRAY(4) OF INTEGER;
  va  va_type := va_type(2,4);   -- varray
  TYPE nt_type IS TABLE OF INTEGER;
  nt  nt_type := nt_type(1,3,5);  -- nested table
BEGIN
  aa(1):=3; aa(2):=6; aa(3):=9; aa(4):= 12;
  DBMS_OUTPUT.PUT('aa.COUNT = '); print(aa.COUNT);
  DBMS_OUTPUT.PUT('aa.LIMIT = '); print(aa.LIMIT);
  DBMS_OUTPUT.PUT('va.COUNT = '); print(va.COUNT);
  DBMS_OUTPUT.PUT('va.LIMIT = '); print(va.LIMIT);
  DBMS_OUTPUT.PUT('nt.COUNT = '); print(nt.COUNT);
  DBMS_OUTPUT.PUT('nt.LIMIT = '); print(nt.LIMIT);
END;
Example #29 PRIOR and NEXT Methods:
DECLARE
  TYPE nt_type IS TABLE OF NUMBER;
  nt nt_type := nt_type(18, NULL, 36, 45, 54, 63);
BEGIN
  nt.DELETE(4);
  DBMS_OUTPUT.PUT_LINE('nt(4) was deleted.');
  FOR i IN 1..7 LOOP
    DBMS_OUTPUT.PUT('nt.PRIOR(' || i || ') = '); print(nt.PRIOR(i));
    DBMS_OUTPUT.PUT('nt.NEXT(' || i || ')  = '); print(nt.NEXT(i));
  END LOOP;
END;
/

Example #30 prints the elements of a sparse nested table from first to last, using FIRST and NEXT, and from last to first, using LAST and PRIOR.
Example #30 Printing Elements of Sparse Nested Table:
DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  n NumList := NumList(1, 2, NULL, NULL, 5, NULL, 7, 8, 9, NULL);
  idx INTEGER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('First to last:');
  idx := n.FIRST;
  WHILE idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT('n(' || idx || ') = ');
    print(n(idx));
    idx := n.NEXT(idx);
  END LOOP;
   DBMS_OUTPUT.PUT_LINE('--------------');
  DBMS_OUTPUT.PUT_LINE('Last to first:');
  idx := n.LAST;
  WHILE idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT('n(' || idx || ') = ');
    print(n(idx));
    idx := n.PRIOR(idx);
  END LOOP;
END;
/


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect