Databases Reference
In-Depth Information
Using bind variables and parsing
We have already discussed bind variables and parsing in the
Using bind variables
recipe
in
Chapter 4
,
Optimizing SQL Code
. In this recipe, we will see another example, using the
same principles applied to a PL/SQL procedure.
How to do it...
The following steps will demonstrate the bind variables using PL/SQL:
1.
Connect to the database as user
SH
:
CONNECT sh@TESTDB/sh
2.
Create a function to calculate the maximum length of data stored in an arbitrary
field with a variable condition on another field:
CREATE FUNCTION CONDITIONAL_COLUMN_LEN(TABLE_NAME IN VARCHAR2,
COLUMN_NAME IN VARCHAR2, COND_FIELD IN VARCHAR2,
COND_VALUE IN VARCHAR2) RETURN NUMBER
IS
L_RESULT NUMBER := 0;
L_STMT VARCHAR2(2000);
BEGIN
L_STMT := 'SELECT MAX(LENGTH(' || COLUMN_NAME ||
')) FROM ' || TABLE_NAME ||
' WHERE ' || COND_FIELD || ' = ' || COND_VALUE;
EXECUTE IMMEDIATE L_STMT INTO L_RESULT;
RETURN L_RESULT;
END;
/
3.
Calculate using the function created in the previous step for the records in the
table
CUSTOMERS
with an ID between 1 and 10000:
SET TIMING ON
DECLARE X NUMBER := 0;
BEGIN
FOR J IN 1..10000 LOOP
X := X + CONDITIONAL_COLUMN_LEN ('CUSTOMERS',
'CUST_FIRST_NAME', 'CUST_ID', J);
