Bind Variables

Bind Variables also we call as host variables .Check the use of bind variable

Bind Variables in SQL*PLUS:

You declare a bind variable in SQL*Plus, then reference the bind variable in the SELECT statement(declare bind variable, assign value into bind variable and use bind variable in select statement)

SQL> variable deptno number

SQL> exec :deptno := 10
SQL> select * from emp where deptno = :deptno;

Bind Variables in PL/SQL:

PL/SQL itself takes care of most of the issues to do with bind variables, in fact every reference(p_empno) to a PL/SQL variable is a bind variable.

create or replace procedure upd_sal(p_empno in number)

as

  begin

    update emp

    set sal=sal*2

    where empno = p_empno;

    commit;

  end;

/

Bind Variables in Dynamic SQL:

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:

create or replace procedure upd_sal(p_empno in number)

as

  begin

    execute immediate

     'update emp set

     sal = sal*2 where empno = :1' using p_empno;

  commit;

  end;

/