[Oracle] PL/SQL 조건문 (if문, case문)

 

[ IF문 ]

 

• if ~ end if

if(조건) then
	실행명령;
end if;

 

• 예제

DECLARE
	emp_id employees.employee_id%TYPE;
	emp_name employees.last_name%TYPE;
	emp_dept employees.department_id%TYPE;
	emp_dept_name varchar(20) := NULL;
BEGIN
	select employee_id, last_name, department_id
	INTO emp_id, emp_name, emp_dept
	from EMPLOYEES e
	where EMPLOYEE_ID= 124;

	IF(emp_dept = 50) THEN 
		emp_dept_name := 'Shipping';

	ELSIF(emp_dept = 60) THEN 
		emp_dept_name := 'IT';
	END IF;

	dbms_output.put_line(emp_id || ' ' || emp_name || ' ' || emp_dept || ' ' || emp_dept_name);
END;
/

 

 

[ CASE문 ]

 

case when x = y then a else b end 

 => 조건 x  = y 가 true 면 a, false 면 b

 

case when x < y then a when x = y then b else c end

 => 조건 x < y 가 true 면 a, 조건 x =  y 가 true면 b, 그렇지 않으면 c

case x when 'a' then 'b' else 'c' end

 => x 가 a 이면  b,  그렇지 않으면  c 

 

• 예제

DECLARE
	emp_id employees.employee_id%TYPE;
	emp_name employees.last_name%TYPE;
	emp_dept employees.department_id%TYPE;
	emp_dept_name varchar(20) := NULL;
BEGIN
	select employee_id, last_name, department_id
	INTO emp_id, emp_name, emp_dept
	from EMPLOYEES
	where LAST_NAME = 'Austin';

	EMP_DEPT_NAME := CASE EMP_DEPT WHEN 50 THEN 'Shipping' WHEN 60 THEN 'IT' END;

	dbms_output.put_line(emp_id || ' ' || emp_name || ' ' || emp_dept || ' ' || emp_dept_name);
END;
/