7. 일반함수(1)

1. nvl, nvl2 : null 치환 함수
1) nvl, nvl2

** 사용법
(1) nvl(대상, 치환값)
- 대상과 치환값의 데이터 타입이 일치해야 함(단, 첫번째 인수의 데이터 타입이 최종 데이터 타입이 됨)

 

select ename, sal, comm, 
       sal+comm as 총급여1,
       sal + nvl(comm,0) as 총급여2
  from EMP;

다음 결과를 볼 수 있는데 아래 결과를 살펴보자.

 

select sal, comm, nvl(comm, '보너스없음')
  from emp; 

 

이유는 comm의 데이터 타입이 숫자인데 null값에 '보너스없음'이라는 문자를 채워넣을 수 없음.

 

select ename, sal, comm, 
       sal+comm as 총급여1,
       sal + nvl(comm,0) as 총급여2
  from EMP;

 

총급여 1을 보면 comm이 null인 값들은 연산이 되지 않으므로 comm이 null인 값들은 총급여 또한 null로 나온다.

그래서 nvl 함수를 이용하여 comm에 있는 null값을 0으로 치환하여 계산에 문제가 없게 만들 수 있는것을 알 수 있다.

(2) nvl2(대상, null아닐때 치환값, null일때 치환값)
- 두번째, 세번째 데이터 타입이 일치해야 함(단, 두번째 인수의 데이터 타입이 최종 데이터 타입이 됨)

 

 

다음과 같이 나타낼 수 있다. 

또한 데이터 타입에 대한것을 살펴보면

 

select hpage, nvl2(hpage, 100,0) as 홈페이지구분      
  from professor ;
 

: 정상출력

select hpage, nvl2(hpage, 'a',0) as 홈페이지구분       
  from professor ;

: 정상/대표데이터 타입이 문자이므로 0도 문자에 포함되서 정상출력

 

  select hpage, nvl2(hpage, 0,'a') as 홈페이지구분       --에러/대표데이터 타입이 숫자이므로 3번째 문자와 일치하지않음
  from professor ;

: 에러/대표데이터 타입이 숫자이므로 3번째 문자와 일치하지않음

 

2. nullif

- oracle null 치환 함수
- 검사대상이 특정값과 같으면 null을 리턴, 다르면 원래값 유지 


** 사용법 : nullif(대상,비교값)
- 대상이 비교값과 같으면 null 리턴, 다르면 대상값 그대로 유지
  
select comm, nullif(comm,0)
  from emp;

다음에서 볼 수 있듯이 comm을 0 과 비교해서 같으면 null 출력하고 다르면 comm의 원본값을 그대로 출력하는데,

1번 행을 보면 comm이 null이고 비교대상이 0이다. null과 0은 다르므로 null을 comm에 있는 null을 출력한다.

그럼 만약 comm이 0이라면 비교대상이 0이므로 그것 또한 null로 출력된다.

 

 

 

3. coalesce

- null 치환 함수
- 여러 인수 전달 가능
- 널이 아닌 값이 나올때까지 검사 가능

** 사용법 : coalesce(대상1, 대상2, 대상3, 대상4, ....)
-대상1이 null이 아니면 대상1 리턴
-대상1이 null이면 대상2 검사, 대상2가 null이 아니면 대상2 리턴
-대상1이 null이면 대상2 검사, 대상2가 null이면 대상3 검사

 

select comm, coalesce(comm,0)
  from emp;

 

select deptno2, deptno1, 
       coalesce(deptno2, deptno1,100)
  from student;

 

** 다음 중 결과값이 다른 하나는?

1) nvl(comm,0)            
2) isnull(comm,0)
3) nullif(comm,0)
4) coalesce(comm,0)

 

답: 3번   -> 두 값이 같을때 null로 만드는 함수

 

참고)

** 문자, 숫자 타입의 정렬규칙
create table test2(
col1 varchar2(4),
col2 number(4));
 
insert into test2 values(100, 100);    -- 정상
commit;
insert into test2 values(2, 'a');           -- 에러
insert into test2 values(50, '13');       -- 정상(묵시적 형변환으로 인해 숫자타입으로 변경되어 저장)
insert into test2 values(2, 40);           -- 정상
commit;



-숫자컬럼의 정렬
select *
  from test2
 order by col2; -- 13 < 40 < 100 순으로 정렬


-문자컬럼의 숫자 정렬(문자 정렬 규칙에 따라 맨 왼쪽값부터 비교)
 select *
  from test2
 order by col1; -- 100 < 2 < 50 순으로 정렬(문자 정렬 규칙을 따름)