Bind variables are used to ease code maintenance and to save memory and processing on the server. When you save memory and processing power, you improve the overall performance of the server. The inner details of how this saves memory has been enumerated in other places on the web. This article is designed to help developers users bind variables when running queries against Postgres Plus Advanced Server.
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 SPL 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;
This works fine technically but violates the rules of modularity in coding. We can parameterize the message 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;
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 nearly indeitical queries to the server 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 (?). EnterpriseDB's SPL uses named variables with a bind for dynamic SQL, as does it's toolset.
Binds in SPL
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".
If I am using EXECUTE IMMEDIATE, I would write a query like this:
DECLARE
v_empno emp.empno%TYPE;
v_query_string VARCHAR2(2001);
v_name emp.ename%TYPE;
v_job emp.job%TYPE;
BEGIN
v_query_string :=
'SELECT ename, job
FROM emp
WHERE empno = :e_id';
v_empno := 7876;
EXECUTE IMMEDIATE v_query_string
INTO v_name, v_job
USING v_empno;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name ||
', Job: ' || v_job );
END;
Example:
Connected to EnterpriseDB 8.3.0.12 (localhost:5444/edb) AS enterprisedb
EDB*Plus: Release 8.3 (Build 14)
Copyright (c) 2008, EnterpriseDB Corporation. All rights reserved.
QL> set serveroutput on
SQL> DECLARE
2 v_empno emp.empno%TYPE;
3 v_query_string VARCHAR2(2001);
4 v_name emp.ename%TYPE; v_job emp.job%TYPE;
5 BEGIN
6 v_query_string :=
7 'SELECT ename, job
8 FROM emp
9 WHERE empno = :e_id';
10 v_empno := 7876;
11 EXECUTE IMMEDIATE v_query_string
12 INTO v_name, v_job
13 USING v_empno;
14 DBMS_OUTPUT.PUT_LINE('Name: ' || v_name ||
15 ', Job: ' || v_job );
16
17 END;
18
19 /
Name: ADAMS, Job: CLERK
EDB-SPL 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. Advanced Server 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 SPL 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 (Advanced Server 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
|