Use bind variable in SqlPlus and PL-SQL

Look through the whole article: http://www.rittmanmead.com/2004/03/24/bind-variables-explained/
http://www.akadia.com/services/ora_bind_variables.html

Bind Variables in SQL*Plus.

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

因为变量是专门为PLSQL语言设计的, 如果要在SqlPlus中使用, 那么绑定变量的写法有些特别. 除了声明绑定变量之外, 在其他地方使用绑定变量, 必须在绑定变量之前加上:, 告诉SqlPlus, 这是一个绑定变量. 为绑定变量赋值, 需要使用Exec 语句, 比如exec :deptno:=10 . 调用函数或存储过程也需要使用exec.

SQL>variable deptno number --You can replace variable with var

SQL> exec :deptno := 10 --注意这是为绑定变量赋值的正确写法, 必须加exec

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

What we've done to the SELECT statement now is take the literal value out of it, and replace it with a placeholder (our bind variable), with SQL*Plus passing the value of the bind variable to Oracle when the statement is processed. This bit is fairly straighforward (you declare a bind variable in SQL*Plus, then reference the bind variable in the SELECT statement)

Bind Variables in PL/SQL

Taking PL/SQL first of all, the good news is that 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 without you
knowing. Take, for example, the following bit of PL/SQL:

下面是一个存储过程, 在存储过程中,你使用的各种变量(包括formal argument和local variable)都是绑定变量.

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

Now you might be thinking that you've got to replace the p_empno with a bind variable.
However, the good news is that every reference to a PL/SQL variable is in fact a bind
variable.

Dynamic SQL
在PLSQL中, 你需要注意怎样使用绑定变量的场合, 只有在PLSQL语句块中包含了Dynamic SQL.
In fact, the only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL.
Dynamic SQL, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. For next example would always require a hard parse when it is submitted:
create or replace procedure dsal(p_empno in number)

as
begin
  execute immediate
   'update emp set sal = sal*2 where empno = '||p_empno;
commit;
end;

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

create or replace procedure dsal(p_empno in number)
as
begin
execute immediate
'update emp set
sal = sal*2 where empno = :x' using p_empno;
commit;
end;

And that's all there is to it. One thing to bear in mind, though, is that you can't substitute actual object names (tables, views, columns etc) with bind variables - you can only subsitute literals. If the object name is generated at runtime, you'll still need to string concatenate these parts, and the SQL will only match with those already in the shared pool when the same object name comes up. However, whenever you're using dynamic SQL to build up the predicate part of a statement, use bind variables instead and you'll reduce dramatically the amount of latch contention going on.