Ø
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