VARIABLE

SQLPlus 에서 Bind Variable 선언하고 사용하는 예

variable bind_var_name number
variable bind_var_name char
variable bind_var_name char(n)
variable bind_var_name nchar
variable bind_var_name nchar(n)
variable bind_var_name varchar2
variable bind_var_name varchar2(n)
variable bind_var_name nvarchar2(n)
variable bind_var_name clob
variable bind_var_name nclob
variable bind_var_name refcursor
variable bind_var_name binary_float
variable bind_var_name binary_double

Declares a bind variable that can be used in subsequent SQL statements executed in SQL*Plus.
The value of a variable can be printed with print.

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

-------------------------------------------------------------------------------------------------------------------

SQL Plus에서 바인드 변수를 선언하고 Stored Procedure의 OUT 파라미터값을 그 변수에 저장하여 출력하는 방법
테스트을 위해 Stored Procedure를 하나 아래와 같이 생성한다.

 create or replace PROCEDURE proc2
  (emp_no IN emp.empno%TYPE,
  e_name OUT emp.ename%TYPE,
  salary OUT emp.sal%TYPE,
  job OUT emp.job%TYPE
 )
 Is
 
 begin
    select ename, sal, job into e_name, salary, job from emp where empno=emp_no;
    dbms_output.put_line(emp_no||':'||e_name||':'||salary||':'||job);
 end proc2;

위와같이 선언한 Stored Procedure에 파라미터를 전달하기 위해서는 SQL Plus에서도 변수를 사용할 필요가 있다.

SQL> var ename varchar2(20);
SQL> var salary number;
SQL> var job varchar2(20);

SQL> exec proc2(7369, :ename, :salary, :job);

PL/SQL procedure successfully completed.

SQL> print :ename;

ENAME
--------------------------------
SMITH

SQL>