[Oracle/PL-SQL 예제] SQL 함수(일반 함수 및 조건식,DECODE,CASE,NVL,NVL2)

[정민이]

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

일반 함수 및 조건식(General Functions and Conditional Expressions)

NVL, NVL2

NVL(expr1, expr2) : IF expr1 IS NULL expr2 ELSE expr1,  expr1과 expr2의 DataType이 다르면 expr2를 비교전 expr1로 변환 한다.
NVL2(expr1, expr2, expr3): IF expr1 IS NULL THEN expr3 ELSE expr2, expr1은 어떠한 DataType 이라도 가능하며 expr2 및 expr3의 경우 LONG을 제외한 DataType이면 된다. 만약 expr2와 espr3가 DataType이 다르다면 비교전에 expr3을 expr2로 변환 한다.

--> 아래 예문은 NVL함수 안의 표현식의 형이 다르므로 오류가 발생 한다.
SQL> select ename, nvl(comm, 'NOT APPLICABLE') "COMMISSION"
  2  from emp
  3  where deptno=30;
select ename, nvl(comm, 'NOT APPLICABLE') "COMMISSION"
                        *
1행에 오류:
ORA-01722: 수치가 부적합합니다
SQL> 1 select ename, nvl(to_char(comm), 'NOT APPLICABLE') "COMMISSION"
SQL> /
ENAME      COMMISSION
---------- ----------------------------------------
ALLEN      300
WARD      500
MARTIN    1400
BLAKE      NOT APPLICABLE
TURNER    0
JAMES      NOT APPLICABLE
6 개의 행이 선택되었습니다.

SQL> select ename, sal, comm, nvl2(to_char (comm), sal and comm', sal) income
  2  from emp
  3  where deptno = 20;

ENAME            SAL      COMM INCOME
---------- ---------- ---------- ------------
SMITH            800        96 SAL AND COMM
JONES            2975            SAL
SCOTT            3000            SAL
ADAMS            1100            SAL
FORD            3000            SAL
[실습 4-14 NVL, NVL2]

DECODE, CASE

DECODE (column|expr1, search1, result1 [,search2,result2,,,][,default return]) : column1이 search1과 같으면 result1, search2와 같으면 result2 아무것도 같지 않으면 default return을 돌려 주며 4개 이상의 아규먼트로 이루어 진다.
CASE: DECODE 함수와 기능이 비슷하지만 좀더 직관적인 함수이다. 오라클 9i 이전 버전 에서는 DECODE 함수가 주로 이용 되었으며 아래 예문을 통해 의미를 이해 하자.

SQL> select ename, decode(deptno, 10, 'Accounting',
  2                              20, 'Research',
  3                              30, 'Sales',
  4                              40, 'Operations',
  5                              'Unknown') department
  6  from emp
7        /
--> 아래의 CASE 함수와 동일한 결과가 나타난다.
SQL> select ename,
  2  (case deptno
  3        when 10 then 'Accounting'
  4        when 20 then 'Research'
  5        when 30 then 'Sales'
  6        when 40 then 'Operations'
  7        else 'Unknown'
  8        end) department
  9  from emp
10  /

ENAME      DEPARTMENT
---------- ----------
SMITH      Research
ALLEN      Sales
……
16 개의 행이 선택되었습니다.
--> 10번 부서의 급여 평균이 800보다 크면 ‘H’, 작으면 ‘L’을 출력해 보자.
SQL> select case
  2          when (select avg(sal) from emp where deptno=10) >= 800 then 'H'
  3                            else 'L'
  4          end "급여수준"
  5  from dual;

-
H