[Oracle] 기본 함수

1. 함수의 기능

• 함수는 SQL의 매우 강력한 기능이며 다음 작업을 수행하는데 함수를 사용할 수 있다.

1. 데이터에 대한 계산 수행

2. 개별 데이터 항목 수정

3. 행 그룹에 대한 출력 조작

4. 표시할 날짜 및 숫자의 형식 지정

SQL 함수는 단일 행 함수와 여러행 함수의 두 가지 유형으로 이루어져 있다.

함수 - 단일 행 함수 (행당 하나의 결과를 반환)

- 여러 행 함수 (행 집합당 하나의 결과를 반환)

1-1. 단일 행 함수의 종류

단일 행 함수 : 문자함수 숫자함수 날짜함수, 변환함수(묵시적 데이터 변환, 명시적 데이터 변환), 일반함수

단일 행 함수
문자함수	숫자함수	날짜 함수	변환 함수		일반함수
			묵시적 데이터  변환	명시적 데이터 변환

• 단일 행 함수는 데이터 항목을 조작하구 인수를 받아들이고 하나의 값을 변환한다.

• 단일 행 함수는 반환되는 각 행에서 실행되고 행 당 하나의 결과를 반환한다.

• 열이나 표현식을 인수로 받아들일 수 있다.

(1) 문자함수

- INITCAP() 함수

: 영어에서 첫 글자만 대문자로 출력하고 나머지는 전부 소문자로 출력하는 함수이다.

사용법 
INITCAP(문자열 또는 컬럼명) 

EX)
SELECT ENAME, INITCAP(ENAME)
FROM EMP;

- LOWER(), UPPER()함수

: LOWER()함수는 입력되는 값을 전부 소문자로 변경하는 함수이고, UPPER()함수는 입력되는 값을 전부 대문자로 변경하는 함수이다.

사용법
LOWER(문자열 또는 컬럼명) 
UPPER(문자열 또는 컬럼명)

EX)
SELECT ENAME, LOWER(ENAME), UPPER(ENAME)
FROM EMP;

- SUBSTR() 함수

: 주어진 문자열에서 특정길이의 문자만 골라내는 함수이다.

사용법
SUBSTR('문자열' 또는 컬럼명 , 시작위치, 골라낼 글자 수)

EX)
SELECT ENAME, SUBSTR(ENAME,1,2)
FROM EMP;

- SUBSTRB() 함수

: SUBSTR()함수와 동일하며 차이점은 추출할 자리수가 아니라 추출할 Byte수를 지정한다. (한글을 사용할 때 많이 씀)

사용법
SUBSTRB('문자열' 또는 컬럼명, 시작위치, Byte 수)

EX)
SELECT ENAME, SUBSTR(ENAME,1,2), SUBSTRB(ENAME,1,2)
FROM EMP;

- INSTR() 함수

: 주어진 문자열이나 컬럼에서 특정 글자의 위치를 찾는 함수

사용법
INSTR('문자열' 또는 컬럼, 찾는 글자, 시작위치, 몇 번째인지(기본값 1))

EX)
SELECT HIREDATE, INSTR(HIREDATE,'/',1,2)
FROM EMP;

- LPAD() , RPAD()함수

: LPAD() 함수는 왼쪽 공백에 특별한 문자로 채우는 함수이고, RPAD()함수는 오른쪽 공백에 특별한 문자로 채우는 것이다.

사용법
LPAD('문자열 또는 컬럼명, 자리수, '채울문자');
RPAD('문자열 또는 컬럼명, 자리수, '채울문자');

EX)
SELECT LPAD(ENAME,10,'*'), RPAD(ENAME,10,'*')
FROM EMP;

- REPLACE() 함수

: 주어진 첫 번째 문자열이나 컬럼에서 문자1을 문자2로 바꾸는 함수이다.

사용법
REPLACE('문자열' 또는 컬럼명, '문자1' ,'문자2');

EX)
SELECT REPLACE(ENAME, SUBSTR(ENAME,2,2),'***')
FROM EMP;

(2) 숫자함수

- ROUND(), TRUNC() 함수

: ROUND()함수는 반올림하는 함수고, TRUNC()함수는 자리수를 버리는 함수이다.

사용법
ROUND(숫자, 자리수)
TRUNC(숫자, 자리수)

EX)
SELECT ROUND(3.141592, 2) AS "ROUND" , TRUNC(3.141592, 4) AS "TRUNC"
FROM EMP;

- MOD(), CEIL(), FLOOR() 함수

: MOD() 함수는 나머지 값을 구하는 함수

: CEIL() 함수는 주어진 숫자와 가장 가까운 큰 정수를 구하는 함수

: FLOOR() 함수는 주어진 숫자와 가장 가까운 작은 정수를 구하는 함수

사용법
MOD(값 OR 컬럼명, 나누는 수)
CEIL(값 OR 컬럼명)
FLOOR(값 OR 컬럼명)

SELECT MOD(10,3) AS "MOD", CEIL(2.251) AS "CEIL", FLOOR(2.651) AS "FLOOR"
 FROM EMP;

(3) 날짜함수

- SYSDATE 함수

: 현재의 날짜와 시간을 출력해주는 함수이므로 오라클은 OS로부터 시간을 가져오므로 OS운영체제의 시간을 변경하면 DB의 시간도 바뀜

사용법
EX) 
SELECT SYSDATE
 FROM EMP;

- MONTHS_BETWEEN 함수

: 두 날짜를 입력 받아 두 날짜 사이의 개월 수를 출력하는 함수

사용법
MONTHS_BETWEEN(나중_날짜, 이전_날짜)

EX)
SELECT SYSDATE, FLOOR(MONTHS_BETWEEN(SYSDATE, HIREDATE))
 FROM EMP;

(4) 데이터 형 변환 함수

- TO_CHAR() 함수

: 숫자형을 문자형으로 변환하거나 날짜를 문자로 변환하는 함수

사용법
TO_CHAR(컬럼명,숫자,날짜, '원하는 모양')

EX)
날짜를 년으로 표시
SELECT TO_CHAR(SYSDATE, 'YYYY')
FROM EMP;
숫자에 달러 표시
SELECT TO_CHAR(6000, '$9,999')
FROM EMP;

- TO_NUMBER() 함수

: 문자열로된 숫자를 정수형 숫자로 바꾸어 주는 함수

사용법
TO_NUMBER('숫자처럼 생긴 문자')

EX)
SELECT TO_NUMBER('3.1459')
FROM EMP;

- TO_DATE() 함수

: 날짜형식으로된 문자를 날짜타입으로 바꾸어주는 함수

사용법
TO_DATE('날자형식의 문자')

EX)
SELECT TO_DATE('2015/12/25', 'YYYY/MM/DD')
FROM EMP;

DUAL로 입력과 결과를 함께 확인하기

위 예시의 EMP는 Oracle 학습용 샘플 테이블을 전제로 한다. 설치 환경에 EMP가 없다면 함수 자체가 틀린 것이 아니라 테이블이 없는 것이다. 표준적인 실습에서는 DUAL을 사용해 함수 입력과 결과를 바로 볼 수 있다.

SELECT INITCAP('hELLO WORLD') AS title,
       SUBSTR('ORACLE', 2, 3) AS piece,
       INSTR('ORACLE', 'A') AS letter_pos,
       ROUND(3.14159, 2) AS rounded,
       TRUNC(3.14159, 2) AS truncated
FROM DUAL;

결과는 Hello World, RAC, 위치 3, 반올림 3.14, 절삭 3.14다. ROUND(3.146, 2)와 TRUNC(3.146, 2)처럼 세 번째 소수 자리가 6인 값을 넣으면 각각 3.15, 3.14로 차이가 드러난다. SUBSTR의 시작 위치는 1부터 세며 SUBSTRB는 바이트 단위로 다룬다. 다국어 문자열에서 문자 수와 바이트 수가 같다고 가정하면 잘린 문자열이 기대와 달라질 수 있다.

날짜는 형식을 명시하기

SELECT TO_DATE('2024-12-25', 'YYYY-MM-DD') AS holiday,
       TO_CHAR(TO_DATE('2024-12-25', 'YYYY-MM-DD'), 'YYYY') AS year_text,
       MONTHS_BETWEEN(
         TO_DATE('2025-02-01', 'YYYY-MM-DD'),
         TO_DATE('2025-01-01', 'YYYY-MM-DD')
       ) AS months
FROM DUAL;

MONTHS_BETWEEN은 이 예에서 1이다. 일반적인 두 날짜 사이에서는 소수 개월 값이 나올 수 있어 “항상 달력 월 수의 정수”라고 생각하면 안 된다. SYSDATE는 데이터베이스 서버의 현재 날짜·시각을 사용하므로 쿼리를 실행하는 순간과 서버 설정에 따라 결과가 달라진다. TO_DATE('2015/12/25')처럼 형식 모델 없이 문자열을 넣으면 세션의 NLS_DATE_FORMAT 설정에 의존해 어떤 환경에서는 실패하거나 다른 뜻으로 해석될 수 있다. 형식 모델을 함께 적고, 애플리케이션에서는 가능한 한 문자열 날짜를 만들기보다 드라이버의 날짜 바인딩을 사용한다.

flowchart LR
  I[문자열 입력] -->|TO_DATE + 명시적 형식| D[DATE 값]
  D -->|날짜 연산| R[DATE 또는 숫자 결과]
  D -->|TO_CHAR + 표시 형식| S[출력 문자열]

함수	반환 관점	흔한 실수
TO_DATE	문자열 → DATE	형식 모델 생략과 NLS 의존
TO_CHAR	날짜/숫자 → 문자	화면용 문자열을 다시 날짜처럼 비교
TO_NUMBER	문자열 → 숫자	숫자가 아닌 글자나 지역별 소수점 형식
NVL·COALESCE	NULL 대체	원래 NULL과 대체값의 의미를 혼동

WHERE TO_CHAR(order_date, 'YYYY') = '2024'처럼 컬럼에 함수를 씌우면 날짜 범위 인덱스 활용이 불리할 수 있다. 연도 검색에는 order_date >= DATE '2024-01-01' AND order_date < DATE '2025-01-01'처럼 범위를 직접 표현하고 실행 계획을 확인한다. 함수의 계산 결과뿐 아니라 컬럼 타입, NULL 처리, 인덱스 영향까지 함께 살펴야 한다.

참고: Oracle TO_DATE, 형식 모델, SUBSTR 계열, MONTHS_BETWEEN.