728x90

오라클 함수

함수 : 특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어

내장 함수 (기본 제공)
- 단일행 함수 : 데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수 ⇒ 아래 내용
- 다중행 함수 : 여러 행이 입력되어 하나의 행으로 결과가 나오는 함수
사용자 정의 함수

<단일행 함수>

문자 함수

UPPER(문자열)
모두 대문자로 변환
LOWER(문자열)
모두 소문자로 변환
INITCAP(문자열)
첫 글자는 대문자로, 나머지는 소문자로 변환

⇒ 문자열 검색에서 주로 사용

LENGTH(문자열)
문자열의 길이
LENGTHB(문자열)
문자열의 바이트 수 (한글은 2BYTE)

DUAL 테이블
DUMMY 테이블로 임시 연산이나 함수의 결과 값 확인 용도로 사용

SUBSTR : 문자열 일부 추출
SUBSTR(문자열, 시작 위치, 길이)
SUBSTR(문자열, 시작 위치)
문자열의 시작 위치부터 길이만큼 추출, 문자열 시작 위치부터 끝까지 추출
INSTR : 특정 문자 위치
INSTR(문자열, 찾을 문자, 시작 위치(DEFALUT 1), 찾을 문자 번째(DEFAULT 1))
문자열의 시작 위치부터 몇 번째 문자를 찾아 위치 추출 (시작 위치가 음수이면 오른쪽 끝부터 왼쪽으로 검색)
찾는 문자가 없으면 0 반환
REPLACE : 문자 변환
REPLACE(문자열, 찾을 문자, 대체 문자(선택))
문자열에서 찾을 문자를 다른 문자로 대체 (대체할 문자가 없으면 찾은 문자 삭제)
LPAD, RPAD : 빈 공간 채우기
LPAD(문자열, 자리수, 채울 문자(선택))
문자열에서 자리수만큼 빈 공간을 왼쪽에 문자로 채우기 (채울 문자가 없으면 공백)
RPAD(문자열, 자리수, 채울 문자(선택))
문자열에서 자리수만큼 빈 공간을 오른쪽에 문자로 채우기 (채울 문자가 없으면 공백)
⇒ 개인정보 출력 시 사용 가능 (데이터 일부 노출)
CONCAT : 문자열 합치기
CONCAT(문자열1, 문자열2)
= || 연산자로 합치기 가능
TRIM, LTRIM, RTRIM : 특정 문자 지우기

TRIM(삭제 옵션(선택) 삭제할 문자(선택) FROM 원본 문자열)
LTRIM(문자열, 삭제할 문자(선택))
RTRIM(문자열, 삭제할 문자(선택))

숫자 함수

ROUND : 반올림
ROUND(숫자, 위치(선택))
반올림 위치에서 숫자 반올림 (위치 없으면 소수점 첫째 자리에서 반올림)
TRUNC : 버림
TRUNC(숫자, 위치(선택))
버림 위치에서 숫자 버림 (위치 없으면 소수점 첫째 자리에서 버림)
CEIL, FLOOR : 가까운 정수 찾기
CEIL(숫자), FLOOR(숫자)
숫자와 가까운 큰 정수(CEIL), 작은 정수(FLOOR) 반환
MOD : 나머지 값
MOD(숫자, 나눌 숫자)
숫자 데이터를 특정 숫자로 나눈 나머지

날짜 함수

SYSDATE
오라클 데이터베이스 셔터가 놓은 os의 현재 날짜와 시간 보여줌
ADD_MONTHS : 몇 개월 이후
ADD_MONTHS(날짜, 더할 개월 수)
MONTHS_BETWEEN : 두 날짜 개월 수 차이
MONRHA_BETWEEN(날짜, 날짜)
NEXT_DAY, LAST_DAY : 달의 마지막 날, 돌아오는 요일
NEXT_DAY(날짜, 요일)
LAST_DAY(날짜)

형 변환 함수

암시적 형 변환 (자동 형 변환) : EMPNO(NUMBER)+'500' ⇒ SUCCESS
명시적 형 변환 : 'ABC'+EMPNO(NUMBER) ⇒ ERROR

TO_CHAR
숫자 또는 날짜를 문자로 변환
TO_NUMBER
문자를 숫자로 변환
TO_DATE
문자를 날짜로 변환

연/월/일 시:분:초
YYYY/MM/DD HH24:MI:SS

날짜 형식
CC : 세기
YYYY, RRRR : 4자리 연
YY, RR(1900년대) : 2자리 연
MM : 2자리 월
MON : 월 약자
MONTH : 월 전체
DD : 2자리 일
DDD : 1년 중 며칠
DY : 요일 약자
DAY : 요일 전체
W : 1년 중 몇 번째 주

시간 형식
HH24 : 24시간
HH, HH12 : 12시간
MI : 분
SS : 초
AM, PM, A.M., P.M. : 오전 오후 표시

숫자 형식
9 : 숫자 한 자리 (공백 가능)
0 : 빈자리를 0으로 채움
$ : 달러 표시 붙임
L : L 지역 화폐 단위 기호
. : 소수점
, : 천 단위 구분 기호

NULL 처리 함수

NVL
NVL(데이터, NULL일 경우 반환할 데이터)
NULL이 아니면 데이터 그대로 반환, NULL이면 두 번째 입력된 데이터 반환
NVL2
NVL2(데이터, NULL이 아닐 경우 반환할 데이터, NULL일 경우 반환할 데이터)
NULL이 아니면 두 번째 입력된 데이터 반환, NULL이면 세 번째 입력된 데이터 반환

DECODE 함수와 CASE문

DECODE
IF 조건문 또는 SWITCH CASE 조건문과 비슷
조건에 일치하지 않을 때 반환 값이 없으면 NULL 반환

  DECODE(검사 대상,
          조건1, 일치 시 반환 결과, 
          조건2, 일치 시 반환 결과,
          ...,
          일치하지 않을 시 반환 결과)

CASE
특정 조건에 따라 반환할 데이터 설정 시 사용
조건식의 TRUE, FALSE 여부만 검사하기에 기준 데이터 없어도 사용 가능

  CASE 검사 대상
      WHEN 조건1 THEN TRUE일 때 반환 결과
      WHEN 조건2 THEN TRUE일 때 반환 결과
      ...
      ELSE FALSE일 때 반환 결과
  END

<다중행 함수>

= 그룹 함수 = 복수행 함수

여러 행을 바탕으로 하나의 결과 값을 도출해내기 위해 사용하는 함수

SUM

데이터의 합을 구하는 함수

SUM([DISTINCT, ALL 중 하나(선택)] [합계 구할 열이나 데이터])
OVER (분석을 위한 문법) -- OVER : 함수를 분석하는 용도로 사용

-- EXAMPLE --
SELECT SUM(DISTINCT SAL), SUM(ALL SAL), SUM(SAL) -- ALL은 아무 옵션을 지정하지 않은 것과 같은 효과
FROM EMP;

COUNT

데이터의 개수를 구하는 함수 (NULL은 개수에서 제외)

COUNT([DISTINCT, ALL 중 하나(선택)] [개수 구할 열이나 데이터])
OVER (분석을 위한 문법)

-- EXAMPLE --
SELECT COUNT(*)
FROM EMP
WHERE DEPTNO = 30;

MAX, MIN

데이터의 최대 값과 최소 값을 구하는 함수 (날짜 및 문자에도 사용 가능)

MAX([DISTINCT, ALL 중 하나(선택)] [최대값 구할 열이나 데이터]) -- DISTINCT가 별로 필요 없음
OVER (분석을 위한 문법)

MIN([DISTINCT, ALL 중 하나(선택)] [최소값 구할 열이나 데이터])
OVER (분석을 위한 문법)

-- EXAMPLE --
SELECT MAX(SAL), MIN(SAL)
FROM EMP;

AVG

데이터의 평균 값을 구하는 함수

AVG([DISTINCT, ALL 중 하나(선택)] [개수 구할 열이나 데이터]) -- DISTINCT 중복 값을 제외하고 평균을 계산하므로 결과 값이 달라짐
OVER (분석을 위한 문법)