형변환 함수
TO_CHAR
숫자 타입 또는 날짜 타입의 값을 문자타입으로 변환시켜주는 함수입니다.
TO_CHAR(숫자|날짜, [포맷])변환 사례가 다양한데, 그 예시를 우선 숫자에서 문자로 변환하는 것부터 보면 다음과 같습니다.
SELECT TO_CHAR(1234) FROM DUAL;
--1234
SELECT TO_CHAR(1234, '999999') AS "NUMBER" FROM DUAL;
--9의 자리수만큼 공간확보, 오른쪽 정렬, 빈칸공백
-- 1234
SELECT TO_CHAR(1234, '00000') AS "NUMBER" FROM DUAL;
--0의 자리수만큼 공간확보, 오른쪽 정렬, 빈칸 0으로 채움
--01234
SELECT TO_CHAR(1234, 'L99999')FROM DUAL;
--현재 설정된 나라의 로컬 화폐단위 포함
-- \1234 --\는 원화입니다.
SELECT TO_CHAR(1234, '$99999')FROM DUAL;
-- $1234
SELECT TO_CHAR(350000, 'L9,999,999') FROM DUAL;
-- \350,000 --\는 원화입니다.날짜 타입에서 문자타입으로 변경도 가능합니다.
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE) FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;
-- 시:분:초
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL;
-- AM, PM 어떤 것을 쓰던 형식에 맞춰 나옵니다.
SELECT TO_CHAR(SYSDATE, 'HH24 HH:MI:SS') FROM DUAL;
-- 24시간으로 표현합니다.
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY DY') FROM DUAL;
-- DAY : 금요일 처럼 요일로 표현
-- DY : 금 처럼 한 글자로 표현
SELECT TO_CHAR(SYSDATE, 'MON, YYYY') FROM DUAL;
-- MON : 1월 처럼 달 표현
-- 사원들의 이름, 입사날짜 (0000년 00월 00일) 조회
SELECT EMP_NAME, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"')
-- 정해진 형식대로만 사용가능, 정해지지 않은 형식이면 큰따옴표 사용
FROM EMPLOYEE;이외에도 추가적인 포맷이 존재합니다.
연도와 관련된 포맷
SELECT TO_CHAR(SYSDATE, 'YYYY'),
TO_CHAR(SYSDATE, 'YY'),
TO_CHAR(SYSDATE, 'RRRR'),
-- RR룰이 존재한다 -> 50년 이상 값이 + 100 -> EX) 1954
TO_CHAR(SYSDATE, 'RR')
FROM DUAL;월과 관련된 포맷
SELECT TO_CHAR(SYSDATE, 'MM') -- 이번달 숫자 두자리
, TO_CHAR(SYSDATE, 'MON')
, TO_CHAR(SYSDATE, 'MONTH')
FROM DUAL;일에 관련된 포맷
SELECT TO_CHAR(SYSDATE, 'DDD') -- 오늘이 이번년도에서 몇번재 일수
, TO_CHAR(SYSDATE, 'DD') -- 오늘 일자
, TO_CHAR(SYSDATE, 'D') -- 요일 -> 숫자
FROM DUAL;요일에 대한 포맷
SELECT TO_CHAR(SYSDATE, 'DAY')
, TO_CHAR(SYSDATE, 'DY')
FROM DUAL;TO_DATE
숫자타입 또는 문자타입을 날짜 타입으로 변경하는 함수입니다.
TO_DATE(숫자 | 문자, [포맷])SELECT TO_DATE(20100101) FROM DUAL;
SELECT TO_DATE(240219) FROM DUAL;
-- 50년 미만을 자동으로 20XX으로 설정, 50년 이상은 19XX로 설정됩니다.
SELECT TO_DATE('020505') FROM DUAL
-- 숫자는 0으로 시작하면 안됨
SELECT TO_DATE('20240219 120800', 'YYYYMMDD HH24MISS') FROM DUAL;
-- 포맷을 정해줘야 시, 분, 초를 표시할 수 있습니다.TO_NUMBER
문자타입의 데이터를 숫자타입으로 변환시켜주는 함수입니다.
TO_NUMBER(문자, [포맷])SELECT TO_NUMBER('05123456789') FROM DUAL;
--5123456789
SELECT '100000' + '55000' FROM DUAL;
--155000
--SQL은 문자열 자동형변환을 제공
SELECT '100,000' + '55,000' FROM DUAL;
--ORA-01722:invalid number
--포맷은 따로 처리해야 됩니다.
SELECT TO_NUMBER('100,000', '999,999') + TO_NUMBER('55,000', '99,999')
FROM DUAL;
--155000NULL 처리 함수
NVL
NULL을 다른 값으로 변환시켜주는 함수입니다.
NVL(컬럼, 해당컬럼이 NULL일 경우 보여줄 값)기본적으로 계산을 할 때 한 요소라도 NULL이면 최종 결과도 NULL이 나옵니다. 예를 들어 보너스 포함 연봉을 계산할 때 보너스가 없는(NULL) 사람의 경우 연봉이 NULL이 나오게 되는 결과가 생깁니다. 이를 NVL로 해결합니다.
-- 전사원의 이름, 보너스포함 연봉
SELECT EMP_NAME, (SALARY + (SALARY * NVL(BONUS, 0))) * 12
FROM EMPLOYEE;NVL2
NVL에서 NULL이 아닌 경우를 추가한 함수입니다.
NVL2(컬럼, 반환값1, 반환값2)- 반환값 1 : 해당컬럼이 존재할 경우 보여줄 값
- 반환값 2 : 해당컬럼이 NULL일 경우 보여줄 값
-- 보너스 유무 조사
SELECT EMP_NAME, BONUS, NVL2(BONUS, 'O', 'X')
FROM EMPLOYEE;NULLIF
비교대상1, 비교대상2 두 값이 일치하면 NULL, 일치하지 않는다면 비교대상1을 반환합니다.
SELECT NULLIF('123', '123') FROM DUAL; --(null)
SELECT NULLIF('123', '456') FROM DUAL; --123선택 함수
DECODE는 Java의 switch문, CASE WHEN THEN문은 if ~ else ~ 문과 비슷합니다.
DECODE
Java의 switch문과 비슷합니다. 우선 다음과 같은 형식을 따릅니다.
DECODE(비교하고자하는 대상(컬럼, 연산식, 함수식),
비교값1, 결과값1,
비교값2, 결과값2,
비교값3, 결과값3,
...
기본결과값)Java의 switch문에서는 아래와 같습니다.
switch(비교대상) {
case 비교값1:
결과1
break;
case 비교값2:
결과2
break;
...
default:
기본실행코드
}이를 이용해서 예시를 보도록 하겠습니다.
직원의 성명, 기존급여, 인상된 급여 조회 * 각 직급별로 인상해서 조회
- J7인 사원은 급여를 10% 인상(SALARY * 1.1)
- J6인 사원은 급여를 15% 인상(SALARY * 1.15)
- J5인 사원은 급여를 20% 인상(SALARY * 1.2)
- 그외 사원은 급여를 5% 인상(SALARY * 1.05)SELECT EMP_ID, SALARY AS "기존급여",
DECODE(JOB_CODE,
'J7', SALARY * 1.1,
'J6', SALARY * 1.15,
'J5', SALARY * 1.2,
SALARY * 1.05
) AS "인상된 급여"
FROM EMPLOYEE;CASE WHEN THEN
Java의 if ~ else ~ 문과 비슷합니다. 다음과 같은 형식을 따릅니다.
CASE
WHEN 조건식1 THEN 결과값1
WHEN 조건식2 THEN 결과값2
...
ELSE 결과값
END그리고 다음과 같이 급여별 초급, 중급, 고급으로 나누는 쿼리문도 작성 가능합니다.
SELECT EMP_NAME, SALARY,
CASE WHEN SALARY >= 5000000 THEN '고급'
WHEN SALARY >= 3500000 THEN '중급'
ELSE '초급'
END
FROM EMPLOYEE;그룹 함수
앞서 본 함수들은 행의 수만큼 입력 받으면 행의 수만큼 반환을 해주는 단일행 함수입니다.
그룹 함수는 행의 수와 관계 없이 단 하나의 행만을 반환합니다.
종류
SUM, AVG
직관적으로 파악가능하지만 각각 합계와 평균을 구하는 함수입니다.
SUM(숫자타입컬럼)
AVG(숫자타입컬럼)MIN, MAX
직관적으로 파악가능하지만 각각 최소와 최대를 구하는 함수입니다.
MIN(모든타입가능)
MAX(모든타입가능)MIN, MAX는 모든타입에 대해 적용이 가능합니다. 문자열은 ABC, 가나다순, 날짜는 최근 날짜가 더 큰 값을 가진다는 것을 이용하면 됩니다.
COUNT
해당 조건에 맞는 행의 갯수를 세서 반환하는 함수입니다. 양식은 아래와 같습니다.
COUNT(* | 컬럼 | DISTINCT 컬럼)매개변수별 의미는 다음과 같습니다.
COUNT(*)
-- 조회된 결과에 모든 행의 갯수를 세서 반환
COUNT(컬럼)
-- 제시한 해당 컬럼값이 NULL이 아닌 것만 행의 갯수를 세서 반환
COUNT(DISTINCT 컬럼)
-- 해당 컬럼값 중복을 제거한 후 행의 갯수 세서 반환