Re: How to use bind variable

"max" <oracle_rep_spum_at_libero.it> wrote in message news:3ab54fee.1192527_at_news.libero.it...
> Hi,
> i read that using bind variables speed application up cause reduce
> parsing.
> I tried to use bind variable in a stored procedure but i could not.
>
> Take for instance,
>
> CREATE OR REPLACE upd_sal (empno number) as
>
> :b_empno NUMBER;
>
> BEGIN
> :b_empno:= empno;
> UPDATE emp SET sal = (sal +1)
> WHERE empno= :b_empno;
> END;
> /
>
> NO WAY, it doesn't work. The compilation produces errors.
> How and where must I declare the bind variable???
> Tia
> Massimiliano
>

 CREATE OR REPLACE upd_sal (p_empno number) as  BEGIN
 UPDATE emp SET sal = (sal +1)
 WHERE empno= p_empno;
 END; Any pl/sql variable used in your statement will be used as a bind variable in the context of your statement.
Be aware though the variable name needs to be different from any column name.
The : prefix is used in dynamic sql only (execute immediate 'update ... where empno = :p_empno' using p_empno)

Hth,

Sybrand Bakker, Oracle DBA

Hth, Received on Mon Mar 19 2001 - 00:02:54 CST