Database_23_일반 함수_조건 표현식_CASE, DECODE

    
-- 조건 표현식
        -- SQL 문 안에서 IF-THEN-ELSE 논리를 사용할 수 있도록 합니다.
            -- CASE 표현 방식
                -- ANSI SQL을 준수합니다.
                /*
                    CASE expr WHEN comparison_expr 1 THEN return_expr 1
                                [ WHEN comparison_expr 2 THEN return_expr 2 ]
                                [ ELSE else_expr ]
                    END
                        -- expr가 comparison_expr와 동일한 첫 WHEN-THEN 쌍을 찾아 return_expr를 반환합니다.
                        -- 조건을 만족하는 WHEN-THEN 쌍이 없고 ELSE 절이 존재하는 경우 else_expr를 반환합니다.
                        -- ELSE 절이 존재하지 않는 경우 널 값을 반환합니다.
                */
            -- DECODE 표현 방식 
                -- ORACLE 구문에만 존재합니다.
                /*
                    DECODE (col|expression, search 1, result 1
                                                [ , search 2, result 2 ]
                                                [ , default ]
                        -- expression을 각 search 값과 비교한 후 동일한 값의 result를 반환합니다.
                        -- 기본값을 생략하는 경우 검색 값에 결과 값과 일치하는 값이 없으므로 널 값을 반환합니다.
                        -- col == expr
                */
        -- CASE 
            -- 부서 번호 50, 60, 80, 그 외 >> 급여 인상률 5%, 10%, 15%, 인상 없음 
SELECT last_name, salary, department_id, salary,
        CASE department_id WHEN 50 THEN salary*1.05
                /* = CASE WHEN department_id=50 THEN salary*1.05
                                   WHEN 60 THEN salary*1.1
                                   WHEN 80 THEN salary*1.15
                                   ELSE salary */
        END AS case_salary
FROM employees;

        -- DECODE
SELECT last_name, salary, department_id, salary,
        DECODE( department_id, 50, salary*1.05
                                       , 60, salary*1.1
                                       , 80, salary*1.15
                     , salary ) AS case_salary
FROM employees;

    
        -- CASE 문으로 변환
    /*
    SELECT last_name, salary+salary*NVL(commission_pct, 0), NVL2(commission_pct, '영업사원', '일반사원')
    FROM employees;
    */
    SELECT last_name, salary+salary*NVL(commission_pct, 0),
                CASE WHEN commission_pct IS NOT NULL THEN '영업사원'
                                              ELSE '일반사원'
                END AS Grade
    FROM employees;