Oracle - 변환 함수

암시적 데이터 유형 변환

Oracle 서버는 자동으로 표현식에서 데이터 유형 변환을 수행합니다. 

job_id 의 Datatype은 VARCHAR2 이지만 job_id = 2 표현식을 사용하면 숫자 2가 문자열 '2'로 암시적으로 변환됩니다.

Source	Target
VARCHAR2 or CHAR	NUMBER
VARCHAR2 or CHAR	DATE

 

명시적 데이터 유형 변환

 

 TO_CHAR 함수

TO_CHAR(date,['format_model'])

format_model 

• 작은 따옴표로 묶어야 합니다.
• 대소문자를 구분합니다.
• 임의의 유효한 날짜 형식 요소를 포함할 수 있습니다.
• 채워진 공백을 제거하거나 선행 0을 출력하지 않는 fm 요소를 갖습니다.
• 쉼표로 날짜갑소가 구분됩니다. 

날짜 형식 모델

요소	결과
YYYY	숫자로 된 전체 연도
YEAR	영어 철자로 표기된 연도
MM	월의 2자리 값
MONTH	월의 3자 약어
DY	요일의 3자 약어
DAY	요일 전체 이름
DD	숫자 형식의 월간 일
HH 또는 HH12	12시간 형식
HH24	24시간 형식
MI	분(0~59)
SS	초(0~59)
SSSSS	자정 이후의 초(0~86399)

 

날짜에 TO_CHAR 함수 사용

SELECT last_name, TO_CHAR(hire_date,'fmDD MONTH YYYY') AS HIREDATE
FROM employees
## fmDD 공백없이 표현

CREATE TABLE test1
AS
SELECT last_name, TO_CHAR(hire_date, 'YYYYMMHH24MISS') AS HIREDATE
FROM employees;

DESC test1;

 

숫자에 TO_CHAR함수 사용

요소	결과
9	숫자를 나타냄
0	0이 표시되로록 강제 적용
$	부동 달러 기호 배치
.	소수점 출력
,	천 단위 표시자로 출력

SELECT TO_CHAR(salary, '$99,999.00') AS SALARY
FROM employees
WHERE last_name = 'Ernst';

 

TO_NUBER 함수

TO_NUMBER(char[, 'format_model'])

TO_DATE 함수

TO_DATE(char[,'format_model'])

 

문자 형식을날짜 형식으로 변경

CREATE table test2
AS
SELECT last_name, TO_DATE(hire_date, 'YYYY/MM HH24:MI:SS') AS HIREDATE
FROM test1;

DESC test2;

 

일반 함수

함수	설명
NVL	null 값을 실제 값으로 변환합니다.
NVL2	expr1이 null 이 아닌 경우 NVL2는 expr2를 반환합니다. expr1이 null인 경우 NVL2는 expr3를 반환합니다. 인수 expr1은 임의의 데이터 유형을 가질 수 있습니다.
NULLIF	두 표현식을 비교하여 같으면 null을 반환하고 같지 않으면 첫 번째 표현식을 반환합니다.
COALESCE	표현식 리스트에서 null 이 아닌 첫 번째 표현식으로 반환합니다.

 

NVL 함수 

• null 값을 실제 값으로 변환합니다.
• 사용할 수 있는 데이터 유형은 날짜, 문자 및 숫자입니다.
• 데이터 유형이 일치해야합니다.  

NVL(expr1, expr2)

• expr1은 null을 포함할 수있는 소스 값 또는 표현식입니다.
• expr2은 null을 변환하기 위한 대상 값입니다.  

데이터 유형	변환 예제
NUMBER	NVL(number_column, 9)
DATE	NVL(date_column, '2023-01-14')
CHAR 또는 VARCHAR2	NVL(character_column, 'Unavailable')

SELECT last_name, salary, NVL(commission_pct,0), (salary*12) + (salary*12*(NVL(commission_pct,0))) AN_SAL
FROM employees;

 

NVL2 함수

• 첫번째 표현식을 검사합니다.
• 첫번째 표현식이 null이 아니면 NVL2 함수는 두번쨰 표현식을 반환합니다. 
• 첫번째 표현식이 null이면 세번째 표현식을 반환합니다.

NVL2(expr1, expr2, expr3)

• expr1은 null을 포함할 수 있는 소스 값 또는 표현식입니다,.
• expr은 expr1이 null이 아닌 경우에 반환되는 값입니다.
• expr3는 expr1이 null인 경우 반환되는 값입니다. 

SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM', 'SAL') income
FROM employees
WHERE department_id IN (50,80);

 

NULLIF  함수

• NULLIF 함수는 두 표현식을 비교합니다.

NULLIF(expr1, expr2)

• NULL IF 함수가 expr1과 expr2를 비교합니다.
• 두 표현식이 같으면 이 함수는 null을 반환합니다.
• 두표현식이 다르면 이 함수는 expr1을 반환합니다. 그러나 exrp1에 대해 리터럴 NULL을 지정할 수 없습니다.  

SELECT first_name, LENGTH(fist_name) "expr1",
       last_name, LENGTH(last_name) "expr2",
       NULLIF(LENGTH(first_name), LENGTH(last_name)) AS result
FROM employees;

 

COALESCE 함수

• NVL 함수 대신 COALESCE 함수를 사용했을 때의 이점은 COALESCE 함수가 여러 대체값을 수용할 수 있다는 것입니다. 
• 첫번쨰 표현식이 null이 아닌 경우 COALESCE 함수는 해당 표현식을 반환합니다. 그렇지 않은 경우 나머지 표현식에 CALESCE를 수행합니다.

COALESCE(expr1, expr2, ... exprn)

• expr1은 표현식이 null 이 아닌 경우 이 표현식을 반환합니다.
• expr2은 첫번째 표현식이 null이고 이 표현식이 null이 아닌 경우 이 표현식을 반환합니다.
• exprn은 선행 표현식이 null인 경우 이 표현식을 반환합니다. 

SELECT last_name, salary, commission_pct,
       COALESCE(slary + (commission_pct*salary), salary+2000) "New Salary"
FROM employees;

• 커미션을 받지 않는 사원에게 $2000의 급여 인상을 제공합니다.
• 또한 커미션을 받는 사원의 경우 기존 급여에 커미션 금액을 추가한새 급여를 query에서 계산해야 합니다.