Re: bind variables

A copy of this was sent to lance sturla <lsturla_at_ix.netcom.com> (if that email address didn't require changing) On Sun, 14 Nov 1999 21:40:00 -0800, you wrote:

>Can someone explain what a bind variable is?
>a beginner lance

A bind variable is like a replaceable parameter in a SQL statement. It allows us to compile and optimize a sql statement once and execute it for many people over and over and over again with different values. As the time to parse/optimize sql queries can take most of the execution time (eg: for a simple single table query that reads a table by a primary key 70-90% of the execution time of the query might be spent in parsing/optimizing. if we instead use a bind variable, we can execute that query many times with different values and we only pay the price of parsing/optimizing once).

So, the query:

select * from emp where ename='KING';

does not use a bind variable. the query:

select * from emp where ename=:x;

does. PLSQL is an 'autobinding' language. If you write a procedure such as:

...

   l_ename varchar2(30) default 'KING';    l_empno number;
begin

   select empno into l_empno from emp where ename = l_ename; exception

   ...
end;

That sql statement will automagically use bind variables for us -- it is just like submitting "select empno from emp where ename = :x"

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Nov 15 1999 - 06:11:15 CST