[Oracle] 자주 사용하는 함수 정리(변환 함수, 문자열 조작 함수, 계산 함수, 집합 함수, 날짜 함수 등)

일단.. 프로젝트에서 백엔드를 하고 있어서 SQL문 작성을 하는 일이 많은데

자주 쓰는 함수인데도 매번 구글링 하는 게 귀찮아서 메모 메모,,!

 

함수

Oracle에서의 함수도

프로그램 언어에서 사용하는 메서드(인수에 값을 넣어 원하는 결괏값을 리턴해서 받는 것)와 같다.

 

주요 함수 

・변환 함수 : 문자와 날짜, 숫자형 데이터들 사이에서 형 변환하는 함수

변환 함수
TO_CHAR(val[, fm])	- val을 CHAR형으로 변환 - 포맷 형식을 지정하는 경우는 fm도 지정한다. ※ 포맷 형식에 대해서는 → ~~~을 확인
TO_NUMBER(str)	- str을 NUMBER형으로 변환
TO_DATE(str[, fm])	- str을 DATE형으로 변환 ※ 포맷 형식에 대해서는 → ~~~을 확인
TO_TIMESTAMP(str[, fm])	str을 TIMESTAMP형으로 변환 ※ 포맷 형식에 대해서는 → ~~~을 확인
NVL(a, b)	a가 null이면 b를, null이 아니면 a를 반환
NVL2(a, b, c)	a가 null이면 c를, null이 아니면 b를 반환
CAST(m, n)	값 m을 데이터형 n으로 변환 ex) CAST(1, CHAR(1)) → '1'

・문자열 조작 함수 : 문자와 날짜, 숫자형 데이터들 사이에서 형변환하는 함수

문자열 조작 함수
LENGTH(str)	- 문자열 길이(문자수) 반환 ex) LENGTH('test') → 7
LENGTHB(str)	- 문자열 길이(바이트 수) 반환 ex) LENGTHB('예제') → 4 or 6 ※ 오라클 캐릭터셋에 따라 1글당 2byte or 3byte
LOWER(str)	- 문자열을 모두 소문자로 변환 후 반환 ex) LOWER('aBcDe') → 'abcde'
UPPER(str)	- 문자열을 모두 대문자로 변환 후 반환 ex) LOWER('aBcDe') → 'ABCDE'
SUBSTR(str,m[,n])	- str의 m글자부터 n글자까지만 추출 - n을 생략한 경우는 끝까지 추출 ex) SUBSTR('GYEONGIM',5,3) → NGI  -- 5번째 글자부터 3글자 추출      SUBSTR('GYEONGIM',5) → NGIM -- 5번째 글자부터 끝까지 추출
SUBSTRB(str,m[,n])	- str의 m바이트부터 n바이트까지만 추출 - n을 생략한 경우 끝까지 추출 ex) SUBSTRB('GYEONGIM',5, 3) → NGI      SUBSTRB('성경임', 1, 4) → '성경'      SUBSTRB('성경임', 1, 5) → '성경' -- 1글자당 3바이트로 가정했을 때 '경'까지 ㅇㅋ
TRIM(str)	- str의 좌우 공백을 제거 ex) TRIM('　ABC　') → 'ABC'
RTRIM(str)	- str의 오른쪽 공백을 제거 ex) RTRIM('　ABC　') → '　ABC'
LTRIM(str)	- str의 왼쪽 공백을 제거 ex) LTRIM('　ABC　') → 'ABC　'
INSTR (str1,str2 [,m[,n]])	- str1의 m글자에서 str2를 검색해서 n번째로 발견된 문자 위치를 반환하는 m - n을 생략한 경우는 1로 간주 ex) INSTR('ABCDECD', 'CD', 1, 2) → 6 -- ABCDECD의 1번째 문자부터 2번째 CD가 있는 위치
DECODE(val1,val2,res1,res2)	- val1=val2이면 res1을 반환하고, 다르면 res2를 반환 - (인수는 4개) ex) DECODE(1, 1, '남', '여') → 남
DECODE(val, cond1, res1, cond2, res2, res3 ...)	- val=cond1라면 res1을 반환하고 val=cond2면 res2를 반환하고, 그 이외라면 re3를 반환한다 - 파라미터는 255개까지(파라미터는 무조건 홀수개수) ex) DECODE (2,1, '빨강', 2, '파랑', 3, '노랑', '검정') → 파랑
INITCAP(str)	- 선두 문자만 대문자로 그 외는 소문자로 변환 ex) INITCAP('oRaClE') → Oracle
LPAD(str1,m,str2)	- str1 왼쪽에 str2를 채우고 m글자로 반환 ex) LPAD('234',6,'0') → 000234
RPAD(str1,m,str2)	- str1 오른쪽에 str2를 채우고 m글자로 반환 ex) RPAD('234',6,'0') → 234000
REPLACE(str1,str2,str3)	- str1에 있는 str2를 str3으로 변환 ex) REPLACE('ABCABCABC','C','X') → ABXABXABX -- 문자 C를 X로 변환
TRANSLATE(str1,str2,str3)	- 설명이 어려우므로 예제로 확인.. ex) translate('ABCHIJ', 'ABCDEFGHIJ','1234567890')　→123890      translate('ACEGJ', 'ABCDEFGHIJ','1234567890')　→13570
ASCII(char)	- 문자 char에 대응하는 ASCII 코드 반환 ex) ASCII('A') → 65
CHR(n)	- ASCII 코드 n에 대응하는 문자를 반환 ex) CHR(65) → A
TO_MULTI_BYTE(str)	- str를 2바이트 문자로 변환 ex) TO_MULTI_BYTE('1Aa') → １Ａａ
TO_SINGLE_BYTE(str)	- str를 1바이트 문자로 변환 ex) TO_SINGLE_BYTE('￥ａＡ') → \aA
GREATEST(str1[,strn..])	- 인수중 가장 큰 수를 반환 ex) GREATEST(1,4,6,3,32,9) → 32
LEAST(str1[,strn..])	- 인수 중 가장 작은 수를 반환 ex) LEAST(1,4,6,3,32,9) → 1
REVERSE	- 문자열을 거꾸로해서 반환 ex) REVERSE('ABCDE') → 'EDCBA'

・계산 함수

계산 함수
MOD(m, n)	- m을 n으로 나눈 나머지를 반환 ex) MOD(6, 4) → 2
TRUNC(m[, n])	- m을 소수점 이하 n자리로 자른 값을 반환 ※ n을 생략하면 소수점 이하는 모두 자름 ex) TRUNC(12.987, 2) → 12.98, TRUNC(12.987) → 12
ROUND(m[, n])	- 소수 m을 소수점 이하 n자리수로 반올림한 값을 반환 ex) ROUND(12.987, 2) → 12.99, ROUND(1234.56, -2) → 1200, ROUND(1234.56) → 1234
CEIL(m)	- 소수 m을 반올림한 정수를 반환 ex) CEIL(1.732) → 2, CEIL(-2.3) → -2
FLOOR(m)	- 소수 m을 내림한 정수를 반환 ex) FLOOR(42.195) → 42, FLOOR(-42.195) → -43
SIGN(m)	- 수치 m이 음수면 -1, 양수면 1, 0이면 0을 반환 ex) SIGN(-9) → -1, SIGN(3) → 1, SIGN(0) → 0
ABS(m)	- m의 절대값을 반환 ex) ABS(-8) → 8, ABS(8) → 8
POWER(m, n)	- m의 n승을 반환 ex) POWER(2,3) → 8
SQRT(m)	- m의 제곱근을 반환 ex) SQRT(3) → 1.73205081

 

・집계 함수 : 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수

집계 함수
SUM(<컬럼 이름>)	- 지정한 컬럼의 합계를 반환
COUNT(<컬럼 이름>)	- 지정한 컬럼의 갯수를 반환
MAX(<컬럼 이름>)	- 지정한 컬럼의 최대값을 반환
MIN(<컬럼 이름>)	- 지정한 컬럼의 최소값을 반환
AVG(<컬럼 이름>)	- 지정한 컬럼의 평균치를 반환

・날짜 함수

날짜 함수
ADD_MONTHS(d, m)	- 날짜 d에 m개월 더한 날짜를 반환 ex) ADD_MONTHS(DATE '2022-05-19', 4)→ 2022/09/19
LAST_DAY(d)	- 날짜 d의 달의 마지막 날을 반환 ex) LAST_DAY(DATE '2022-05-19') → 2022/05/31
NEXT_DAY(d, m)	- 날짜 d 이후 가장 가까운 요일 m 날짜를 반환 ※ 요일은 1:일~7:토 ex) NEXT_DAY(DATE '2022-05-19', 1) → 2022/05/22
EXTRACT ([year|month|day|hour|minute|second] from d)	- TIMESTAMP형 d의 연 or 월 or 일 or 시 or 분 or 초만 추출하기 ※ 연월일만 있는 DATE형도 OK ex) EXTRACT(YEAR from TO_DATE ('2022/05/19')) → 2022
MONTHS_BETWEEN(d1, d2)	- 날짜형 d1과 날짜형 d2 사이의 월수를 반환 ex) MONTHS_BETWEEN(TO_DATE('2022-05-19'), TO_DATE('2022-11-19')) →-6
ROUND(d[, fm])	- 날짜 d를 지정한 포맷 fm에서 반올림한 값을 반환 ※ 포맷 형식에 대해서는 →~~를 참고 ex) ROUND(TO_DATE('22-03-01'), 'YY') → 2022/01/01,      ROUND(TO_DATE('22-07-01'), 'YY')→ 2023/01/01 　  ROUND(TO_DATE('22-03-01'), 'MM') → 2022/03/01      ROUND(TO_DATE('22-03-20'), 'MM') → 2022/04/01
SYSDATE	- 현재 일시(연월 일시 분초)를 반환 ex) SELECT SYSDATE FROM DUAL;
SYSTIMESTAMP	- 현재 일시(연월 일시 분초 밀리초)를 반환 ex) SELECT SYSTIMESTAMP FROM DUAL;

 

・USERENV 함수 : 이전 버전과의 호환성을 위해 유지되는 레거시 함수

ex) SELECT USERENV('LANGUAGE') "Language" FROM DUAL; 

기타
USERENV('isdba')	- 연결 사용자가 DBA 역할을 가지고 있는지의 여부를 반환 - 있으면 true, 없으면 false
USERENV('language')	- 사용하고 있는 언어를 반환
USERENV('sessionid')	- 세션 식별자를 반환
USERENV('terminal')	- 터미널 이름 반환
USERENV('instance')	- 인스턴스 식별 번호를 반환