Optimizing SQL Code - Oracle Database 11gR2 Performance Tuning - page 148

Using bind variables

We have discussed bind variables in the A working example recipe in Chapter 1 , Starting
with Performance Tuning .

In this recipe, it is time to dig deeper into this topic, illustrating the benefits of using bind
variables and testing the result of our efforts with simple examples. We will see examples
on query statements, but the same methodologies and results apply to DML statements.

Getting ready

Follow these steps to prepare the database:

1. Create a package named Chapter4 to test various aspects related to bind variables.

2. Connect to SQL*Plus using the SH schema:

CONNECT sh@TESTDB/sh

3. Create the required package:

CREATE OR REPLACE PACKAGE sh.CHAPTER4 AS
PROCEDURE WORKLOAD_NOBIND;
PROCEDURE WORKLOAD_BIND;
PROCEDURE WORKLOAD_BIND_STATIC;
PROCEDURE TEST_INJECTION(NAME IN
sh.customers.cust_last_name%TYPE);
PROCEDURE TEST_INJECTION2(NAME IN
sh.customers.cust_last_name%TYPE);
END;
/

CREATE OR REPLACE PACKAGE BODY sh.CHAPTER4 AS
PROCEDURE TEST_NOBIND(CUSTID IN sh.customers.cust_id%TYPE)
IS
BEGIN
DECLARE aRow sh.customers%ROWTYPE;
l_stmt VARCHAR2(2000);
BEGIN
l_stmt := 'SELECT * FROM sh.customers s WHERE s.cust_id='
|| TO_CHAR (CUSTID);