단일행함수 : 변환함수, 일반함수

SQL함수에는 두가지 유형이 있다.

1. 단일행함수 : 행당 하나의 결과 반환
2. 여러행함수 : 행 집합당 하나의 결과 반환

이 중 단일행함수에 대해 알아보자.

단일행함수종류

단일행함수는 총 5가지이다.

1. 문자함수
2. 숫자함수
3. 날짜함수
4. 변환함수
5. 일반함수

변환함수

변환함수 종류

변환함수는 총 3가지이다.

1. to_char() : 자주사용, 금액 천의 단위로 변환할때 주로 사용
2. to_number() : 거의 사용안함.
3. to_date() : 가장 많이 사용.

변환함수 출력형식

출력형식은 아래와 같다.

1. Y대신 R을 써도 연도표시가능

   • RRRR : 숫자로 된 전체 연도 ex)2020
   • RR : 두자리숫자로 된 전체 연도 ex)20

2. 문자열은 큰 따옴표(“”)로 묶어 추가한다.

3. 숫자 접미어는 숫자를 영어 철자로 표기한다.
   아래 예시는 오늘이 6월 30일인 경우

$ select to_char(sysdate, 'fmDdspth "of" month YYYY fnHH:MI:SS AM')
$ from dual;

//반환값 : thirtieth of june 2020 02:41:38PM

주요예시

1. 예시 - 대소문자
   대문자면 대문자로, 소문자면 소문자로, INITCAP이면 INITCAP형식으로 출력된다.

$ select to_char(sysdate, 'YEAR-MONTH-DD DAY HH24:MI:SS AM') as today
$ from dual;
//반환값 : TWENTY TWENTY-JUNE-30 TUESDAY 14:32:24 PM


$ select to_char(sysdate, '"Today is" RRRR-mm-dd DY! "and time is " HH:MI') as today
$ from dual;
//반환값 : Today is 2020-Jun-30 TUE! and time is 02:32

2. 예시 - 천단위로 표시
   기존 DB값 : 7900, 10000
   아래 코드 후 : $7,900.00, $10,000.00

$ to_char(salary, 'fm$999,999.00') salary
$ from employees

여기서 fm은 지정해놓은 포맷길이 앞뒤에 있을 수 있는 공백이나 문자들을 삭제한다.

추가링크 : fm 설명자세히

쿼리구문에 큰따옴표(“ “)가 사용되는 경우

1. 컬럼명 [AS] “Alias” : 대소문자, 공백, 특수문자 포함 시
2. to_char(sysdate, ‘YYYY-MM-DD “Time” HH24:MI’) : 사용자 형식 내 문자열 포함 시

일반함수

종류	의미
NVL(expr1, expr2)	expr1이 null이면 expr2 반환
NVL2(expr1, expr2, expr3)	expr1이 null이면 expr2 반환
NULLIF(expr1, expr2)	expr1 = expr2이면 null반환, 같지않으면 expr1을 반환
COALESCE(expr1, expr2, …, exprn)	expr1이 null이 아니면 expr1을 출력하고 종료 후 다음행시작한다. 만약 expr1이 null이면 expr2가 null인지 검사하고 expr2가 null아니면 expr2의 실제값 반환하고 종료한다. 만약 expr2가 null이면 세번째인수 검사하면서 실제값이 나올때까지 n번 한다.

NVL 함수 : 가장 많이 사용

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

아래는 직원들의 연봉을 계산하는 쿼리이다.

$ select last_name, salary, commission_pct,
$ (salary*12) + (salary*12*commission_pct) as annual salary
$ from employees;

//출력값 : 에러

commission이 없는 직원의 경우 null값이 입력되어있기때문에 산술연산자가 제대로 계산되지 않는다.
이때 사용하는 것이 NVL함수이다
commission값이 null이면 0으로 변경해서 산술이 잘 이뤄지도록 만든다.

$ select last_name, salary, commission_pct,
$ (salary*12) + (salary*12*nvl(commission_pct,0)) as annual salary
$ from employees;

//출력값 : 정상적으로 출력됨

NVL2함수 : 첫번째 표현식을 검사해서 NULL이 아니면 두번째 표현식을 반환하고 NULL이면 세번째 표현식을 반환한다.

약간 내 느낌에 NVL2는 삼항연산자같다.ㅋㅋㅋㅋ
(조건 expr1 == null)? 참(expr3반환):거짓(expr2반환)

예시문제

employees테이블로부터 사원들의 last_name과 commission_pct를 출력하되 커미션을 받지 않는 직원들은 “No Commission”이라고 출력하는 구문을 작성하시오.

내코드

commission_pct의 데이터형태는 number인데 No Commission은 문자열이라서 replace로 숫자0을 문자열No Commission로 대체했다.

$ SELECT last_name, replace(nvl(commission_pct, '0'), '0', 'No Commission') 
$ as "No Commission"
$ FROM employees;

강사님 코드

to_char로 null이 나올때마다 문자열No Commission로 대체했다

$ SELECT last_name, NVL(TO_CHAR(commission_pct), 'No Commission') COMM
$ FROM employees;