SQL & PL/SQL

PL/SQL 101 : Substitution vs. Bind Variables

Author: @"BluShadow"
Last Updated: 27/05/2015

Introduction

We frequently get posts on the community where either people don't understand the difference between a substitution variable and a bind variable, or people fail to understand why they can't use the "&" substitution variables in their PL/SQL procedures and functions to prompt for input at run time. This article will hopefully help clarify in your mind what the differences are so that you can understand where and when to use these.

1. Substitution Variables

The clue here is in the name... "substitution". It relates to values being substituted into the code before it is submitted to the database.

These substitutions are carried out by the interface being used. In this example we're going to use SQL*Plus as our interface...

So let's take a bit of code with substitution variables:

create or replace function myfn return varchar2 is
  v_dname varchar2(20);
begin
  select dname
  into   v_dname
  from   dept
  where  deptno = &p_deptno;
  return v_dname;
end;

Now when this code is submitted...

SQL> /

SQL*Plus, parses the code itself, and sees the "&" indicating a substitution variable.
SQL*Plus, then prompts for a value for that variable, which we enter...

Enter value for p_deptno: 20
old   7:   where  deptno = &p_deptno;
new   7:   where  deptno = 20;

... and it reports back that it has substituted the &p_deptno variable for the value 20, actually showing us the whole line of code with it's value.

This code is then submitted to the database. So if we look at what code has been created on the database we see...

SQL> select dbms_metadata.get_ddl('FUNCTION', 'MYFN', USER) from dual;
DBMS_METADATA.GET_DDL('FUNCTION','MYFN',USER)
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "SCOTT"."MYFN" return varchar2 is
  v_dname varchar2(20);
begin
  select dname
  into   v_dname
  from   dept
  where  deptno = 20;
  return v_dname;
end;

The database itself knows nothing about any substitution variable... it just has some code, fixed with the value we supplied to SQL*Plus when we compiled it.

The only way we can change that value is by recompiling the code again, and substituting a new value for it.

Also, with substitution variables we don't necessarily have to use them just for 'values' (though that it typically what they're used for)... we can use them to substitute any part of the code/text that we are supplying to be compiled.. e.g.

create or replace function myfn(x in number, y in number) return number is
begin
  return &what_do_you_want_to_return;
end;
/
Enter value for what_do_you_want_to_return: y*power(x,2)
old   3:   return &what_do_you_want_to_return;
new   3:   return y*power(x,2);
Function created.
SQL> select dbms_metadata.get_ddl('FUNCTION', 'MYFN', USER) from dual;
DBMS_METADATA.GET_DDL('FUNCTION','MYFN',USER)
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "SCOTT"."MYFN" (x in number, y in number) return number is
begin
  return y*power(x,2);
end;

It really does substitute the substitution variable, with whatever text you supply.

2. Bind Variables

Bind variables are a completely difference concept to substitution variables.

Bind variables typically relate to SQL queries (they can be used in dynamic PL/SQL code, but that's not good practice!), and are a placeholder for values within the query. Unlike substitution variables, these are not prompted for when you come to compile the code.

Now there are various ways of supplying bind variables, and I'll use a couple of examples, but there are more (such as binding when creating queries via the DBMS_SQL package etc.)

In the following example:

create or replace function myfn(p_deptno in number) return varchar2 is
  v_dname varchar2(20);
  v_sql   varchar2(32767);
begin
  v_sql := 'select dname from dept where deptno = :1';
  execute immediate v_sql into v_dname using p_deptno;
  return v_dname;
end;
/
Function created.

The ":1" is the bind variable in the query.

If you examine queries running in the database you will typically see bind variables represented as :1, :2, :3 and so on, though it could be anything preceded by a ":" such as :A, :B, :C, :X, :FRED, :SOMETHING etc.

When the query is passed to the SQL engine (in this case by the EXECUTE IMMEDIATE statement), the query is parsed and optimised and the best execution plan determined. It doesn't need to know what that value is yet to determine the best plan. Then when the query is actually executed, the value that has been bound in (in this case with the USING part of the execute immediate statement) is used within the execution of the query to fetch the required data.

The advantage of using bind variables is that, if the same query is executed multiple times with different values being bound in, then the same execution plan is used because the query itself hasn't actually changed (so no hard parsing and determining the best plan has to be performed, saving time and resources).

Another example of using bind variable is this:

create or replace function myfn(p_deptno in number) return varchar2 is
  v_dname varchar2(20);
begin
  select dname
  into   v_dname
  from   dept
  where deptno = p_deptno;
  return v_dname;
end;
/
Function created.

Now, this isn't immediately obvious, but what we have here is the ability of the PL language to seamlessly integrate SQL within it (giving us PL/SQL). It looks as though we just have an SQL statement in our code, but in reality, the PL engine parses the query and supplies the query to the SQL engine with a bind variable placeholder for where the PL variable (parameter p_deptno in this case) is within it. So the SQL engine will get a query like...

select dname
from   dept
where  deptno = :1

and then the PL engine will handle the binding of the value (p_deptno) into that query when it executes it, as well as dealing with the returning value being put INTO the PL variable v_dname. Again, the SQL supplied to the SQL engine can be optimised and re-used by code because it isn't hard coded with values.

So, here, the binding of values is implicit because the PL engine is removing the need for us to have to code them explicitly.

The other advantage of using bind variables is that you don't have to worry about the datatypes.

Going back to our dynamic SQL example, we often we see people creating code such as this (actually we see them posting it as it's not working for them, because they don't understand bind variables and datatypes)...

create or replace function myfn(p_hiredate in date) return number is
  v_empno number;
  v_sql   varchar2(32767);
begin
  v_sql := 'select empno from emp where hiredate = to_date('''||to_char(p_hiredate,'DD/MM/YYYY')||''',''DD/MM/YYYY'')';
  execute immediate v_sql into v_empno;
  return v_empno;
end;
/
Function created.

The developer is trying to concatenate in a date or varchar variable with the appropriate single quotes and formatting required to make the SQL make sense. Not only does that prevent the SQL explain plan from being re-used with different values, but it makes the code hard to maintain and get right in the first place (as well as leaving things open to SQL injection in some cases)

But, with bind variable, that's not necessary... simply doing...

create or replace function myfn(p_hiredate in date) return number is
  v_empno number;
  v_sql   varchar2(32767);
begin
  v_sql := 'select empno from emp where hiredate = :1';
  execute immediate v_sql into v_empno using p_hiredate;
  return v_empno;
end;
/
Function created.

... is all that is needed.

The SQL engine knows that it is expecting a DATE datatype for the value because of what it's being compared against, and the USING statement is supplying a value of DATE datatype. There's no need to mess around with date formats or multiple quotes etc. it just simply works. The same applies with other datatypes.

3. Hard vs. Soft Parsing - The benefits of Binding

As a final demonstration for this article, I'll demonstrate what I've talked about above; how using bind variable correctly will allow the SQL engine to re-use the execution plans it's already worked out for a query, even if the values being bound in change.

Off-screen, I've created a quick procedure called "show_sqls" on my database which basically queries the v$sqltext view to display what SQL plans are stored in the database. I won't detail the actual procedure here, you can easily search the web for v$sqltext and find plenty of examples. All my procedure is going to show is the ID and Hash Value of the SQL statement and the execution plan, and the SQL text itself...

Firstly then, let's create a PL/SQL procedure that doesn't bind variables properly into a dynamic SQL statement (how NOT to do it!):

create or replace procedure myproc(deptno in number) is
  cnt       number;
  sqltext   varchar2(32767);
begin
  sqltext := 'select /* WITHOUTBINDING */ count(*) from emp where deptno = '||to_char(deptno);
  execute immediate sqltext into cnt;
end;
/
Procedure created.

Here you can see we're just going to concatenate the department number value that's passed in, to the end of the sql string.

So, let's call that procedure with our first parameter value of 10...

SQL> exec myproc(10);

PL/SQL procedure successfully completed.

The procedure executes successfully, so let's see what query was stored on the database...

SQL> exec show_sqls('%* WITHOUTBINDING *%');
SQL ID: 5tf6vt1wa4ju3  - Hash Value: 2023900995 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 10

PL/SQL procedure successfully completed.

Here you can see the SQL text that's been stored has the department number included as part of the SQL string.
Let's call our procedure again with a different value, and see what's stored in the database...

SQL> exec myproc(20);

PL/SQL procedure successfully completed.

SQL> exec show_sqls('%* WITHOUTBINDING *%');
SQL ID: 5tf6vt1wa4ju3  - Hash Value: 2023900995 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 10
SQL ID: 4t32471jpz4us  - Hash Value: 1667208024 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 20

PL/SQL procedure successfully completed.

Ok, so now you can see the database has stored two different SQL's (one for each of the values), each with their own SQL ID, and each with their own Hash Value, indicating that they will each have their own execution plan, and each one will have had to be hard parsed to determine that plan.

Once more...

SQL> exec myproc(30);

PL/SQL procedure successfully completed.

SQL> exec show_sqls('%* WITHOUTBINDING *%');
SQL ID: 04hhpu038hh4m  - Hash Value: 109592723 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 30
SQL ID: 5tf6vt1wa4ju3  - Hash Value: 2023900995 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 10
SQL ID: 4t32471jpz4us  - Hash Value: 1667208024 - SQL: select /* WITHOUTBINDING */ count(*) from emp where deptno = 20

PL/SQL procedure successfully completed.

That's proof enough that each time we are executing our procedure with a different value, the database is having to hard parse the query and store a new execution plan for it.

Now, Let's change our procedure to correctly use bind variables...

create or replace procedure myproc(deptno in number) is
  cnt       number;
  sqltext   varchar2(32767);
begin
  sqltext := 'select /* WITHBINDING */ count(*) from emp where deptno = :1';
  execute immediate sqltext into cnt using deptno;
end;
/

As you can see, we've used a bind variable in the SQL string, and we're passing the value in using the USING clause of our execute immediate statement. Let's see what happens...

SQL> exec myproc(10);

PL/SQL procedure successfully completed.

SQL> exec show_sqls('%* WITHBINDING *%');
SQL ID: csrv5d64p1v0x  - Hash Value: 2303781917 - SQL: select /* WITHBINDING */ count(*) from emp where deptno = :1

PL/SQL procedure successfully completed.

Sure enough, our procedure executed ok, and we have an SQL statement stored in the database, with it's own ID and Hash Value. Notice that the SQL Text for this statement still shows our bind variable ":1", and not the value that was passed in when it executed.

Let's call it again with another value...

SQL> exec myproc(20);

PL/SQL procedure successfully completed.

SQL> exec show_sqls('%* WITHBINDING *%');
SQL ID: csrv5d64p1v0x  - Hash Value: 2303781917 - SQL: select /* WITHBINDING */ count(*) from emp where deptno = :1

PL/SQL procedure successfully completed.

The procedure executed ok with our different value, BUT this time we still only have a single SQL statement stored in the database. Even though the value was different, the SQL engine was able to determine that it already has an execution plan for our SQL statement, because nothing has changed with the statement itself; this is soft parsing rather than having to re-determine another execution plan, and thus the same execution took place as before, with our different value bound in at the time it actually executed the statement.

One last time...

SQL> exec myproc(30);

PL/SQL procedure successfully completed.

SQL> exec show_sqls('%* WITHBINDING *%');
SQL ID: csrv5d64p1v0x  - Hash Value: 2303781917 - SQL: select /* WITHBINDING */ count(*) from emp where deptno = :1

PL/SQL procedure successfully completed.

Sure enough, regardless of the value we pass in, the SQL engine is able to re-use the same statement over and over again, saving it time and effort, as well as the resources for storing multiple SQL statements.

In real applications, where multiple users are hitting the same SQL statements against the database, but just for different search criteria, using bind variables is going to help the database resources as well as improve performance.

Summary

So, in summary, Substitution variables are variables that the user interface detects and prompts for text to substitute into the code before submitting it to the database, and Bind variables are placeholders in queries that allow SQL queries to be soft parsed rather than hard parsed when the query is re-used, help prevent SQL injection, and allow for the values to be supplied easily and seamlessly within the code issuing it.