Bind variable

Bind Variable:-Bind variables are the variables which are used in place of literal.

Defining a Bind Variable :- By variable command we can define a bind variable;

Example:-
SQL>variable x number;
SQL>variable vename varchar2(20);

Initialization of Bind Variable :- By exec command, we can provide a value to bind variable.

Example:-
SQL>exec :x := 10;
SQL>exec vename := 'ROHIT';

Display The Bind variable:-There are 3 ways of displaying the value held by bind variable.

1) Using DBMS OUTPUT package.
2) Using Print command
3) Setting Auto print parameter on

1) Using DBMS OUTPUT package:-

Example:- 

begin
dbms_output.put_line(:x);
end;
/

2) Using Print command :-

Example:-

SQL> variable x number
SQL> exec :x := 10

PL/SQL procedure successfully completed.

SQL> print :x

         X
----------
        10
SQL>

3) Setting Auto print parameter on

Example:-

SQL> set autoprint on
SQL> variable y number
SQL> exec :y := 10

PL/SQL procedure successfully completed.


         Y
----------
        10

SQL>

Referencing a bind variable:- When we use bind variable , we just put a colon(:) before
variable. It is a method to referencing a bind variable.

Why performance improved through Bind variable ?
Ans :- Let us understand with below example

If we are executing below query
select * from emp where deptno=10;
select * from emp where deptno=20;
select * from emp where deptno=30;

In all above queries, Oracle will hard parse to get the execution plan
as hash value will be different for all queries.To avoid hard parsing
when you are using same query with different values , we use bind variable.

Bind Variables in SQL*Plus

In SQL*Plus you can use bind variables as follows:

Example:-

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,
to the point where most code that you write already uses bind variables.

Example:-

create or replace procedure dsal(p_empno in number)
as
  begin
    update emp
    set sal=sal*2
    where empno = p_empno;
    commit;
  end;
/

Here p_empno is a bind variable.Every reference to a PL/SQL variable is a bind variable.


Bind Variable in calling Funtion/Procedure:-


Bind Variable as a Performance Booster:-Below is a test for examine the excution time with
bind variable and without bind variable.

SQL> alter system flush shared_pool;
SQL> set serveroutput on;

Without Bind variable:- 

declare
      type rc is ref cursor;
      l_rc rc;
      l_dummy all_objects.object_name%type;
      l_start number default dbms_utility.get_time;
  begin
      for i in 1 .. 1000
      loop
          open l_rc for
          'select object_name
             from all_objects
            where object_id = ' || i;
          fetch l_rc into l_dummy;
          close l_rc;
          -- dbms_output.put_line(l_dummy);
      end loop;
      dbms_output.put_line
       (round((dbms_utility.get_time-l_start)/100, 2) ||
        ' Seconds...' );
  end;
/

87.78  Seconds...

With bind variable :-

declare
      type rc is ref cursor;
      l_rc rc;
      l_dummy all_objects.object_name%type;
      l_start number default dbms_utility.get_time;
  begin
      for i in 1 .. 1000
      loop
          open l_rc for
          'select object_name
             from all_objects
            where object_id = :x'
          using i;
          fetch l_rc into l_dummy;
          close l_rc;
          -- dbms_output.put_line(l_dummy);
      end loop;
      dbms_output.put_line
       (round((dbms_utility.get_time-l_start)/100, 2) ||
        ' Seconds...' );
end;
/

.25 Seconds...