ORACLE 조건문(IF,CASE) -(5)

[    ORACLE 조건문(IF,CASE)    ]

/* [ 제어문 ] : 조건문, 반복문 - 조건문 : if문, case문(switch문과 유사) - 반복문 : basic loop문, while문(반복횟수를 정하지 않을 경우) : for문(반복횟수 지정할 경우) // if문 : if ~ end if EX) if(조건) then 실행명령; elsif(조건) then 실행명령; else 실행명령; end if; // case문 EX) case 변수명 when 값1 then 실행명령; when 값2 then 실행명령; ... end; */ set serveroutput on; -- if문 Ex) 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 --if문 시작 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; DBMS_OUTPUT.PUT_LINE(emp_id||' '||emp_name||' '||emp_dept||' '||dept_name); 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 = 103; if(emp_dept = 50) then dept_name := 'Shipping'; elsif(emp_dept = 60) then -- elseif가 아니라 elsif임을 주의... dept_name := 'IT'; elsif(emp_dept = 70) then dept_name := 'Public Relation'; ELSE dept_name := 'Other'; end if; DBMS_OUTPUT.PUT_LINE(emp_id||' '||emp_name||' '||emp_dept||' '||dept_name); end; / 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 = 130; if (emp_comm > 0) then dbms_output.put_line(emp_id||' 의 보너스는 '||emp_comm); else DBMS_OUTPUT.PUT_LINE(emp_id||'의 보너스는 없습니다.'); end if; end; / -- case문 EX) 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(dept_name); end; /