[oracle] Function 형 변환 함수

재현·2024년 6월 8일

🔍 함수

✍ 특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특별한 명령어, 크게 내장형 함수 와 사용자 정의 함수 로 나뉜다.

✍ 내장 함수 : 오라클 내부에서 기본으로 제공하는 함수, 단일행 함수와, 다중행 함수 로 나뉜다.
✍ 단일행 함수(Single-row-function) : 데이터가 한 행씩 입력되고, 입력된 각 행별로 결과가 하나씩 나오는 함수
✍ 다중행 함수(Multiple-row-function): 여러 행이 입력되어, 하나의 행으로 결과가 반환되는 함수

✍ 이 글에선 오라클에서 제공하는 내장 함수 중 형 변환 함수를 알아보도록 하겠습니다.

🔍 묵시적 형 변환

✍ 명시적으로 함수를 사용하여 형 변환을 하지 않아도 ORACLE 내에서 인식이 가능한 범위안에서의 형 변환이다.

-- 묵시적 형 변환
SELECT '5500' - 4000
FROM DUAL; -- 1500 출력

✍ 지금부터 소개하는 함수가 명시적 형 변환이다.
✍ 그림을 보고 형 변환시킬 수 있는 범위를 이해할 수 있다.

🔍 TO_CHAR 함수

✍ 날짜와 숫자를 문자로 형 변환시키는 함수

TO_CHAR([날짜 데이터 또는 숫자 데이터(필수)], '[출력되길 원하는 문자 포멧(필수)]')

-- TO_CHAR(DATE)
SELECT FIRST_NAME, TO_CHAR(HIRE_DATE, 'MM/YY') AS MONTH_HIRED
FROM EMPLOYEES
WHERE FIRST_NAME = 'Steven';

SELECT FIRST_NAME, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"') HIRE_DATE
FROM EMPLOYEES;

-- NLS_DATE_LANGUAGE=english
SELECT FIRST_NAME, TO_CHAR(HIRE_DATE, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM',
                           'NLS_DATE_LANGUAGE=english') HIRE_DATE
FROM EMPLOYEES;

-- TO_CHAR(NUMBER)
SELECT FIRST_NAME, LAST_NAME, TO_CHAR(SALARY, '$999,999') SALARY
FROM EMPLOYEES
WHERE FIRST_NAME = 'David';

-- ####### = 표시하려는 것보다 크기가 더 클때
SELECT TO_CHAR(2000000, '$999,999') SALARY FROM DUAL;

-- 소수점 자리까지 출력, 반올림
SELECT FIRST_NAME, LAST_NAME, SALARY*0.123456 SALARY1,
    TO_CHAR(SALARY*0.123456, '$999,999.99') SALARY2
FROM EMPLOYEES
WHERE FIRST_NAME = 'David';

🔍 TO_NUMBER 함수

✍ 문자 데이터를 숫자 데이터로 변환시키는 함수

TO_NUMBER('[문자열 데이터(필수)]', '[인식될 숫자 형태(필수)]')

-- , 이나 $ 같은 형 변환이 불가한 문자를 넣었을 경우에는 포맷을 지정해야한다
-- 1500 출력
SELECT TO_NUMBER('$5,500.00', '$99,999.99') - 4000
FROM DUAL;

-- ERROR
SELECT '$5,500.00' - 4000 FROM DUAL;

🔍 TO_DATE 함수

✍ 문자 데이터를 날짜 데이터로 변환시키는 함수

TO_DATE('[문자열 데이터(필수)]', '[인식될 날짜 형태(필수)]')

-- 포맷 형식을 문자열에 맞춰서 작성해야함.
SELECT FIRST_NAME, HIRE_DATE 
FROM EMPLOYEES
WHERE HIRE_DATE = TO_DATE('2003/06/17', 'YYYY/MM/DD');

🔍 NVL 함수

✍ 데이터가 NULL일 경우 다른 값으로 반환하여 결과값을 출력해주는 함수

NVL([NULL인지 여부를 검사할 데이터 또는 열(필수)], 
    [앞의 데이터가 NULL일 경우 반환할 데이터(필수)])

-- NVL(A, B) = A가 NULL이 아니면 그 값을 그대로 반환하고 NULL이면 두 번째 인수를 반환
SELECT FIRST_NAME, SALARY+SALARY*NVL(COMMISSION_PCT, 0)
FROM EMPLOYEES;

🔍 NVL2 함수

✍ 데이터가 NULL일 경우 다른 값으로 반환하여 결과값을 출력해주는 함수(NVL과의 차이)

NVL2([NULL인지 여부를 검사할 데이터 또는 열(필수)], 
     [앞의 데이터가 NULL이 아닐 경우 반환할 데이터 또는 계산식(필수)]
     [앞의 데이터가 NULL일 경우 반환할 데이터 또는 계산식(필수)])

-- NVL2(A, B, C) = A가 NULL이 아닐 경우 B의 값을 반환, NULL일 경우 C의 값을 반환
SELECT FIRST_NAME, NVL2(COMMISSION_PCT, 
                   SALARY+(SALARY*COMMISSION_PCT), SALARY) ANN_SAL
FROM EMPLOYEES;

🔍 COALESCE 함수

✍ 데이터가 NULL일 경우 다른 인자를 확인 후 출력하는 함수(데이터가 전부 NULL일시 NULL 출력)

COALESCE([NULL인지 여부를 검사할 데이터 또는 열(필수)], 
		[NULL인지 여부를 검사할 데이터 또는 열2(필수), 
        [NULL을 대체하여 출력할 문자열(선택)]

-- COALESCE(A, B ....) = NULL이 아닌 최초의 표현식을 출력
SELECT FIRST_NAME, COALESCE(SALARY+(SALARY*COMMISSION_PCT), SALARY) ANN_SAL
FROM EMPLOYEES;

🔍 LNNVL 함수

✍ 조회하려고 하는 조건의 반대 조건으로 부여하여 출력하는 함수(해당 컬럼의 NULL이 존재할 경우 NULL과 같이 출력하기 위해서 사용한다.)

-- LNNVL(A) = A 결과가 FALSE or UNKNOWN이면 TRUE, TRUE면 FALSE를 반환
SELECT FIRST_NAME, COALESCE(SALARY*COMMISSION_PCT, 0) AS BONUS
FROM EMPLOYEES
WHERE LNNVL(SALARY*COMMISSION_PCT >= 650);
-- SALARY*COMMISSION_PCT가 650보다 크거나 같은 사람들이 TRUE이 때문에 FALSE를 반환하여 출력되는 것은 650 미만이거나 NULL인 사람들이다.

🔍 DECODE 함수

✍ 여러 조건을 작성하여 해당되는 조건에 부합되는 결과값을 출력하는 함수

DECODE([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(필수)],
       [조건1], [데이터가 조건1에 일치할 때 반환할 결과],
       [조건2], [데이터가 조건2에 일치할 때 반환할 결과],
       ...
       [조건n], [데이터가 조건n에 일치할 때 반환할 결과],
       [위 조건 모두에 해당하지 않을 경우 반환할 결과 - 지정하지 않으면 NULL 반환])

-- DECODE(column or expression, search1, result1, search2, result2, default)
SELECT JOB_ID, SALARY,
       DECODE(JOB_ID, 'IT_PROG', SALARY*1.10,
                      'FI_MGR', SALARY*1.15,
                      'FI_ACCOUNT', SALARY*1.2,
                       SALARY) AS REVISED_SALARY
FROM EMPLOYEES;

🔍 CASE ~ WHEN ~ THEN 함수

✍ 여러 조건을 작성하여 해당되는 조건에 부합되는 결과값을 출력하는 함수

CASE [검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)]
      WHEN [조건1] THEN [조건1의 결과 값이 true일 때 반환할 결과]
      WHEN [조건2] THEN [조건2의 결과 값이 true일 때 반환할 결과]
      ...
      WHEN [조건n] THEN [조건n의 결과 값이 true일 때 반환할 결과]
      ELSE [위 조건 모두에 해당하지 않을 경우 반환할 결과 - 지정하지 않으면 NULL 반환]
END

-- CASE column or expression WHEN condition1 THEN result1 ELSE result END
SELECT JOB_ID, SALARY,
    CASE JOB_ID WHEN 'IT_PROG' THEN SALARY*1.10
                WHEN 'FI_MGR' THEN SALARY*1.15
                WHEN 'FI_ACCOUNT' THEN SALARY*1.2
        ELSE SALARY
    END AS REVISED_SALARY
FROM EMPLOYEES;

SELECT JOB_ID, SALARY,
    CASE WHEN JOB_ID = 'IT_PROG' THEN SALARY*1.10
         WHEN JOB_ID ='FI_MGR' THEN SALARY*1.15
         WHEN JOB_ID ='FI_ACCOUNT' THEN SALARY*1.2
        ELSE SALARY
    END AS REVISED_SALARY
FROM EMPLOYEES;

✍ CASE 문과 DECODE의 차이
CASE문은 DECODE 함수와 달리 기준 데이터가 필수가 아니다.
CASE문은 모든 조건에 부합하지 않는 경우에 대해 반환값을 설정하지 않으면 NULL 반환.
CASE문은 DECODE 함수와 달리 (=) 비교 외에도 다른 조건을 걸 수 있다.