[Oracle] SQL 함수의 정의

1. 함수의 정의

함수는 특정 기능을 실행하는 객체를 말하며,
SQL에 더욱 강력한 기능을 제공할 뿐아니라 쿼리를 더욱 효과적으로 작성 할 수 있습니다.
SQL에서 함수와 자바 CLASS에서 함수는 기능상 비슷 하다고 할 수 습니다.

1-1 함수의 유형

단일행 함수 단일 행만 연산하여 행 당 하나의 결과를 반환 숫자, 문자, 날짜 함수

다중행 함수 행 그룹 당 하나의 결과를 반환 하며 이러한 함수를 그룹 함수라고 한다. 다중 행 함수

1-2 함수의 사용

• 파라미터를 사용하고, 하나의 결과 값을 반환 함
• 행(row) 당 하나의 결과를 반환
• 중첩(nested) 될 수 있음
• 컬럼 또는 표현식을 파라미터로 사용함
• Function_name[(param1, param2,....)

2. 문자(CHARACTER) 함수

문자 함수는 문자를 입력 값으로 받으면 문자 또는 숫자 값을 반환

2-1 LOWER 함수

소문자로 변환합니다.

SQL : SELECT LOWER('ABC abc') FROM dual
RESULT : abc abc

2-2 UPPER

대문자로 변환합니다.

SQL : SELECT UPPER('ABC abc') FROM dual
RESULT : ABC

2-3 SUBSTR

문자 값의 위치 m에서 n까지 지정된 문자를 반환 합니다.
(m이 음수면 문자 값의 끝부터 세며 n을 생략하면 문자열의 끝까지 모든 문자가 반환 됩니다.

SQL : SELECT SUBSTR('123456789', 1,5) FROM dual
RESULT : 12345

2-4 LENGTH

표현식의 문자 수를 반환합니다.

SQL : SELECT LENGTH('123456') FROM dual
RESULT : 6

2-5 INSTR

지정된 문자열의 위치를 숫자로 반환합니다. 검색 시작 위치 m과 문자열의 발생 횟수 n을 지정할 수 있습니다.
(선택사항) m과  n의 기본값은 1이며, 기본값을 지정된 경우 처음부터 검색을 시작하고 첫번째 발생 위치를 보고 합니다.

SQL : SELECT INSTR('1234A1234','A') FROM dual
RESULT : 5

2-6 LPAD / RPAD

전체 폭(width)이 n이 되도록 문자 값을 오른쪽 정렬하고 빈 곳을 지정한 string으로 채웁니다.
전체 쪽이 n이 되도록 문자 값을 왼쪽 정렬하고 빈 곳을 지정한 string으로 채웁니다.

SQL : SELECT LPAD('2009', 10, '*') FROM dual
RESULT : ******2009

SQL : SELECT RPAD('2009', 10, '*') FROM dual
RESULT : 2009******

2-7 TRIM

문자열에서 접두어나 접미어 또는 두 가지 모두를 자릅니다.
trim_character 또는 trim_source가 문자 리터럴이면 작은 따움표 로 묶어야 합니다.
Oracle8i 이상에서 사용 가능한 기능입니다.

SQL : SELECT TRIM('H' FROM 'HelloWorld') FROM dual
RESULT : elloWorld

2-8 REPLACE

파라미터로 주어지는 첫 번째 문자열에서, 두 번째 문자열 모두 세 번째 문자열로 바꾼 후 결과를 반환합니다.


SQL : SELECT REPLACE('ILoveSoccer', 'Soccer', 'Perl') FROM dual
RESULT : ILovePerl

3. 숫자(CHARACTER) 함수

숫자를 입력 받으면, 숫자를 반환

3-1 ROUND

숫자 값을 소수점 n째 자리로 반올립 합니다.
n을 지정하지 않는 경우 소수점 이하 값이 없어 집니다.

SQL : SELECT ROUND(45.926,2) FROM dual
RESULT : 45.93
* 소수점 2자리로 반올림

3-2 TRUNC

숫자 값을 소수점 n째 자리까지 남기고 버립니다.
n을 지정하지 않은 경우에는 기본값 0이 지정됩니다.

SQL : SELECT TRUNC(45.926,2) FROM dual
RESULT : 45.92
* 소수점 2자리까지 남기고 버림

3-3 MOD

m을 n으로 나눈 나머지를 반환 합니다.

SQL : SELECT MOD(1600,300) FROM dual
RESULT : 100
* 몫은 5이며 1600 - (300 * 5) = 100 나머지 값

3-4 CEIL

올림한 후 정수를 반환 합니다.

SQL : SELECT CEIL(45.926) FROM dual
RESULT : 46
* 올림 한 후 정수로 반환

4. 날짜(DATE) 함수

오라클 데이터베이스는 내부적으로 숫자 형식으로 날짜를 저장하며, 날짜에는 세기, 연도, 월, 일, 시, 분, 초가 모두 저장 됩니다.
기본 날짜 표시 형식은 DD-MON-RR 형식입니다.

현재 년도	주어진 날짜	해석(RR)	해석(YY)
1994	27-OCT-94	1995	1995
1994	24-OCT-17	2017	1917
2001	27-OCT-97	1997	2097

데이터베이스 서버의 현재 날짜 및 시간을 반환하는 함수는 SYSDATE 이며, 일반적으로 DUAL이라는 더미 테이블에서 선택 한다고 할 수 있습니다.


4-1 날짜의 산술 연산 ( SYSDATE 함수 )

날짜를 숫자로 저장 -> 산술 연산자를 사용하여 계산할 수 있음

연산	결과	설명
date + number	날짜	날짜에 숫자를 더함
date - number	날짜	날짜에 숫자 뺌
date - date	일(day) 수	한 날짜에서 다른 날짜를 뺌
date + number/24	날짜	날짜에 시간 수를 더함

SQL : SELECT hire_date, (SYSDATE - hire_date)/7 AS WEEKS
         FROM employees
         WHERE department_id = 10;
RESULT :
HIRE_DATE                   WEEKS
-----------------------------------
17-SEP-89                    1056.136

* 현재 날짜(SYSDATE)에서 입사일을 빼면 근무일한 일(day)수가 나오게 되며, 근무 일수를 7로 나누어 사원이 근무한 주 수를 계산하는 방식이다.

4-2 날짜 함수

날짜 함수는 오라클 날짜를 연산하며,
모든 날짜 함수는 DATE 데이터 유형 값을 반환하며 MONTHS_BETWEEN만 숫자 값을 반환 합니다.

함수	설명
MONTHS_BETWEEN	두 날짜 간의 달 수
ADD_MONTHS	날짜에 달 수 더하기
NEXT_DAY	지정한 날짜의 다음 날
LAST_DAY	해당 달의 마지막 날

• MONTHS_BETWEEN은 정수가 아닐 수도 있음
•  ADD_MONTHS는 n 값은 정수여야 하며 음수 일 수 있음
• NEXT_DAY 지정한 요일에 해당하는 날짜를 찾음
• LAST_DAY 마지막 날짜를 찾음

SQL :
SELECT employee_id, hire_date "입사일",
       MONTHS_BETWEEN (SYSDATE, hire_date) "근무한 달 수",
       ADD_MONTHS ( hire_date,6) "6개월 이후 일",
       NEXT_DAY (hire_date, 'FRIDAY') "입사 후 첫 금요일",
       LAST_DAY (hire_date) "마지막 날"
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 50;

RESULT :