오라클 함수
오라클 함수는 함수를 제작한 주체를 기준으로
오라클에서 기본으로 제공하고 있는 내장 함수와 사용자가 필요에 의해 직접 정의한 사용자 정의 함수로 나뉜다.
내장 함수의 종류
입력 방식에 따라 데이터 처리에 사용하는 행이 나뉜다.
- 단일행 함수 : 데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수
- 다중행 함수 : 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수
문자 데이터를 가공하는 문자 함수
문자 데이터를 가공하거나 문자 데이터로부터 특정 결과를 얻고자 할 때 사용한다.
대/소문자를 바꿔주는 UPPER, LOWER, INITCAP 함수
| 함수 | 설명 |
| UPPER(문자열) | 괄호 안 문자 데이터를 모두 대문자로 변환하여 반환 |
| LOWER(문자열) | 괄호 안 문자 데이터를 모두 소문자로 변환하여 반환 |
| INITCAP(문자열) | 괄호 안 문자 데이터 중 첫 글자는 대문자, 나머지 문자를 소문자로 변환하여 반환 |
위의 함수들을 사용하려면 입력 데이터에 열 이름이나 데이터를 직접 지정해야 한다.
문자열 길이를 구하는 LENGTH 함수
SELECT ENAME, LENGTH(ENAME)
FROM EMP;해당 코드를 실행하게 되면, 두 번째 열이 LENGTH 함수를 사용한 열이 된다.
각 행별 사원 이름이 몇 글자인지 표시되어 출력된다.
문자열 일부를 추출하는 SUBSTR 함수
문자열 중 일부를 추출할 때 SUBSTR 함수를 사용한다.
▼ 사용 방법
| 함수 | 설명 |
| SUBSTR(문자열 데이터, 시작 위치, 추출 길이) | 문자열 데이터의 시작 위치 ~ 추출 길이만큼 추출한다. 시작 위치가 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 시작한다. |
| SUBSTR(문자열 데이터, 시작 위치) | 문자열 데이터의 시작 위치 ~ 데이터 끝까지 추출한다. 시작 위치가 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 시작한다. |
문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수
문자열 데이터 안에서 특정 문자나 문자열이 어디에 포함되어 있는지를 알고자 할 때 사용하는 함수.
총 네 개의 입력 값을 지정할 수 있으며 최소 두 개의 입력값, 즉 원본 문자열 데이터와 원본 문자열 데이터에서 찾으려는 문자 두 가지는 반드시 지정해 주어야 한다.
INSTR([대상 문자열 데이터(필수)],
[위지를 찾으려는 부분 문자(필수)],
[위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값은 1)],
[시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본값은 1)]
WELECT INSTR('HELLO, ORACLE!', 'L', 2, 2)
FROM DUAL;
위 코드를 실행시키게 되면, HOLLO, ORACLE! 문자열에서 2번째 글자인 O부터 검색을 시작한다.
O부터 L을 검색하여 두 번째 L이 4번째에 위치해 있으므로, 결과는 4가 된다.
특정 문자를 다른 문자로 바꾸는 REPLACE 함수
특정 문자열 데이터에 포함된 문자를 다른 문자로 대체할 경우 사용하는 함수.
REPLACE([문자열 데이터 또는 열 이름(필수)]. [찾는 문자(필수)], [대체할 문자(선택)]
만약 대체할 문자를 입력하지 않는다면 찾는 문자로 지정한 문자는 문자열 데이터에서 삭제된다.
SELECT REPLACE('010-1234-5678', '-', ' ')
FROM DUAL;위 코드의 실행 결과는 - 문자가 ' ' 공백으로 대체되었기 때문에
010 1234 5678 이 된다.
데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수
둘은 각가 Left Padding(왼쪽 패딩), Right Padding(오른쪽 패딩)을 뜻한다.
데이터와 자릿수를 지정 후 데이터 길이가 지정한 자릿수보다 작을 경우 나머지 공간을 특정 문자로 채우는 함수이다.
LPAD는 남은 빈 공간을 왼쪽에, RPAD는 오른쪽에 채운다.
만약 빈 공간에 채울 문자를 지정하지 않으면 LPAD와 RPAD 함수는 빈 공간의 자릿수만큼 공백 문자로 띄운다.
LPAD([문자열 데이터 또는 열 이름(필수)], [데이터의 자릿수(필수)], [빈 공간에 채울 문자(선택)])
RPAD([문자열 데이터 또는 열 이름(필수)], [데이터의 자릿수(필수)], [빈 공간에 채울 문자(선택)])
SELECT 'Oracle',
LPAD('Oracle',10,'#') AS LPAD_1,
RPAD('Oracle',10,'*') AS RPAD_1,
위 코드를 실행시키면
LPAD_1 에서는 Oracle 문자의 자릿수 -> 6개, 지정한 자릿수->10개 이므로 남은 4자리를 대체할 문자인 #을 왼쪽에 채운다.
- 결과 : ####Oracle
RPAD_1 에서도 LPAD_1과 동일하지만, 오른쪽에 *을 채우는 코드이므로
- 결과 : Oracle****
두 문자열 데이터를 합치는 CONCAT 함수
두 개의 문자열 데이터를 하나로 연결해 주는 역할.
두 열 사이에 콜론(:) 넣고 사용한다.
SELECT CONCAT(EMPNO, CONCAT(' : ', ENAME))
FROM EMP
WHERE ENAME = 'SOCTT';- 실행 결과 : 7788 : SCOTT
특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수
문자열 데이터 내에서 특정 문자를 지우기 위해 사용하는 함수이다.
TRIM([삭제 옵션(선택)] [삭제할 문자(선택] FROM [원본 문자열 데이터(필수)]
원본 문자열 데이터를 제외한 나머지 데이터는 생략 가능 O
삭제할 문자가 생략된 경우에는 기본적으로 공백을 제거한다.
삭제할 옵션은 왼쪽에 있는 글자를 지우는 LEADING, 오른쪽 글자를 지우는 TRAILING, 양쪽의 글자를 모두 지우는 BOTH를 사용한다.
SELECT '[' || TRIM(LEADING '_' FROM '_ _Oracle_ _') || ']'
FROM DUAL;실행 결과 : [ _Oracle_ _]
숫자 데이터 연산, 수치 조정하는 숫자 함수
특정 위치에서 반올림하는 ROUND 함수
특정 숫자를 반올림하되 반올림할 위치를 지정할 수 있다.
위치를 지정하지 않으면 소수점 첫째 자리에서 반올림한 결과가 반환된다.
ROUND([숫자(필수)], [반올림 위치(선택)]
특정 위치에서 버리는 TRUNC 함수
지정된 자리에서 숫자를 버림 처리하는 함수.
ROUND와 마찬가지로 버림 처리할 자릿수 지정이 가능하다.
반올림 위치를 지정하지 않으면 소수점 첫째 자리에서 버림 처리됨
TRUNC([숫자(필수)], [버림 위치(선택)]
지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수
CEIL 함수와 FLOOR 함수는 각각 입력된 숫자와 가까운 큰 정수, 작은 정수를 반환하는 함수이다.
CEIL([숫자(필수)]
FLOOR([숫자(필수)]
숫자를 나눈 나머지 값을 구하는 MOD함수
MOD([나눗셈될 숫자(필수)], [나눌 숫자(필수)]
숫자를 나누고 나머지 값을 출력해 준다.
날짜 데이터를 다루는 날짜 함수
오라클에서 날짜 데이터, 즉 DATE형 데이터는 간단한 연산이 가능하다.
날짜 데이터끼리의 더하기는 연산 X
SYSDATE 함수
별다른 입력 데이터 없이, 오라클 데이터베이스 서버가 놓인 OS의 현재 날짜와 시간을 보여준다.
SELECT SYSDATE AS NOW, -- 현재의 날짜 및 시간
SYSDATE-1 AS YESTERDAY, -- 하루 이전의 날짜
SYSDATE+1 AS TOMORROW -- 하루 이후의 날짜
FROM DUAL;
몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수
특정 날짜에 지정한 개월 수 이후 날짜 데이터를 반환하는 함수
ADD_MONTHS([날짜 데이터(필수)], [더할 개월 수(정수)(필수)])
두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수
두 개의 날짜 데이터를 입력하고 두 날짜 간의 개월 수 차이를 구하는 데 사용한다.
MONTHS_BETWEEN([날짜 데이터1(필수)], [날짜 데이터2(필수)])
돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY 함수
NEXT_DAY 함수는 날짜 데이터와 요일 문자열을 입력해, 입력한 날짜 데이터에서 돌아오는 요일의 날짜를 반환한다.
NEXT_DAY 함수의 기본 사용법
NEXT_DAY([날짜 데이터(필수)], [요일 문자(필수)])
LAST_DAY 함수는 하나의 날짜 데이터만을 입력 데이터로 사용, 해당 날짜가 속한 달의 마지막 날짜를 반환해 준다.
LAST_DAY 함수의 기본 사용법
LAST_DAY([날짜 데이터(필수)])
날짜를 반올림, 버림을 하는 ROUND, TRUNC 함수
숫자 데이터의 반올림, 버림에 사용한 ROUND, TRUNC 함수는 날짜 데이터 입력 데이터로 사용할 수 있다.
이때는 소수점 위치 정보를 입력하지 않고 반올림, 버림의 기준이 될 포맷 값을 지정해 준다.
| 입력 데이터 종류 | 사용 방식 |
| 숫자 데이터 | ROUND([숫자(필수)], [반올림 위치]) |
| TRUNC([숫자(필수)], [버림 위치]) | |
| 날짜 데이터 | ROUND([날짜데이터(필수)], [반올림 기준 포맷]) |
| TRUNC([날짜데이터(필수)], [버림기준 포맷]) |
▼오라클에서 날짜 데이터 사용 시 기준 포맷 값
자료형을 변환하는 형 변환 함수
암시적 형 변환
'자동 형 변환'이라고 불린다.
SELECT EMPNO, ENAME, EMPNO + '500'
FROM EMP
WHERE ENAME = 'SCOTT';
위 코드를 실행하게 되면,
EMPNO + '500' 의 결과는 EMPNO(숫자형_사원번호)와 500(문자열)을 더한 8288이라는 결과가 출력된다.
이는 숫자로 인식 가능한 문자 데이터가 자동으로 숫자로 바뀐 후 연산이 수행된 것이다.
명시적 형 변환
오라클에서 자동으로 변환되는 방식이 아닌 우리가 자료형을 직접 지정한 방식을 뜻한다.
▼명시적 형 변환의 종류
| 종류 | 설명 |
| TO_CHAR | 숫자 또는 날짜 데이터를 문자 데이터로 변환 |
| TO_NUMBER | 문자 데이터를 숫자 데이터로 변환 |
| TO_DATE | 문자 데이터를 날짜 데이터로 변환 |
날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수
날짜, 숫자 데이터를 문자 데이터로 변환해 주는 함수.
주로 날짜 데이터 -> 문자데이터 변환할 때 많이 사용.
TO_CHAR([날짜데이터(필수)], '[출력되길 원하는 문자 형태(필수)]')
원하는 형태로 날짜 출력하기
예시
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS 현재날짜시간
FROM DUAL;위 코드를 실행하게 되면, 결과는 ==> 2024/07/13 23:59:01 형식으로 출력이 된다.
특정 언어에 맞춰서 날짜 출력하기
이 결과를 특정 언어에 맞는 XX월, XX요일 등으로 출력하려면 TO_CHAR 함수에 날짜 출력 언어를 추가로 지정해 줄 수 있다.
TO_CHAR([날짜 데이터(필수)], '[출력되길 원하는 문자 형태(필수)]'
'NLS_DATE_LANGUAGE = language' (선택))
예시
SELECT SYSDATE,
TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = KOREAN') AS MON_KOR,
TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = JAPANESE') AS MON_JPN,
TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = ENGLISH') AS MON_ENG
FROM DUAL;실행결과 :
1) MON_KOR : 7월
2) MON_JPN : 7月
3) MON_ENG : JUL
시간형식 지정하여 출력하기
▼시간을 출력하기 위한 형식
| 형식 | 설명 |
| HH24 | 24시간으로 표현한 시간 |
| HH, HH12 | 12시간으로 표현한 시간 |
| MI | 분 |
| SS | 초 |
| AM, PM, A.M, P.M | 오전, 오후 표시 |
문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수
ex)
1. SELECT 1300 - '1500'이나, SELECT '1300' - 1500 은 암시적 형 변환으로 연산이 정상적 O
2. SELECT '1,300' - '1,500'의 경우에는 숫자 사이의 쉼표(,)로 인해 숫자로 변환이 되지 않는다.
예시 2번처럼 숫자 데이터가 가공된 문자 데이터로 저장되어 있고, 그 데이터를 연산에 사용하려는 경우
문자 데이터를 숫자 형태로 강제로 인식시켜 주어야 한다.
이때 사용하는 함수가 TO_NUMBER 이다.
TO_NUMBER('[문자열 데이터(필수)]', '[인식될 숫자형태(필수)]')
문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수
TO_DATE('[문자열 데이터(필수)]', '[인식될 날짜형태(필수)]')
NULL 처리 함수
NVL 함수의 기본 사용법
NVL([NULL인지 여부를 검사할 데이터 또는 열(필수)], [앞의 데이터가 NULL일 경우 반환할 데이터](필수))
열 또는 데이터를 입력하여 해당 데이터가 NULL이 아닐 경우 데이터를 그대로 반환, NULL일 경우 지정한 데이터 반환
NVL2 함수의 기본 사용법
NVL2([NULL인지 여부를 검사할 데이터 또는 열(필수)],
[앞 데이터가 NULL이 아닐 경우 반환할 데이터 또는 계산식(필수)],
[앞의 데이터가 NULL일 경우 반환할 데이터 또는 계산식(필수)]),
열 또는 데이터를 입력하여 해당 데이터가 NULL이 아닐 때와 NULL일 때 출력 데이터를 각각 지정한다.
상황에 따라 다른 데이터를 반환하는 DECODE 함수와 CASE문
NVL, NVL2 함수는 데이터가 NULL인 경우 어떤 데이터를 반환할지 정하는 함수이다.
특정 열 값이나 데이터 값에 따라 어떤 데이터를 반환할지 정할 때는 DECODE 함수 또는 CASE문 사용한다.
DECODE 함수
DECODE 함수는 기준이 되는 데이터를 먼저 지정한 후 해당 데이터 값에 따라 다른 결과 값을 내보내는 함수이다.
기준이 되는 데이터를 지정 후 해당 데이터 값에 따라 다른 결과 값을 내보내는 함수이다.
DECODE([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과)],
[조건1], [데이터가 조건1과 일치할 때 반환할 결과],
[조건2], [데이터가 조건2와 일치할 때 반환할 결과],
...
[조건N], [데이터가 조건 N과 일치할 때 반환할 결과],
[위 조건1~조건N과 일치한 경우가 없을 때 반환할 결과])
CASE문
CASE [검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)]
WHEN [조건1] THEN [조건1의 결과 값이 TRUE일 때, 반환할 결과]
WHEN [조건1] THEN [조건1의 결과 값이 TRUE일 때, 반환할 결과]
.....
WHEN [조건N] THEN [조건N의 결과 값이 TRUE일 때, 반환할 결과]
ELSE [위 조건1~조건N과 일치하는 경우가 없을 때 반환할 결과]
END
CASE문은 기준 데이터를 명시하고 그 값에 따라 반환 데이터를 정하는 DECODE함수와 달리
각 조건에 사용하는 데이터가 서로 상관없어도 된다.
'SQL > 오라클_데이터베이스 입문' 카테고리의 다른 글
| [SQL] 데이터 정의어_12 (0) | 2024.08.01 |
|---|---|
| [SQL] 데이터 조작어_10 (0) | 2024.07.31 |
| [SQL] WHERE절과 연산자_5 (0) | 2024.07.28 |
| [SQL] SELECT문의 기본 형식_4 (1) | 2024.07.28 |
| [SQL] 관계형 데이터베이스와 오라클 데이터베이스_2 (0) | 2024.07.28 |