📘 함수
컬럼의 값을 읽어서 연산한 결과를 반환
- 단일행(SINGLE ROW) 함수 : N개의 값을 읽어 N개의 결과 반환
- 그룹(GROUP) 함수 : N개의 값을 읽어 1개의 결과 반환
- 함수는 SELECT절, WHERE절, ORDER BY, GROUP BY, HAVING 사용 가능
📜 함수 사용법
✏ 단일행 함수
LENGTH(문자열 | 컬럼) : 문자열 길이 반환
SELECT LENGTH('HELLO WORLD') FROM DUAL;12글자인 이메일만 조회
SELECT EMAIL, LENGTH(EMAIL) FROM EMPLOYEE
WHERE LENGTH(EMAIL) = 12; INSTR('문자열' | 컬럼명, '찾을문자', [찾을 위치 시작위치, [순번]])
지정한 위치부터 지정한 순번째로 검색되는 문자의 시작 위치를 반환
문자열에서 맨 앞에있는 B 위치 조회
SELECT INSTR('AABAACAABBAA', 'B') FROM DUAL;문자열에서 5번째 부터 검색해서 맨 앞에있는 B 위치 조회
SELECT INSTR('AABAACAABBAA', 'B', 5) FROM DUAL;
EMPLOYEE 테이블에서 사원명, 이메일 중 '@'위치 조회
SELECT EMP_NAME, EMAIL, INSTR(EMAIL, '@')
FROM EMPLOYEE;SUBSTR('문자열' | 컬럼명, 잘라내기 시작할 위치, 잘라낼 길이)
컬럼이나 문자열에서 지정한 위치부터 지정된 길이만큼 문자열을 잘라내서 반환
--> 잘라낼 길이 생략 시 끝까지 잘라냄
EMPLOYEE 테이블에서 사원명, 이메일 중 아이디만 조회
SELECT EMP_NAME, SUBSTR(EMAIL, 1, INSTR(EMAIL, '@') -1) AS 아이디
FROM EMPLOYEE ORDER BY 아이디; TRIM([옵션] '문자열' | 컬럼명 [FROM '문자열'|컬럼명] )
주어진 컬럼이나 문자열의 앞, 뒤, 양쪽에 있는 지정된 문자를 제거
--> (보통 양쪽 공백 제거에 많이 사용)
옵션 : LEADING(앞쪽), TRAILING(뒤쪽), BOTH(양쪽, 기본값)
SELECT ' J B ', TRIM(' J B ') FROM DUAL; -- 양쪽 공백 제거 (중간 미포함)
SELECT '---JB---', TRIM(BOTH '-' FROM '---JB---') FROM DUAL; -- BOTH 또는 생략 시 : 양쪽 '-' 기호 제거
-- LEADING : 앞쪽만 제거
-- TRAILING : 뒤쪽만 제거
✏ 숫자 관련 함수
ABS(숫자 | 컬럼명) : 절대값
SELECT ABS(10), ABS(-10) FROM DUAL;MOD(숫자 | 컬럼명, 숫자 | 컬럼명) : 나머지 값 반환
EMPLOYEE 테이블에서 사원의 월급을 100만으로 나눴을 때 나머지
SELECT EMP_NAME, SALARY, MOD(SALARY, 10000000) FROM EMPLOYEE;-- ROUND(숫자 | 컬럼명 [ , 소수점 위치]) : 반올림
SELECT 123.456, ROUND(123.456) FROM DUAL; -- 소수점 첫째자리에서 반올림
SELECT 123.456, ROUND(123.456, 1) FROM DUAL;-- 소수점 첫째자리까지 출력 == 소수점 둘째자리 반올림
SELECT 123.456, ROUND(123.456, 2) FROM DUAL;
SELECT 123.456, ROUND(123.456, 0) FROM DUAL; -- 소수점 첫째자리에서 반올림
SELECT 123.456, ROUND(123.456, -1) FROM DUAL; -- 소수점 0번째자리에서 반올림
SELECT 123.456, ROUND(123.456, -2) FROM DUAL; -- 소수점 -1번째자리에서 반올림
CEIL(숫자 | 컬럼명) : 올림
FLOOR(숫자 | 컬럼명) : 내림
--> 둘다 소수점 첫째자리에서 올림/내림처리
SELECT 123.5, CEIL(123.5), FLOOR(123.5) FROM DUAL;TRUNC(숫자 | 컬럼명 [, 위치]) : 특정 위치 아래를 버림(절삭)
SELECT TRUNC(123.456, 1), TRUNC(123.456, -1) FROM DUAL;버림과 내림의 차이점
SELECT TRUNC(-123.5), FLOOR(-123.5) FROM DUAL;✏ 날짜(DATE) 관련 함수
SYSDATE : 시스템에 현재 시간(년, 월, 일, 시, 분, 초)을 반환
SELECT SYSDATE FROM DUAL;SYSTIMESTAMP : SYSDATE + MS 단위 추가
SELECT SYSTIMESTAMP FROM DUAL;MONTHS_BETWEEN(날짜, 날짜) : 두 날짜의 개월 수 차이 반환
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, '2022/02/21')) || '개월' AS 수강기간 FROM DUAL;
EMPLOYEE 테이블에서 사원의 이름, 입사일, 현재 근무 개월 수, 근무 햇수 조회
SELECT EMP_NAME, HIRE_DATE,
'근무 ' || CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) || '개월 차' "근무 개월 수",
'근무 ' || CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12) || '년 차' "근무 햇 수"
FROM EMPLOYEE;ADD_MONTHS(날짜, 숫자) : 날짜에 숫자만큼의 개월 수를 더함.
SELECT ADD_MONTHS(SYSDATE, 4) + 7 FROM DUAL;LAST_DAT(날짜) : 해당 달의 마지막 날짜를 구함
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY('2022/04/01') FROM DUAL;EXTRACT : 년, 월, 일 정보를 추출하여 리턴
EXTRACT(YEAR FROM 날짜) : 년도만 추출
EXTRACT(MONTH FROM 날짜) : 월만 추출
EXTRACT(DAY FROM 날짜) : 일만 추출
EMPLOYEE 테이블에서 각 사원의 이름, 입사년도, 입사월, 입사일 조회
SELECT EMP_NAME 이름,
EXTRACT(YEAR FROM HIRE_DATE) "입사년도",
EXTRACT(MONTH FROM HIRE_DATE) "입사월",
EXTRACT(DAY FROM HIRE_DATE) "입사일"
FROM EMPLOYEE
WHERE EXTRACT(MONTH FROM HIRE_DATE) = 1;✏ 형변환 함수
형변환 함수
문자열(CHAR), 숫자(NUMBER), 날짜(DATE)끼리 서로 형변환 가능
문자열로 변환
TO_CHAR(날짜, [포맷]) : 날짜형 데이터를 문자형 데이터로 변경
TO_CHAR(숫자, [포맷]) : 숫자형 데이터를 문자형 데이터로 변경
<패턴>
9 : 숫자 한칸을 의미, 여러 개 작성 시 오른쪽 정렬
0 : 숫자 한칸을 의미, 여러 개 작성 시 오른쪽 정렬 + 빈칸 0 추가
L : 현재 DB의 설정된 나라의 화폐 기호
SELECT TO_CHAR(1234,'99999') FROM DUAL; -- 숫자 5칸, 오른쪽 정렬
SELECT TO_CHAR(1234,'00000') FROM DUAL; -- 숫자 5칸, 오른쪽 정렬, 빈칸 0추가
SELECT TO_CHAR(1000000, '9,999,999') FROM DUAL; -- 자릿수 구분
SELECT TO_CHAR(1000000, 'L9,999,999') FROM DUAL; -- 화폐기호
SELECT TO_CHAR(1000000, '$9,999,999') FROM DUAL; -- 화폐기호직원들의 급여를 '\999,999,999' 형식으로 조회
SELECT EMP_NAME, TO_CHAR(SALARY, 'L999,999,999') FROM EMPLOYEE;
날짜에 TO_CHAR 적용
YYYY : 년도 / YY : 년도 (짧게)
RRRR : 년도 / RR : 년도 (짧게)
MM : 월 / DD : 일
AM 또는 PM : 오전 / 오후 표시
HH : 시간 / HH24 : 24시간 표기법
MI : 분 / SS : 초
DAY : 요일(전체) / DY : 요일(요일명만 표시)
SELECT SYSDATE, TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL;직원들의 입사일을 '0000년 00월 00일 (수)' 형식으로 출력
SELECT EMP_NAME, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"(DY)') FROM EMPLOYEE;--> 년, 월, 일은 오라클에 등록된 날짜 표기 패턴이 아니라서 오류
--> 기존에 없던 패턴 추가 시 ""(쌍따옴표)로 감싸줘서 문자열 그대로를 출력하게 함
✏ 날짜로 변환 TO_DATE
TO_DATE(문자형 데이터, [포맷]) : 문자형 데이터를 날짜로 변경
TO_DATE(숫자형 데이터, [포맷]) : 숫자형 데이터를 날짜로 변경
--> 지정된 포맷으로 날짜를 인식함
SELECT '2022-03-22', TO_DATE('2022-03-22') FROM DUAL;
SELECT TO_DATE('20100103') FROM DUAL; -- 숫자도 가능
SELECT TO_DATE('041030 143000', 'YYMMDD HH24MISS') FROM DUAL;
SELECT TO_CHAR (TO_DATE('041030 143000', 'YYMMDD HH24MISS'), 'YYYY/MM/DD HH24"시" MI"분"') FROM DUAL;EMPLOYEE 테이블에서 각 직원이 태어난 생년월일 조회
SELECT EMP_NAME, TO_DATE( SUBSTR (EMP_NO, 1, 6), 'RRMMDD') FROM EMPLOYEE;Y : 현재 세기 (21세기 == 20XX년 == 2000년대)
R : 1세기 기준으로 절반(50년) 이상이면 이전 세기(1900년대)
절반(50년) 미만이면 현재 세기(2000년대)
SELECT TO_DATE('19490115', 'RRRRMMDD') FROM DUAL;✏ 숫자로 형변환 TO_NUMBER
TO_NUMBER(문자데이터, [포맷]) : 문자형데이터를 숫자 데이터로 변경
SELECT TO_NUMBER('1,000,000', '9,999,999') + 10 FROM DUAL;✏ NULL 처리 함수
NVL(컬럼명, 컬럼값이 NULL일때 바꿀 값) : NULL인 컬럼값을 다른 값으로 변경
EMPLOYEE 테이블에서 이름, 급여, 보너스, 급여 * 보너스 조회
--> 숫자와 NULL 연산 시 결과도 NULL
SELECT EMP_NAME, SALARY, NVL(BONUS, 0), SALARY * BONUS FROM EMPLOYEE;NVL2(컬럼명, 바꿀값1, 바꿀값2)
해당 컬럼의 값이 있으면 바꿀값1로 변경,
해당 컬럼이 NULL이면 바꿀값2로 변경
EMPLOYEE 테이블에서 기존 보너스를 받던 사원의 보너스를 0.8로 보너스를 받지 못했던 사원의 보너스를 0.3 으로 변경하여 이름, 기존 보너스, 변경된 보너스 조회
SELECT EMP_NAME, BONUS, NVL2(BONUS, 0.8, 0.3) FROM EMPLOYEE;