형변환 함수
(1) TO_CHAR
: TO_CHAR(NUMBER/DATE, FORMAT)
- 결과값을 포맷에 맞춰 숫자 또는 날짜 타입을 문자 타입으로 반환한다.
- 숫자를 문자열로 변환하기
-- 0인 포맷의 빈칸에 지정해준 형식 0으로 채운다.
SELECT TO_CHAR(1234, '0000000')
FROM DUAL; -- '0001234'
-- 9인 포맷의 빈칸에 공백으로 채운다.
SELECT TO_CHAR(1234, '9999999')
FROM DUAL; -- ' 1234'
-- 현재 설정한 나라의 화폐 단위 표시
SELECT TO_CHAR(1234, 'L0000000')
FROM DUAL; -- '￦0001234'
SELECT TO_CHAR(1234, 'L9999999')
FROM DUAL; -- ' ￦1234'
SELECT TO_CHAR(1234, 'L99,999')
FROM DUAL; -- ' ￦1,234'
- 날짜를 문자열로 변환하기
SELECT TO_CHAR(SYSDATE)
FROM DUAL; -- '22/03/21'
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
FROM DUAL; -- '2022-03-21'
-- 연도로 표현할 수 있는 포맷
SELECT TO_CHAR(SYSDATE, 'YYYY') -- '2022'
, TO_CHAR(SYSDATE, 'RRRR') -- '2022'
, TO_CHAR(SYSDATE, 'YY') -- (20)'22'
, TO_CHAR(SYSDATE, 'RR') -- (20)'22'
, TO_CHAR(SYSDATE, 'YEAR') -- TWENTY TWENTY-TWO(로마식 표기)
FROM DUAL;
-- 월로 표현할 수 있는 포맷
SELECT TO_CHAR(SYSDATE, 'MM') -- '03'
, TO_CHAR(SYSDATE, 'MON') -- '3월'
, TO_CHAR(SYSDATE, 'MONTH') -- '3월'
, TO_CHAR(SYSDATE, 'RM') -- 'Ⅲ'
FROM DUAL;
-- 일로 표현할 수 있는 포맷
SELECT TO_CHAR(SYSDATE, 'D') -- '2' -- 일주일 기준 일요일부터 며칠 째인지 알려줌
, TO_CHAR(SYSDATE, 'DD') -- '21' -- 한 달 기준 1일부터 며칠 째인지 알려줌
, TO_CHAR(SYSDATE, 'DDD') -- '080' -- 1년 기준 1월 1일부터 며칠 째인지 알려줌
FROM DUAL;
-- 요일로 쓸 수 있는 포맷
SELECT TO_CHAR(SYSDATE, 'DY') -- '월' -- '요일' 없이 반환
, TO_CHAR(SYSDATE, 'DAY') -- '월요일'
FROM DUAL;
- 2015년 이후에 입사한 직원들의 이름과 입사일 조회하기
SELECT EMP_NAME, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일" (DY)')
FROM EMPLOYEE
WHERE EXTRACT(YEAR FROM HIRE_DATE) >= 2015 -- 날짜에서 연도만 추출하기
ORDER BY HIRE_DATE; -- 오름차순
- 시, 분, 초: 오전(AM), 오후(PM)
º 12시간 기준
-- 오후(AM/PM을 생략하면 시간만 반환)
SELECT TO_CHAR(SYSDATE, 'PM HH:MI:SS')
FROM DUAL; -- '오후 01:54:58'
º 24시간 기준
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS')
FROM DUAL; -- '13:56:17'
(2) TO_DATE
: TO_DATE(NUMBER/CHARACTER, FORMAT) - 숫자 또는 문자 타입을 날짜 타입으로 변환한다.
- 결과값은 DATE 타입으로 반환된다.
- 반환 기본 포맷: YY/MM/DD
SELECT TO_DATE(20220309)
FROM DUAL; -- 22/03/09
SELECT TO_DATE(001234)
FROM DUAL; -- 에러. 0으로 시작하는 숫자로 인식해서 오류 발생.
SELECT TO_DATE('001234')
FROM DUAL; -- 00/12/34 -- 문자열 타입으로 넣으면 날짜 타입으로 반환된다.
(3) TO_NUMBER
: TO_NUMBER(CHARACTER, FORMAT) - 문자 타입을 숫자 타입으로 변환한다.
- 결과값은 숫자(NUMBER) 타입으로 반환된다.
- 자동 형변환(문자열 → 숫자)
SELECT '123' + '123'
FROM DUAL; -- 246 / 자동형변환 후 산술 연산까지 한다.
SELECT '100,000' + '50,000'
FROM DUAL; -- 에러. 문자 ','가 포함되어 있어서 자동 형변환이 안된다.
SELECT TO_NUMBER('100,000', '999,999') + TO_NUMBER('50,000', '99,999')
FROM DUAL; -- TO_NUMBER로 형변환을 한 후 산술 연산한다.
NULL 처리 함수
(1) NVL
: NVL(컬럼명, 해당 컬럼명이 NULL일 경우 반환할 값)
- 해당 컬럼값이 존재할 경우(NULL이 아닐 경우) : 기존의 컬럼값을 반환한다.
- 해당 컬럼값이 존재하지 않을 경우(NULL값일 경우) : 제시한 특정값을 반환한다.
- 주의사항 : 컬럼의 타입과 반환할 값의 타입이 서로 같아야 한다. 타입이 다르면 오류가 난다.
- 사원명, 보너스 조회하기(단, 보너스가 없으면 0으로 출력한다.)
SELECT EMP_NAME, BONUS, NVL(BONUS, 0)
FROM EMPLOYEE;
기존 BONUS 컬럼은 보너스가 없을 때 (null)이 출력된다.
NVL을 사용하여 해당 컬럼값이 null값일 때 0으로 반환하게 하면
왼쪽 결과와 같이 null이 아니라 0으로 반환된다.
- 사원명, 부서코드 조회하기(단, 부서코드가 없으면 '없음'으로 출력한다.)
SELECT EMP_NAME, DEPT_CODE, NVL(DEPT_CODE, '없음')
FROM EMPLOYEE;
기존 DEPT_CODE 컬럼은 컬럼값이 없을 때 null값이 출력된다.
그런데 NVL을 사용하여 null을 '없음'으로 반환하게 하면
왼쪽과 같이 null이 아니라 '없음'으로 반환된다.
(2) NVL2
: NVL2(컬럼명, 결과값1, 결과값2)
- 해당 컬럼에 데이터가 존재할 경우(NULL이 아닐 경우) : 결과값1을 반환한다.
- 해당 컬럼에 데이터가 존재하지 않은 경우(NULL일 경우) : 결과값2를 반환한다.
- Java의 삼항연산자와 유사
- 보너스가 있는 사원은 '보너스O', 보너스가 없는 사원은 '보너스X'을 조회
SELECT EMP_NAME, NVL2(BONUS, '보너스O', '보너스X')
FROM EMPLOYEE;
BONUS 컬럼을 놓고 보너스에 데이터값이 있으면 첫 번째 결과값을 반환하고, 데이터값이 없으면 두 번째 결과값을 반환한다.
(3) DECODE
: DECODE(비교대상, 조건값1, 결과값1, 조건값2, 결과값2, ..., 조건값n, 결과값n, 결과값)
- 비교대상 : 컬럼명, 산술연산(결과는 NUMBER), 함수가 해당된다.
- Java의 switch문과 유사
- 사번, 사원명, 주민등록번호, 성별 조회하기
SELECT EMP_ID, EMP_NAME, EMP_NO, DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남자', 2, '여자') 성별
FROM EMPLOYEE;
비교대상은 성별이다. 성별은 주민등록번호 뒷자리 첫 번째 자리에 해당하므로 특정 문자열을 추출하는 SUBSTR 함수를 사용하면 된다.
그 다음으로 조건값과 결과값을 설정해야 한다. 주민등록번호의 성별 숫자가 1일 때(조건값1)는 남성(결과값1)을, 2일 때(조건값2)는 여성(결과값2)을 반환한다.
(4) CASE WHEN THEN
: CASE WHEN 조건식1 THEN 결과값1
WHEN 조건식2 THEN 결과값2
...
ELSE 결과값
END
- 특정 조건을 마음대로 제시할 수 있다.
- Java의 if ~ if else문과 유사
- 직번, 직원명, 주민등록번호, 성별 조회하기
SELECT EMP_ID, EMP_NAME, EMP_NO
, CASE WHEN SUBSTR(EMP_NO, 8, 1) = 1 THEN '남성'
WHEN SUBSTR(EMP_NO, 8, 1) = 3 THEN '남성'
ELSE '여성'
END 성별
FROM EMPLOYEE;
DECODE에서 했듯이 성별은 주민등록번호에서 SUBSTR 함수로 뽑아내면 된다.
'SUBSTR(EMP_NO, 8, 1)'이 1이면 남성을 반환하고, 2000년생부터는 성별 자리가 3이므로 똑같이 'SUBSTR(EMP_NO, 8, 1)'이 3이면 남성을 반환한다. 그 다음 여성이 남았는데, 성별 자리 1과 3을 제외하면 당연히 여성이므로 어떠한 조건을 적지 않고 ELSE절에 결과값만 적어주면 된다. 그리고 마무리 END.