1.Associative array(or index-by table)
2.varray(variable-size arrray)
3.Nested table
Number of Elements::
If the no of elements is specified ,it is the maximum no of elements in the collection.
If the no of elements isunspecified, the maximum no of elements in the collection is
the upper limit of the index type
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
An 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
Example=> 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 compares a varray variable and a nested table variable to NULL
correctly.
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 Function 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 in Example #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 in Example #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;
/
Posted by Manoja at 01:01
Email This
BlogThis!
Share to Twitter
Share to Facebook
Labels: Assigning Values to Collection Variables, Associative arrays in pl/sql, Collection methods in pl/sql, Collections constructors in pl/sql, collections constructors in pl/sql language, collections methods first last next prior count in pl/sql, Comparing Nested Tables for Equality and Inequality, Declaring Associative Array Constant, DELETE Collection Method, EXTEND Method with Nested Table
