05-1. 오라클 함수
- 함수란?
함수(function)는 수학에서 정의한 개념으로 x와 y변수가 존재하고 x값이 변하면
그 변화에 따라 어떤 연산 또는 가공을 거쳐 y값도 함께 변할 때 이 y를 함수라고 한다.
y값이 종속적으로 변하기 때문에 '따름수'라고도 한다. - 오라클 함수의 종류
기본으로 제공하고 있는 내장 함수(built-in function)와 직접 정의한 사용자 정의 함수(user-defined function)으로
나뉜다. - 내장 함수의 종류
내장 함수는 입력 방식에 따라 데이터 처리에 사용하는 행이 나뉜다.
데이터가 한 행씩 입력되고 각각 결과가 하나씩 나오는 함수를 단일행 함수(single-row function)이라 한다.
여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수를 다중행 함수(multiple-row function)이라 한다.
05-2. 문자 데이터를 가공하는 문자 함수
문자함수는 데이터를 가공하거나 문자 데이터로부터 특정 결과를 얻고자 할 때 사용하는 함수이다.
- 대 · 소문자를 바꿔주는 UPPER, LOWER, INITCAP 함수
함수 설명 UPPER(문자열) 괄호 안 문자 데이터를 모두 대문자로 변환하여 반환 LOWER(문자열) 괄호 안 문자 데이터를 모두 소문자로 변환하여 반환 INITCAP(문자열) 괄호 안 문자 데이터 중 첫 글자는 대문자로, 나머지 문자를 소문자로 변환 후 반환
SELECT [조회하려는 열1 이름], UPPER([열1 이름]), LOWER([열1 이름]), INITCAP([열1 이름]) FROM [조회할 테이블 이름]; - 문자열 길이를 구하는 LENGTH 함수
특정 문자열의 길이를 구할 때 LENGTH함수를 사용한다.
LENGTH 함수 사용하기
행별 열의 문자열의 길이가 몇 글자인지 출력한다.SELECT [조회하려는 열1 이름], LENGTH ([열1 이름]) FROM [조회할 테이블 이름];
WHERE절에서 LENGTH 함수 사용하기
LENGTH 함수를 WHERE절에 사용하면 문자열 길이를 비교하여 행을 선별하는 것도 가능하다.
SELECT [조회하려는 열1 이름], LENGTH ([열1 이름]) FROM [조회할 테이블 이름] WHERE LENGTH([열1 이름])>=5;이를 통하여 [열1 이름]의 문자열 길이가 5자 이상인 행들만 선별한다.
LENGTH 함수와 LENGTHB 함수 비교하기
LENGTHB함수는 문자열의 바이트 수를 반환하기 때문에 LENGTH의 결과값과 다르다.
한글은 한 문자당 2byte로 처리된다.
SELECT LENGTH('한글'), LENGTHB('한글') FROM [조회할 테이블 이름];
다음과 같은 경우 '한글'은 두글자이므로 2, 4(두글자=4byte)가 출력된다. - 문자열 일부를 추출하는 SUBSTR 함수
문자열 중 일부를 추출할 때 SUBSTR 함수를 사용한다.
함수 설명 SUBSTR(문자열 데이터, 시작 위치, 추출 길이) 문자열 데이터의 시작 위치부터 추출 길이만큼 추출한다.
시작 위치가 음수일 경우에는 마지막 위치부터 거슬러 올라간 위치에서 시작한다.SUBSTR(문자열 데이터, 시작 위치) 문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출한다.
시작 위치가 음수일 경우에는 마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출한다.
예를 들어 SALESMAN 을 SUBSTR(열 이름, 1, 2)로 추출한다면 SA가 출력되고,
SUBSTR(열 이름, 3, 2)로 추출한다면 AL가 출력되고,
SUBSTR(열 이름, 5)로 추출한다면 SMAN이 출력된다. - 문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수
데이터 안에 특정 문자나 문자열이 어디에 포함되어 있는지를 알고자 할 때 INSTR함수를 사용한다.
SELECT INSTR ([대상 문자열 데이터(필수)], [위치를 찾으려는 부분 문자(필수)], [위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값은 1)], [시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본값은 1)]) FROM [조회할 테이블 이름];
SELECT INSTR('HELLO, ORACLE!', 'L')의 경우 처음 부터 몇번째인지 찾기 때문에 3이 출력된다.
SELECT INSTR('HELLO, ORACLE!', 'L', 5)의 경우 5번째 글자부터 찾기 때문에 12가 출력된다.
SELECT INSTR('HELLO, ORACLE!', 'L', 2, 2)의 경우 2번째 글자부터 찾고 두번째 L을 찾기 때문에 4가 출력된다. - 특정 문자를 다른 문자로 바꾸는 REPLACE 함수
REPLAC 함수는 특정 문자열 데이터에 포함된 문자를 다른 문자로 대체할 경우에 사용된다.
SELECT '010-1234-5678' AS REPLACE_BEFORE, REPLACE('010-1234-5678', '-', ' ') AS REPLACE_1 REPLACE('010-1234-5678', '-') AS REPLACE_2 FROM DUAL;
REPLACE('010-1234-5678', '-', ' ') AS REPLACE_1 의 경우 '-' 대신 ' '이 출력되어
010 1234 5678이 출력되고,
REPLACE('010-1234-5678', '-') AS REPLACE_2 의 경우 '-'가 삭제되서 출력되어
01012345678이 출력된다. - 데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수
LPAD와 RPAD는 각각 Left Padding(왼쪽 패딩), Right Padding(오른쪽 패딩)을 뜻한다.
데이터 자릿수를 지정한 후 데이터 길이가 지정한 자릿수보다 작을 경우 남은 공간을 특정 문자로 채우는 함수이다.
SELECT 'Oracle', LPAD('Oracle',10,'#') AS LPAD_1, RPAD('Oracle',10,'*') AS RPAD_1, LPAD('Oracle',10) AS LPAD_2, RPAD('Oracle',10) AS RPAD_2 FROM DUAL;
LPAD('Oracle',10,'#') AS LPAD_1 의 경우 Oracle(6글자)을 제외한 나머지 자릿수를 왼쪽부터 '#'으로 채워서 출력되어
'####Oralcle'이 출력된다.
RPAD('Oracle',10,'*') AS RPAD_1의 경우 Oracle을 제외한 나머지 자릿수를 오른쪽부터 '*'으로 채워서 출력되어
'Oracle****'이 출력된다.
LPAD('Oracle',10) AS LPAD_2의 경우 Oracle을 제외한 나머지 자릿수를 왼쪽부터 빈칸으로 채워서 출력되어
' Oracle'이 출력된다.
RPAD('Oracle',10) AS RPAD_2의 경우 Oracle을 제외한 나머지 자릿수를 오른쪽부터 빈칸으로 채워서 출력되어
'Oracle '이 출력된다. - 두 문자열 데이터를 합치는 CONCAT 함수
두 문자열 데이터를 하나의 데이터로 연결해주는 역할을 하는 함수이다.
SELECT CONCAT(EMPNO, ENAME), CONCAT(EMPNO, CONCAT(' : ', ENAME)) FROM EMP WHERE ENAME='SCOTT';
ex) SCOTT의 EMPNO = 7788
SELECT CONCAT(EMPNO, ENAME)의 경우 ENAME 가 'SCOTT'인 행의 EMPNO와 합쳐서 출력되어
7788SCOTT이 출력된다.CONCAT(EMPNO, CONCAT(' : ', ENAME))의 경우 ' : ' 가 연결되서 출력되어
7788 : SCOTT이 출력된다.
※ || 연산자는 CONCAT 함수와 유사하게 열이나 문자열을 연결한다. - 특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수
문자열 데이터 내에서 특정문자를 지우기 위해 사용되는 함수이다.
LTRIM은 문자열 데이터 내에서 왼쪽 문자를 지울때 사용되고 LEADING을 쓰기도한다.
RTRIM은 문자열 데이터 내에서 오른쪽 문자를 지울때 사용되고 TRAILING을 쓰기도한다.
TRIM은 문자열 데이터 내에서 양쪽 문자를 지울때 사용되고 BOTH를 쓰기도한다.
05-3. 숫자 데이터를 연산하고 수치를 조정하는 숫자 함수
- 특정 위치에서 반올림하는 ROUND 함수
ROUND([숫자(필수)], [반올림 위치(선택)]
ROUND(1234.5678, 2)인 경우에 소숫점 둘째자리까지 반올림하여 출력되어
1234.57이 출력된다. - 특정 위치에서 버리는 TRUNC 함수
TRUNC([숫자(필수)], [버림 위치(선택)])
TRUNC (1234.5678, 2) 인 경우에 소숫점 둘째자리 전은 버림 처리하여 출력되어
1234.56이 출력된다. - 지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수
CEIL([숫자(필수)]) FLOOR([숫자(필수)])
CEIL(3.14)의 경우에 입력된 숫자에 가장 가까운 큰 정수를 반환하므로
4가 출력된다.
FLOOR(3.14)의 경우에 입력된 숫자에 가장 가까운 작은 정수를 반환하므로
3이 출력된다. - 숫자를 나눈 나머지 값을 구하는 MOD 함수
MOD([나눗셈 될 숫자(필수)], [나눌 숫자(필수)])
MOD(15, 6)의 경우에 15를 6으로 나누고 그 나머지를 출력하므로
3이 출력된다.
05-4. 날짜 데이터를 다루는 날짜 함수
- 몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수
ADD_MONTHS([날짜 데이터(필수)], [더할 개월 수(정수)(필수)])
ADD_MONTHS(2018-07-13, 3)의 경우에 3개월이 지난 날짜를 출력하므로
2018-10-13이 출력된다. - 두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수
MONTHS_BETWEEN([날짜 데이터1(필수)], [날짜 데이터2(필수)])
MONTHS_BETWEEN(1980-12-17, 2018-07-13)의 경우 날짜데이터1-날짜데이터2를 하면 -450.90..개월이므로
-450.90...이 출력된다.
MONTHS_BETWEEN(2018-07-13, 1980-12-17)의 경우 날짜데이터1-날짜데이터2를 하면 450.90..개월이므로
450.90...이 출력된다. - 돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY 함수
NEXT_DAY([날짜 데이터(필수)], [요일 문자(필수)])
NEXT_DAY(2024-07-29, '금요일')의 경우 돌아오는 금요일이 24년 8월 2일이므로
2024-08-02가 출력된다.
LAST_DAY([날짜 데이터(필수)])
LAST_DAY(2024-07-29)의 경우 7월의 마지막 날이 31일 이므로
2024-07-31이 출력된다. - 날짜를 반올림, 버림을 하는 ROUND, TRUNC 함수
입력 데이터 종류 사용 방식 숫자 데이터 ROUND([숫자(필수)], [반올림 위치]) TRUNC([숫자(필수)], [버림 위치]) 날짜 데이터 ROUND([날짜데이터(필수)], [반올림 기준 포맷]) TRUNC([날짜데이터(필수)], [버림 기준 포맷])
< 오라클에서 날짜 데이터를 사용할 때 기준 포맷 값 >
포맷 모델 기준 단위 CC, SCC 네자리 연도의 끝 두 자리를 기준으로 사용
(2016년이면 2050 이하이므로, 반올림할 경우 2001년으로 처리)SYYYY, YYYY, 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에서 제정한 날짜 기준 해당 연도의 주(week)를 기준 W 해당 월의 주(1~5번째 주)를 기준 DDD, DD, J 해당 일의 정오(12:00:00)를 기준 DAY, DY, D 한 주가 시작되는 날짜를 기준 HH, HH12, HH24 해당일의 시간을 기준 MI 해당일 시간의 분을 기준
05-5. 자료형을 변환하는 형 변환 함수
- 날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수
TO_CHAR([날짜데이터(필수)], '[출력되길 원하는 문자 형태(필수)]')
TO_CHAR(2018-07-14 오전 12:00:13, 'MM')에서 월 두자를 출력하므로
07을 출력한다.
※ YYYY/MM/DD HH24:MI:SS로 날짜를 표시할 수 있다. - 문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수
TO_NUMBER('[문자열 데이터(필수)]', '[인식될 숫자형태(필수)]')
문자열을 지정한 형태의 숫자로 인식하여 숫자 데이터로 변환합니다. - 문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수
TO_DATE('[문자열 데이터(필수)]', '[인식될 날짜형태(필수)]')
문자열 데이터를 날짜형의 데이터로 변환합니다.
05-6. NULL 처리 함수
- NVL 함수의 기본 사용법
NVL([NULL인지 여부를 검사할 데이터 또는 열(필수)], [앞의 데이터가 NULL일 경우 반환할 데이터(필수)])
열 또는 데이터를 입력하여 해당 데이터가 NULL이 아닐 경우 데이터를 그대로 반환하고,
NULL인 경우 지정한 데이터를 반환한다. - NVL2 함수의 기본 사용법
NVL2([NULL인지 여부를 검사할 데이터 또는 열(필수)], [앞 데이터가 NULL이 아닐 경우 반환할 데이터 또는 계산식(필수)] [앞 에이터가 NULL일 경우 반환할 데이터 또는 계산식(필수)])
열 또는 데이터를 입력하여 해당 데이터가 NULL이 아닐 때와 NULL일 때 출력 데이터를 각각 지정한다.
05-7. NULL 처리 함수
- DECODE 함수
DECODE([검사 대상이 될 열 또는 데이터, 연산나 함수의 결과], [조건1], [데이터가 조건1과 일치할 때 반환할 결과], [조건2], [데이터가 조건2와 일치할 때 반환할 결과], ... [조건N], [데이터가 조건N과 일치할 때 반환할 결과], [위 조건1~조건N과 일치한 경우가 없을 때 반환할 결과])
if조건문 또는 switch-case 조건문과 비슷한 함수이다. - CASE문
CASE [검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)] WHEN [조건 1] THEN [조건1의 결과 값이 true일 때, 반환할 결과] WHEN [조건 2] THEN [조건2의 결과 값이 true일 때, 반환할 결과] ... WHEN [조건 N] THEN [조건N의 결과 값이 true일 때, 반환할 결과] ELSE [위 조건1~조건N과 일치하는 경우가 없을 때 반환할 결과] END
기준 데이터를 반드시 명시하고 그 값에 따라 반환 데이터를 정하는 DECODE 함수와 달리
CASE 문은 각 조건에 사용하는 데이터가 서로 상관 없어도 된다.
2024.07.29
'SQL(Oracle) > Oracle기초정리(오라클로 배우는 데이터베이스 입문)' 카테고리의 다른 글
| [Oracle기초정리] 06. 데이터를 추가, 수정, 삭제하는 데이터 조작어 (0) | 2024.07.30 |
|---|---|
| [Oracle기초정리] 04. 더 정확하고 다양하게 결과를 출력하는 WHERE절과 연산자 (0) | 2024.07.28 |
| [Oracle기초정리] 03. SELECT문의 기본 형식 (0) | 2024.07.28 |
| [Oracle기초정리] 02. 관계형 데이터베이스와 오라클 데이터베이 (5) | 2024.07.27 |
| [Oracle기초정리] 01. 데이터베이스 (0) | 2024.07.27 |