Use Bind Variables

The Oracle database software caches SQL statements issued; the cached statement is then reused if an identical statement is issued later. When a statement is reused the execution time is reduced. There s a catch, however: the SQL statement must be absolutely identical in order for the cached statement to be reused. This means that

All characters must be the same.
All letters must be of the same case.
The use of spaces in the statement must be the same.

If you need to supply different column values in a statement then you can use bind variables instead of literal column values. You ll see examples that clarify these ideas next .

Non-Identical SQL Statements

In this section, you ll see some non-identical SQL statements. The following examples retrieve products #1 and #2 using separate non-identical SELECT statements:

 SELECT * FROM products WHERE product_id = 1; SELECT * FROM products WHERE product_id = 2;

These statements are not identical, because the value 1 is used in the first statement but the value 2 is used in the second.

The following non-identical statements have spaces in different positions :

 SELECT * FROM products WHERE product_id = 1; SELECT * FROM products WHERE product_id = 1;

The following non-identical statements use a different case for some characters:

 select * from products where product_id = 1; SELECT * FROM products WHERE product_id = 1;

Now that you ve seen some non-identical statements, let s take a look at defining an identical statement using a bind variable.

Defining Identical SQL Statements Using a Bind Variable

You can ensure a statement is identical by using bind variables to represent column values. You create a bind variable using the VARIABLE command. For example, the following command creates a variable named product_id_bv of type NUMBER :

 VARIABLE product_id_bv NUMBER

Note	You can use the types shown in Table A-1 of Appendix A to define the type of a bind variable.

You can reference a bind variable in a SQL or PL/SQL statement by specifying a colon followed by the variable name . The following example shows an anonymous PL/SQL block that sets product_id_bv to 1:

 BEGIN  :product_id_bv := 1; END; /

The following query uses product_id_bv to specify the product_id column value in the WHERE clause. Because product_id_bv was set to 1 earlier, the query retrieves the details of product #1:

  SELECT * FROM products WHERE product_id = :product_id_bv;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- ------------------------------ DESCRIPTION PRICE -------------------------------------------------- ----------  1 1 Modern Science A description of modern science 19.95

The next example sets product_id_bv to 2 and repeats the query:

  BEGIN   :product_id_bv := 2;   END;   /   SELECT * FROM products WHERE product_id = :product_id_bv;  PRODUCT_ID PRODUCT_TYPE_ID NAME ---------- --------------- ------------------------------ DESCRIPTION PRICE -------------------------------------------------- ----------  2 1 Chemistry Introduction to Chemistry 30

Because the query used in this example is identical to the previous query, the cached query is reused and there s an improvement in performance.

Tip	You should typically use bind variables if you re performing the same query many times. Also, in the example the bind variables are session specific and need to be reset if the session is lost.

Listing and Printing Bind Variables

You list bind variables in SQL*Plus using the VARIABLE command. For example:

  VARIABLE  variable product_id_bv datatype NUMBER

You display the value of a bind variable in SQL*Plus using the PRINT command. For example:

  PRINT product_id_bv  PRODUCT_ID_BV -------------  2

Using a Bind Variable to Store a Value Returned by a PL/SQL Function

You can also use a bind variable to store returned values from a PL/SQL function. The following example creates a bind variable named average_product_price_bv and stores the result returned by the function average_product_price() (this function was described in Chapter 11 and calculates the average product price for the supplied product_type_id ):

  VARIABLE average_product_price_bv NUMBER   BEGIN   :average_product_price_bv := average_product_price(1);   END;   /   PRINT average_product_price_bv  AVERAGE_PRODUCT_PRICE_BV ------------------------  24.975

Using a Bind Variable to Store Rows from a REFCURSOR

You can also use a bind variable to store returned values from a REFCURSOR , which can contain a list of rows. The following example creates a bind variable named products_refcursor_bv and stores the result returned by the function product_package.get_products_ref_cursor() (described in Chapter 11 and returns rows from the products table in a REFCURSOR ):

  VARIABLE products_refcursor_bv REFCURSOR   BEGIN   :products_refcursor_bv := product_package.get_products_ref_cursor();   END;   /   PRINT products_refcursor_bv  PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  1 Modern Science 19.95  2 Chemistry 30  3 Supernova 25.99  4 Tank War 13.95  5 Z Files 49.99  6 2412: The Return 14.95  7 Space Force 9 13.49  8 From Another Planet 12.99  9 Classical Music 10.99  10 Pop 3 15.99  11 Creative Yell 14.99 PRODUCT_ID NAME PRICE ---------- ------------------------------ ----------  12 My Front Line 13.49