[오라클로 배우는 데이터베이스] 06. 데이터 처리와 가공을 위한 오라클 함수
1. 오라클 함수
- 오라클 함수의 종류
1) 내장 함수(built-in function): 오라클에서 기본으로 제공
- 단일행 함수: 데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수
- 다중행 함수: 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수
2) 사용자 정의 함수(user-defined funtion): 필요에 의해 직접 정의해서 사용
2. 문자 데이터를 가공하는 문자 함수
- 대·소문자를 바꿔주는 UPPER, LOWER, INITCAP함수
| 함수 | 설명 |
| UPPER(문자열) | 괄호 안 문자 데이터를 모두 대문자로 변환 |
| LOWER(문자열) | 괄호 안 문자 데이터를 모두 소문자로 변환 |
| INITCAP(문자열) | 괄호 안 문자 데이터중 첫 글자는 대문자, 나머지는 소문자로 변환 |
- 문자열 길이를 구하는 LENGTH함수
- 문자열 일부를 추출하는 SUBSTR함수
| 함수 | 설명 |
| SUBSTR(문자열 데이터, 시작 위치, 추출 길이) | 문자열 데이터의 시작 위치부터 추출 길이만큼 추출 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 시작 |
| SUBSTR(문자열 데이터, 시작 위치) | 문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출 |
- SUBSTR 함수와 다른 함수 함께 사용하기
- 문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수
| 총 네 개의 입력 값 지정가능 INSTR([대상 문자열 데이터(필수)], [위치를 찾으려는 부분 문자(필수)], [위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값은 1)], [시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본값은 1)]) |
- 특정 문자를 다른 문자로 바꾸는 REPLACE 함수
| REPLACE([문자열 데이터 또는 열 이름(필수)], [찾는 문자(필수)],[대체할 문자(선택)]) ※ 대체할 문자를 입력하지 않는다면 찾는 문자로 지정한 문자는 문자열 데이터에서 삭제됨 |
-데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수
| 남은 빈 공간을 왼쪽에 채움 LPAD([문자열 데이터 또는 열이름(필수)], [데이터의 자릿수(필수)], [빈 공간에 채울 문자(선택)]) ※ 빈 공간에 채울 문자를 선택하지 않으면 공백 문자로 띄움 RPAD([문자열 데이터 또는 열이름(필수)], [데이터의 자릿수(필수)], [빈 공간에 채울 문자(선택)]) 남은 빈 공간을 오른쪽에 채움 |
- 두 문자열 데이터를 합치는 CONCAT 함수
| 두 개의 문자열 데이터를 하나의 데이터로연결해주는 역할 두 개의 입력 데이터 지정을 하고 열이나 문자열 데이터 모두 지정가능 ※ || 연산자로 문자열 연결 가능 |
- 특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수
| TRIM 함수 기본 사용법 TRIM([삭제옵션(선택)][삭제할 문자(선택)] FROM [원본 문자열 데이터(필수)] ※ 삭제옵션 LEADING: 왼쪽에 있는 글자를 지움 TRAILING: 오른쪽에 있는 글자를 지움 BOTH: 양쪽에 있는 글자를 지움 |
| LTRIM: 왼쪽의 지정문자 삭제(문자열 지정안할시 공백이 삭제됨) LTRIM([원본 문자열 데이터(필수)], [삭제할 문자집합(선택)]) |
| RTRIM: 오른쪽의 지정문자 삭제(문자열 지정안할시 공백이 삭제됨) RTRIM([원본 문자열 데이터(필수)], [삭제할 문자집합(선택)]) |
3. 숫자 데이터를 연산하고 수치를 조정하는 함수
- ROUND함수
| ※ 특정 위치에서 반올림 하는 함수 ROUND([숫자(필수)], [반올림 위치(선택)])-반올림 위치 지정안할 경우 소수점 첫째 자리에서 반올림 수행 |
- TRUNC함수
| ※ 특정 위치에서 버리는 함수 ROUND([숫자(필수)], [버림 위치(선택)])-반올림 위치 지정안할 경우 소수점 첫째 자리에서 버림 수행 |
- CEIL, FLOOR함수
| CEIL([숫자(필수)]) : 지정한 숫자와 가까운 가장 큰 정수 FLOOR([숫자(필수)]) : 지정한 숫자와 가까운 가장 작은 정수 |
- MOD함수
| MOD([나눗셈 될 숫자(필수)],[나눌 숫자(필수)]) : 특정 숫자를 나누고 그 나머지를 출력하는 함수 ※ 데이터의 홀짝수 구별 용도로도 사용 가능 |
4. 날짜 데이터를 다루는 날짜 함수
| ※ 날짜 데이터 끼리의 연산 가능 여부 | |
| 연산 | 설명 |
| 날짜 데이터+숫자 | 날짜 데이터보다 숫자만큼 일수 이후의 날짜 |
| 날짜 데이터-숫자 | 날짜 데이터보다 숫자만큼 일수 이전의 날짜 |
| 날짜 데이터- 날짜 데이터 | 두 날짜 데이터 간의 일수 차이 |
| 날짜 데이터+날짜 데이터 | 연산 불가, 지원하지 않음 |
| SYSDATE 함수 : 현재 날짜와 시간 ADD_MONTHS([날짜 데이터(필수)], [더할 개월 수(정수)(필수)]) |
| MONTHS_BETWEEN: 두 날짜 간의 개월 수 차이를 구하는 함수 MONTHS_BETWEEN([날짜 데이터(필수)],[날짜 데이터2(필수)]) |
| NEXT_DAY: 입력한 날짜 데이터에서 돌아오는 요일의 날짜 반환 NEXT_DAY([날짜 데이터(필수)],[요일 문자(필수)]) LAST_DAY: 해당 날짜가 속한 달의 마지막 날짜 반환 LAST_DAY([날짜 데이터(필수)]) |
- 날짜의 반올림, 버림을 하는 ROUND, TRUNC함수
※ 날짜 데이터로 사용할 때는 반올림, 버림의기준이 될 포맷값 지정
| 포맷 모델 | 기준 단위 |
| CC, SCC | 네자리 연도의 끝 두자리를 기준으로 사용 (2016년이면 2050 이하이므로, 반올림할 경우 2001년으로 처리) |
| SYYYY, TTTT, YEAR, SYEAR, YYY, YY, Y | 날짜 데이터의 해당 연 월 일의 7월 1일을 기준 (2016년 7월 1일 일 경우, 2017년으로 처리) |
| IYYY, IYY, IY, I | ISO 8601에서 제정한 날짜 기준년도 포맷을 기준 |
| Q | 각 분기의 두 번째 달의 16일 기준 |
| MONTH, MON, MM, RM | 각 달의 16일 기준 |
| WW | 해당 연도의 몇 주(1~53번째 주)를 기준 |
| IW | ISO 8601에서 제정한 날짜 기준 해당 연도의 주(WEEL)를 기준 |
| W | 해당 월의 주(1~5번째 주)를 기준 |
| DDD, DD, J | 해당 일의 정오(12:00:00)를 기준 |
| DAY, DY, D | 한 주가 시작되는 날짜를 기준 |
| HH, HH12, HH24 | 해당일의 시간을 기준 |
| MI | 해당일 시간의 분을 기준 |
5. 자료형을 변환하는 형 변환 함수
: 지정된 자료형을 필요에 따라 바꿔주는 함수
| 종류 | 설명 |
| TO_CHAR | 숫자 또는 날짜 데이터를 문자데이터로 변환 |
| TO_NUMBER | 문자 데이터를 숫자 데이터로 변환 |
| TO_DATE | 문자 데이터를 날짜 데이터로 변환 |
- 원하는 출력 형태로 날짜 출력하기
| TO_CHAR 함수 TO_CHAR([날짜데이터(필수)],'[출력되길 원하는 문자 형태(필수)]') |
- 특정 언어에 맞춰 날짜 출력하기
| TO_CHAR([날짜 데이터(필수)],'[출력되길 원하는 문자 형태(필수)]', 'NLS_DATE_LANGUAGE = language'(선택)) - 날짜 데이터를 출력할 문자 형태를 지정하고 원하는 언어 양식 지정 |
- 시간 형식 지정하여 출력하기
| 형식 | 설명 |
| 9 | 숫자의 한 자리를 의미함(빈 자리를 채우지 않음) |
| 0 | 빈 자리를 0으로 채움을 의미함 |
| $ | 달러($) 표시 붙여서 출력 |
| L | L(local) 지역 화폐 단위 기호 붙여서 출력 |
| . | 소수점을 표시함 |
| , | 천 단위의 구분 기호를 표시함 |
- 문자 데이터를 숫자 데이터로 변환하기
| TO_NUMBER('[문자열 데이터(필수)]', '[인식될 숫자 형태(필수)]' |
- 문자 데이터를 날짜 데이터로 변환하기
| TO_DATE('[문자열 데이터(필수)]', '[인식될 날짜 형태(필수)]' |
6. NULL 처리 함수
-NVL 함수의 기본 사용법
| NVL([NULL인지 여부를 검사할 데이터 또는 열(필수)],[앞의 데이터가 NULL일 경우 반환할 데이터(필수)]) |
-NVL2 함수의 기본 사용법
| NVL2([NULL인지 여부를 검사할 데이터 또는 열(필수)], [앞의 데이터가 NULL일 경우 반환할 데이터 또는 계산식(필수)]), [앞 데이터가 NULL이 아닐 경우 반환할 데이터 또는 계산식(필수)]) |
7. 상황에 따라 다른 데이터를 반환하는 DECODE함수와 CASE문
※ 조건별로 동일한 자료형의 데이터를 반환해야 함
- DECODE 함수: 기준이 되는 데이터 지정 후 해당 데이처 값에 따라 다른 결과 값을 내보내는 함수
| DECODE([검사 대상이 될 열 또는 데이터, 연산이나 함수 결과], [조건1],[데이터가 조건1와 일치할 때 반환할 결과], [조건2],[데이터가 조건2와 일치할 때 반환할 결과], ... [조건n],[데이터가 조건n과 일치할 때 반환할 결과], [위 조건1~n과 일치한 경우가 없을 때 반환할 결과]) |
- CASE 함수: DECODE함수와 비슷하지만 더 다양한 조건에서 사용가능
| CASE [검사 대상이될 열 또는 데이터, 연산이나 함수의 결과(선택)] WHEN [조건1] THEN [조건1의 결과 값이 true일 때, 반환할 결과] WHEN [조건2] THEN [조건2의 결과 값이 true일 때, 반환할 결과] ... WHEN [조건n] THEN [조건n의 결과 값이 true일 때, 반환할 결과] ELSE[위 조건1~조건n과 일치하는 경우가 없을 때 반환할 결과] END |
