[Oracle] 단일행 함수 - 일반 함수

일반 함수

• nvl : null 값 대신 다른 데이터 출력
• decode : if 문을 SQL로 구현한 함수 1
• case : if 문을 SQL로 구현한 함수 2

 

 

 

nvl 함수는 Null Values 의 약자로 null 값, 즉 결측치를 다루는 함수이다.

null 값은 데이터가 없는 상태 또는 알 수 없는 값이라고 한다.

nvl(컬럼명, null 값을 대체할 값)

nvl2(컬럼명, null 아닐 때 출력 될 컬럼, null 일 때 출력 될 컬럼)

 

 

 

예제 1. 이름과 커미션을 출력하는데, 커미션이 null 인 사원들은 0으로 출력

select ename, comm, nvl(comm, 0)
    from emp;

 

 

 

예제 2. 이름과 커미션을 출력하는데, 커미션이 null 인 사원들은 no comm 으로 출력

select ename, nvl(to_char(comm), 'no comm')
    from emp;

숫자형 자리에 문자형 데이터를 넣으려고 해서 에러가 발생한다.

no comm 이라는 문자를 출력해주기 위해서 comm 을 문자형으로 변경한다.

 

 

 

예제 3. 이름, 월급, 커미션을 출력하는데, 커미션이 null 이 아닌 사원들은 월급+커미션으로 출력하고, null 인 사원들은 월급으로 출력

select ename, sal, nvl2(comm, sal+comm, sal)
    from emp;

 

 

 

 

 

decode(컬럼명, 컬럼의 데이터1, 출력할 값1, 기본값)

decode(컬럼명, 컬럼의 데이터1, 출력할 값1,
               컬럼의 데이터2, 출력할 값2,
               컬럼의 데이터3, 출력할 값3, 기본값)

 

 

 

예제 1. 이름, 직업, 월급, 보너스를 출력하는데, 직업이 SALESMAN 이면 보너스를 9500, ANALYST 면 2300, CLERK 이면 3400, 나머지는 0 을 출력

select ename, job, sal, decode(job, 'SALESMAN', 9500
                                  ,  'ANALYST', 2300
                                  ,  'CLERK', 3400, 0) as "보너스"
    from emp;

 

 

 

예제 2. 이름, 월급, 직업, 보너스를 출력하는데, 직업이 PRESIDENT 면 null 을 출력하고, 나머지 사원들은 자기 월급을 출력하고, 보너스가 높은 사원부터 출력

select ename, sal, job
        , decode(job, 'PRESIDENT', null, sal) as "보너스"
    from emp
    order by 4 desc nulls last;

decode 의 버그 : decode 의 세 번째 인자 값에 따라서 네 번째 인자 값의 데이터 유형이 결정된다.

 

왜 보너스가 3000이 맨 위로 나타나지 않고 950이 맨 위에 있는가?

세 번째 인자 값이 null 이라 문자형이기 때문에 네 번째 인자 값이 문자형이 된다.

암시적 형 변환이 내부적으로 발생한 것이다.

그래서 숫자형이면 3000이 제일 큰 값인데, 문자형이라 950이 제일 큰 값이다.

 

해결 방법 - null 에 to_number 함수를 둘러서 숫자형으로 형 변환한다.

select ename, sal, job
        , decode(job, 'PRESIDENT', to_number(null), sal) as "보너스"
    from emp
    order by 4 desc nulls last;

 

 

 

 

decode 와 case 의 차이점

decode 는 등호(=) 비교만 가능하다.

그러나 case 문은 등호(=) 와 부등호(>, <) 비교 둘 다 가능하다.

case 예) 월급이 3000 이상이면 보너스를 8000 이 되게 하라

decode 예) 직업이 SALESMAN 이면 보너스를 9000 이 되게 하라

 

 

 

예제 1. 사원들의 이름, 직업, 월급, 보너스를 출력하는데 월급이 3000 이상이면 보너스를 500을 출력하고 월급이 2000 이상이면 보너스를 300을 출력하고 월급이 1000 이상이면 보너스를 200을 출력하고 나머지는 0을 출력

select ename, job, sal, case when sal >= 3000 then 500
                             when sal >= 2000 then 300
                             when sal >= 1000 then 200
                        else 0 end as "보너스"
    from emp;