─━ IT ━─

[Oracle] NVL, COALESCE 설명 및 사용법

DKel 2021. 2. 6. 12:00
반응형

Number와 Binay_Double의 NVL 예제

 

문법

NVL ( expr1 , expr2 )

COALESCE ( expr_list ) - Oracle 9i 이상 지원.

 

요청 파라미터

expr1 NULL로 설정 가능한 식 (숫자 식, 문자열 식, 날짜 식, etc)

expr2 숫자, 문자열, 날짜

expr_list 쉼표로 구분된 목록 (2개 이상의 값 목록)

 

리턴

return [첫 번째 인수의 형식, NULL 또는 숫자라면 우선 순위가 높은 형태로 반환]

 

NVL 함수의 내용

식 expr1 이 NULL이면 expr2 의 값을 되 돌린다. Null Value Logic의 약자

COALESCE 함수의 내용, Oracle 9i


NVL 함수를 일반화한 함수 인수에 포함된 첫번째 "비 NULL 값"을 돌린다.

COALESCE ( expr1 , ..., exprN )처럼 가변 인자를 가진다.

 

COALESCE 함수의 추가 설명과 주의사항

COALESCE 함수 인수의 수 (N)가 1 인 경우에는 오류를 돌린다.

N = 2 ... NVL ( expr1 , expr2 )와

같은 N> 2의 경우 ...

CASE WHEN expr1 IS NOT NULL then COALESCE ( expr2 , ..., exprN ) end

===> 자기 참조 내용과 같게된다.

 


덧붙여서 NVL(expr1, NVL(expr2 (... (NVL (exprN-1 , exprN ) ...)의 작성 방법은 

함수의 최상단에서 평가되기 때문에 쓸데없는 처리가 많아지므로 앞에 두는 것이 좋다.

COALESCE(col1, '<NULL'>) ⇒ NVL(col1, '<NULL>')와 같은 의미

주의 사항

COALESCE의 expr_list 인수의 최소 및 최대 2 개 이상 256 이하이다.
내장 SQL 함수의 가변 인수는 아마 상한이 255 또는 256 이하까지 (※)이다. DECODE 함수 는 상한이 255 개가된다 
(※) PL / SQL의 인수는 최대 65536이지만 SQL 함수는 256 개 이상의 인수를 가질 함수는 본 적이 없다.

NVL의 사용 예

문자열의 경우

SQL> select  vc2, NVL (vc2, 'IS NULL' ), dump ( NVL (vc2, 'IS NULL' ), 1016) dump_code 
  2 from nvl_sample; 
 
VC2               NVL (VC2, 'ISNULL' ) DUMP_CODE
 ------ ---------- ---------------------- ------------------ ------------------------
안녕하세요 Typ = 1 Len = 5 CharacterSet = KO16SJISTILDE : .. 
<NULL> IS NULL Typ = 1 Len = 7 CharacterSet = KO16SJISTILDE : ..

 

다른 문자 집합 숫자 형에 의한 NVL

NVL의 인수로 다른 문자 집합의 문자열을 사용하면 첫번째 인수의 형태로 평가하고 되돌아간다.
수치 형의 경우에는 BINATY_DOUBLE > BINATY_FLOAT > NUMBER 우선순위에서 형식 변환이 행해진다.

인수1 expr1 을 NVARCHAR2 (AL16UFT16) 인수 2 expr2 를 데이터베이스 문자 집합에서 NVL을 실행한 샘플

SQL> select  nvc2, NVL (nvc2, '이것은 NULL입니다 (SJIS)' ) nvl_nvchar2, 
  2          dump ( NVL (nvc2, '이것은 NULL입니다 (SJIS)' ), 1016) dump_code 
  3 from nvl_sample; 
 
NVC2 NVL_NVCHAR2 DUMP_CODE - --------------- ---------------------- ------------- -----------------------------
하이요(UTF16)하이요(UTF16) Typ = 1 Len = 20 CharacterSet = AL16UTF16 : 30,42 .. 
<NULL> 값이 NULL입니다 (SJIS) Typ = 1 Len = 30 CharacterSet = AL16UTF16 : 30,53 .. 
                                                                  ^^^^^^^^^^ 
SQL> select  nvc2, NVL

 (nvc2, n '이 NULL입니다 (UTF16)' ) nvl_nvchar2, 
  2    dump ( NVL (nvc2, n '이 NULL입니다 (UTF16)' ), 1016) dump_code 
  3 from nvl_sample; 
 
NVC2 NVL_NVCHAR2 DUMP_CODE ----- ----------- ---------------------- ----------------- -------------------------
하이요(UTF16)하이요(UTF16) Typ = 1 Len = 20 CharacterSet = AL16UTF16 : 30,42 .. 
<NULL> 이것은 NULL입니다 (UTF16) Typ = 1 Len = 32 CharacterSet = AL16UTF16 : 30,53 ..

 

NUMBER와 BINAY_DOUBLE의 NVL 예

QL> select  num, dump ( NVL (num, 0d)) from nvl_sample2; 
 
       NUM DUMP ( NVL (NUM, 0D))
 ---------- ------------- --------------------------- 
         1 Typ = 101 Len = 8 : 191,240,0,0,0,0,0,0 
<NULL > Typ = 101 Len = 8 : 128,0,0,0,0,0,0,0 - Typ = 101 : TO_BINARY_DOUBLE을 나타내는
SQL> select  d, dump ( NVL (d, 0)) from nvl_sample2; 
 
         D DUMP ( NVL (D, 0))
 ---------- ------------- --------------------------- 
  1.0E + 000 Typ = 101 Len = 8 : 191,240,0,0,0,0,0, 0 
<NULL> Typ = 101 Len = 8 : 128,0,0,0,0,0,0,0

 

COALESCE의 사용 예

SQL> select  COALESCE (null, null, 'NOT NULL' , null) from dual ; COALESCE (NULL, NU
 ---------------- 
NOT NULL
SQL> select  COALESCE (null, null) all_null from dual ; 
 
ALL_NULL -------------------- 
<NULL>
SQL> select  COALESCE (null) from dual ; 
            * 
행 1에 오류가 발생했습니다. : 
ORA-00938 : 함수의 인수가 부족합니다.
반응형