함수(Function)
- 하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리하여 놓은 작은 서브 프로그램으로,
- 호출하여 값을 전달하면 수행 결과를 리턴하는 방식의 코드
- 값 전달 및 호출 -> 작성 수행 -> 결과값 리턴
함수의 유형
- 단일 행 함수 : 각 행마다 반복적으로 적용되어 입력 받은 행의 개수 만큼 결과 리턴
- 그룹 함수 : 특정한 행들의 집합으로 그룹이 형성되어 적용. 그룹당 1개의 결과 리턴
문자처리 함수
| 작성법 | 리턴값 | 설명 |
|---|---|---|
| LENGTH(COL | STRING) | NUMBER | 컬럼의 값/문자열의 길이(문자 개수) 리턴 LENGTHB: Byte로 계산하여 출력 |
| INSTR(STRING, STR, [POSITION], [OCCURRENCE]) | NUMBER | 문자(열)의 위치를 리턴 |
| LPAD(STRING, N, [STR]) RPAD(STRING, N, [STR]) | CHARACTER | 컬럼/문자열에 임의의 문자열을 왼쪽/오른쪽에 붙여 총 길이 N의 문자열 리턴 |
| LTRIM(STRING,STR) RTRIM(STRING,STR) | CHARACTER | 컬럼/문자열의 왼쪽/오른쪽에서 지정한 STR에 포함된 모든 문자를 제거한 나머지를 리턴 |
| TRIM(STRING) TRIM(CHAR FROM STRING) TRIM(LEADING | TRAILING | BOTH [CHAR] FROM STRING) | CHARACTER | 컬럼/문자열의 지정한 위치에 지정한 한 글자를 제거한 나머지를 리턴 |
| SUBSTR(STRING,POSITION, [LENGTH]) | CHARACTER | 컬럼/문자열에서 지정한 위치부터 지정한 개수(생략시 문자열 끝까지)의 문자열을 잘라내어 리턴 |
| LOWER(STRING) UPPER(STRING) INITCAP(STRING) | CHARACTER | 컬럼/문자열을 소문자/대문자/첫글자만 대문자 형태로 변환하여 리턴 |
| CONCAT(STRING,STRING) | CHARACTER | 컬럼 혹은 문자열을 두 개 전달받아 하나로 합친 후 리턴 3개 이상의 컬럼/문자열을 결합할 경우 || 사용을 권장 |
| REPLACE(STRING, STR1, STR2) | CHARACTER | 컬럼/문자열에 변경할, 변경될의 문자열 값을 전달받아 변경 후 리턴 |
-
INSTR
- POSITION : 찾을 위치의 시작 값 (기본값 1)
- POSITION > 0 : STRING의 시작부터 끝 방향으로 찾음
- POSITION < 0 : STRING의 끝부터 시작 방향으로 찾음
- OCCURRENCE : 검색된 STR의 순번 (기본값 1), 음수 사용 불가
- POSITION : 찾을 위치의 시작 값 (기본값 1)
-
LPAD
- N : 반환할 문자(열)의 길이(바이트) / 원래 STRING길이보다 작다면 N만큼 잘라서 표시
- STR : 문자(열), 생략 시 공백
숫자처리 함수
| 작성법 | 리턴값 | 설명 |
|---|---|---|
| ABS(NUMBER) | NUMBER | 숫자의 절대값 리턴 |
| MOD(NUMBER,DIVISION) | NUMBER | 숫자를 나누어 나머지를 리턴 |
| ROUND(NUMBER) ROUND(NUMBER, POSITION) | NUMBER | 지정한 위치 이후 반올림하여 값을 리턴 |
| CEIL(NUMBER) | NUMBER | 소수점 자리의 수 올림하여 정수 리턴 |
| FLOOR(NUMBER) | NUMBER | 소수점 자리의 수 버리고 정수 리턴 |
| TRUNC(NUMBER, POSITION) | NUMBER | 지정한 위치 이후 소수점 자리의 수를 버리고 리턴 |
날짜처리 함수
| 작성법 | 리턴값 | 설명 |
|---|---|---|
| SYSDATE | DATE | 시스템에 저장되어 있는 현재 날짜 리턴 ※ 그외 현재 시각 명령어들 CURRENT_DATE, LOCALTIMESTAMP, CURRENT_TIMESTAMP |
| MONTHS_BETWEEN(DATE1, DATE2) | NUMBER | 날짜 두 개의 개월 수의 차이를 리턴 계산시 해당 월의 끝 날짜까지 계산 |
| ADD_MONTHS(DATE, NUMBER) | DATE | 날짜에 숫자만큼 개월 수를 더하여 특정 날짜 리턴 |
| NEXT_DAY(DATE, STRING [OR NUMBER]) | DATE | 날짜에 인자로 전달받은 요일 중 가장 가까운 요일의 날짜 출력 (문자열: '월' '월요일'.. / 숫자: 1=일요일,...7=토요일) |
| LAST_DAY(DATE) | DATE | 날짜가 속한 달의 마지막 날짜를 구하여 리턴 |
| EXTRACT(YEAR FROM DATE) EXTRACT(MONTH FROM DATE) EXTRACT(DAY FROM DATE) | DATE | 년, 월, 일 정보를 추출하여 리턴 |
형 변환 함수
- 문자, 숫자, 날짜 데이터들을 각 각 다른 형태로 변환할때 사용하는 함수
※ 일반적으로 숫자↔문자↔날짜 형태로 변경하여 씀
| 작성법 | 설명 |
|---|---|
| TO_CHAR(DATE, [FORMAT]) TO_CHAR(NUMBER, [FORMAT] | 숫자 / 날짜형 데이터 -> 원하는 형태의 문자형 데이터로 변환하여 리턴 |
| TO_DATE(CHARACTER, [FORMAT]) TO_DATE(NUMBER,[FORMAT]) | 숫자 / 문자형 데이터 -> 날짜형 데이터로 변환하여 리턴 |
| TO_NUMBER(CHARACTER,[FORMAT]) | 문자형 데이터 -> 숫자형 데이터로 변환하여 리턴 |
- ex) TO_NUMBER
SELECT TO_NUMBER('1,000,000','999,999,999')FROM DUAL;
SELECT to_NUMBER('100') + to_number('200')FROM DUAL;
SELECT TO_NUMBER('10000') FROM DUAL;※ 오라클의 자동 형변환
select '100' + '200' FROM DUAL; - 원래대로라면 문자끼리 연산을 하기 때문에 에러가 발생하겠지만
+(산술연산)를 통하여 문자이지만 숫자로 자동 형변환하여 정상 연산 처리
(결과는 300 출력)
- FORMAT 패턴
| 작성법 | 설명 |
|---|---|
| YYYY | 년도표시 (4자리) |
| YY | 년도표시 (2자리) |
| MONTH | 월표시 |
| MM | 월을 숫자로 표시 |
| MON | 월을 알파벳으로 표시 |
| DD | 날짜 표시 |
| D | 요일을 숫자로 표현 (1:일요일 ,...) |
| DAY | 요일 표현 |
| DY | 요일을 약어로 표현 |
| HH, HH12 | 시간(12시간으로 표현) |
| HH24 | 시간(24시간으로 표현) |
| MI | 분 |
| SS | 초 |
| AM,PM | 오전, 오후 시 |
| FM | 월,일,시,분,초 앞의 0을 제거하고 표시 |
| ,(comma) | 콤마 형식으로 변환 (ex. 9999 -> 9,999) |
| .(period) | 소수점 형식으로 변환 (ex. 9999 -> 99.99) |
| 0 | 왼쪽에 0을 삽입 (ex. 9999 -> 09999) |
| $ | $통화로 표시 (ex. 9999 -> $9999) |
| L | Local 통화로 표시 (ex. 9999 -> \9999) |
| XXXX | 16진수로 표시 (ex. 9999 -> 240F) |
NULL 처리함수(NVL)
| 작성법 | 설명 |
|---|---|
| NVL(P1, P2) | NULL로 되어 있는 컬럼(P1)의 값을 지정한 숫자 혹은 문자(P2)로 변경하요 리턴 대체하고자 하는 값과 컬럼의 타입이 일치 해야 함 |
DECODE - 선택함수
| 작성법 | 설명 |
|---|---|
| DECODE(표현식,조건1,결과1,조건2,결과2,...[조건없는 결과(DEFAULT)] ) | 표현식의 값과 일치하는 조건N의 결과N 리턴 (일치하는 값) (Java의 Switch문과 유사) |
- EX
SELECT DECODE(SUBSTR(EMP_NO,8,1), 1, '남', 2, '여') AS "성별" FROM EMPLOYEE;CASE
| 작성법 | 설명 |
|---|---|
| CASE WHEN 조건1 THEN 결과1 WHEN 조건2 THEN 결과2 WHEN 조건3 THEN 결과3 ELSE 결과N END | 여러가지 경우에서 선택을 할 수 있는 기능을 제공. (범위형태의 값도 가능) |
- EX
SELECT
CASE WHEN SUBSTR(EMP_NO, 1, 2) <= 64 THEN '60년대 초반생'
WHEN SUBSTR(EMP_NO, 1, 2) =65 THEN '60년대 중반생'
ELSE '60년대 후반생'
END
FROM EMPLOYEE WHERE SUBSTR(EMP_NO,1,2) BETWEEN 60 AND 69
ORDER BY 1 ASC;그룹 함수
- 하나의 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등을 하나의 컬럼으로 리턴하는 함수
| 작성법 | 설명 |
|---|---|
| SUM(COL) | 해당 컬럼 값들의 총 합 리턴 |
| AVG(COL) | 해당 컬럼 값들의 평균 리턴 |
| COUNT(COL) | 테이블에서 조건을 만족하는 행의 개수 리턴 |
| MAX(COL) MIN(COL) | 그룹의 최대값과 최소값 리턴 |
개발자로 매일 한 걸음