23.10.11. Oracle SQL 조건제어문

■ 조건제어문

- SQL문에서 IF문을 사용해서 조건제어문을 수행 할 수 없다.
- CASE 표현식, DECODE 함수 사용.

<PL/SQL> IF문
① IF 기준값 = 비교값 THEN 
    참값 
ELSE
    거짓값
END IF;

② IF 기준값 = 비교값 THEN 
    참값1
ELSE IF 기준값 = 비교값2 THEN
    참값2
ELSE IF 기준값 = 비교값3 THEN
    참값3
    ...
ELSE 
    기본값
END IF;

- DECODE 함수는 기준값과 비교값을 내부적으로 같다(=)라는 비교연산자를 사용한다. 
IF 기준값 = 비교값 THEN 
    참값 
    /*FALSE 면 NULL 로 리턴한다.*/
ELSE
    거짓값
END IF;
- DECODE(기준값, 비교값, 참값) : FALSE 면 NULL 로 리턴한다.
- DECODE(기준값, 비교값, 참값, 기본값) : FALSE 면 '기본값' 로 리턴한다.
  DECODE(기준값, 
        비교값1, 참값1,
        비교값2, 참값2,
        비교값3, 참값3,
        비교값4, 참값4,
        ...
        기본값);

 

SELECT 
    employee_id, 
    salary, 
    job_id, 
    DECODE(job_id,
           'IT_PROG', salary * 1.1,
           'ST_CLERK',salary * 1.2,
           'SA_REP', salary * 1.3,
           salary ) revised_salary
FROM hr.employees;

1. CASE 표현식(9i) 

- 기준값과 비교값의 대해서 모든 비교연산자, 논리연산자 를 사용할 수 있다. 
1) 비교연산자: =, >, <, >=, <=, !=, ^=,<>, BETWEEN AND, IN, IS NULL, IS NOT NULL, LIKE

2) 논리연산자: AND, OR, NOT

- 기준값 = 비교값 
① 
CASE 기준값
    WHEN 비교값1 THEN 참값1
    WHEN 비교값2 THEN 참값2
    WHEN 비교값3 THEN 참값3
    ...
    ELSE 기본값
END

- 기준값 '비교연산자' 비교값 '논리연산자' 기준값 '비교연산자' 비교값
② 
CASE 
    WHEN 기준값 '비교연산자' 비교값1 THEN 참값1
    WHEN 기준값 '비교연산자' 비교값2 THEN 참값2
    WHEN 기준값 '비교연산자' 비교값3 THEN 참값3
    WHEN 기준값 '비교연산자' 비교값4 AND(OR) 기준값 비교연산자 비교값5 THEN 참값4
    ...
    ELSE 기본값
END

SELECT 
    employee_id, 
    salary, 
    job_id, 
    CASE job_id --기준값
        WHEN 'IT_PROG' THEN salary * 1.1
        WHEN 'ST_CLERK' THEN salary * 1.2
        WHEN 'SA_REP' THEN salary * 1.3
        ELSE salary -- 생략가능
        END revised_salary
FROM hr.employees;

SELECT 
    employee_id, 
    salary, 
    job_id, 
    CASE 
        WHEN job_id = 'IT_PROG' THEN salary * 1.1
        WHEN job_id = 'ST_CLERK' THEN salary * 1.2
        WHEN job_id = 'SA_REP' THEN salary * 1.3
        ELSE salary -- 생략가능
        END revised_salary
FROM hr.employees;

[문제1] 사원들의 급여를 기준으로 아래와 같이 출려해주세요.

급여(salary) 범위	GRADE
0 ~ 4999	low
5000 ~ 9999	medium
10000 ~ 19999	good
20000 ~	excellent

[방법1] 비교연산자

SELECT 
    salary ,
    case
        when salary < 5000 then 'low'
        when salary < 10000 then 'medium'
        when salary < 20000 then 'good'
        else 'excellent'
        end grade
FROM hr.employees 
ORDER by salary;

[방법2] BETWEEN - AND 

SELECT 
    salary ,
    case
        when salary between 0 and 4999 then 'low'
        when salary >= 5000 and salary < 10000 then 'medium'
        when salary >= 10000 and salary < 20000 then 'good'
        else 'excellent'
        end grade
FROM hr.employees ;

■ decode 함수, case 표현식 null check 방법

- decode(컬럼명, null, 조건문, 조건문, ...)
- decode 함수는 null check 는 null 키워드를 사용한다. 
- case 표현식에서는 null check 는 is null, is not null 연산자를 사용한다.

SELECT
    employee_id , 
    salary , 
    commission_pct , 
    decode( commission_pct, null, (salary*12), (salary*12) + (salary * 12 * commission_pct) ) annual_salary_1 ,
    case
        when commission_pct is null then (salary * 12)
        else (salary*12) + (salary * 12 * commission_pct)
    end annual_salary_2
FROM hr.employees;

■ yy와 rr 차이점(년도별)

ex) 95-10-27
- yy: 현재 년도의 세기를 반영 → 20세기 1995-10-27, 21세기 2095-10-27 
- rr: 2000년 부터는 표기법을 자동화로 변경해준다.

SELECT 
    to_char(to_date('95-10-27', 'yy-mm-dd'), 'yyyy-mm-dd') "yy 형식" , 
    to_char(to_date('95-10-27', 'rr-mm-dd'), 'yyyy-mm-dd') "rr 형식"
FROM dual;

 

[ RR 타입 기준표 ]
데이터 입력 년도 (반환 날짜)
현재 년도	0 ~ 49	50 ~ 99
0 ~ 49	현재 세기 반영	이전 세기 반영
50 ~ 99	이후 세기 반영	현재 세기 반영

EX)

현재 년도	데이터 입력 날짜	YY	RR
1994	95-10-27	1995	1995
1994	17-10-27	1917	2017
2001	17-10-27	2017	2017
2048	52-10-27	2057	1952
2051	47-10-27	2047	2147