Using bind variables in SQL*Plus

Using bind variables in SQL*Plus
  There are two types of variables that can be used in SQL*Plus: bind variables and user variables (which are declared with define. The following discusses bind variables. In SQL*Plus, a bind variable is declared with variable:
var num_var number
var txt_var varchar2(15)
After the declaration, a value can be assigned to the variable
begin
  select 44, 'fourty-four' into :num_var, :txt_var from dual;
end;
/
The value of the bind variable can then be printed with print:
print num_var
using the variable:
create table sqlplus_bindvar_ex(
  num number, txt varchar2(15)
);

begin
  insert into sqlplus_bindvar_ex values (:num_var, :txt_var)
end;
/
Assigning a value to a bind variable with execute
Since an execute is basically a wrapper around a begin .. end PL/SQL block, a variable can be assigned a value like so:
exec :num_var := 42
exec :txt_var := 'fourty-two'
And then another record can be inserted:
exec insert into sqlplus_bindvar_ex values (:num_var, :txt_var)
select * from sqlplus_bindvar_ex
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/386081/viewspace-436066/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/386081/viewspace-436066/