17. Oracle PL/SQL 조건문

16_ConditionStatmt.sql

0.00MB

IF문 의 구조

IF ( 조건 ) THEN

         실행명령

END IF;

SET SERVEROUTPUT ON; 

DECLARE 
    emp_id employees.employee_id%TYPE; 
    emp_name employees.last_name%TYPE; 
    emp_dept employees.department_id%TYPE; 
    dept_name varchar2(20):= null; 
BEGIN 
    SELECT employee_id, last_name, department_id 
    INTO emp_id, emp_name, emp_dept 
    FROM employees 
    WHERE employee_id = 124; 
     
    IF(emp_dept = 50) THEN 
        dept_name := 'Shipping'; 
    END IF; 
    IF(emp_dept = 60) THEN 
        dept_name := 'IT'; 
    END IF; 
    IF(emp_dept = 70) THEN 
        dept_name := 'Public Relation'; 
    END IF; 
    IF(emp_dept = 80) THEN 
        dept_name := 'Sales'; 
    END IF; 
     
    dbms_output.put_line(emp_id||' '||emp_name||' '||emp_dept||' '||dept_name); 
     
END; 
/ 

IF - ELSIF - END IF 문 의 구조

IF ( 조건 ) THEN

         실행명령

ELSIF ( 조건 ) THEN

         실행명령

END IF;

DECLARE 
    emp_id employees.employee_id%TYPE; 
    emp_name employees.last_name%TYPE; 
    emp_dept employees.department_id%TYPE; 
    dept_name varchar2(20):= null; 
BEGIN 
    SELECT employee_id, last_name, department_id 
    INTO emp_id, emp_name, emp_dept 
    FROM employees 
    WHERE employee_id = 124; 
     
    IF(emp_dept = 50) THEN 
        dept_name := 'Shipping'; 
    ELSIF(emp_dept = 60) THEN 
         dept_name := 'IT'; 
    ELSIF(emp_dept = 70) THEN 
        dept_name := 'Public Relation'; 
    ELSIF(emp_dept = 80) THEN 
        dept_name := 'Sales'; 
    END IF; 
     
    dbms_output.put_line(emp_id||' '||emp_name||' '||emp_dept||' '||dept_name); 
     
END; 
/ 

IF - ELSE  문 의 구조

IF ( 조건 ) THEN

         실행명령

ELSE

         실행명령

END IF;

DECLARE 
    emp_id employees.employee_id%TYPE; 
    emp_name employees.last_name%TYPE; 
    emp_comm employees.commission_pct%TYPE := null; 
BEGIN 
    SELECT employee_id, last_name, commission_pct 
    INTO emp_id, emp_name, emp_comm 
    FROM employees 
    WHERE employee_id = 160; 
     
    IF(emp_comm > 0) THEN 
        dbms_output.put_line(emp_name||'의 보너스는'||emp_comm||'입니다 '); 
    ELSE 
        dbms_output.put_line(emp_name||'의 보너스는 없습니다. '); 
    END IF;    
END; 
/ 

---

CASE 문 의 구조

CASE 대상 WHEN (조건 ) THEN 실행명령

              WHEN (조건 ) THEN 실행명령

              END;

DECLARE 
    emp_id employees.employee_id%TYPE; 
    emp_name employees.last_name%TYPE; 
    emp_dept employees.department_id%TYPE; 
    dept_name varchar2(20):= null; 
BEGIN 
    SELECT employee_id, last_name, department_id 
    INTO emp_id, emp_name, emp_dept 
    FROM employees 
    WHERE employee_id = &empno; 
     
    dept_name :=    CASE emp_dept 
                    WHEN 50 THEN 'Shipping' 
                    WHEN 60 THEN 'IT' 
                    WHEN 70 THEN 'Public Relation' 
                    WHEN 80 THEN 'Sales' 
                    END; 
      
    dbms_output.put_line(emp_id||' '||emp_name||' '||emp_dept||' '||dept_name);  
END; 
/