▶ 함수(Function)
하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성해 놓은 작은 서브 프로그램
호출하며 값을 전달하면 결과를 리턴하는 방식으로 사용
√ 유형
▶ 문자 처리 함수
√ LENGTH
주어진 컬럼 값/문자열의 길이(문자 개수) 반환
＊ CHAR|STRING : 문자 타입 컬럼 또는 문자열
√ 예시
SELECT EMP_NAME, LENGTH(EMP_NAME), EMAIL, LENGTH(EMAIL)
FROM EMPLOYEE;
√ INSTR
지정한 위치부터 지정한 숫자 번째로 나타나는 문자의 시작 위치 반환
＊ STRING : 문자 타입 컬럼 또는 문자열
＊ STR : 찾으려는 문자열
＊ POSITION : 찾을 위치 시작 값(기본 값 1)
- POSITION > 0 이면 STRING의 시작부터 끝 방향으로 찾고
- POSITION < 0 이면 STRING의 끝부터 시작 방향으로 찾음
＊ OCCURRENCE : SUBSTRING이 반복될 때 지정하는 빈도(기본 값 1), 음수 사용 불가
√ 예시
- EMAIL 컬럼의 문자열 중 ‘@’의 위치를 구하시오.
SELECT EMAIL, INSTR(EMAIL, ‘@’, -1, 1) 위치
FROM EMPLOYEE;√ LTRIM/RTRIM
주어진 컬럼, 문자열의 왼쪽/오른쪽에서 지정한 STR에 포함된 모든 문자를 제거한 나머지 반환
＊ STRING : 문자 타입 컬럼 또는 문자열
＊ STR : 제거하려는 문자(열), 생략 시 공백문자
√ 예시
SELECT EMP_NAME, LTRIM(PHONE, '010'), RTRIM(EMAIL, '@kh.or.kr')
FROM EMPLOYEE;
√ TRIM
주어진 컬럼, 문자열의 앞/뒤/양쪽에 있는 지정한 문자를 제거한 나머지 반환
＊ STRING : 문자 타입 컬럼 또는 문자열
＊ CHAR : 제거하려는 문자(열), 생략 시 공백문자
＊ LEADING : TRIM할 CHAR의 위치 지정, 앞(LEADING)/뒤(TRAILING)/양쪽(BOTH) 지정 가능(기본 값 양쪽)
√ 예시
√ SUBSTR
컬럼이나 문자열에서 지정한 위치부터 지정한 개수의 문자열을 잘라내어 반환
＊ STRING : 문자 타입 컬럼 또는 문자열
＊ POSITION : 문자열을 잘라낼 위치로 양수면 시작방향에서 지정한 수만큼, 음수면 끝 방향에서 지정한 수만큼의 위치 의미
＊ LENGTH : 반환할 문자 개수(생략 시 문자열의 끝까지 의미, 음수면 NULL 리턴)
√ 예시
√ LPAD/RPAD
주어진 컬럼, 문자열에 임의의 문자열을 왼쪽/오른쪽에 덧붙여 길이 N의 문자열 반환
✓ 예시
SELECT LPAD(EMAIL, 20, ‘#’)
FROM EMPLOYEE;
SELECT RPAD(EMAIL, 20, ‘#’)
FROM EMPLOYEE;
✓ LOWER/UPPER/INITCAP
컬럼의 문자 혹은 문자열을 소문자/대문자/첫 글자만 대문자로 변환하여 반환
＊ STRING : 문자 타입 컬럼 또는 문자열
✓ 예시
▶ 숫자 처리 함수
✓ ABS
인자로 전달 받은 숫자의 절대값 반환
＊ NUMBER : 숫자 혹은 숫자 데이터 컬럼
✓ 예시
✓ MOD
인자로 전달 받은 숫자를 나누어 나머지 반환
＊ NUMBER : 숫자 혹은 숫자 데이터 컬럼
＊ DIVISION : 나눌 수 혹은 나눌 숫자 데이터 컬럼
✓ 예시
✓ ROUND
인자로 전달 받은 숫자 혹은 컬럼에서 지정한 위치부터 반올림하여 값 반환
＊ NUMBER : 숫자 혹은 숫자 데이터 컬럼
＊ POSITION : 반올림할 위치(생략 시 기본 값 O)
✓ 예시
✓ FLOOR
인자로 전달 받은 숫자 혹은 컬럼에서 소수점 자리의 수를 버림 후 반환
＊ NUMBER : 숫자 혹은 숫자 데이터 컬럼
✓ 예시
✓ TRUNC
인자로 전달 받은 숫자 혹은 컬럼에서 지정한 위치부터의 자리의 수를 버리고(절삭) 반환
＊ NUMBER : 숫자 혹은 숫자 데이터 컬럼
＊ POSITION : 버릴 위치(생략 시 기본 값 O)
✓ 예시
✓ CEIL
인자로 전달 받은 숫자 혹은 컬럼을 올림 후 반환
＊ NUMBER : 숫자 혹은 숫자 데이터 컬럼
▶ 날짜 처리 함수
✓ SYSDATE
시스템에 저장되어 있는 현재 날짜 반환
✓ 예시
SELECT SYSDATE
FROM DUAL;✓ MONTHS_BETWEEN
인자로 날짜 두 개를 전달받아 개월 수 차이를 숫자 데이터형으로 반환
＊ DATE1 : 기준이 되는 날짜
＊ DATE2 : 개월 수를 구하려는 날짜
✓ 예시
- EMPLOYEE테이블에서 사원의 이름, 입사일, 근무 개월 수 조회
SELECT EMP_NAME,
HIRE_DATE,
MONTHS_BETWEEN(SYSDATE, HIRE_DATE)
FROM EMPLOYEE;
✓ ADD_MONTHS
인자로 전달받은 날짜에 인자로 받은 숫자만큼 개월 수를 더하여 특정 날짜 반환
＊ DATE1 : 기준이 되는 날짜
＊ DATE2 : 더하려는 개월 수
✓ 예시
- EMPLOYEE테이블에서 사원의 이름, 입사일, 입사 후 6개월이 된 날짜 조회
SELECT EMP_NAME,
HIRE_DATE,
ADD_MONTHS(HIRE_DATE, 6)
FROM EMPLOYEE;
✓ LAST_DAY
인자로 전달받은 날짜가 속한 달의 마지막 날짜 반환
＊ DATE : 기준이 되는 날짜
✓ 예시
- EMPLOYEE테이블에서 사원의 이름, 입사일, 입사한 달의 마지막 날 조회
SELECT EMP_NAME,
HIRE_DATE,
LAST_DAY(HIRE_DATE)
FROM EMPLOYEE;
✓ EXTRACT
년, 월, 일 정보 추출하여 반환
＊ DATE : 기준이 되는 날짜
✓ 예시
- EMPLOYEE테이블에서 사원의 이름, 입사 년, 입사 월, 입사 일 조회
SELECT EMP_NAME,
EXTRACT(YEAR FROM HIRE_DATE) YEAR,
EXTRACT(MONTH FROM HIRE_DATE) MONTH,
EXTRACT(DAY FROM HIRE_DATE) DAY
FROM EMPLOYEE;
▶ 형 변환 함수
✓ TO_CHAR
날짜 혹은 숫자형 데이터를 문자형 데이터로 변환하여 반환
＊ DATE : 문자형으로 변환하려는 날짜형 데이터
＊ NUMBER : 문자형으로 변환하려는 숫자형 데이터
＊ FORMAT : 문자형으로 변환 시 지정할 출력 형식
✓ FORMAT 형식
✓ TO_CHAR 예시1
SELECT EMP_NAME,
TO_CHAR(HIRE_DATE, 'YYYY-MM-DD'),
TO_CHAR(HIRE_DATE, 'YY/MON, DAY, DY')
FROM EMPLOYEE;
✓ TO_CHAR 예시2
SELECT EMP_NAME,
TO_CHAR(SALARY, ‘L999,999,999’),
TO_CHAR(SALARY, ‘000,000,000’)
FROM EMPLOYEE;
✓ TO_DATE
숫자 혹은 문자형 데이터를 날짜형 데이터로 변환하여 반환
＊ CHARACTER : 날짜형으로 변환하려는 문자형 데이터
＊ NUMBER : 날짜형으로 변환하려는 숫자형 데이터
＊ FORMAT : 날짜형으로 변환 시 지정할 출력 형식
✓ 예시
- EMPLOYEE테이블에서 2000년도 이후에 입사한 사원의 사번, 이름, 입사일 조회
SELECT EMP_NO,
EMP_NAME,
HIRE_DATE
FROM EMPLOYEE
WHERE HIRE_DATE > TO_DATE(20000101, ‘YYYYMMDD’);
✓ TO_NUMBER
날짜 혹은 문자형 데이터를 숫자형 데이터로 변환하여 반환
＊ CHARACTER : 숫자형으로 변환하려는 문자형 데이터
＊ FORMAT : 날짜형으로 변환 시 지정할 출력 형식
✓ 예시
SELECT TO_NUMBER('1,000,000', '99,999,999')
- TO_NUMBER('550,000', '999,999')
FROM DUAL;
▶ NULL 처리 함수
✓ NVL
NULL로 되어 있는 컬럼의 값을 인자로 지정한 숫자 혹은 문자로 변경하여 반환
＊ P1 : NULL데이터를 처리할 컬럼명 혹은 값
＊ P2 : NULL값을 대체하고자 하는 값
✓ 예시
SELECT EMP_NO,
EMP_NAME,
SALARY,
NVL(BONUS, 0),
(SALARY + (SALARY * NVL(BONUS, 0)))*12
FROM EMPLOYEE;
▶ 선택 함수
✓ DECODE
비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환
＊ 표현식 : 값에 따라 선택을 다르게 할 컬럼 혹은 값
＊ 조건 : 해당 값이 참인지 거짓인지 여부 판단
＊ 결과 : 해당 조건과 일치하는 경우 반환할 값
＊ DEFAULT : 모든 조건이 불일치 시 반환할 값
✓ 예시
SELECT EMP_ID,
EMP_NAME,
EMP_NO,
DECODE(SUBSTR(EMP_NO, 8, 1), ‘1’, ‘남’, ‘2’, ‘여‘) AS 성별
FROM EMPLOYEE;✓ CASE
비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환(조건은 범위 값 가능)
＊ 조건 : 해당 값이 참인지 거짓인지 여부 판단
＊ 결과 : 해당 조건과 일치하는 경우 반환할 값
＊ DEFAULT : 모든 조건이 불일치 시 반환할 값
✓ CASE 예시1
SELECT EMP_ID, EMP_NAME, EMP_NO,
CASE WHEN SUBSTR(EMP_NO, 8, 1) = 1 THEN ‘남’
ELSE ‘여’
END AS 성별
FROM EMPLOYEE;
✓ CASE 예시2
SELECT EMP_NAME, SALARY,
CASE WHEN SALARY > 5000000 THEN '1등급‘
WHEN SALARY > 3500000 THEN '2등급‘
WHEN SALARY > 2000000 THEN '3등급'
ELSE '4등급‘
END 등급
FROM EMPLOYEE;
▶ 그룹 함수
하나 이상의 행을 그룹으로 묶어 연산하며 총합, 평균 등을 하나의 컬럼으로 반환하는 함수
✓ SUM
해당 컬럼 값들의 총합 반환
✓ 예시
- EMPLOYEE테이블에서 남자 사원의 급여 총합 조회
SELECT SUM(SALARY),
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = 1;
- EMPLOYEE테이블에서 부서코드가 D5인 직원의 보너스 포함 연봉 조회
SELECT SUM(SALARY + (SALARY*NVL(BONUS, 0))*12)
FROM EMPLOYEE
WHERE DEPT_CODE = ‘D5’;
✓ AVG
해당 컬럼 값들의 평균 반환
✓ 예시
- EMPLOYEE테이블에서 전 사원의 보너스 평균을 소수 셋 째 자리에서 반올림 한 것 조회
SELECT ROUND(AVG(NVL(BONUS, 0)), 2)
FROM EMPLOYEE;＊ NVL을 하지 않을 시 NULL 값을 가진 행은 평균 계산에서 제외되어 계산
✓ MAX/MIN
그룹의 최대값과 최소값 반환
✓ 예시
- EMPLOYEE테이블에서 가장 높은 급여와 가장 낮은 급여 조회
SELECT MAX(SALARY), MIN(SALARY)
FROM EMPLOYEE;
- EMPLOYEE테이블에서 가장 오래된 입사일과 가장 최근인 입사일 조회
SELECT MAX(HIRE_DATE), MIN(HIRE_DATE)
FROM EMPLOYEE;
✓ COUNT
테이블 조건을 만족하는 행의 개수 반환
✓ 예시
- EMPLOYEE테이블에서 전체 사원 수 조회
SELECT COUNT(*)
FROM EMPLOYEE;
- EMPLOYEE테이블에서 부서코드가 D5인 직원의 수 조회
SELECT COUNT(DEPT_CODE)
FROM EMPLOYEE
WHERE DEPT_CODE = ‘D5’;
- EMPLOYEE테이블에서 사원들이 속해있는 부서의 수 조회
SELECT COUNT(DISTINCT DEPT_CODE)
FROM EMPLOYEE;
