2. PL/SQL 변수 선언
A. 변수 사용 및 처리
B. 변수 이름에 대한 요구사항
C. 변수 선언 및 초기화
[:= | DEFAULT expr] ;
DECLARE
v_hiredatae DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := ‘Atlanta’;
c_comm CONSTANT NUMBER := 1400;
EX)
set serveroutput on
declare
v_myname varchar2 (20) ;
begin
dbms_output.put_line( ‘ My name is : ‘ || v_myname);
v_myname := ‘John’;
dbms_output.put_line( ‘ My name is : ‘ || v_myname);
end;
/
D. 문자열 리터럴의 구분자
E. 변수 Type
F. %TYPE
identifier table.column_name%TYPE;
EX)
declare
v_emp_lname employees.last_name%type := ‘King’ ;
v_lname v_emp_lname%type := ‘John’ ;
begin
dbms_output.put_line(‘Name is : ‘ || v_emp_lname);
dbms_output.put_line(‘Name is : ‘ || v_lname);
end;
/
G. Boolean
flag Boolean := false;
flag := emp_sal1 < emp_sal2;
H. Composite Data Types
I. Bind 변수
EX)
1)
variable b_result number
begin
select (salary*12) + nvl(commission_pct,0) into :b_result
from employees
where employee_id = 100;
end;
/
print b_result
2)
variable b_emp_salary number
SET AUTOPRINT ON
begin
select salary into :b_emp_salary
from employees
where employee_id = 178;
end;
/
select last_name, salary from employees
where salary =:b_emp_salary;