「Oracle」DECODE関数とNVL関数のサンプル

2020年10月6日

構文
DECODE(expr, condition1, value1 [,condition2, value2…] [,default])
NVL(expr1, expr2)
1.DECODE関数

SELECT

DECODE(0, 1, 'TA', 2, 'TB', 3, 'TC') AS RESULT_D

FROM

DUAL;

SELECT DECODE(0, 1, 'TA', 2, 'TB', 3, 'TC') AS RESULT_D FROM DUAL;

SELECT
    DECODE(0, 1, 'TA', 2, 'TB', 3, 'TC') AS RESULT_D
FROM
  DUAL;

結果　NULL

2.DECODE関数　デフォルト値

SELECT

DECODE(0, 1, 'TA', 2, 'TB', 3, 'TC','DEFALUT VALUE') AS RESULT_D

FROM

DUAL;

SELECT DECODE(0, 1, 'TA', 2, 'TB', 3, 'TC','DEFALUT VALUE') AS RESULT_D FROM DUAL;

SELECT
    DECODE(0, 1, 'TA', 2, 'TB', 3, 'TC','DEFALUT VALUE') AS RESULT_D
FROM
  DUAL;

結果　DEFALUT VALUE

3.NVLとDECODE関数

SELECT

NVL(

DECODE(20, 21, 'TA', 22, 'TB', 23, 'TC')

, 'DEFALUT VALUE'

) AS RESULT_D

FROM

DUAL;

SELECT NVL( DECODE(20, 21, 'TA', 22, 'TB', 23, 'TC') , 'DEFALUT VALUE' ) AS RESULT_D FROM DUAL;

SELECT
  NVL( 
    DECODE(20, 21, 'TA', 22, 'TB', 23, 'TC')
    , 'DEFALUT VALUE'
  ) AS RESULT_D
FROM
  DUAL;

結果　DEFALUT VALUE

Posted by arkgame