Databases Reference
In-Depth Information
There's more...
In PL/SQL procedures and functions, the use of bind variables is automatic—we can use the
variables declared in the function/procedure directly in the SQL code, without worrying about
assigning values to the placeholders, as in JDBC (Java DataBase Coectivity) programming.
The only exception to this behavior is when we use dynamic SQL statements, as in the
function used in this recipe. We have seen that in such situations bind variables can be
adopted with the
USING
clause of the
EXECUTE
statement.
In our example function, we cannot use bind variables
for
TABLE_NAME
,
COLUMN_NAME
, and
COND_FIELD
parameters, because they are not parameters of our
query. Field and table names in a query cannot be passed
as bind parameters, because the parser needs to know all
the objects involved in the query before the binding phase.
See also
F
We have discussed bind variables in depth in
Chapter 4
,
Optimizing SQL Code
in
the recipe
Using bind variables
Array processing and bulk-collect
In this recipe, we will see how to use the
BULK COLLECT
and
FORALL
statements to speed
up the processing of huge amounts of data in a single statement.
We will also see how to limit the amount of memory used for these statements, to avoid
a decrease in performance due to reduced available memory for other processes.
How to do it...
The following steps will demonstrate array processing:
1.
Connect to the
SH
schema:
CONNECT sh@TESTDB/sh
2.
Create a
MY_CUSTOMERS
table to store the
ID
and
FIRST_NAME
of the customers:
CREATE TABLE sh.MY_CUSTOMERS (
CUST_ID NUMBER,
CUST_FIRST_NAME VARCHAR2(20));
