흔히 조건 제어문에는 IF ELSE, SWITCH CASE문을 많이 사용한다. PL/SQL에서는 위의 문법을 제공하지만 순수 SQL에서는 이와 같은 문법을 제공하지 않는다. ORACLE에서는 DECODE함수와 CASE표현식을 통해 위와 같은 기능을 제공하고 있다.

decode

오라클에서 오래전에 개발된 함수로 많은 회사에서 이미 사용되었다. 또한 오라클에서만 지원하는 함수이고 비교 연산자는 =의 역할밖에 못 한다, 그리고 가독성의 단점 때문에 사용을 권장하지 않는다.

decode 문법

deocde(기준값,비교값1,참값1,비교값2, 참값2, ..., 기본값)

decode 문법 예시

다음 작성된 예시 코드를 보며 들여쓰기와 사용 방식에 대해 알아보자.

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) AS revised_salary
FROM
    hr.employees;

작동 방식

작동 방식은 다음과 같다.

1. job_id와 'IT_PROG'가 같은지 비교한다.
2. 같으면 salary*1.1의 값을 반환하고 같지 않으면 다음 조건을 비교한다.
3. 어떠한 값도 참값을 반환하지 않았다면 기본값인 salary를 반환하고 함수를 종료한다.

deocde의 주의점

SELECT
    decode(commission_pct, NULL, 'no comm', commission_pct)
FROM
    hr.employees;

이때 4번 째 인자값인 commission_pct는 데이터 타입이 char형으로 변환된 것을 확인할 수 있다.

이는 서로 다른 데이터 타입이 주어질 때 3번째 인자의 데이터 타입을 기준으로 4번 째 인자의 데이터 타입이 변경된다.

즉, decode 함수의 4번째 인수의 data-type 변환은 3번째 data-tpye에 의존한다.
++null은 암묵적으로 char 형이다.

따라서 아래의 코드를 수행할 경우 오류를 유발하니 위치에 따라 조심해야 한다.

--오류발생 // 'no comm'은 숫자로 변형 안돼
SELECT
    decode(commission_pct, NULL,commission_pct, 'no comm')
FROM
    hr.employees;
    

--잘못된 결과 반환 // 오류는 아니지만 잘못된 데이터 반환
SELECT
    max(decode(job_id,'AD_PRES', null, salary))
FROM
    hr.employees

--요로코롬 실행
SELECT
    max(decode(job_id,'AD_PRES', to_number(null), salary))
FROM
    hr.employees

case

비교적 최근 9버전부터 적용된 문법으로 오라클 이외의 DBMS에서도 사용 가능하다. decode와 달리 모든 비교 연산자 및 논리연산자가 사용 가능하다. 단, 콤마를 사용하여 구분하지 않는다. 다시말해 콤마를 사용하면 안된다.

case 문법

case when 조건 then 참값n, ..., else 기본값 end 표현식(별칭)

case 문법 예시

case 문법은 2가지 방법으로 사용될 수 있다.

• = 연산자만 사용할 경우.

SELECT
    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;

모든 비교 조건이 =일 경우 비교 기준값을 when 절 밖으로 빼어내어 반복을 생략할 수 있다.

• 다른 조건문을 사용, 혼용 할 경우.

SELECT
    case 
        when job_id = 'IT_PROG' then salary * 1.1
        when job_id = 'ST_CLERK' then salary * 1.2
        when job_id in ('SA_REP','AD_PRES','AD_VP')
            and salary <= 2000 then salary * 1.3
        else salary 
    end revised_salary
FROM
    hr.employees;

해당 문법을 볼 때 콤마를 사용하지 않는다는 점에 주의하자.

case 문법 주의점

• decode에서는 서로 다른 데이터 타입의 출력에 대해 암묵적으로 형 변환을 자동으로 진행하였다.

하지만 case의 경우 조건에 따른 출력 값의 자료형이 다르면 오류를 발생시키는 것을 확인할 수 있다.

SELECT
    CASE
        WHEN commission_pct IS NULL THEN
            'no comm'
        ELSE
            commission_pct
    END
FROM
    hr.employees;

• decode와 달리 case는 반환 값이 null이더라도 알아서 결과 값을 반환한다. 굳이 null을 형 변환 할 필요 없다.

SELECT
    max(case when job_id = 'AD_PRES' then null else salary end)
FROM
    hr.employees;