NVL(Null Value) 함수 와 연결 연산자 Pipe

Null을 값으로 여겨 연산을 하면 그 값은 가감승제에 상관없이 Null이 된다. 이는 연산 시에 상당히 불편하다. 
예를 들어 기본 계정인 scott에서 접근이 가능한 emp Table 을 기준으로 연봉을 봉급 * 12 + 커미션이라고 할 때 일반적인 쿼리 문은

SELECT ename "사원명",sal "급여", comm "커미션" ,sal *12 +comm "연봉"
FROM EMP;

이나 이는 결과적으로 완전하지 않다. 왜냐하면 comm 에 들어있는 값이 Null인 경우 연봉 역시 Null이 되기 때문이다.
이런 것 처럼 Null값으로 인한 연산이 불가능 할 때 이를 보조하기 위한 방법으로 null 값일때 이를 어떤 특이 값으로 바꾸게 해주는 함수를 사용하면 된다.

SELECT ename "사원명",sal "급여", comm "커미션" ,sal *12 + NVL(comm,0) "연봉"
FROM EMP;

SELECT ename "사원명",sal "급여", comm "커미션" ,sal *12 + NVL2(comm,comm,0) "연봉"
FROM EMP;

SELECT ename "사원명",sal "급여", comm "커미션" ,COALESCE (sal *12 +comm,sal *12,comm,0) "연봉" FROM EMP;

NVL함수는 null이 아닐 경우 자신의 값을 바로 반환하며, NVL2함수는 null이 아닐 경우 자신의 뒤에 2번째 값(또는 수식), null 일경우는 3번째 값(또는 수식)을 반환한다.

다양한 항을 처리하도록 하는 COALESCE 함수는 여러 값을 받아 순차적으로 null확인을 한다. 즉, 첫번째 수식이 null이 아닐 경우 첫번째 수식을 반환하고, null일 경우 두번째 값을, 두번째 값이 null이라면 다음 식을 확인하는 방식으로 앞에서 부터 순서대로 진행을 하며 마지막 값을 최후의 값(기본값)으로 가진다.

이를 기본 계정인 hr로 로그인 하여 다시한번 테스트 해보자.
현재 접속된 사용자 소유의 테이블과 뷰의 목록을 조회 한다

SELECT * FROM tab;

DESC EMPLOYEES;

SELECT * FROM EMPLOYEES;

EMPLOYEES Table 중에서 first_name , last_name, salary, commission_pct, 을 출력하고 Yearpay 을 계산하여 출력하여 보자. 이를 출력하는 데에 각 함수별로 사용해 본다.

SELECT first_name, last_name, salary, commission_pct,
     salary * 12 +12 * NVL((salary*commission_pct),0) "yearpay"
FROM EMPLOYEES;

SELECT first_name, last_name, salary, commission_pct,
     NVL2(commission_pct,(1+commission_pct)*salary * 12 ,salary * 12) "yearpay"
FROM EMPLOYEES;

SELECT first_name, last_name, salary, commission_pct, 
     +((1+commission_pct)*salary * 12, salary*12,0) "yearpay"
FROM EMPLOYEES;

MS-SQL 에서는 SELECT 1+2 의 경우는 3을,  SELECT '1'+'2'의 경우는 문자열로 인식하여 '12'를 반환한다. 이와 비슷한 기능이 Oracle에 있는 데 이를 여결연산자라 하고 Pipe를 2개 사용하여 (||) 나타낸다. 서로간에 연결을 할 때, 형에 따른 제약 없이 '무조건' 연결 시켜 보여준다.

SELECT  1 || '2'||'하하'||SYSDATE ||'입니다' FROM dual;

SELECT first_name|| ' '|| last_name "이름", salary "급여", commission_pct "커미션%",
    COALESCE((1+commission_pct)*salary * 12, salary*12,0) "연봉"

FROM EMPLOYEES;