Collections and
Bulk Binds
Introduction to Object Types and Records
Collections
Moving from Cursor-Loops to
Collections
Using Collection Methods (Count,
First, Last, etc)
Bulk Binding
Handling and Reporting Exceptions
Multi-Dimensional Arrays
When to use what
Returning Result Sets
Cursor Attributes
Introduction to Object Types and Records
A database object type is very
similar to a CREATE TABLE statement, but it does not create a
"container" for data. Rather it is a "template" for data.
Example:
CREATE TYPE food_t AS OBJECT (
name VARCHAR2(100),
food_group VARCHAR2
(100),
grown_in VARCHAR2
(100) );
DECLARE
-- Create a new object with a
constructor
my_favorite_vegetable_rec
food_t := food_t ('Brussel
Sprouts', 'VEGETABLE', 'Farm,Greenhouse,Backyard');
BEGIN
--Read an attribute value
DBMS_OUTPUT.put_line
(my_favorite_vegetable_rec.name);
--Modify an attribute value
my_favorite_vegetable_rec.food_group
:= 'SATISFACTION';
IF INSTR (my_favorite_vegetable_rec.grown_in,
'yard') > 0 THEN
--Pass an object
as a parameter
order_seeds
(my_favorite_vegetable_rec);
END IF;
END;
A PL/SQL RECORD is a
composite datatype. In contrast to a
scalar datatype like NUMBER, a record is
composed of multiple
pieces of information, called fields. Records can be declared using
relational
tables or explicit cursors as "templates" with the %ROWTYPE
declaration attribute. You can also declare records based on TYPES that you define yourself. Records are very
handy
constructs for PL/SQL developers. The easiest way to define a record is
by
using the %ROWTYPE syntax in your declaration. For example, the
following
statement:
DECLARE
bestseller books%ROWTYPE;
creates a record that has a structure corresponding
to the books table; for every column in the table, there is a field in
the
record with the same name and datatype as
the column.
The %ROWTYPE keyword is especially valuable because the declaration is
guaranteed to match the corresponding schema-level template and is
immune to
schema-level changes in definition of the shape of the table. If we
change the
structure of the books table, all we have to do is recompile the above
code and
bestseller will take on the new structure of that table. A second way
to declare a
record is to define your own RECORD TYPE:
DECLARE
TYPE extra_book_info_t
IS RECORD (
title
books.title%TYPE,
is_bestseller
BOOLEAN,
reviewed_by names_list );
first_book
extra_book_info_t;
Notice
that the user-defined record datatype
above includes
a field (“title”) that is based on the column definition of a database
table, a
field (“is_bestseller”) based on a scalar
data type
(PL/SQL Boolean flag), and a collection (list of names of people who
reviewed
the book). Next,
we can declare a record based on this type (you do not use %ROWTYPE in
this
case, because you are already referencing a type to perform the
declaration).
Once you have declared a record, you can then manipulate the data in
these
fields (or the record as a whole) as you can see below:
DECLARE
bestseller
books%ROWTYPE;
required_reading
books%ROWTYPE;
BEGIN
-- Modify a
field value
bestseller.title
:= 'ORACLE PL/SQL
PROGRAMMING';
-- Copy one
record to another
required_reading
:= bestseller;
END;
Note
that in the above code we have used the structure of the books table to
define
our PL/SQL records, but the assignment to the title field did not in
any way
affect data inside that table. You should also be aware that while you
can
assign one record to another, you couldn’t perform comparisons or
computations
on records. Neither of these statements will compile:
BEGIN
IF
bestseller = required_reading THEN
...
BEGIN
left_to_read :=
bestseller - required_reading;
You
can also pass records as arguments to procedures and functions.
This
technique
allows you to shrink down the size of a parameter list (pass a single
record
instead of a lengthy and cumbersome list of individual values). Here is
an example of a function with a
record in
the parameter list:
CREATE
OR REPLACE PROCEDURE calculate_royalties
( book_in
IN books%ROWTYPE, quarter_end_in
IN DATE )
IS ...
Another Example:
DECLARE
-- Declare a basic table type type
TYPE a_char_data IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
-- Declare a complex record type
TYPE r_data IS RECORD (
ssn VARCHAR2(9) NOT NULL := -1,
name a_char_data, -- Notice the table_type used here
dob DATE );
-- Declare a index by table using the complex record type
TYPE a_multi IS TABLE OF r_data INDEX BY BINARY_INTEGER;
-- Declare a variable using the complex array
v_data a_multi;
BEGIN
-- Set some values
v_data(1).ssn := '123456789';
v_data(1).dob := '01-JAN-1900';
-- Notice the second subscript
v_data(1).name(1) := 'Lewis';
v_data(1).name(2) := 'Joe';
dbms_output.put_line(v_data(1).ssn);
-- Loop through the v_data(1).name table
FOR i IN v_data(1).name.FIRST..v_data(1).name.LAST LOOP
dbms_output.put_line(v_data(1).name(i));
END LOOP;
END;
/
Let me walk you through exactly what this example is doing:
- In the declare section:
- First we declare our very basic array of VARCHAR2(10).
- Next we declare a record type with an embedded index by
table.
- So now we have a ragged record type. A single record with a
dimensional name column.
- Then we declare a new table based on the complex record type.
This creates an array of ragged records.
- And finally, we create the complex variable.
- Executeable section:
- We populated the ssn and dob columns of the first record of
the v_data variable.
- Next we populated the first and second rows of the name table
in the first row of the v_data variable.
- Finally, we displayed the ssn of the first row of the v_data
variable and then looped through the name table of the first row of the
v_data variable.
There are three flavors of collection types, one, which is only
available in PL/SQL (associative arrays), and two others (nested tables
and varrays) that are shared between both
languages.
The following scenarios generally indicate a need for collections:
Repeated access to the same, static database
information. If, during execution of your program (or during a session,
since your collection can be declared as package data and thereby
persist with all its rows for the entire session), you need to read the
same data more than once, load it into a collection. Multiple scannings
of the collection will be much more efficient than multiple executions
of a SQL query.
Management of program-only lists. You may
build and manipulate lists of data that exist only within your program,
never touching a database table. In this case, collections-and,
specifically, associative arrays-will be the way to go.
1-Associative Arrays (ALSO CALLED
PL/SQL Tables (oRACLE 7) OR INDEX_BY_TABLES (ORACLE 8)
Probably the most familiar collection type is the PL/SQL index-by
table (called associative arrays since 9i Release 2). The code block below is a typical use of an
associative array.
DECLARE
--This
was the ONLY option before 9.2.0
TYPE num_array
IS TABLE OF NUMBER
INDEX BY
BINARY_INTEGER;
powers num_array;
BEGIN
FOR i
IN 1..100 LOOP
powers(i) := power(2, i);
END LOOP;
END;
This creates an array of unlimited size (up to your OS and DB version
limitations) of NUMBER which is indexed by a BINARY_INTEGER datatype.
The index is just the subscript and BINARY_INTEGER is just a numeric
data type. An index by table does NOT have to be initialized.
In previous versions of Oracle, the only wat to declare an associative
arrays was using the "index by binary_integer", that meant that the
only index allowed on an associative array was the row number. There
were several drawbacks to this scheme. First, it required reliance on
an outmoded datatype, since BINARY_INTEGER has since been superseded by
PLS_INTEGER as a more efficient integer datatype. Second, it meant that
if the list being manipulated had a non-integer key, the developer had
to write some very complex and/or compute-intensive logic (namely, to
perform full collection scans or create alternative indexes via
hashing) to take advantage of collections.
These restrictions have now been lifted. You can now declare
associative arrays to be indexed by BINARY_INTEGER, PLS_INTEGER,
VARCHAR2 and even anchored declarations of those types using %TYPE. All
of the following statements are valid declarations of associative array
types with integer indexes:
DECLARE
TYPE array_t1 IS TABLE OF NUMBER
INDEX BY
BINARY_INTEGER;
TYPE array_t2 IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
TYPE array_t3 IS TABLE OF NUMBER
INDEX BY POSITIVE;
TYPE array_t4 IS TABLE OF NUMBER
INDEX BY NATURAL;
BEGIN
...
END;
You can even use a user-defined subtype, thus:
DECLARE
SUBTYPE my_integer
IS PLS_INTEGER NOT NULL;
TYPE array_t4 IS
TABLE OF NUMBER
INDEX BY my_integer;
--Another
Example
create or replace TYPE array_t
IS TABLE OF number INDEX BY varchar2(64);
--New in 9i
A welcome addition
in 9i Release 2 is the ability to
use the VARCHAR2 data type as an index key.
Similar to associative arrays in Perl
and Awk, or Hashtables
in Java, this
data type enables the look-up of a value using a VARCHAR2.
Many existing PL/SQL routines can benefit
from this capability. Here are some examples
of such declarations:
DECLARE
TYPE array_t1 IS
TABLE OF NUMBER
INDEX BY VARCHAR2(64);
TYPE array_t3 IS
TABLE OF NUMBER
INDEX BY VARCHAR2(32767);
TYPE array_t4 IS
TABLE OF NUMBER
INDEX BY employee.last_name%TYPE;
BEGIN
.....
Example:
DECLARE
idx VARCHAR2(64);
TYPE population_type IS
TABLE OF NUMBER INDEX BY idx%TYPE;
country_population
population_type;
continent_population
population_type;
howmany PLS_INTEGER;
BEGIN
country_population('Norway') := 4000000;
country_population('Greenland') := 100000;
country_population('Iceland') := 750000;
continent_population('Australia') := 30000000;
continent_population('Antarctica') := 1000;
continent_population('Antarctica') := 1001;
howmany :=
country_population.COUNT;
DBMS_OUTPUT.PUT_LINE
('COUNT = ' || howmany);
idx :=
continent_population.FIRST;
DBMS_OUTPUT.PUT_LINE
('FIRST row = ' || idx);
DBMS_OUTPUT.PUT_LINE
('FIRST value = ' || continent_population(idx));
idx :=
continent_population.LAST;
DBMS_OUTPUT.PUT_LINE
('LAST row = ' || idx);
DBMS_OUTPUT.PUT_LINE
('LAST value = ' || continent_population(idx));
idx :=
country_population.FIRST;
WHILE idx IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE ( idx || ' = ' || country_population(idx) );
idx :=
country_population.NEXT(idx);
END LOOP;
END;
Example:
Populate an index by varchar2 table with 500,000 tuples and a database
table with the same
data.
drop table table1;
create table table1 (
idx varchar2(4000),
data varchar2(4000) );
create or replace procedure P is
--This is Using the new feature of Oracle 9i index by varchar2(4000)
type text_tab_t is table of varchar2(4000) index by varchar2(4000);
v_text_tab text_tab_t;
v_index table1.idx%type;
v_data table1.data%type;
t_start integer;
t_end integer;
c_nof_rows constant pls_integer := 500000;
procedure Show_Elapsed_Time ( p_start in number, p_end in number ) is
begin
Dbms_Output.Put_Line ( Lpad ( To_Char ( ((p_end-p_start)/100), 9999.9 ), 15 ) || ' seconds' );
end Show_Elapsed_Time;
begin
t_start := Dbms_Utility.Get_Time;
for j in 1..c_nof_rows
loop
v_index := To_Char(j) || ' suffix';
insert into table1( idx, data )
values (v_index, 'data for ' || v_index );
end loop;
execute immediate ( 'create unique index t_idx on table1( idx )' );
for j in ( select idx, data from t )
loop
v_index := j.idx; v_data := j.data;
end loop;
t_end := Dbms_Utility.Get_Time;
Show_Elapsed_Time ( t_start, t_end );
t_start := Dbms_Utility.Get_Time;
for j in 1..c_nof_rows
loop
v_index := To_Char(j) || ' suffix';
v_text_tab( v_index ) := 'data for ' || v_index;
end loop;
v_index := v_text_tab.First;
while v_index is not null
loop
v_index := v_text_tab.Next(v_index);
end loop;
t_end := Dbms_Utility.Get_Time;
Show_Elapsed_Time ( t_start, t_end );
end P;
/
exec p
For Database Table
Method = 297.2
seconds
For index by varchar2 Table
Method = 17.55 seconds
2-Nested
Tables
Unlike associative arrays, the nested table data type is also a
SQL data type. A nested table is similar
to an associative array in that there is no maximum size to the array
however
prior to assigning a new element to a nested table a PL/SQL program
needs to
explicitly extend the size before adding new elements.
A nested table is an object type and
therefore needs to first be initialized with a constructor before being
used. For many PL/SQL programs, these
two added requirements make associative arrays a better choice
for
basic array functionality in code, however we will see that with nested
tables
a whole new set of options will open up that would not be possible with
associative arrays.
DECLARE
TYPE nest_tab_t
IS TABLE OF NUMBER;
--initialization of this type
nt nest_tab_t := nest_tab_t();
BEGIN
FOR i
IN 1..100 LOOP
nt.EXTEND;
nt(i)
:= i;
END LOOP;
END;
Note that the variable was initialized to an empty nested table
using the constructor for its type.
Also, the example shows how the nested table EXTEND method is
used to
allocate a new element to the array so that it can be assigned to in
the next
statement.
3-Varrays
The last collection type to be discussed is the varray. Like nested
tables, varrays can be both PL/SQL types
and SQL
types and therefore can take advantage of the many of the features
listed
above. The main differences with varrays
in PL/SQL is that their maximum size must be specified when the type is
declared. It should be noted that both
varray types as well as nested table types can define the column type
of a SQL
table. In the former case, if the size
of the varray type is 4000 bytes or less,
it can be
stored in-line in the data block along with other column values. In contrast,
the column data for a nested table is
stored in a system managed child table making it very similar to a
normal
parent/child table relationship. Because
they have a shared type, PL/SQL nested table or varray variables can be
used to
atomically insert values into tables that use them. Apart
from this capability, varrays
are of less interest than nested tables to the
PL/SQL developer because they have the restriction of an upper bound
and most
anything one can do in code with a varray,
one can do
with a nested table. Example:
declare
type v is varying array(50) of varchar2(30);
Examples
for nested tables and varrays
set
serveroutput on
declare
type nestab is table of
number;
type varr is varying
array(50) of varchar2(30);
someNumbers nestab;
someNames varr;
i
binary_integer;
begin
someNumbers :=
tn(10,4,6,9,2,5);
someNames :=
v('Fred','Joe','Caesar');
i:=3;
if someNumbers(i) = 6 then
dbms_output.put_line ('someNumbers(' || i || ') = 6');
else
dbms_output.put_line ('someNumbers(' || i || ') <> 6');
end if;
someNumbers(i) := 7;
if someNumbers(i) = 6 then
dbms_output.put_line ('someNumbers(' || i || ') = 6');
else
dbms_output.put_line ('someNumbers(' || i || ') <> 6');
end if;
someNumbers.delete(1); --delete element 1
someNumbers.delete(4); --delete element 4
--More
Ways to delete -- If an element doesn't exist no
exception rais
--
someNumbers.delete(20,30); --delete elements 20 through 30
--someNumbers.delete;
--delete entire PL/SQL Table
i := someNumbers.first();
while i is not null loop
dbms_output.put_line (i || ': ' || someNumbers(i));
i :=
someNumbers.next(i);
end loop;
end;
/
Table
Functions
To do this, the PL/SQL code executes a SQL statement passing the
local nested table variable to the server.
There are two special functions necessary to achieve this
functionality. The TABLE function tells
the server to bind over the values of the nested table,
perform the requested SQL operation and return the results back as if
the
variable was a SQL table in the database.
The CAST function is an explicit directive to the server to map
the
variable to the SQL type that was defined globally in the previous step. With this capability, many new operations become
possible.. For
example, one
can take a nested table of objects that have been created in code and
send them
to the server for ordering or aggregation.
Almost any SQL operation is possible. For example a nested table
can be
joined with other SQL tables in the database.
The next example shows a simple ordering of an array by the
second
field.
DECLARE
eml_dmo_nt email_demo_nt_t := email_demo_nt_t();
BEGIN
-- Some logic that
populates the nested table …
eml_dmo_nt.EXTEND(3);
eml_dmo_nt(1) := email_demo_obj_t(45,
3, '23');
eml_dmo_nt(2) := email_demo_obj_t(22,
3, '41');
eml_dmo_nt(3) := email_demo_obj_t(18,
7, 'over_100k');
-- Process the data in assending order of email id.
FOR r IN (SELECT * FROM TABLE(CAST(eml_dmo_nt AS email_demo_nt_t))
ORDER BY 1)
LOOP
dbms_output.put_line(r.email_id
|| ' ' || r.demo_id);
END LOOP;
END;
Oracle9i
Release 2 Record Improvements
Oracle now
let you do
any of the following with static SQL (i.e. SQL statements that are
fully
specified at the time your code is compiled):
- Use
collections of records as the target in a BULK COLLECT INTO statement.
You no
longer need to fetch into a series of individual variables.
- Insert
a row into a table using a record. You no longer need to list the
individual
fields in the record separately, matching them up with the columns in
the
table.
- Update
a row in a table using a record. You can now take advantage of the
special SET
ROW syntax to update the entire row with the contents of a record with
a
minimum of typing.
- Use
a record to retrieve information from the RETURNING clause of an
UPDATE, DELETE
or INSERT.
Some
restrictions do remain at Version 9.2.0 for records in SQL, including:
• You
cannot use the EXECUTE IMMEDIATE statement (Native Dynamic SQL) in
connection
with record-based INSERT, UPDATE or DELETE statements. (It is supported
for
SELECT, as stated above)
• With
DELETE and UPDATE...RETURNING, the column-list must be written
explicitly in
the SQL statement.
• In
the bulk syntax case, you cannot reference fields of the in-bind table
of
records elsewhere in the SQL statement (especially in the where clause).
Let's
explore this great new functionality
with a
series of examples, all of which will rely on the employees table, defined in
the hr schema
that is installed in the seed database. The script to create this
schema is demo/schema/human_resources/hr_cre.sql
under
the Oracle Home directory.
The
samples also rely on common features such as an index-by-*_integer
table,
records of
employees%rowtype and a procedure
to show the
rows of such a table. These are implemented in the Emp_Utl package.
Full example moving from
Cursor-Loops to Collections
The original method for loading one table into another table was:
CREATE TABLE t1 AS SELECT *
FROM all_objects
WHERE 1=0;
CREATE OR REPLACE PROCEDURE
test_proc IS
BEGIN
FOR x IN
(SELECT * FROM all_objects)
LOOP
INSERT INTO t1
(owner, object_name, subobject_name, object_id,
data_object_id, object_type, created, last_ddl_time,
timestamp, status, temporary, generated, secondary)
VALUES
(x.owner, x.object_name, x.subobject_name, x.object_id,
x.data_object_id, x.object_type, x.created,
x.last_ddl_time, x.timestamp, x.status, x.temporary,
x.generated, x.secondary);
END LOOP;
COMMIT;
END test_proc;
Elapsed: 00:00:20.02
Ignoring the initial table creation script, the test_proc procedure
does three things:
1. Declares a cursor that points to the resultset from SELECT * FROM
ALL_OBJECTS
2. Starts at record one, and inserts into the t1 table the columns from
the first row in the cursor (her eis the big problem a lot of calls
between PL/SQL and SQL)
3. Then, it loops back and gets the next row of data, until all rows
from the cursor have been retrieved.
The data is then committed, and the procedure ends.
The following solution uses a nested
table to hold the data from the
ALL_OBJECTS table, and performs BULK COLLECT to load all
of the source tables' data into the nested table.
truncate table t1;
CREATE OR REPLACE
PROCEDURE
fast_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE My_ARRAY IS
TABLE OF all_objects%ROWTYPE;
l_data My_ARRAY;
CURSOR c IS SELECT *
FROM all_objects;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
FORALL i IN 1..l_data.COUNT
INSERT INTO t1 VALUES l_data(i);
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
END fast_proc;
/
Elapsed: 00:00:09.06
The next example is a variation on this, that does much the
same thing with slightly more compact code
truncate table t1;
create or replace
procedure
fast_proc2
is
TYPE My_ARRAY
IS TABLE OF
all_objects%ROWTYPE;
l_data My_ARRAY;
begin
--Here I put all the rows in memory on this collection
select * BULK
COLLECT INTO l_data
from ALL_OBJECTS;
-- Now I work with that collection
FORALL x in l_data.First..l_data.Last
INSERT INTO t1 VALUES l_data(x) ;
end;
/
Elapsed: 00:00:09.27
Using
Collection Methods
The following collection methods help generalize code, make collections
easier to use, and make your applications easier to maintain:
- EXISTS
- COUNT
- LIMIT
- FIRST and LAST
- PRIOR and NEXT
- EXTEND
- TRIM
- DELETE
A collection method is a built-in function or procedure that
operates on collections and is called using dot notation. The syntax
follows: collection_name.method_name[(parameters)]
Collection methods cannot be called from SQL statements. Also,
EXTEND and TRIM cannot be used with associative arrays. EXISTS, COUNT,
LIMIT, FIRST, LAST, PRIOR, and NEXT are functions; EXTEND, TRIM, and
DELETE are procedures. EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE
take parameters corresponding to collection subscripts, which are
usually integers but can also be strings for associative arrays.
Only EXISTS can be applied to atomically null collections. If you apply
another method to such collections, PL/SQL raises COLLECTION_IS_NULL.
Bulk Binding
Bulk binding improves performance by reducing the
context switches
between the pl/sql and sql engines for execution of SQL statements.
Bulk Collect causes the sql engine to bulk-bind the entire output
collection before sending it to the pl/sql engine. An
‘in-bind’ is when we pass a value from a
program to the SQL engine ,
often
either to constrain on a column or to specify a value for a DML
statement
Commonly,
in-binds are only of interest because they
are essential for SQL statements to be sharable. When
DBA’s talk of the importance of
applications using ‘bind variables’ it is in the context of in-binds
since, in
applications that use dynamic SQL, using literals instead of bind
variables
causes each SQL statement to be parsed. This
is a critical consideration for overall database performance |
An
‘out-bind’
occurs when values are passed from the SQL engine
back to the host language. Oracle
makes
the distinction between values that are passed back via a RETURNING
clause in
SQL as opposed to when values are passed back by during a fetch
operation but
for the purpose of this paper I will refer to both of these operations
as
out-binds.
When processing a cursor, application developers can
choose to
either fetch back values one-at-a-time or returned in a batch operation
which
will bind back many rows to the host application in a single operation. Before Oracle 8i values
being bound out into PL/SQL host variables had to be fetched one at a
time. The following CURSOR FOR-LOOP
construct is a familiar one.
--Archive historical data
DECLARE
CURSOR sales_cur (p_customer_id NUMBER) IS
SELECT * FROM sales
WHERE customer_id = p_customer_id;
v_customer_id NUMBER := 1234;
BEGIN
FOR rec IN sales_cur (v_customer_id) LOOP
INSERT INTO sales_hist(customer_id, detail_id,
process_date)
VALUES (v_customer_id,
rec.sales_id, sysdate);
END LOOP;
END;
--Elapsed: 00:00:44.02 for
360,000 records
--The insert was executed 360352
times
In a CURSOR FOR-LOOP, a record variable is implicitly
declared
that matches the column list of the cursor. On
each iteration of the loop, the execution context is switched
from
the PL/SQL engine to the SQL engine, performing
an out-bind of the column values into the record
variable
once for each loop iteration. Likewise,
an in-bind for the insert statement will occur once on each iteration. Although stored PL/SQL code has the advantage
over other host languages of keeping this interaction within the same
process,
the context switching between the SQL engine and the PL/SQL engine is
relatively expensive making the above code very inefficient.In addition, the cursor is defined as
SELECT
* instead of just selecting from the columns to be utilized which is
also
inefficient. Whether the code references
a column or not, Oracle will have to fetch and bind over all of the
columns in
the select list, slowing down code execution |
A better way to perform the above task would be to
utilize bulk
binding, introduced in Oracle 8i, for both the fetch and the
insert
statements. We have two new PL/SQL
operators to accomplish this. The BULK
COLLECT (for SELECT and FETCH) statement is used to specify bulk out-binds; while the FORALL (for INSERT, UPDATE and
DELETE) statement is used to provide
bulk
in-binds for DML statements.
According to the documentation, FORALL
is defined as:
"The keyword FORALL instructs the PL/SQL engine to bulk-bind input
collections before sending them to the SQL engine. Although the FORALL
statement contains an iteration scheme, it is not a FOR loop. Its
syntax follows:
FORALL
index IN lower_bound..upper_bound
INSERT/UPDATE/DELETE Statements;
and BULK COLLECT is explained as;
"The keywords BULK COLLECT tell the SQL engine to bulk-bind output
collections before returning them to the PL/SQL engine. You can use
these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO
clauses. Here is the syntax:
... BULK COLLECT INTO
collection_name[, collection_name] ..."
The index can be referenced only within the FORALL statement and
only as a collection subscript. The SQL statement must be an INSERT,
UPDATE, or DELETE statement that references collection elements. And,
the bounds must specify a valid range of consecutive index numbers. The
SQL engine executes the SQL statement once for each index number in the
range."
So the previous query could be re-defined as:
--Archive historical data
DECLARE
-- Here I defined a type based on a field of one table
TYPE sales_typ IS TABLE OF sales.sales_id%TYPE
INDEX BY
BINARY_INTEGER;
--Define sales_ids as the sales_typ type
sales_ids sales_t;
v_customer_id NUMBER := 1234;
max_rows CONSTANT NUMBER :=
100;
CURSOR sales_cur (p_customer_id NUMBER) IS
SELECT sales_id
FROM sales
WHERE customer_id = p_customer_id;
BEGIN
OPEN sales_cur(v_customer_id);
LOOP
EXIT WHEN sales_cur%NOTFOUND;
FETCH sales_cur BULK COLLECT INTO sales_ids LIMIT
max_rows;
FORALL i IN 1..sales_ids.COUNT
INSERT INTO sales_hist (customer_id,
detail_id, process_date)
VALUES(v_customer_id,
sales_ids(i), sysdate);
END LOOP;
CLOSE sales_cur;
END;
--Elapsed: 00:00:08.02 for
360,000 records
--The insert was executed 72
times only
In this example, the fetch statement returns
with the sales_ids
array populated with all of the values fetched for the current
iteration, with
the maximum number of rows fetched set to 10,000. Using
this method, only a single context
switch is required for the SELECT statement to populate the sales_ids
array and
another switch to bind all of the fetched values to the INSERT
statements. Note also that the FORALL
statement is not a
looping construct – the array of values is given over in batch to the
SQL
engine for binding and execution. This
second implementation will run at approximately 15 times the speed of
the
first, illustrating the importance of efficient binding in data driven
code.
One potential issue
with the bulk binding technique is the use of memory by the PL/SQL
array
variables. When a BULK COLLECT statement
returns, all of the fetched values are stored in the target array. If the number of values returned is very
large, this type of operation could lead to memory issues on the
database
server. The memory consumed by PL/SQL
variables is private memory, allocated dynamically from the operating
system. In dedicated server mode it
would be the server process created for the current session that
allocates
memory. In the case where such
allocation becomes extreme, either the host will become memory bound or
the
dedicated server process will reach a size where it tries to allocate
beyond
its addressing limits, normally 2 GB on many platforms.
In either case the server processes call to malloc()
will fail raising an ORA-04030 out of
process memory error.
To prevent this
possibility when loading anything larger than a small reference table, use the optional LIMIT ROWS operator to control the
‘batch size’ of each
BULK
COLLECT operation. In the code example
below the cursor will iterate though batches of 100 rows fetching in
the
values and inserting 100 rows. Do not go
over 500. On the
final iteration, the cursor will fetch the remaining balance. Placement of the EXIT WHEN clause should be
before the FETCH statement or the last, incomplete batch will not be
processed.
The above example is only for the purpose of
demonstrating coding
techniques. The same logic could be
accomplished totally in SQL using an INSERT AS SELECT statement. Doing this in pure SQL would be faster
yet
since no host binds or procedural execution would be required at all.
Example
Using the BULK COLLECT clause to collect few rows into a collection
DECLARE
TYPE NumTab IS TABLE
OF emp.empno%TYPE;
TYPE NameTab IS
TABLE OF emp.ename%TYPE;
enums
NumTab; -- no need to initialize
names NameTab;
BEGIN
SELECT empno, ename
BULK COLLECT INTO enums, names FROM emp;
FOR i in
enums.FIRST..enums.LAST LOOP
DBMS_OUTPUT.PUT_LINE(enums(i) || ' ' || names(i));
END LOOP;
END;
/
--The following is an example for the FETCH INTO clause
DECLARE
TYPE NameTab IS
TABLE OF emp.ename%TYPE;
TYPE SalTab IS TABLE
OF emp.sal%TYPE;
names
NameTab;
sals
SalTab;
CURSOR c1 IS SELECT
ename, sal FROM emp;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO
names, sals;
FOR i IN
names.FIRST..names.LAST LOOP
DBMS_OUTPUT.PUT_LINE(names(i) || ' ' || sals(i));
END
LOOP;
CLOSE c1;
END;
/
Oracle9i Release 2 also allows updates using record definitions
by using the ROW keyword:
DECLARE
TYPE test1_tab IS TABLE OF
test1%ROWTYPE;
t_tab test1_tab :=
test1_tab();
BEGIN
FOR i IN 1 .. 10000 LOOP
t_tab.extend;
t_tab(t_tab.last).id
:= i;
t_tab(t_tab.last).description := 'Description: ' || To_Char(i);
END LOOP;
FOR i IN t_tab.first ..
t_tab.last LOOP
UPDATE test1
SET ROW = t_tab(i)
WHERE id = t_tab(i).id;
END LOOP;
COMMIT;
END;
/
SELECT
with RECORD bind
As
we noted earlier, while it was possible before 9.2.0 to SELECT
INTO a record, you could not BULK SELECT INTO a collection of records.
The
resulting code was often very tedious to write and not as efficient as
would be
desired. Suppose, for example, that we would like to retrieve
all employees hired before June
25, 1997, and then give
them all big, fat raises.
With
Oracle9i Release
2, our program becomes much shorter, intuitive and maintainable. What
you see
below is all we need to write to take advantage of BULK COLLECT to
populate a
single associative array of records:
DECLARE
v_emprecs emp_util.emprec_tab_t;
CURSOR cur IS SELECT
* FROM
employees
WHERE hire_date < '25-JUN-97';
BEGIN
OPEN cur;
FETCH cur BULK
COLLECT INTO v_emprecs LIMIT 10;
CLOSE cur;
emp_util.give_raise
(v_emprecs);
END;
[Note:
the clause limit 10 is
equivalent to where rownum
<= 10.]
Even
more wonderful, we can now combine BULK COLLECT fetches into records
with
NATIVE DYNAMIC SQL. Here is an
example, in which we give raises to
employees
for a specific schema:
CREATE
OR REPLACE PROCEDURE give_raise
(schema_in IN VARCHAR2)
IS
v_emprecs
emp_util.emprec_tab_t;
cur
SYS_REFCURSOR;
BEGIN
OPEN cur
FOR 'SELECT
* FROM ' || schema_in
|| '.employees' || 'WHERE
hire_date < :date_limit' USING '25-JUN-97';
FETCH cur BULK
COLLECT INTO v_emprecs LIMIT 10;
CLOSE cur;
emp_util.give_raise
( schema_in, v_emprecs);
END;
SYS_REFCURSOR
is a pre-defined weak REF CURSOR type that was added to the PL/SQL
language in
Oracle9i Release
1.
INSERT
with RECORD
bind
For years,
one of our
favorite
"wish-we-had’s"
was the ability to insert a row into a table using a record. Prior to
Oracle9i Release 2, if we
had put our
data into a record, it would then be necessary to "explode" the
record into its individual fields when performing the insert, as in:
DECLARE
v_emprec
employees%ROWTYPE
:= emp_util.get_one_row;
BEGIN
INSERT INTO employees_retired
( employee_id, last_name,
...)
VALUES ( v_emprec.employee_id,
v_emprec.last_name, ...);
END;
This
is very difficult coding. In Oracle9i Release
2, we can now take advance of simple, intuitive and compact syntax to
bind an
entire record to a row in an insert. This is shown below:
DECLARE
v_emprec
employees%rowtype
:= Emp_Util.Get_One_Row;
BEGIN
INSERT INTO employees_retired
VALUES v_emprec;
END;
Notice
that we do not put the record inside parentheses. You are,
unfortunately, not
able to use this technique with Native Dynamic SQL. You can, on the
other hand,
insert using a record in the highly efficient FORALL statement. This
technique
is valuable when you are inserting a large number of rows.
Take
a look at the following example. The following table explains the
interesting parts of the retire_them_now procedure
.
Line(s) Description
3-4
Declare an exception, enabling us
to trap by
name an error that occurs during the bulk insert
5-7
Declare an associative array,
each row of
which contains a record having the same structure as the employees
table.
9 – 14 Load up the array with the
information for all
employees who are over 40 years of age
15-18 The turbo-charged insert
mechanism, FORALL, that includes a
clause to allow FORALL to continue past errors and references a record
(the
specified row in the array)
20-26 Typical code you would write to
trap any error that was raised
during the bulk insert and display or deal with each error individually.
Bulk INSERTing
with a record.
--------------------------------
CREATE OR REPLACE PROCEDURE
retire_them_now
IS
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
TYPE employees_t IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
retirees employees_t;
BEGIN
FOR rec IN (SELECT *
FROM employees
WHERE hire_date < ADD_MONTHS (SYSDATE, -1 * 18 * 40))
LOOP
retirees (SQL%ROWCOUNT) := rec;
END LOOP;
FORALL indx IN retirees.FIRST .. retirees.LAST
SAVE EXCEPTIONS
INSERT INTO employees
VALUES retirees (indx);
EXCEPTION
WHEN bulk_errors
THEN
FOR j IN 1 ..
SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE ( 'Error from element #' ||
TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' ||
SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
END LOOP;
END;
UPDATE SET ROW with
RECORD bind
Oracle9i Release 2 now
gives you an
easy and powerful way to update an entire row in a table from a record:
the SET
ROW clause. The ROW keyword
is functionally equivalent to *.
It is most useful when the source of the row is one table and the
target is a
different table with the same column specification, for example in a
scenario
where rows in an application table are updated once or many times and
may
eventually be deleted, and where the latest state of each row
(including when
it has been deleted) must be reflected in an audit table. (Ideally we’d
use
MERGE with a RECORD bind, but this isn’t supported yet.).
The
new syntax for the Static SQL, single row case is obvious and compact:
DECLARE
v_emprec employees%ROWTYPE
:= emp_util.get_one_row;
BEGIN
v_emprec.salary
:= v_emprec.salary * 1.2;
UPDATE employees_2 SET ROW = v_emprec
WHERE employee_id = v_emprec.employee_id;
END;
Prior
to Oracle9i Release
2, this same functionality would require listing the columns
explicitly.
DELETE
and UPDATE with RETURNING with RECORD bind
You
can also take advantage of rows when using the RETURNING clause in both
DELETEs and UPDATEs.
The
RETURNING clause allows you to
retrieve and return information that is
processed in the DML statement without using a separate, subsequent
query.
Record-based functionality for RETURNING means that you can return
multiple
pieces of information into a record, rather than individual variables.
Example:
RETURNING into a record from a
DELETE
statement.
-----------------------------------------------------------
DECLARE
v_emprec
employees%ROWTYPE;
BEGIN
DELETE FROM
employees
WHERE employee_id = 100
RETURNING employee_id, first_name,
last_name, email, phone_number,
hire_date,
job_id,
salary, commission_pct, manager_id,
department_id
INTO v_emprec;
emp_util.show_one
(v_emprec);
END;
You can
also retrieve less than a full row of information by relying on
programmer-defined record types, as this next example shows:
DECLARE
TYPE
key_info_rt IS RECORD (
id NUMBER,
nm VARCHAR2 (100) );
v_emprec key_info_rt;
BEGIN
DELETE FROM employees
WHERE employee_id = 100
RETURNING employee_id, first_name
INTO v_emprec;
...
END;
Next, suppose
that we execute a DELETE
or
UPDATE that modifies more than one row. In this case, we can use the
RETURNING
clause to obtain information from each of the individual rows modified
by using
BULK COLLECT to populate a collection of records! Example:
RETURNING multiple rows of
information
from an UPDATE statement.
------------------------------------------------------------------------------
DECLARE
v_emprecs
emp_util.emprec_tab_t;
BEGIN
UPDATE employees
SET salary = salary * 1.1
WHERE hire_date < = '25-JUN-97'
RETURNING employee_id, first_name,
last_name, email, phone_number,
hire_date,
job_id,
salary, commission_pct,
manager_id, department_id
BULK
COLLECT INTO v_emprecs;
emp_util.show_all
(v_emprecs);
END;
Bulk Binding in Native
Dynamic SQL
Let's look at the following example:
BEGIN
SELECT employee_id
BULK
COLLECT INTO employee_ids
FROM
employees
WHERE salary
< 3000;
...
END;
What if you want to execute this same query (and then process the
rows) for one of any number of different employee tables, segregated by
location?
In Oracle 9i, we can take the preceding query and transform it into a
dynamic SQL query that will handle this additional complexity. There
are many application implementation situations that require dynamic
SQL.
CREATE OR REPLACE PROCEDURE
process_employees
(loc_in IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE
'SELECT
employee_id FROM ' ||
loc_in || '_employees'
BULK
COLLECT INTO employee_ids;
...
END;
In-Binding
Both the EXECUTE IMMEDIATE
and FORALL (for bulk DML
operations) offer a USING
clause to bind variable values into the SQL statement. Let's follow the
progression of explicit row-from-row processing to bulk binding to bulk
binding in native dynamic DML to see how the USING clause is deployed.
We start with this kind of explicit FOR loop in our Oracle7 and Oracle8
code base:
FOR indx IN
employee_ids.FIRST .. employee_ids.LAST
LOOP
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = employee_ids (indx);
END LOOP;
Then, with Oracle8i, we get rid
of most of the context switches by moving to FORALL:
FORALL indx
IN
employee_ids.FIRST .. employee_ids.LAST
UPDATE
employees
SET salary = salary * 1.1
WHERE employee_id = employee_ids (indx);
And that handles all of our needs-unless, once again, we need or
would like to perform this same operation on different tables, based on
location (or for any other kind of dynamic SQL situation). In this
case, we can combine FORALL with EXECUTE IMMEDIATE, with these
wonderful results:
CREATE OR
REPLACE PROCEDURE upd_employees (
loc_in
IN VARCHAR2,
employees_in IN employees_t )
IS
BEGIN
FORALL indx in
employees_in.first..employees_in.last
EXECUTE IMMEDIATE
'UPDATE ' || loc_in || ' employees SET salary =
salary*1.1'
|| ' WHERE employee_id = :the_id'
USING employee_in (indx);
END;
Notice that in the USING clause, we must include both the name of
the collection and the subscript for a single row using the same FORALL
loop index variable.
Out-Binding
Let's again follow the progression from individual row updates to bulk
bind relying on BULK COLLECT INTO to retrieve information, and finally
the dynamic approach possible in Oracle 9i.
Oracle8 enhanced DML capabilities by providing support for the
RETURNING clause. Shown in the following FOR loop, it allows us to
obtain information (in this case, the updated salary) from the DML
statement itself (thereby avoiding a separate and expensive query).
BEGIN
FOR indx IN
employee_ids.FIRST .. employee_ids.LAST
LOOP
UPDATE employees SET salary =
salary * 1.1
WHERE employee_id = employee_ids (indx)
RETURNING salary
INTO salaries (indx);
END LOOP;
END;
Starting with Oracle8i, we can take advantage of FORALL to improve
performance dramatically:
BEGIN
FORALL indx IN employee_ids.FIRST .. employee_ids.LAST
UPDATE employees SET salary =
salary * 1.1
WHERE employee_id = employee_ids (indx)
RETURNING salary
BULK COLLECT INTO salaries;
END;
There's one seemingly odd aspect of this code you should remember:
Inside the DML statement, any reference to the collection that drives
the FORALL statement must be subscripted as in:
WHERE employee_id = employee_ids (indx)
In the RETURNING clause, however, you BULK COLLECT INTO the
collection and not a single subscripted row of the collection.
That's all well and good, but what if (not to sound like a broken
record) we want to execute this same update for any of the employee
tables for different locations? Time to go to NDS and, with Oracle 9i
only, also employ a RETURNING BULK COLLECT clause:
CREATE OR
REPLACE PROCEDURE upd_employees (loc_in IN VARCHAR2, employees_in IN
employees_t)
IS
my_salaries salaries_t;
BEGIN
FORALL indx in employees_in.first.. employees_in.last
EXECUTE IMMEDIATE 'UPDATE '|| loc_in
|| ' employees SET salary = salary*1.1'
|| ' WHERE employee_id = :the_id
RETURNING salary INTO :salaries'
USING employee_in (indx)
RETURNING BULK COLLECT INTO
my_salaries;
END;
Handling and Reporting
Exceptions
In Oracle8i, FORALL was wonderful for rapid processing of bulk DML
statements. One problem with it, however, is that you lose some of the
granularity in exception handling that you help with row-by-row
processing. Suppose, for example, that we want to load a whole bunch of
words into a vocabulary table. We can do it very efficiently as follows:
BEGIN
FORALL indx IN words.FIRST ..
words.LAST
INSERT INTO vocabulary text)
VALUES (words (indx));
END;
If, however, an error occurred in any single INSERT, the entire
FORALL statement would fail. With Oracle
9i, you can now do this with both static
and dynamic FORALL SQL statements, by taking advantage of the SAVE
EXCEPTIONS clause.
FORALL indx
IN words.first..words.last
SAVE
EXCEPTIONS
INSERT
INTO vocabulary(text)
VALUES ( words(indx) );
Use of SAVE EXCEPTIONS allows the FORALL to continue through all the
rows indicated by the collection; it "saves up" the exceptions as it
encounters them. This saving step begs the obvious question: How can
you, the developer, get information about the errors that were "saved"?
By taking advantage of the new SQL%BULK_COLLECTIONS pseudo-collection,
as demonstrated in the code shown in the following example:
Example Using the SQL%BULK_COLLECTIONS pseudo-collection.
DECLARE
bulk_errors EXCEPTION;
PRAGMA
EXCEPTION_INIT (bulk_errors, -24381);
BEGIN
FORALL
indx IN words.FIRST .. words.LAST SAVE
EXCEPTIONS
INSERT INTO t (text) VALUES (words
(indx));
EXCEPTION
WHEN
bulk_errors THEN
--For each error found, try to identify the cause of that error
FOR j IN 1 ..
SQL%BULK_EXCEPTIONS.COUNT
LOOP
Dbms_Output.Put_Line
('Iteration Number ' || sql%bulk_exceptions(j).error_index);
Dbms_Output.Put_Line ('Error
' || Sqlerrm(sql%bulk_exceptions(j).error_code*-1));
--Detecting Unique Constraint Violation
if
substr(Sqlerrm(SQL%BULK_EXCEPTIONS(J).ERROR_CODE * -1),1,9) =
'ORA-00001' then
v_RowsDuplicated := v_RowsDuplicated + 1;
else
Dbms_Output.Put_Line ('Other type of Error on Issuedata Import');
end if;
END LOOP;
v_newerrors := SQL%BULK_EXCEPTIONS.COUNT;
v_errors := v_errors + v_newerrors;
dbms_output.put_line('Total Errors= ' ||
to_char(v_errors));
END;
Each row of this pseudo-collection is a record consisting of two
fields: ERROR_INDEX and ERROR_CODE. The ERROR_INDEX shows which index
in the original bulk-load collection causes the failure. ERROR_CODE is
the error number encountered.
You must both use the SAVE EXCEPTIONS construct and handle the
BULK_ERRORS exception to get the intended benefit (that is, that all
non-erroring rows are inserted).
Another new feature that has been provided in Oracle 9i,
Release 1 is the long awaited capability of multi-dimensional arrays,
which
Oracle has implemented as collections of collections.
Multi-dimensional arrays have been around in
most programming languages for a long time and are now available in
PL/SQL. Technically, all collection
types support only a single dimension, however by now allowing a
collection
element to become a collection, one has the effectively the same data
structure. The following code shows the
way to declare and reference a two-dimensional array of numbers.
DECLARE
TYPE
element
IS TABLE OF NUMBER INDEX BY
BINARY_INTEGER;
TYPE twoDimensional
IS TABLE OF element INDEX BY
BINARY_INTEGER;
twoD twoDimensional;
BEGIN
twoD(1)(1) := 123;
twoD(1)(2) := 456;
END;
At first one would think that, while an interesting capability,
it has no potential impact on performance but it will be shown later in
this
paper how the combination of this capability along with the use of
packaged
variables can open up the door to dramatically speeding up PL/SQL code.
Package
Variables
PL/SQL packages offer a number of advantages over stand-alone
code or anonymous blocks. While some
of
the advantages come from the familiar ability to organize code into
logical
collections of related procedures and functions, an often ignored
aspect is the
use of package-level variables and package initialization sections.
A package variable is essentially a variable that is declared
globally, typically at the top of the package body outside of any
procedure
definition. Once set, a package variable
will maintain its state for the life of the session, as opposed to
variables
local to procedure definitions that only exist for the duration of the
procedure call. Every package body
implementation can optionally include a block of code at the end of the
specification referred to as the initialization section.
This code will run only once – when the
package is first referenced and is normally used to initialize any
package
variables that are used.
The use of package variables is a powerful technique to speed up
SQL statements that has been used for many years. Consider
a procedure that is called
repeatedly in a busy OLTP database that inserts a row into a table. One of the values passed to the procedure is
a key that is used to first lookup another value, which in turn, is
used in the
insert. Most PL/SQL code will first
execute a select statement for the row of interest, binding the value
to a
local variable that will then be used on the subsequent insert
statement. A unique indexed table look-up
is relatively
quick but if an application is being driven hard, the cost can be more
than one
would expect. Referencing a
pre-initialized array value is approximately 20 times faster than an
index
based table look-up, making this technique a real time saver for
intensive operations.
Using package variables, the lookup can be avoided entirely by
first initializing an array with the desired values.
For example, in a marketing application a
procedure is passed a zip code as an argument and must perform an
insert into a
fact table that requires the city name as a denormalized
value in the table. Assume also that
there is a lookup table, with a numeric zip code as the primary key as
well as
the city and state information that the zip code maps to.
Code to avoid all of the look-ups would look
like this.
CREATE OR REPLACE PACKAGE
BODY direct_mkt
AS
TYPE zip_array
IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
zip_deref zip_array;
PROCEDURE do_insert(f_name VARCHAR2, l_name VARCHAR2,
zip NUMBER)IS
BEGIN
INSERT INTO user_data (f_nm, l_nm, city_nm)
VALUES (f_name, l_name, zip_deref(zip));
COMMIT;
END;
--
Package
initialization section.
BEGIN
FOR rec
IN (SELECT zip_code, city FROM dma)
LOOP
zip_deref(rec.zip_code)
:= rec.city;
END LOOP;
END;
Until Oracle 9i Release 2, this technique couldn’t be used
if the look-up key was non-numeric or composite, but now with the
combination
of VARCHAR2 associative arrays and multi-dimensional arrays, it can be
extended
to almost any look-up table of a reasonable size. For
example consider a table of individuals
mapped to email addresses that has as it’s
primary
key, a composite index of numeric user id and an email address that
they are
associated with. The following code
shows how to implement this.
CREATE OR REPLACE PACKAGE
BODY email_push AS
TYPE email_array IS TABLE OF VARCHAR2(30)
INDEX BY VARCHAR2(30);
TYPE usermail_array
IS TABLE OF email_array INDEX BY
BINARY_INTEGER;
user_emails usermail_array;
FUNCTION lookup(p_userid NUMBER, p_email VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN user_emails(p_userid)(p_email);
END;
BEGIN
FOR rec
IN (SELECT user_id, email, l_name
FROM user_emails) LOOP
user_emails(rec.user_id)(rec.email) := rec.l_name;
END LOOP;
END;
When to use what
If you're new to PL/SQL collections, you may have a fair understanding
of their mechanics by this point, but are uncertain when to use a
particular type.The following table summarizes each collection's
capabilities.
Has
Ability To
|
Varray
|
Nested
|
Associative
Array
|
be indexed by non-integer
|
No
|
No
|
Yes
|
preserve element order
|
Yes
|
No
|
No
|
be stored in database
|
Yes
|
Yes
|
No
|
have elements selected
indidually in database
|
Yes
|
Yes
|
--
|
have elements updated indidually
in database
|
Yes
|
Yes
|
--
|
In addition, the following bullet points can be referred
to when deciding what collection best suits a particular solution.
Varray
- Use to preserve ordered list
- Use when working with a fixed set, with a known number
of entries
- Use when you need to store in the database and operate
on the Collection as a whole
Nested Table
- Use when working with an unbounded list that needs to
increase dynamically
- Use when you need to store in the database and operate
on elements individually
Associative Array
- 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.
There
are three general approaches for returning results sets: returning
cursors,
returning
collections, or using table functions.
1-Cursors
Variables
One of the best ways to isolate an application from SQL
dependencies is to write a package of PL/SQL functions that return the
REF
CURSOR type to the calling programs written in other host languages
such as
Java or C. Cursor variables can be
either weakly typed, which are more
flexible, or
strongly typed, which provide greater type safety.
Of course, the application
must know the number and data types of
the returned columns as well as their semantics in order to use the
data, but
it can be totally isolated from the way the data is stored. The following function returns a weakly
typed cursor using the new 9i type SYS_REFCURSOR.
FUNCTION email_cur RETURN sys_refcursor IS
rc
sys_refcursor;
BEGIN
OPEN rc FOR
SELECT * FROM
email;
RETURN rc;
END;
An application can call the function and bind the returned open
cursor to a local cursor variable. The
application then iterates through the result set as if it had been
defined and
opened locally.
2-Returning
Collections
Another approach to returning result sets is to write a function
that explicitly returns a PL/SQL collection type. The
method has been around for years but it
is really best suited for use by calling PL/SQL programs.
Also, since there are no predefined collection
types in PL/SQL, the returned type must either be
declared in a shared package header or be a SQL type declared globally
in the
database. A function that returns a
shared collection type is shown below.
The type, email_demo_nt_t, was
defined earlier
in this paper.
FUNCTION get_email_demo(p_email_id
NUMBER) RETURN email_demo_nt_t IS
eml_dmo email_demo_nt_t;
BEGIN
SELECT email_demo_obj_t(email_id, demo_id, value)
BULK COLLECT INTO eml_dmo
FROM email_demographic
WHERE email_id
= p_email_id;
/* Apply some business logic and return
the result set. */
RETURN eml_dmo;
END;
Note that when the BULK COLLECT feature is used, it is not
necessary to initialize or extend a nested table because Oracle does it
automatically. But it is necessary to
call the constructor function for the object type in the query itself
to be
able to fetch scalar column values into the nested table of objects. This would not be necessary if fetching from
a column of that object type.
3-Table
Functions
Most client programs however, don’t really want to deal with
trying to bind to a PL/SQL user defined type; instead, they want a
cursor. The TABLE function provides a way
to take a
function like the one above and return its results to the caller
directly as a
cursor. Recall that the TABLE function
takes a variable of a globally defined collection type as an argument,
therefore a function with a return type of the same collection type,
like the
one above, can be used as an argument to the TABLE function as well. Without modifying the above procedure, a
program can return its output collection as a cursor using the
following
syntax. Views can be wrapped around this
kind of SQL statement to make life easier for a calling application.
SELECT * FROM TABLE( CAST(
get_email_demo(45) AS email_demo_nt_t ));
Pipelined
Table Functions
While that approach works, it is really only appropriate for
smaller result sets of perhaps a few thousand rows.
When the function executes to populate the
result set, the data is buffered in the local variable of the procedure. Only after the procedure has finished
executing, will the rows be returned to the calling application. Memory to store the buffered data is
dynamically allocated from the operating system by the server process
executing
the procedure If the result set was very
large,
operating system memory could become depleted.
Pipelined table functions are an Oracle 9i
facility that address this issue by providing a mechanism to
stream the
values from the function back to the calling application while the
function is
executing. A small amount of data
remains buffered in the function’s address space so that result sets
can be sent
back in batches, which is faster than row-by-row processing. This is a far more scalable design for this
functionality since the operating system memory footprint is
independent of the
size of the result set.
To utilize this feature, the function must be declared as
PIPELINED and collection objects must be returned one at a time via a
new
function called PIPE ROW. The function
contains a RETURN statement without arguments that is used to terminate
the
cursor. The function can now be
rewritten to take advantage of pipelining.
FUNCTION get_email_demo RETURN email_demo_nt_t PIPELINED IS
CURSOR email_demo_cur IS
SELECT email_demo_obj_t(email_id, demo_id, value)
FROM email_demographic;
eml_dmo_nt email_demo_nt_t;
BEGIN
OPEN email_demo_cur;
LOOP
FETCH email_demo_cur BULK COLLECT INTO eml_dmo_nt
LIMIT 1000;
EXIT WHEN email_demo_cur%NOTFOUND;
FOR i IN 1..eml_dmo_nt.COUNT LOOP
/* Apply some business logic on the
object here, and return a row. */
PIPE ROW (eml_dmo_nt(i));
END LOOP;
END LOOP;
RETURN;
END;
Note that while the return type of the function is still the
collection type, the local variable being assigned is the object type. In this example, the fetch is performed using
the BULK COLLECTfeature.
The documents illustrate the much slower
row-by-row fetch. Since the signature of
the procedure has not been changed, only the implementation, it can be
called
the same way as the previous table function using the TABLE and CAST
functions.
Cursor
Attributes
More information can be found HERE
Specific for BULK and FORALL:
-
For
FORALL, %FOUND and %NOTFOUND reflect the overall results, not the
results of any individual statement, including the last (this
contradicts Oracle documentation). In other words, if any one of the
statements executed in the FORALL modified at least one row, %FOUND
returns TRUE and %NOTFOUND returns FALSE.
-
For
FORALL, %ISOPEN always returns FALSE because the cursor is closed when
the FORALL statement terminates.
-
For
FORALL, %ROWCOUNT returns the total number of rows affected by all the
FORALL statements executed, not simply the last statement.
-
For
BULK COLLECT, %FOUND and %NOTFOUND always return NULL and %ISOPEN
returns FALSE because the BULK COLLECT has completed the fetching and
closed the cursor. %ROWCOUNT always returns NULL, since this attribute
is only relevant for DML statements.
-
The nth row in this
pseudo index-by table stores the
number of rows processed by the n th
execution of the DML operation in the FORALL statement. If no rows are
processed, then the value in %BULK_ROWCOUNT is set to 0.
The %BULK_ROWCOUNT attribute is a handy device, but it
is also quite limited. Keep the following in mind:
-
Even though it looks like an index-by table, you
cannot apply any methods to it.
-
%BULK_ROWCOUNT
cannot be assigned to other collections. Also, it cannot
be passed as a parameter to subprograms.
-
The
only rows defined for this pseudo index-by table are the same rows
defined in the collection referenced in the FORALL statement.