Collection
A collection is a group of elements of the same datatype. Each element is identified by
a unique subscript that represents its position in the collection
However, often in your programs you want to represent a group of elements such as
the grades for a class of emps. To support this technique, PL/SQL provides collection
datatypes that work just like arrays available in other third-generation programming
languages.
An Oracle PL/SQL collection is a single-dimensional array; it consists of one
or more elements accessible through an index value.
Why Collections?
Storing elements in a collection can provide a number of advantages. For starters, collections can help
to simplify code. If you need to process a number of items of a similar type, storing these items in a
collection will allow you to loop through each element with ease, referencing each one by an index. In
addition, most languages define collection types that contain built-in methods to operate on the
collection. Probably the biggest advantage a collection can provide is improved application
performance. Developers utilize collections to 'cache' static data that needs to be regularly accessed.
This results in reduced calls to a database.
Collections are used in some of the most important performance optimization features
of PL/SQL, such as
BULK COLLECT. SELECT statements that retrieve multiple rows with a single
fetch, increasing the speed of data retrieval.
FORALL. Inserts, updates, and deletes that use collections to change multiple
rows of data very quickly
Table functions. PL/SQL functions that return collections and can be called in the
FROM clause of a SELECT statement.
You can also use collections to work with lists of data in your program that are not
stored in database tables.
aCollection Concepts and Terminology
Before exploring collections, it is helpful to have a common collections vocabulary that
includes the following terms.
Index value. The location of the data in a collection. Index values are usually integers
but for one type of collection can also be strings.
Element. The data stored at a specific index value in a collection. Elements in a
collection are always of the same type (all of them are strings, dates, or records).
PL/SQL collections are homogeneous.
Sparse. A collection is sparse if there is at least one index value between the lowest
and highest defined index values that is not defined. For example, a sparse collection
has an element assigned to index value 1 and another to index value 10 but nothing in
between. The opposite of a sparse collection is a dense one.
Method. A collection method is a procedure or function that either provides
information about the collection or changes the contents of the collection. Methods
are attached to the collection variable with dot notation (object-oriented syntax), as in
my_collection.FIRST.
Types of Collections
There are now three types of collections to choose from, each with its own set of
characteristics and each best suited to a different circumstance.
PL/SQL Tables
A PL/SQL table is similar to a one-column database table. The rows of a PL/SQL table
are not stored in any predefined order, yet when they are retrieved in a variable, each
row is assigned a consecutive subscript starting at 1,
The two types of PL/SQL tables are associative tables (formerly called index-by tables)
and nested tables.
Associative array. The first type of collection available in PL/SQL, this was originally
called a “PL/SQL table” and can be used only in PL/SQL blocks. Associative arrays can
be sparse or dense and can be indexed by integer or string.
These are single-dimensional, unbounded, sparse (do not need to be filled up
sequentially) collections of homogeneous elements. In the next example I declare such
array, populate it with some data and iterate through the collection.
The other difference between associative arrays and PLSQL nested tables is that nested
tables are initially dense whereas associative arrays are sparse.
An index-by table (also called an associative array) is a set of key-value pairs. Each key
is unique, and is used to locate the corresponding value. The key can be either an
integer or a string.
An index-by table is created using the following syntax. Here we are creating an indexby
table namedtable_name whose keys will be of subscript_type and associated values
will be of element_type
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;
First we declare the table type:
FOR EXAMPLE
DECLARE
TYPE ename_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
_
Next we declare a variable of that type:
Ename_tab ename_type;
As mentioned, the individual elements of a PL/SQL table are referenced using subscript
notation
as follows:
table_name(subscript)
SQL> declare
2 TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;3
phone_nos phone_no_tab;
4 begin
5 phone_nos(1) := '+44 (0) 117 942 2508';
6 dbms_output.put_line('phone_no(1) is '||phone_nos(1));
7 end;
8 /
phone_no(1) is +44 (0) 117 942 2508
As you can see from the above example the elements (cells) of the array are created
automatically by Oracle. Line 3 acts as as the declaration and constructor of the array.
However if we access a cell that doesn't have any data in it, we will generate a
NO_DATA_FOUND exception as in the following example:
SQL> DECLARE2 TYPE phone_no_tab IS IS TABLE OF VARCHAR2(20) INDEX BY
BINARY_INTEGER;3 phone_nos phone_no_tab;4 BEGIN
5 phone_nos (21) := '0117 942 2508';
6 DBMS_OUTPUT.PUT_LINE('phone_no(1) is '||phone_nos(1));
7 END;
8 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6
This technique is demonstrated in the following example:
DECLARE
CURSOR ename_cur IS
SELECT ename
FROM emp
WHERE rownum <= 10;
TYPE ename_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_tab ename_type;
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN ename_cur LOOP
v_counter := v_counter + 1;
ename_tab(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('ename('||v_counter||'): '||
ename_tab(v_counter));
END LOOP;
END;
In This creates an array (table) that is available within our PL/SQL session. Inserting and
retrieving data is done in the same way as with nested tables but there is no need to
initialise the array or extend it. Instead we just access the cells we want, whether we
want to insert data or retrieve data from the array,
Summary
1. Associative arrays are appropriate for relatively small lookup tables where the
collection can be constructed in memory each time a procedure is called or a
package is initialized.
2. They are good for collecting information whose volume is unknown beforehand,
because there is no fixed limit on their size.
3. Their index values are more flexible, because associative array subscripts can be
negative, can be nonsequential, and can use string values instead of numbers
when appropriate.
Nested table. Added in Oracle8 Database, the nested table can be used in PL/SQL
blocks, in SQL statements, and as the datatype of columns in tables. Nested tables can
be sparse but are almost always dense. They can be indexed only by integer. You can
use the MULTISET operator to perform set operations and to perform equality
comparisons on nested tables.
NESTED TABLES
The general syntax for creating a nested table is as follows (the reserved words and
phrases in
brackets are optional):
TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name TYPE_NAME;
Notice that this declaration is very similar to the declaration of an associative array,
except that it has no INDEX BY BINARY_INTEGER clause. As in the case of an associative
array, restrictions
apply to an element_type of a nested table.
It is important to note that a nested table must be initialized before its individual
elements can
be referenced.
This declaration is similar to declaration of an index-by table, but there is no INDEX BY
clause.
A nested table can be stored in a database column and so it could be used for
simplifying SQL operations where you join a single-column table with a larger table. An
associative array cannot be stored in the database.
DECLARE
CURSOR ename_cur IS SELECT ename
FROM emp
WHERE rownum <= 10;
TYPE ename_type IS TABLE OF emp.ename%TYPE;
ename_tab ename_type;
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
ename_tab(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('ename('||v_counter||'): '||
ename_tab(v_counter));
END LOOP;
END;
This example causes the following error:
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 14
This example causes an error because a nested table is automatically NULL when it is
declared. In other words, no individual elements exist yet because the nested table
itself is NULL. For you to reference the individual elements of the nested table, it must
be initialized with the help of a system-defined function called a constructor. The
constructor has the same name as the nested table type.
Solution-----
DECLARE
CURSOR ename_cur IS
SELECT ename
FROM emp
WHERE rownum <= 10;
TYPE ename_type IS TABLE OF emp.ename%TYPE;
ename_tab ename_type := ename_type();
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
ename_tab.EXTEND;
ename_tab(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('ename('||v_counter||'): '||
ename_tab(v_counter));
END LOOP;
END;
DID YOU KNOW?
What is the difference between a NULL collection and an empty collection? If a
collection has not
been initialized, referencing its individual elements causes the following error:
DECLARE
TYPE integer_type IS TABLE OF INTEGER;
integer_tab integer_type;
v_counter integer := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE (integer_tab(v_counter));
END;
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 7
If a collection has been initialized so that it is empty, referencing its individual elements
causes a
different error:
DECLARE
TYPE integer_type IS TABLE OF INTEGER;
integer_tab integer_type := integer_type();
v_counter integer := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE (integer_tab(v_counter));
END;
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 7
COLLECTION METHODS
In the previous examples, you have seen one of the collection methods, EXTEND. A
collection method is a built-in function that is called using dot notation as follows:
collection_name.method_name
The following list explains collection methods that allow you to manipulate or gain
information about a particular collection:
. EXISTS returns TRUE if a specified element exists in a collection. This method can be
used to avoid SUBSCRIPT_OUTSIDE_LIMIT exceptions.
. COUNT returns the total number of elements in a collection.
. EXTEND increases the size of a collection.
. DELETE deletes either all elements, elements in the specified range, or a particular
element from a collection. Note that PL/SQL keeps placeholders of the deleted
elements.
. FIRST and LAST return subscripts of the first and last elements of a collection. Note
that
if the first elements of a nested table are deleted, the FIRST method returns a value
greater
than 1. If elements have been deleted from the middle of a nested table, the LAST
method
returns a value greater than the COUNT method.
. PRIOR and NEXT return subscripts that precede and succeed a specified collection
subscript.
. TRIM removes either one or a specified number of elements from the end of a
collection.
Note that PL/SQL does not keep placeholders for the trimmed elements.
BY THE WAY
EXTEND and TRIM methods cannot be used with index-by tables.
FOR EXAMPLE
DECLARE
TYPE index_by_type IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
index_by_table index_by_type;
TYPE nested_type IS TABLE OF NUMBER;
nested_table nested_type :=
nested_type(1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
BEGIN
-- Populate index by table
FOR i IN 1..10 LOOP
index_by_table(i) := i;
END LOOP;
IF index_by_table.EXISTS(3) THEN
DBMS_OUTPUT.PUT_LINE ('index_by_table(3) =
'||index_by_table(3));
END IF;
-- delete 10th element from a collection
nested_table.DELETE(10);
-- delete elements 1 through 3 from a collection
nested_table.DELETE(1,3);
index_by_table.DELETE(10);
DBMS_OUTPUT.PUT_LINE ('nested_table.COUNT =
'||nested_table.COUNT);
DBMS_OUTPUT.PUT_LINE ('index_by_table.COUNT = '||
index_by_table.COUNT);
DBMS_OUTPUT.PUT_LINE ('nested_table.FIRST =
'||nested_table.FIRST);
DBMS_OUTPUT.PUT_LINE ('nested_table.LAST = '||nested_table.LAST);
DBMS_OUTPUT.PUT_LINE ('index_by_table.FIRST = '||
index_by_table.FIRST);
DBMS_OUTPUT.PUT_LINE ('index_by_table.LAST =
'||index_by_table.LAST);
DBMS_OUTPUT.PUT_LINE ('nested_table.PRIOR(2) = '||
nested_table. PRIOR(2));
DBMS_OUTPUT.PUT_LINE ('nested_table.NEXT(2) = '||
nested_table.NEXT(2));
DBMS_OUTPUT.PUT_LINE ('index_by_table.PRIOR(2) = '||
index_by_table.PRIOR(2));
DBMS_OUTPUT.PUT_LINE ('index_by_table.NEXT(2) = '||
index_by_table.NEXT(2));
-- Trim last two elements
nested_table.TRIM(2);
-- Trim last element
nested_table.TRIM;
DBMS_OUTPUT.PUT_LINE('nested_table.LAST = '||nested_table.LAST);
END;
Consider the output returned by this example:
index_by_table(3) = 3
nested_table.COUNT = 6
index_by_table.COUNT = 9
nested_table.FIRST = 4
nested_table.LAST = 9
index_by_table.FIRST = 1
index_by_table.LAST = 9
nested_table.PRIOR(2) =
nested_table.NEXT(2) = 4
index_by_table.PRIOR(2) = 1
index_by_table.NEXT(2) = 3
nested_table.LAST = 7
PL/SQL procedure successfully completed.
Nested Tables, like the Varray, can be stored in a relational table as well as function as a PL/SQL program
variable. Unlike Varray, nested tables require no size specification. In other words, they are unbound.
How Nested Tables are Stored in Database?
With Nested Tables, a separate database table will store the data. This table is specified following the
'STORE AS' clause. If a database table has more than one Nested Table type, the same storage table
will store data for all the Nested Tables on that parent table. These storage tables contain a column
called NESTED_TABLE_ID that allows the parent table to reference a row's nested table data.
CREATE TYPE dept_tab IS TABLE OF VARCHAR2(50);
/
CREATE TABLE Department (
DEPTNO NUMBER,
SECTION VARCHAR2(30),
dept_name_tab dept_tab)
NESTED TABLE dept_name_tab STORE AS names_table;
/
--Insert a record into Department, with a Nested Table of dept names.
INSERT INTO Department (DEPTNO, SECTION, dept_name_tab)
VALUES (DEPTNO_SEQ.NEXTVAL,'Engineering',
dept_tab('Electronics','Computer', _
'Instrumentation', 'Information technology'));
How to Operate on Individual Elements of Collection?
To operate on collection elements, use the TABLE command. The TABLE command operator informs
Oracle that you want your operations to be directed at the collection, instead of its parent table.
--1.Select all depts from section 'Engineering' that are like %ELCTRO%'.
SELECT column_value FROM TABLE(SELECT dept_name_tab
FROM Department WHERE SECTION = ''Engineering')
WHERE column_value LIKE '%ELECTRO%';
-- Output of the above query
COLUMN_VALUE
------------------------------
ELECTRONICS
ELCTRICAL
--2.Update DPET NAME ‘Production’ to a new value ‘Production sandwitch’.
--This is possible Only with a nested table, Not Possible in Varray!!
UPDATE TABLE(SELECT dept_name_tab
FROM Department WHERE SECTION = 'ENGINEERING')
SET column_value = 'Production'
WHERE column_value = 'Production Sandwitch';
--3.Select all department entries for ENGINEERING SECTION.
SELECT column_value FROM TABLE(SELECT dept_name_tab
FROM Department WHERE SECTION = 'Engineering');
-- Output of the above query
COLUMN_VALUE
------------------------------
MECHANICAL
ELECTRONICS
COMPUTER
PRODUCTION SANDWITCH
Summary
1. Nested tables are appropriate for important data relationships that must be stored
persistently.
2. Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an
item from the end.
3. Nested table data is stored out-of-line in a store table, a system-generated database table
associated with the nested table.
4. The order and subscripts of elements of nested table are not preserved when it is stored in
database.
5. Nested Tables have an advantage over varrays in that they allow for inserts, updates, and
deletes on individual elements.
PL/SQL Array
Varray. Added in Oracle8 Database, the varray (variable-size array) can be used in
PL/SQL blocks, in SQL statements, and as the datatype of columns in tables. Varrays are
always dense and indexed by integer. When a varray type is defined, you must specify
the maximum number of elements allowed in a collection declared with that type.
You will rarely encounter a need for a varray (How many times do you know in advance
the maximum number of elements you will define in your collection?). The associative
array is the most commonly used collection type, but nested tables have some
powerful, unique features (such as MULTISET operators) that can simplify the code you
need to write to use your collection.
a varray is another collection type. This term stands for “variable-size array.” Similar to PL/SQL tables, each
element of a varray is assigned a consecutive subscript starting at 1.
It is important to note that a varray has a maximum size. In other words, a subscript of a varray has a fixed lower
bound equal to 1, and an upper bound that is extensible if such a need arises.
Therefore, a varray can contain a number of elements, varying from 0 (an empty array) to its maximum size. You
will recall that PL/SQL tables do not have a maximum size that must be specified explicitly.
The general syntax for creating a varray is as follows (the reserved words and phrases in square
brackets are optional):
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF
element_type [NOT NULL];
varray_name TYPE_NAME;
example
DECLARE
CURSOR ename_cur IS
SELECT ename FROM emp
WHERE rownum <= 10;
TYPE ename_type IS VARRAY(10) OF emp.ename%TYPE;
ename_varray ename_type := ename_type();
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN ename_cur LOOP
v_counter := v_counter + 1;
ename_varray.EXTEND;
ename_varray(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('ename('||v_counter||'): '||
ename_varray(v_counter));
END LOOP;
END;
The Varray is short for Variable Array. A Varray stores elements of the same type in the
order in which they are added. The number of elements in a Varray must be known at
the time of its declaration. In other words, a Varray has fixed lower and upper bounds,
making it most similar to collection types from other programming languages. Once it is
created and populated, each element can be accessed by a numeric index.
The following statements declare, and then populate, a Varray that will contain 4
elements of the same type as the column name in table Department:
-- Declaration and initialization of VARRAY.
DECLARE
TYPE deptnames IS VARRAY(4) OF Department.name%TYPE;
dept_names deptnames;
BEGIN
dept_names := deptnames ('COMPUTER','MECHANICAL', 'ELCTRICAL');
END;
-- Create Varray
CREATE TYPE depts IS VARRAY (4) OF VARCHAR2(50);
/
--Create table with varray column.
CREATE TABLE Department (
DEPTNO NUMBER,
SECTION VARCHAR2(30),
Dept_names depts);
/
--Insert a new collection into the column on our book_library table.
INSERT INTO Department (DEPTNO, SECTION, Dept_names)
VALUES (DEPTNO_SEQ.NEXTVAL,'Engineering',
depts ('Electronics','Computer', 'Instrumentation', 'Information technology'));
/
-- Retrieve varray from the database.
SELECT section, Dept_names from Department;
NAME DEPT_NAMES
-------------------- ---------------------------------------------
Engineering DEPTS('Electronics','Computer','Instrumentation',
'Information technology')
-- Note: Insertion order of elements of varray retained after retrieval.
Summary
1. Varrays can be added into database tables.
2. The Varray is ideal for storing fixed values that will be processed collectively.
3. It is not possible to perform inserts, updates, and deletes on the individual
elements in a Varray.
4. When a table contains a Varray type, its data is included in-line, with the rest of
the table's data.
5. Varrays are a good choice when the number of elements is known in advance,
and when the elements are usually all accessed in sequence.
6. When stored in the database, varrays retain their ordering and subscripts.
When to Use What?
Varray
1. Use to preserve ordered list
2. Use when working with a fixed set, with a known number of entries
3. Use when you need to store in the database and operate on the Collection as a whole
Nested Table
1. Use when working with an unbounded list that needs to increase dynamically
2. Use when you need to store in the database and operate on elements individually
Associative Array
1. Use when there is no need to store the Collection in the database. Its speed and indexing
flexibility make it ideal for internal application use.
Conclusion
Oracle PL/SQL is not a difficult programming language to learn. However, like all good programming
languages, there are many things we can do to maximize efficiency and minimize complexity.
Considering PL/SQL's power to interact with the database, it can be tempting to simply fall into the
habit of making excessive database calls to do our work. Collections can help you build simpler, faster
Oracle database applications, the goal of every good PL/SQL developer.
A collection is a group of elements of the same datatype. Each element is identified by
a unique subscript that represents its position in the collection
However, often in your programs you want to represent a group of elements such as
the grades for a class of emps. To support this technique, PL/SQL provides collection
datatypes that work just like arrays available in other third-generation programming
languages.
An Oracle PL/SQL collection is a single-dimensional array; it consists of one
or more elements accessible through an index value.
Why Collections?
Storing elements in a collection can provide a number of advantages. For starters, collections can help
to simplify code. If you need to process a number of items of a similar type, storing these items in a
collection will allow you to loop through each element with ease, referencing each one by an index. In
addition, most languages define collection types that contain built-in methods to operate on the
collection. Probably the biggest advantage a collection can provide is improved application
performance. Developers utilize collections to 'cache' static data that needs to be regularly accessed.
This results in reduced calls to a database.
Collections are used in some of the most important performance optimization features
of PL/SQL, such as
BULK COLLECT. SELECT statements that retrieve multiple rows with a single
fetch, increasing the speed of data retrieval.
FORALL. Inserts, updates, and deletes that use collections to change multiple
rows of data very quickly
Table functions. PL/SQL functions that return collections and can be called in the
FROM clause of a SELECT statement.
You can also use collections to work with lists of data in your program that are not
stored in database tables.
aCollection Concepts and Terminology
Before exploring collections, it is helpful to have a common collections vocabulary that
includes the following terms.
Index value. The location of the data in a collection. Index values are usually integers
but for one type of collection can also be strings.
Element. The data stored at a specific index value in a collection. Elements in a
collection are always of the same type (all of them are strings, dates, or records).
PL/SQL collections are homogeneous.
Sparse. A collection is sparse if there is at least one index value between the lowest
and highest defined index values that is not defined. For example, a sparse collection
has an element assigned to index value 1 and another to index value 10 but nothing in
between. The opposite of a sparse collection is a dense one.
Method. A collection method is a procedure or function that either provides
information about the collection or changes the contents of the collection. Methods
are attached to the collection variable with dot notation (object-oriented syntax), as in
my_collection.FIRST.
Types of Collections
There are now three types of collections to choose from, each with its own set of
characteristics and each best suited to a different circumstance.
PL/SQL Tables
A PL/SQL table is similar to a one-column database table. The rows of a PL/SQL table
are not stored in any predefined order, yet when they are retrieved in a variable, each
row is assigned a consecutive subscript starting at 1,
The two types of PL/SQL tables are associative tables (formerly called index-by tables)
and nested tables.
Associative array. The first type of collection available in PL/SQL, this was originally
called a “PL/SQL table” and can be used only in PL/SQL blocks. Associative arrays can
be sparse or dense and can be indexed by integer or string.
These are single-dimensional, unbounded, sparse (do not need to be filled up
sequentially) collections of homogeneous elements. In the next example I declare such
array, populate it with some data and iterate through the collection.
The other difference between associative arrays and PLSQL nested tables is that nested
tables are initially dense whereas associative arrays are sparse.
An index-by table (also called an associative array) is a set of key-value pairs. Each key
is unique, and is used to locate the corresponding value. The key can be either an
integer or a string.
An index-by table is created using the following syntax. Here we are creating an indexby
table namedtable_name whose keys will be of subscript_type and associated values
will be of element_type
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;
First we declare the table type:
FOR EXAMPLE
DECLARE
TYPE ename_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
_
Next we declare a variable of that type:
Ename_tab ename_type;
As mentioned, the individual elements of a PL/SQL table are referenced using subscript
notation
as follows:
table_name(subscript)
SQL> declare
2 TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;3
phone_nos phone_no_tab;
4 begin
5 phone_nos(1) := '+44 (0) 117 942 2508';
6 dbms_output.put_line('phone_no(1) is '||phone_nos(1));
7 end;
8 /
phone_no(1) is +44 (0) 117 942 2508
As you can see from the above example the elements (cells) of the array are created
automatically by Oracle. Line 3 acts as as the declaration and constructor of the array.
However if we access a cell that doesn't have any data in it, we will generate a
NO_DATA_FOUND exception as in the following example:
SQL> DECLARE2 TYPE phone_no_tab IS IS TABLE OF VARCHAR2(20) INDEX BY
BINARY_INTEGER;3 phone_nos phone_no_tab;4 BEGIN
5 phone_nos (21) := '0117 942 2508';
6 DBMS_OUTPUT.PUT_LINE('phone_no(1) is '||phone_nos(1));
7 END;
8 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6
This technique is demonstrated in the following example:
DECLARE
CURSOR ename_cur IS
SELECT ename
FROM emp
WHERE rownum <= 10;
TYPE ename_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_tab ename_type;
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN ename_cur LOOP
v_counter := v_counter + 1;
ename_tab(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('ename('||v_counter||'): '||
ename_tab(v_counter));
END LOOP;
END;
In This creates an array (table) that is available within our PL/SQL session. Inserting and
retrieving data is done in the same way as with nested tables but there is no need to
initialise the array or extend it. Instead we just access the cells we want, whether we
want to insert data or retrieve data from the array,
Summary
1. Associative arrays are appropriate for relatively small lookup tables where the
collection can be constructed in memory each time a procedure is called or a
package is initialized.
2. They are good for collecting information whose volume is unknown beforehand,
because there is no fixed limit on their size.
3. Their index values are more flexible, because associative array subscripts can be
negative, can be nonsequential, and can use string values instead of numbers
when appropriate.
Nested table. Added in Oracle8 Database, the nested table can be used in PL/SQL
blocks, in SQL statements, and as the datatype of columns in tables. Nested tables can
be sparse but are almost always dense. They can be indexed only by integer. You can
use the MULTISET operator to perform set operations and to perform equality
comparisons on nested tables.
NESTED TABLES
The general syntax for creating a nested table is as follows (the reserved words and
phrases in
brackets are optional):
TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name TYPE_NAME;
Notice that this declaration is very similar to the declaration of an associative array,
except that it has no INDEX BY BINARY_INTEGER clause. As in the case of an associative
array, restrictions
apply to an element_type of a nested table.
It is important to note that a nested table must be initialized before its individual
elements can
be referenced.
This declaration is similar to declaration of an index-by table, but there is no INDEX BY
clause.
A nested table can be stored in a database column and so it could be used for
simplifying SQL operations where you join a single-column table with a larger table. An
associative array cannot be stored in the database.
DECLARE
CURSOR ename_cur IS SELECT ename
FROM emp
WHERE rownum <= 10;
TYPE ename_type IS TABLE OF emp.ename%TYPE;
ename_tab ename_type;
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
ename_tab(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('ename('||v_counter||'): '||
ename_tab(v_counter));
END LOOP;
END;
This example causes the following error:
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 14
This example causes an error because a nested table is automatically NULL when it is
declared. In other words, no individual elements exist yet because the nested table
itself is NULL. For you to reference the individual elements of the nested table, it must
be initialized with the help of a system-defined function called a constructor. The
constructor has the same name as the nested table type.
Solution-----
DECLARE
CURSOR ename_cur IS
SELECT ename
FROM emp
WHERE rownum <= 10;
TYPE ename_type IS TABLE OF emp.ename%TYPE;
ename_tab ename_type := ename_type();
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
ename_tab.EXTEND;
ename_tab(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('ename('||v_counter||'): '||
ename_tab(v_counter));
END LOOP;
END;
DID YOU KNOW?
What is the difference between a NULL collection and an empty collection? If a
collection has not
been initialized, referencing its individual elements causes the following error:
DECLARE
TYPE integer_type IS TABLE OF INTEGER;
integer_tab integer_type;
v_counter integer := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE (integer_tab(v_counter));
END;
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 7
If a collection has been initialized so that it is empty, referencing its individual elements
causes a
different error:
DECLARE
TYPE integer_type IS TABLE OF INTEGER;
integer_tab integer_type := integer_type();
v_counter integer := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE (integer_tab(v_counter));
END;
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 7
COLLECTION METHODS
In the previous examples, you have seen one of the collection methods, EXTEND. A
collection method is a built-in function that is called using dot notation as follows:
collection_name.method_name
The following list explains collection methods that allow you to manipulate or gain
information about a particular collection:
. EXISTS returns TRUE if a specified element exists in a collection. This method can be
used to avoid SUBSCRIPT_OUTSIDE_LIMIT exceptions.
. COUNT returns the total number of elements in a collection.
. EXTEND increases the size of a collection.
. DELETE deletes either all elements, elements in the specified range, or a particular
element from a collection. Note that PL/SQL keeps placeholders of the deleted
elements.
. FIRST and LAST return subscripts of the first and last elements of a collection. Note
that
if the first elements of a nested table are deleted, the FIRST method returns a value
greater
than 1. If elements have been deleted from the middle of a nested table, the LAST
method
returns a value greater than the COUNT method.
. PRIOR and NEXT return subscripts that precede and succeed a specified collection
subscript.
. TRIM removes either one or a specified number of elements from the end of a
collection.
Note that PL/SQL does not keep placeholders for the trimmed elements.
BY THE WAY
EXTEND and TRIM methods cannot be used with index-by tables.
FOR EXAMPLE
DECLARE
TYPE index_by_type IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
index_by_table index_by_type;
TYPE nested_type IS TABLE OF NUMBER;
nested_table nested_type :=
nested_type(1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
BEGIN
-- Populate index by table
FOR i IN 1..10 LOOP
index_by_table(i) := i;
END LOOP;
IF index_by_table.EXISTS(3) THEN
DBMS_OUTPUT.PUT_LINE ('index_by_table(3) =
'||index_by_table(3));
END IF;
-- delete 10th element from a collection
nested_table.DELETE(10);
-- delete elements 1 through 3 from a collection
nested_table.DELETE(1,3);
index_by_table.DELETE(10);
DBMS_OUTPUT.PUT_LINE ('nested_table.COUNT =
'||nested_table.COUNT);
DBMS_OUTPUT.PUT_LINE ('index_by_table.COUNT = '||
index_by_table.COUNT);
DBMS_OUTPUT.PUT_LINE ('nested_table.FIRST =
'||nested_table.FIRST);
DBMS_OUTPUT.PUT_LINE ('nested_table.LAST = '||nested_table.LAST);
DBMS_OUTPUT.PUT_LINE ('index_by_table.FIRST = '||
index_by_table.FIRST);
DBMS_OUTPUT.PUT_LINE ('index_by_table.LAST =
'||index_by_table.LAST);
DBMS_OUTPUT.PUT_LINE ('nested_table.PRIOR(2) = '||
nested_table. PRIOR(2));
DBMS_OUTPUT.PUT_LINE ('nested_table.NEXT(2) = '||
nested_table.NEXT(2));
DBMS_OUTPUT.PUT_LINE ('index_by_table.PRIOR(2) = '||
index_by_table.PRIOR(2));
DBMS_OUTPUT.PUT_LINE ('index_by_table.NEXT(2) = '||
index_by_table.NEXT(2));
-- Trim last two elements
nested_table.TRIM(2);
-- Trim last element
nested_table.TRIM;
DBMS_OUTPUT.PUT_LINE('nested_table.LAST = '||nested_table.LAST);
END;
Consider the output returned by this example:
index_by_table(3) = 3
nested_table.COUNT = 6
index_by_table.COUNT = 9
nested_table.FIRST = 4
nested_table.LAST = 9
index_by_table.FIRST = 1
index_by_table.LAST = 9
nested_table.PRIOR(2) =
nested_table.NEXT(2) = 4
index_by_table.PRIOR(2) = 1
index_by_table.NEXT(2) = 3
nested_table.LAST = 7
PL/SQL procedure successfully completed.
Nested Tables, like the Varray, can be stored in a relational table as well as function as a PL/SQL program
variable. Unlike Varray, nested tables require no size specification. In other words, they are unbound.
How Nested Tables are Stored in Database?
With Nested Tables, a separate database table will store the data. This table is specified following the
'STORE AS' clause. If a database table has more than one Nested Table type, the same storage table
will store data for all the Nested Tables on that parent table. These storage tables contain a column
called NESTED_TABLE_ID that allows the parent table to reference a row's nested table data.
CREATE TYPE dept_tab IS TABLE OF VARCHAR2(50);
/
CREATE TABLE Department (
DEPTNO NUMBER,
SECTION VARCHAR2(30),
dept_name_tab dept_tab)
NESTED TABLE dept_name_tab STORE AS names_table;
/
--Insert a record into Department, with a Nested Table of dept names.
INSERT INTO Department (DEPTNO, SECTION, dept_name_tab)
VALUES (DEPTNO_SEQ.NEXTVAL,'Engineering',
dept_tab('Electronics','Computer', _
'Instrumentation', 'Information technology'));
How to Operate on Individual Elements of Collection?
To operate on collection elements, use the TABLE command. The TABLE command operator informs
Oracle that you want your operations to be directed at the collection, instead of its parent table.
--1.Select all depts from section 'Engineering' that are like %ELCTRO%'.
SELECT column_value FROM TABLE(SELECT dept_name_tab
FROM Department WHERE SECTION = ''Engineering')
WHERE column_value LIKE '%ELECTRO%';
-- Output of the above query
COLUMN_VALUE
------------------------------
ELECTRONICS
ELCTRICAL
--2.Update DPET NAME ‘Production’ to a new value ‘Production sandwitch’.
--This is possible Only with a nested table, Not Possible in Varray!!
UPDATE TABLE(SELECT dept_name_tab
FROM Department WHERE SECTION = 'ENGINEERING')
SET column_value = 'Production'
WHERE column_value = 'Production Sandwitch';
--3.Select all department entries for ENGINEERING SECTION.
SELECT column_value FROM TABLE(SELECT dept_name_tab
FROM Department WHERE SECTION = 'Engineering');
-- Output of the above query
COLUMN_VALUE
------------------------------
MECHANICAL
ELECTRONICS
COMPUTER
PRODUCTION SANDWITCH
Summary
1. Nested tables are appropriate for important data relationships that must be stored
persistently.
2. Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an
item from the end.
3. Nested table data is stored out-of-line in a store table, a system-generated database table
associated with the nested table.
4. The order and subscripts of elements of nested table are not preserved when it is stored in
database.
5. Nested Tables have an advantage over varrays in that they allow for inserts, updates, and
deletes on individual elements.
PL/SQL Array
Varray. Added in Oracle8 Database, the varray (variable-size array) can be used in
PL/SQL blocks, in SQL statements, and as the datatype of columns in tables. Varrays are
always dense and indexed by integer. When a varray type is defined, you must specify
the maximum number of elements allowed in a collection declared with that type.
You will rarely encounter a need for a varray (How many times do you know in advance
the maximum number of elements you will define in your collection?). The associative
array is the most commonly used collection type, but nested tables have some
powerful, unique features (such as MULTISET operators) that can simplify the code you
need to write to use your collection.
a varray is another collection type. This term stands for “variable-size array.” Similar to PL/SQL tables, each
element of a varray is assigned a consecutive subscript starting at 1.
It is important to note that a varray has a maximum size. In other words, a subscript of a varray has a fixed lower
bound equal to 1, and an upper bound that is extensible if such a need arises.
Therefore, a varray can contain a number of elements, varying from 0 (an empty array) to its maximum size. You
will recall that PL/SQL tables do not have a maximum size that must be specified explicitly.
The general syntax for creating a varray is as follows (the reserved words and phrases in square
brackets are optional):
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF
element_type [NOT NULL];
varray_name TYPE_NAME;
example
DECLARE
CURSOR ename_cur IS
SELECT ename FROM emp
WHERE rownum <= 10;
TYPE ename_type IS VARRAY(10) OF emp.ename%TYPE;
ename_varray ename_type := ename_type();
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN ename_cur LOOP
v_counter := v_counter + 1;
ename_varray.EXTEND;
ename_varray(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('ename('||v_counter||'): '||
ename_varray(v_counter));
END LOOP;
END;
The Varray is short for Variable Array. A Varray stores elements of the same type in the
order in which they are added. The number of elements in a Varray must be known at
the time of its declaration. In other words, a Varray has fixed lower and upper bounds,
making it most similar to collection types from other programming languages. Once it is
created and populated, each element can be accessed by a numeric index.
The following statements declare, and then populate, a Varray that will contain 4
elements of the same type as the column name in table Department:
-- Declaration and initialization of VARRAY.
DECLARE
TYPE deptnames IS VARRAY(4) OF Department.name%TYPE;
dept_names deptnames;
BEGIN
dept_names := deptnames ('COMPUTER','MECHANICAL', 'ELCTRICAL');
END;
-- Create Varray
CREATE TYPE depts IS VARRAY (4) OF VARCHAR2(50);
/
--Create table with varray column.
CREATE TABLE Department (
DEPTNO NUMBER,
SECTION VARCHAR2(30),
Dept_names depts);
/
--Insert a new collection into the column on our book_library table.
INSERT INTO Department (DEPTNO, SECTION, Dept_names)
VALUES (DEPTNO_SEQ.NEXTVAL,'Engineering',
depts ('Electronics','Computer', 'Instrumentation', 'Information technology'));
/
-- Retrieve varray from the database.
SELECT section, Dept_names from Department;
NAME DEPT_NAMES
-------------------- ---------------------------------------------
Engineering DEPTS('Electronics','Computer','Instrumentation',
'Information technology')
-- Note: Insertion order of elements of varray retained after retrieval.
Summary
1. Varrays can be added into database tables.
2. The Varray is ideal for storing fixed values that will be processed collectively.
3. It is not possible to perform inserts, updates, and deletes on the individual
elements in a Varray.
4. When a table contains a Varray type, its data is included in-line, with the rest of
the table's data.
5. Varrays are a good choice when the number of elements is known in advance,
and when the elements are usually all accessed in sequence.
6. When stored in the database, varrays retain their ordering and subscripts.
When to Use What?
Varray
1. Use to preserve ordered list
2. Use when working with a fixed set, with a known number of entries
3. Use when you need to store in the database and operate on the Collection as a whole
Nested Table
1. Use when working with an unbounded list that needs to increase dynamically
2. Use when you need to store in the database and operate on elements individually
Associative Array
1. Use when there is no need to store the Collection in the database. Its speed and indexing
flexibility make it ideal for internal application use.
Conclusion
Oracle PL/SQL is not a difficult programming language to learn. However, like all good programming
languages, there are many things we can do to maximize efficiency and minimize complexity.
Considering PL/SQL's power to interact with the database, it can be tempting to simply fall into the
habit of making excessive database calls to do our work. Collections can help you build simpler, faster
Oracle database applications, the goal of every good PL/SQL developer.
No comments:
Post a Comment