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

스터디/DB(ORACLE)

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

고무곰(GomuGom) 2017. 9. 14. 11:52

[ 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;
/

저작자표시 비영리