Oracle, MySQL 조건문 정리

조건문이란?

조건이 참인지 거짓인지에 따라 달라지는 계산이나 상황을 수행

---

Oracle

1. Decode

 - 비교연산이 불가함

부서번호가 10이면 ACCOUNTING, 20이면 RESEARCH, 30이면 SALES
나머지는 OPERATIONS를 출력하는 예제

SELECT deptno, DECODE(deptno, 10 , 'ACCOUNTING' ,
                              20 , 'RESEARCH' ,
                              30 , 'SALES', 'OPERATIONS') name
  FROM dept;
 
 
DEPTNO NAME
------ ----------
     10 ACCOUNTING
     20 RESEARCH
     30 SALES
     40 OPERATIONS

DECODE 함수로 직급에 따라 급여를 인상하도록 하시오.
--직급이 ‘ANALIST”인 사원은 200, ‘SALESMAN’인 사원은 180,
--‘MANAGER’인 사원은 150, ‘CLERK”인 사원은 100을 인상하시오.

SELECT job, DECODE(job, 'CLERK',100,
                      'MANAGER',150,
                     'SALESMAN',180,
                      'ANALIST',200)as "급여인상" 
from emp;

 

2. Case

  - 조건연산자 사용가능 

SELECT deptno, 
       CASE deptno
         WHEN 10 THEN 'ACCOUNTING'
         WHEN 20 THEN 'RESEARCH'
         WHEN 30 THEN 'SALES'
         ELSE 'OPERATIONS'
       END as "Dept Name"
  FROM dept;
 
DEPTNO Dept Name
------- ----------
     10 ACCOUNTING
     20 RESEARCH
     30 SALES
     40 OPERATIONS 

급여별로 인상율을 다르게 계산하였다. 
SELECT ename ,
       CASE
          WHEN sal < 1000  THEN sal+(sal*0.8)
          WHEN sal BETWEEN 1000 AND 2000 THEN sal+(sal*0.5)
          WHEN sal BETWEEN 2001 AND 3000 THEN sal+(sal*0.3)
          ELSE sal+(sal*0.1)
       END sal
  FROM emp; 

---

MySQL

1. IF

SELECT deptno, 
       IF(deptno <= 20,'RESEARCH','OPERATIONS') deptno as "Dept Name"
  FROM dept;
 
DEPTNO Dept Name
------- ----------
     10 RESEARCH
     20 RESEARCH
     30 OPERATIONS
     40 OPERATIONS 

2. Case

SELECT deptno, 
       CASE deptno
         WHEN 10 THEN 'ACCOUNTING'
         WHEN 20 THEN 'RESEARCH'
         WHEN 30 THEN 'SALES'
         ELSE 'OPERATIONS'
       END as "Dept Name"
  FROM dept;
 
DEPTNO Dept Name
------- ----------
     10 ACCOUNTING
     20 RESEARCH
     30 SALES
     40 OPERATIONS