Collections 277
10
The key here is that the arguments to the constructor are
dept1
and
dept2
. Both
of those are records of type
department%ROWTYPE
, and so match the element type
of the table. Obviously it’s a bit cumbersome to set things up this way.
To add more entries to a table than those you created with the constructor, you need to
extend the table, as discussed in the following section.
Extending a Nested Table
To extend a nested table so that you can add more entries to it, use the
extend
method.
The
extend
method allows you to add one entry, or several entries. It also allows you to
clone an existing entry one or more times. The syntax for the
extend
method is as fol-
lows.
collection.extend[(entries_to_add[, entry_to_clone])];
In this syntax the parameters are as follows:
•
collection
is the name of the nested table.
•
entries_to_add

17: --Initialize the index into the table.
18: depts_max := 0;
19:
A
NALYSIS
,
S
YNTAX
,
I
NPUT
continues
14 7982 ch10 11/30/99 1:13 PM Page 277
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
20: --Initialize the table by creating one empty entry.
21: depts := dept_table ();
22:
23: FOR dept IN all_depts LOOP
24: depts_max := depts_max + 1;
25: depts.extend;
26: depts(depts_max).dept_id := dept.dept_id;
27: depts(depts_max).dept_name := dept.dept_name;
28: depts(depts_max).no_of_emps := dept.no_of_emps;
29: END LOOP;
30:
31: --Clone the first entry five times.
32: depts.extend(5,1);
33:
34: --Display the results.
35: FOR inx1 IN 1..depts_max+5 LOOP

extend
is made in order to add space for
the new entry. After all the data has been read, another call to
extend
is made in line 32
278 Day 10
L
ISTING
10.4
continued
O
UTPUT
A
NALYSIS
14 7982 ch10 11/30/99 1:13 PM Page 278
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Collections 279
10
to clone the first entry five times. The last
FOR
loop, in lines 35–39, dumps the contents
of the table to the screen (if you are using SQL*Plus) by using the
DBMS_OUTPUT
pack-
age. Sure enough, you can see that the first entry has been replicated five more times at
the end of the table.
Removing Entries from a Nested Table
You can remove entries from a nested table by using the
delete
method, just as you do

L
ISTING
10.5
The
trim
Method
1: DECLARE
2: --Declare a cursor that returns all department records.
3: CURSOR all_depts IS
4: SELECT *
5: FROM department
6: ORDER BY dept_name;
7:
8: --Define a nested table type.
9: TYPE dept_table IS TABLE OF department%ROWTYPE;
10:
11: --Declare a nested table variable to
,
S
YNTAX
,
I
NPUT
continues
14 7982 ch10 11/30/99 1:13 PM Page 279
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
12: --hold the employee records that we read in.
13: depts dept_table;
14: depts_max PLS_INTEGER;
15: inx1 PLS_INTEGER;

46: --Display the new count.
47: DBMS_OUTPUT.PUT_LINE(depts.count);
48:
49: --Display the results.
50: FOR inx1 IN 1..depts_max+5 LOOP
51: IF depts.exists(inx1) THEN
52: DBMS_OUTPUT.PUT_LINE (
53: depts(inx1).dept_id ||
54: ‘ ‘ || depts(inx1).dept_name);
55: END IF;
56: END LOOP;
57:
58: END;
59: /
280 Day 10
L
ISTING
10.5
continued
14 7982 ch10 11/30/99 1:13 PM Page 280
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Collections 281
10
403 BOTTLING
402 DISTILLATION
501 Employee Newsletters
401 FERMENTATION
405 GUTTING
404 SCALE PROCESSING
502 Technical Writing

count
method) from Listing 10.5,
and run it again, the second list of departments will match the first. In other
words, the
exists
method won’t recognize that you deleted some entries.
This is true with Oracle release 8.1.5, and is almost certainly a bug. The
workaround is to invoke the
count
method at least once.
Note
Using Variable-Sized Arrays
Like nested tables, variable-sized arrays or varrays also came into existence with
the release of Oracle8. Var rays are similar to nested tables, but they have a fixed
maximum size. They differ from nested tables in that when you store a varray into a data-
base column, the order of elements is preserved.
N
EW
T
ERM
14 7982 ch10 11/30/99 1:13 PM Page 281
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Declaring and Initializing a Varray
To create a varray, you use the
VARRAY
keyword in a type declaration to create an array
type. Then you can use that type to declare one or more variables. The syntax for declar-
ing a varray type is as follows.
TYPE type_name IS {VARRAY|VARYING ARRAY} (size) OF entry_type [NOT NULL];
In this syntax the parameters are as follows:

9: BEGIN
10: --Initialize the array and create two entries
11: --using the constructor.
12: depts := dept_array (‘Dept One’,’Dept Two’);
13:
14: --Display the contents of the two entries.
15: FOR inx1 IN 1..2 LOOP
16: DBMS_OUTPUT.PUT_LINE(depts(inx1));
17: END LOOP;
18: END;
19: /
282 Day 10
You need the Enterprise Edition of Oracle8i in order to use varrays.
Note
,
S
YNTAX
,
I
NPUT
14 7982 ch10 11/30/99 1:13 PM Page 282
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Collections 283
10
Dept One
Dept Two
Line 3 declares a type that results in a 100-element array of
VARCHAR2(30)
val-
ues. The

11: --Declare a varray variable to
12: --hold the employee records that we read in.
13: depts dept_array;
14: inx1 PLS_INTEGER;
15: inx2 PLS_INTEGER;
16: BEGIN
17: --Initialize the index into the array.
18: inx1 := 0;
19:
20: --Initialize the array.
21: depts := dept_array ();
22:
23: FOR dept IN all_depts LOOP
24: inx1 := inx1 + 1;
25: depts.extend();
O
UTPUT
A
NALYSIS
I
NPUT
continues
14 7982 ch10 11/30/99 1:13 PM Page 283
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
26: depts(inx1).dept_id := dept.dept_id;
27: depts(inx1).dept_name := dept.dept_name;
28: depts(inx1).no_of_emps := dept.no_of_emps;
29: END LOOP;
30:
31: --Display the results.

10.7
continued
A
NALYSIS
N
EW
T
ERM
If you are not using Oracle8i, you won’t be able to execute any of the bulk
binding examples shown in this chapter.
Note
Two new keywords support binding.
BULK COLLECT
is used with
SELECT
statements to
place all the data into a collection.
FORALL
is used with
INSERT
,
UPDATE
, and
DELETE
statements to execute those statements once for each element in a collection.
14 7982 ch10 11/30/99 1:13 PM Page 284
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Collections 285
10
Using

FETCH
statement, like this:
OPEN all_depts;
FETCH all_depts BULK COLLECT INTO dept_ids, dept_names;
CLOSE all_depts;
For some reason, Oracle does not allow you to use
BULK COLLECT
in a collection of
records. Thus, if you are selecting 10 columns, you need to declare 10 collections, one
for each column.
Listing 10.8 shows an example of
BULK COLLECT
being used to load all department
names and IDs into a nested table.
L
ISTING
10.8
An Example Showing the Use of BULK COLLECT
1: DECLARE
2: --Declare a cursor that returns all department records.
3: CURSOR all_depts IS
4: SELECT dept_id, dept_name
5: FROM department
6: ORDER BY dept_name;
7:
8: --Define a nested table type for each column.
9: TYPE dept_id IS TABLE OF department.dept_id%TYPE;
10: TYPE dept_name IS TABLE OF department.dept_name%TYPE;
11:
12: --Declare a nested table variable for each column.

all_depts
cursor declared in lines 3–6 returns two values: the department
ID and name. Lines 9 and 10 declare nested table types for each of these
columns. Corresponding nested table variables are declared in lines 13–14. The
FETCH
statement in line 18 then uses the
BULK COLLECT
keyword to read all the data selected
directly into the arrays. This is much faster than fetching one row at a time using a
PL/SQL loop.
Note that Listing 10.8 contain no call to the nested tables’ constructor methods. The
FETCH
statement takes care of that for you.
The ability to do bulk binds is a great feature. The single annoying thing about it is that
you cannot declare a nested table of
department%rowtype
, and use that as the target.
BULK COLLECT
won’t handle tables of records.
286 Day 10
O
UTPUT
A
NALYSIS
I imagine that some future release of Oracle will remove the restriction
against
BULK COLLECT
loading tables of records. At least I hope that hap-
pens.
Note

UPDATE department
SET no_of_emps := NULL
WHERE dept_id = dept_ids(x);
In this case, index
x
ranges from the first entry in the
dept_id
table to the last. If you
don’t want the statement to apply to all entries in the table, you can specify a different
range. The use of
dept_ids(x)
indicates that this is where you want nested table values
substituted into the SQL statement when it executes.
Listing 10.9 shows a similar
FORALL
statement being used to change all the department
names to uppercase.
L
ISTING
10.9
An Example Showing the Use of FORALL
1: DECLARE
2: --Declare a cursor that returns all department records.
3: CURSOR all_depts IS
4: SELECT dept_id, dept_name
5: FROM department
6: ORDER BY dept_name;
7:
8: --Define a nested table type for each column.
9: TYPE dept_id IS TABLE OF department.dept_id%TYPE;

35: /
Aside from lines 30–33, this listing is almost exactly like Listing 10.8. One line
(line 23) has been added to the
FOR
loop to make each department name upper-
case. The
FORALL
statement writes the new names back to the database by using an
UPDATE
statement.
FORALL
causes a bulk bind to be used, which is much more efficient
than if you had updated each row individually inside of a PL/SQL loop.
288 Day 10
L
ISTING
10.9
continued
A
NALYSIS
When you use
FORALL
, all the collection elements in the specified range must
exist. If they don’t, you get an error.
Note
If you try to use
FORALL
on a range with missing elements, SQL stops executing the state-
ment and raises an error. At this point, you have to decide whether to commit or roll back
the transaction. If you commit, any changes made prior to the single entry that caused the

NO_DATA_FOUND
,for example, by testing the
validity of each entry with the
exists
method before you attempt to access the value the
entry. The following snippet of code shows how this is done:
IF dept_names.EXISTS(10) THEN
ELSE
/* Element 10 does not exist */
...
END IF;
You can avoid subscript errors by careful coding. If you’re working with varray, you
should know how many elements you declared that varray to hold in the first place. If
you’re working with a nested table, and you aren’t sure of the size anymore, you can use
the count method to check and see how large the table is.
Summary
Today you’ve learned how to declare and use records. You have also had an opportunity
to learn about all of PL/SQL’s collection types, including index-by tables, nested tables,
and variable-sized arrays. PL/SQL’s bulk bind feature delivers some significant perfor-
mance improvements. To use bulk binds, you need to be working with data stored in col-
lections. You can then execute SQL statements that are automatically applied to each
entry in a collection.
Q&A
QHow do I choose whether to use a variable-sized array in my code, a nested
table, or an index-by table?
A If you’re dealing with database columns, then you should first base your decision
on the column type. For example, if you’re reading a
VARRAY
column from a data-
base table, don’t read it into a PL/SQL nested table. Read it into a PL/SQL varray.

Records are commonly declared to match table definitions, or to match the
columns returned by a cursor. They simplify code by allowing you to package
related values, such as the columns in a row, into one unit.
Workshop
You can use this to test your comprehension of this chapter and put what you’ve learned
into practice. You’ll find the answers to the quiz and exercises in Appendix A,
“Answers.”
Quiz
1. Name the three collection types PL/SQL supports.
2. What declaration would you use to declare a variable named
emp_name
with a
datatype and size that exactly match the definition of the
employee.emp_name
col-
umn in the database?
3. What declaration would you use to declare a record named
emp
that matches the
definition of a row in the
employee
table?
290 Day 10
14 7982 ch10 11/30/99 1:13 PM Page 290
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Collections 291
10
4. What method can you call on to be sure that a collection element really exists?
5. What must you be sure to do before you can add data to a nested table or to a
varray?

•Use triggers to maintain a history of changes to a record.
•Use the new database and schema event triggers that were introduced
with Oracle8i.
15 7982 ch11 11/30/99 1:12 PM Page 293
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
What Is a Trigger?
A trigger is a PL/SQL block that is associated with a specific event, stored in a database,
and executed whenever that event occurs. Oracle8i now supports four fundamental types
of triggers:
• Data manipulation language (DML) triggers
•Instead-of triggers
• Data definition language (DDL) triggers
• Database event triggers
DML triggers are the traditional
INSERT
,
UPDATE
, and
DELETE
triggers that Oracle has
supported for years. Instead-of triggers were introduced with Oracle8 as a way to make it
possible to update certain types of views. DDL triggers and Database event triggers are
new with Oracle8i.
DML Triggers
DML triggers are the traditional triggers that can be defined on a table, and are executed,
or fired,in response to the following events:
•A row is inserted into a table.
•A row in a table is updated.
•A row in a table is deleted.
It is not possible to define a trigger to fire when a row is selected.

4: DECLARE
5: dup_flag INTEGER;
6: BEGIN
7: --Force all department names to uppercase.
8: :NEW.dept_name := UPPER(:NEW.dept_name);
9: END;
10: /
Line 1 tells Oracle to create this trigger with the name
department_insert_update
and to replace any existing trigger of the same
name if necessary. Line 2 says that it will be fired whenever a new row is inserted into
the department table or whenever a department record is changed. In line 8 there is one
line of code that uses the built-in
UPPER
function to force the department name to upper-
case. Notice the reference to
:NEW
. This is the default alias for the new value of the
record. The alias
:OLD
can be used to refer to the old value of a field before an update
takes effect. Line 3 tells Oracle to fire this trigger once for each row modified. If you
were to issue an
UPDATE
statement to change the names of all departments in the table,
this trigger would be fired for each one of those records.
To demonstrate the effect of this trigger, try issuing the statements shown in Listing 11.2.
L
ISTING
11.2

triggering SQL statement, or by whether or not they fire for each row affected by the
triggering SQL statement. This results in four basic trigger types.
There are two choices when a trigger fires in relation to an SQL statement: either
before or after. Before triggers are executed before the triggering SQL statement.
After triggers are executed following the triggering SQL statement.
A DML trigger is either a row-level trigger or a statement-level trigger. A row-
level trigger executes once for each row affected by the triggering SQL state-
ment, whereas a statement-level trigger is executed only once. Only row-level triggers
have access to the data values in the affected records. Statement-level triggers do not.
This is because SQL is a set-oriented language--SQL statements can affect many or even
all rows in a table. Statement-level triggers are only fired once, so it would not be possi-
ble to resolve a column reference in such a trigger.
The possible combinations of the choices result in the four DML trigger types listed in
Table 11.1.
T
ABLE
11.1
The Four Basic Trigger Types
When Fired Level Description
Before Statement Executed once for the triggering SQL statement
before that statement is executed.
Before Row Executed once for each record affected by the trigger-
ing SQL statement before the record in question is
changed, deleted, or inserted.
After Row Executed once for each record affected by the trigger-
ing SQL statement after the record in question has
been changed, deleted, or inserted.
After Statement Executed once for the triggering SQL statement after
that statement has been executed.
Triggers execute in response to an SQL statement and can be defined for the