[Oracle/오라클] 함수 (형변환 / NULL / 선택 / 그룹)
형 변환 함수
- TO_CHAR : 날짜 혹은 숫자형 데이터를 문자형 데이터로 변환하여 반환
작성법 - TO_CHAR(DATE | NUMBER, [FORMAT])
// 숫자 타입 => 문자 타입
SELECT TO_CHAR(1234)
FROM DUAL;
// '1234'
SELECT TO_CHAR(1234, '999999')
FROM DUAL;
// ' 1234'
// 6칸짜리 공간 확보, 오른쪽 정렬, 빈칸 공백
SELECT TO_CHAR(1234, 'L99999')
FROM DUAL;
// '￦1234'
// 현재 설정된 나라(LOCAL)의 화폐단위
SELECT TO_CHAR(1234, '$99999')
FROM DUAL;
// '$1234'
SELECT TO_CHAR(1234, 'L99,999')
FROM DUAL;
// '￦1,234'// 날짜 타입 => 문자 타입
SELECT TO_CHAR(SYSDATE)
FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS')
FROM DUAL;
// HH : 12시간 형식
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS')
FROM DUAL;
// HH : 24시간 형식
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS')
FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY DY')
FROM DUAL;
// DAY : 월요일, DY : 월
SELECT TO_CHAR(SYSDATE, 'MON, YYYY')
FROM DUAL;
// 12월, 2022
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일"')
FROM DUAL;// 년도와 관련된 포맷
SELECT TO_CHAR(SYSDATE, 'YYYY'), -- 2022
TO_CHAR(SYSDATE, 'YY'), -- 22
TO_CHAR(SYSDATE, 'RRRR'), -- 2022
TO_CHAR(SYSDATE, 'RR'), -- 22
TO_CHAR(SYSDATE, 'YEAR') -- TWENTY TWENTY-TWO
FROM DUAL;
// 월과 관련된 포맷
SELECT TO_CHAR(SYSDATE, 'MM'), -- 12
TO_CHAR(SYSDATE, 'MON'), -- 12월
TO_CHAR(SYSDATE, 'MONTH'), -- 12월
TO_CHAR(SYSDATE, 'RM') -- XII / 월을 로마 숫자로 표시
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(CHARACTER | NUMBER [FORMAT])
SELECT TO_DATE(20100101)
FROM DUAL;
// 10/01/01
SELECT TO_DATE(100101)
FROM DUAL;
// 10/01/01
SELECT TO_DATE('070101')
FROM DUAL;
// 07/01/01
// 첫 글자가 0인 경우 문자 타입으로 변경해줘야함
SELECT TO_DATE('041030 143000', 'YYMMDD HH24MISS')
FROM DUAL;
// 시간을 표시하고 싶을 때는 꼭 포맷이 있어야 됨
SELECT TO_DATE('140630', 'YYMMDD')
FROM DUAL;
// 14/06/30
// 2014년
SELECT TO_DATE('980630', 'YYMMDD')
FROM DUAL;
// 98/06/30
// 2098년
// 현재 세기(21세기)로 반영
SELECT TO_DATE('980630', 'RRMMDD')
FROM DUAL; -- 1998년
// 98/06/30
// 1998년
// RR : 해당 두자리 년도 값이 50 미만일 경우 현재 세기 반영, 50 이상일 경우 이전 세기 반영
- TO_NUMBER : 날짜 혹은 문자형 데이터를 숫자형 데이터로 변환하여 반환
작성법 - TO_NUMBER (CHARACTER, [FORMAT])
SELECT TO_NUMBER('05123475')
FROM DUAL;
// 5123475
SELECT '1000000' + '55000'
FROM DUAL;
// 1055000
// 자동형변환
SELECT TO_NUMBER('1,000,000', '9,999,999') + TO_NUMBER('55,000', '99,999')
FROM DUAL;
// 1055000
// 강제형변환
NULL 처리 함수
- NVL : NULL로 되어 있는 컬럼의 값을 인자로 지정한 숫자 혹은 문자로 변경하여 반환
작성법 - NVL(P1, P2)
* P1 : NULL데이터를 처리할 컬럼명 혹은 값
* P2 : NULL값을 대체하고자 하는 값
// 사원들의 이름과 보너스 조회
SELECT EMP_NAME, NVL(BONUS, 0)
FROM EMPLOYEE;
// 사원들의 이름과 부서 조회
SELECT EMP_NAME, NVL(DEPT_CODE, '부서없음')
FROM EMPLOYEE;
- NVL2
작성법 - NVL2(컬럼, 반환값1, 반환값2)
* 컬럼값이 존재할 경우 반환값1 반환
* 컬럼값이 NULL일 경우 반환값2 반환
// 사원들의 부서 여부 조회
SELECT EMP_NAME, NVL2(DEPT_CODE, '부서있음', '부서없음')
FROM EMPLOYEE;
- NULLIF
작성법 - NULLIF(비교대상1, 비교대상2)
* 두 개의 값이 일치하면 NULL 반환
* 두 개의 값이 일치하지 않으면 비교대상1 값을 반환
SELECT NULLIF('123', '123')
FROM DUAL;
// NULL
SELECT NULLIF('123', '456')
FROM DUAL;
// 123
선택 함수
- DECODE : 비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환
작성법 - DECODE(표현식, 조건1, 결과1, 조건2, 결과2, ..., DEFAULT)
* DEFAULT : 모든 조건이 불일치 시 반환할 값
// 사원명, 주민번호, 성별 조회
SELECT EMP_NAME, EMP_NO, SUBSTR(EMP_NO, 8, 1),
DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여') AS "성별"
FROM EMPLOYEE;
// 직원의 급여 조회시 각 직급별로 인상해서 조회
// J7인 사원은 급여를 10% 인상, J6인 사원은 급여를 15% 인상, J4인 사원은 급여를 20% 인상
// 그 외의 사원은 급여를 5% 인상
SELECT EMP_NAME, JOB_CODE, SALARY,
DECODE(JOB_CODE, 'J7', SALARY * 1.1,
'J6', SALARY * 1.15,
'J5', SALARY * 1.2,
SALARY * 1.05) AS "인상된 급여"
FROM EMPLOYEE;
- CASE : 비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환(조건은 범위 값 가능)
작성법
CASE
WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
WHEN 조건3 THEN 결과3
...
ELSE 결과N
END
// 사원들의 연봉 레벨 조회
SELECT EMP_NAME, SALARY,
CASE WHEN SALARY >= 5000000 THEN '고급'
WHEN SALARY >= 3500000 THEN '중급'
ELSE '초급'
END AS "레벨"
FROM EMPLOYEE;
그룹 함수
하나 이상의 행을 그룹으로 묶어 연산하며 총합, 평균 등을 하나의 컬럼으로 반환하는 함수
- SUM : 해당 컬럼 값들의 총합 반환
// 전 사원의 총 급여합
SELECT SUM(SALARY) || '원' AS "총 급여합"
FROM EMPLOYEE;
// 70096240원
// 부서코드가 D5인 사원들의 총 연봉합
SELECT SUM(SALARY * 12)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';
// 189120000
- AVG : 해당 컬럼 값들의 평균 반환
// 전체 사원들의 평균 급여 조회
SELECT ROUND(AVG(SALARY)) || '원' AS "평균 급여"
FROM EMPLOYEE;
// 3047663원
// 전체 사원들의 보너스 평균 조회
SELECT ROUND(AVG(NVL(BONUS, 0)), 2)
FROM EMPLOYEE;
// 0.08
- MAX / MIN : 그룹의 최대값과 최소값 반환
// 가장 높은 급여와 가장 낮은 급여 조회
SELECT MAX(SALARY), MIN(SALARY)
FROM EMPLOYEE;
// 가장 오래된 입사일과 가장 최근 입사일 조회
SELECT MAX(HIRE_DATE), MIN(HIRE_DATE)
FROM EMPLOYEE;
- COUNT : 테이블 조건을 만족하는 행의 개수 반환
* COUNT(*) : 조회된 결과의 모든 행 개수를 세서 반환
* COUNT(컬럼) : 제시한 해당 컬럼값이 NULL이 아닌 것만 행 개수 세서 반환
* COUNT(DISTINCT 컬럼) : 해당 컬럼값 중복을 제거한 후 행 개수 세서 반환
// 전체 사원 수
SELECT COUNT(*)
FROM EMPLOYEE;
// 23
// 여자 사원 수
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) IN ('2', '4');
// 8
// 보너스를 받는 사원 수
SELECT COUNT(BONUS)
FROM EMPLOYEE;
// 9
// 부서 배치를 받은 사원 수
SELECT COUNT(DEPT_CODE)
FROM EMPLOYEE;
// 21
// 중복 제거
SELECT COUNT(DISTINCT DEPT_CODE)
FROM EMPLOYEE;
// 6