Using Bind Variables

LewisC’s An Expert’s Guide To Oracle Technology

Ok, I know you you’ve heard of them. Heck, in some cases, you’ve been beaten over the head with them. So why   aren’t you using them?

I’ve heard some people saying that it’s easier to code without bind variables. Really? Concatenating strings,   potentially with embedded quotes and/or varying formats, is easier than using bind variables?

I got an email the other day from a reader who asked to remain anonymous. He said that he read that he should use   bind variables and understood why but wasn’t sure he understood the concept of actually implementing code with   bind variables. He found concatenating a great big string to be easier than figuring out how to use binds.

I think people might be making the issue more complicated than it really is. I am going to try my best to write a   very plain english description of what bind variables are and how to use them.

What are bind variables?

The easiest way to think of a bind variable is to consider it just another variable. Instead of it being a variable   to be used by your application (and whatever language you happen to be using), think of it as a variable to be used   by SQL. Better yet, think of it as a parameter to be used by SQL.

Why do you use parameters in your functions and procedures? Take a look at this very simple PL/SQL procedure:

PROCEDURE start_program IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World');
END;

Now, that procedure works just fine. When the program starts, it calls start_program and it displays a message that   the program is starting. Now we decide to add a message just before the program ends:

PROCEDURE end_program IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Goodbye World');
END;

Great. Works just fine. Kind of stupid to have two versions of basically the same procedure though, isn’t it? We   can parameterize the message (which DBMS_OUTPUT already does but work with me here), so that we can reuse a single   procedure and reduce the maintenance workload we have.

PROCEDURE print_message
  (p_message IN VARCHAR2)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(p_message);
END;

Sweet, now when the program starts, we just call print_message(‘Hello World’); and when the program ends, we   call print_message(‘Goodbye World’);

If you’ve been programming any amount of time, the reasons for doing this are more than obvious. You get a large   maintenance bonus by maintaining fewer lines of code. You might even get a performance bonus (depending on the   language) by using less memory.

So, using a bind variable in a database is like using a parameter to a procedure. Only, instead of you doing the   maintaining, the database does less maintenance. If you send a bunch of queries like:

SELECT last_name, first_name
  FROM employees
  WHERE employee_id = 101;

and

SELECT last_name, first_name
  FROM employees
  WHERE employee_id = 102;

There are two queries in memory. If you select 10000 employees (which a batch program might easily do), that’s a   whole lot queries that are basically duplicates. The database has to keep track of those and that takes time and   memory. The database already has plenty to do keeping track of permissions, users, log files, etc. Why give it more   to do?

The way to write a query with bind variables changes depending on the language and tool. The most common that you   might see are named variables beginning with a colon (:) or a question mark (?). Oracle’s PL/SQL uses named   variables with a bind for dynamic SQL, as does it’s toolset.

Binds in SQL (SQL*Plus or other tools)

In SQL*Plus, you can easily define a variable, set the value and then execute the query:

VARIABLE e_id NUMBER
EXEC :e_id := 101
SELECT last_name, first_name
  FROM employees
  WHERE employee_id = :e_id;

Example:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> VARIABLE e_id NUMBER
SQL> EXEC :e_id := 101

PL/SQL procedure successfully completed.
SQL> SELECT last_name, first_name
  2 FROM employees
  3 WHERE employee_id = :e_id;
LAST_NAME FIRST_NAME
------------------------- --------------------
Kochhar Neena
SQL>

In reality, SQL*Plus scripts are losing favor to procedures and functions. PL/SQL just gives you more power and   removes dependencies on a specific IDE or tool.

Binds in PL/SQL

In general, you don’t need to use bind variables in PL/SQL. PL/SQL binds up your variables for you. The only   times you need to do it manually are when you are manually parsing SQL using “EXECUTE IMMEDIATE” or   DBMS_SQL.

If I am using EXECUTE IMMEDIATE, I would write a query like this:

DECLARE
  v_employee_id employees.employee_id%TYPE;
  v_query_string VARCHAR2(2001);
  v_first_name employees.first_name%TYPE;
  v_last_name employees.last_name%TYPE;
BEGIN
  v_query_string :=
    'SELECT last_name, first_name
     FROM employees
     WHERE employee_id = :e_id';
  v_employee_id := 101;
  EXECUTE IMMEDIATE v_query_string
    INTO v_last_name, v_first_name
    USING v_employee_id;
  DBMS_OUTPUT.PUT_LINE('Last_name: ' || v_last_name ||
            ', First_name: ' || v_first_name );
END;

Example:

Connected to:
  Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set serveroutput on
SQL> DECLARE
2   v_employee_id employees.employee_id%TYPE;
3   v_query_string VARCHAR2(2001);
4
5   v_first_name employees.first_name%TYPE;
6   v_last_name employees.last_name%TYPE;
7 BEGIN
8
9    v_query_string :=
10    'SELECT last_name, first_name
11     FROM employees
12     WHERE employee_id = :e_id';
13
14   v_employee_id := 101;
15
16   EXECUTE IMMEDIATE v_query_string
17     INTO v_last_name, v_first_name
18     USING v_employee_id;
19
20   DBMS_OUTPUT.PUT_LINE('Last_name: ' || v_last_name ||
21           ', First_name: ' || v_first_name );
22
23 END;
24 /
Last_name: Kochhar, First_name: Neena
PL/SQL procedure successfully completed.
SQL>

You could actually rewrite this several different ways but this example shows the gist of it. Notice that the select   list does not include any binds. Oracle knows to send the selected columns out as bind variables.

Also, notice that the name of the bind variable (:e_id) does not need to match the PL/SQL variable (v_employee_id).   If we do think of bind variables as parameters to SQL, that makes sense. SQL only cares about the data coming in and   going back out. The calling program (Oracle in this case) is responsible for correctly mapping inputs and outputs.   The SQL engine just agrees to send the data is the agreed upon order (left to right).

Binds in Java

Java is a bit more verbose but most of this code is connecting to the database. The parts in bold are the important   parts.

import java.sql.*;
import oracle.jdbc.pool.*;
class prog2 {
  public static void main (String args []) throws SQLException
  {
        String url = "jdbc:oracle:oci8:@//192.168.1.7:1521/ORCL";
        OracleDataSource ods = new OracleDataSource();
        ods.setURL(url);
        ods.setUser("hr");
        ods.setPassword("hr");
        Connection conn = ods.getConnection();
PreparedStatement cmd = conn.prepareStatement ("SELECT first_name, last_name FROM employees WHERE employee_id = ?"); 
 cmd.setString(1, "101");
        ResultSet rs = cmd.executeQuery();
        rs.next();
        System.out.println ("Last Name: " +
                      rs.getString(1) +
                      ", First Name: " +
                      rs.getString(2));
        conn.close();
  }
}

Binds in C#

C# is about as verbose as Java but remember that most of this code is C# infrastructure code and creating the   connection. The parts in bold is the pertinent code.

using System;
using System.Data.OracleClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            OracleConnection conn = 
                      new OracleConnection(
                             "User id=hr;Password=hr;Data Source=remoorcl"
                      );
            conn.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;

            String query = "SELECT last_name, first_name " + "FROM employees " + "WHERE employee_id = :e_id";
            cmd.CommandText = query;

            int vEmpId = 101; OracleParameter prm = new OracleParameter(":e_id", vEmpId); cmd.Parameters.Add(prm);

            OracleDataReader dataReader = cmd.ExecuteReader();

            dataReader.Read();

            Console.WriteLine("Last Name: " +
                      dataReader.GetOracleString(0) +
                      ", First Name: " +
                      dataReader.GetOracleString(1));

            conn.Close();
        }
    }
}

Summary

This post got much larger than I intended. Anyway, the important part is that it really is not complicated to use   bind variables and I think it is much less complicated than trying to string together pieces of text and needing to   quote char data and format date data. A bind variable is just a parameter that allows SQL to reuse statements. This   helps performance and reduces memory consumption as well as making your code more maintainable.

LewisC

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.