복잡한 쿼리문을 간단하게 해주고 데이터의 값을 조작하는데 사용되는 것을 함수라 한다. 일반적으로 주어진 데이터(인수)를 처리하고 그 결과를 반환하는 기능을 수행한다.
• 데이터에 대한 계산을 수행한다.
• 각각의 데이터 항복을 변경한다.
• 그룹의 결과를 출력한다.
• display할 날짜 형식을 변경한다.
• 컬럼 데이터 타입을 변경한다.
SQL 함수는 크게 단일행 함수와 복수행 함수로 구분한다.
단일행 함수는 테이블에 저장되어 있는 개별 행을 대상으로 함수를 적용하여 하나의 결과를 반환하는 함수이다.
복수행 함수는 조건에 따라 여러 행을 그룹화하여 그룹별로 결과를 하나씩 변환하는 함수이다.
• Single-row 함수(단일행 함수) : 문자 함수, 숫자함수, 날짜 데이터 타입에 적용할 수 있는 함수, 데이터 타입을 변환하는 함수
• Group (복수행 함수, 그룹함수)
• 단일행 함수는 쿼리시 각각의 행에 대해 수행되며, 행당 하나의 결과값을 리턴해 준다.
• 인수로는 상수, 변수, 표현식이 있다.
• 하나 이상의 인수를 필요로 한다.
• 몇몇 함수는 쿼리에 의해 리턴되는 데이터를 변경한다.
• SELECT, WHERE, 그리고 ORDER BY 절에서 사용할 수 있다.
• 중첩하여 쓰여질 수 있다.
• 문자 함수(Character) : 문자 데이터를 입력받아 문자 또는 숫자 값을 리턴해 주는 함수
• 숫자 함수(Number) : 입력으로 숫자 데이터를 받아 숫자 값을 리턴하는 함수
• 날짜 함수(Date) : 날짜 데이터형에 값이 수행되는 함수
• 변환 함수(Conversion) : 사용자에 의해서 명시적으로 데이터 타입을 변환해 주는 함수와 묵시적으로 데이터형을 변환해 주는 함수가 있다.
• 일반 함수(General)
숫자함수는 입력으로 숫자 데이터를 받아 숫자값을 리턴하는 함수이다.
| 함수 | 설명 |
|---|---|
| ROUND(number) | 숫자값을 특정 위치에서 반올림하여 리턴한다. |
| TRUNC(number) | 숫자값을 특정 위치에서 절삭하여 리턴한다. |
| CEIL | 숫자값을 소숫점 첫째자리에서 올림하여 정수값을 리턴한다. |
| FLOOR | 숫자값을 소숫점 첫째자리에서 절삭하여 정수값을 리턴한다. |
| MOD | 나머지값을 리턴한다. |
| ABS | 숫자값의 절대값을 리턴한다. |
| SIGN | 숫자값의 부호에 따라 1, 0, -1의 값으로 리턴한다. |
| POWER(n1,n2) | n1^n2한 지수곱값을 리턴한다. |
| SQRT(n) | n의 제곱근 값을 리턴한다. |
| SIN(n) | n의 sine 값을 리턴한다. |
| COS(n) | n의 cosine 값을 리턴한다. |
| TAN(n) | n의 tangent 값을 리턴한다. |
| SINH(n) | n의 hyperbolic sine 값을 리턴한다. |
| COS(n) | n의 hyperbolic cosine 값을 리턴한다. |
| TAN(n) | n의 hyperbolic tangent 값을 리턴한다. |
| LOG(a,b) | 밑이 a인 b의 지수 값을 리턴한다. 즉, 뒤의 값이 앞의 값의 몇 배수인지를 알림 |
| LN(n) | n의 자연로그 값을 리턴한다. |
• dual 테이블은 사실상 dummy 데이터값이 존재하고 컬럼의 수가 제한되지 않은 가상의 테이블이다.
문자함수는 문자 데이터를 입력 받아 문자 또는 숫자값을 리턴하는 함수이다.
문자함수의 종류에는 대소문자 변환함수, 문자 조작함수, 문자열 길이 반환함수 등이 있다.
| 함수 | 설명 |
|---|---|
| UPPER | 영어 소문자를 대문자로 바꾸어 리턴한다. |
| LOWER | 영어 대문자를 소문자로 바꾸어 리턴한다. |
| INITCAP | 문자열중 각 단어의 첫글자만 대문자로 바꾸어 리턴한다. |
| LENGTH | 문자열의 길이를 숫자값으로 리턴한다. LENGTHB, LENGTHC, LENGTH2, LENGTH4가 있음 |
| CONCAT | 첫번째 문자열과 두번째 문자열을 연결하여 리턴한다. 연결 연산자(¦¦) 참조 |
| SUBSTR | 문자값 중 특정 위치부터 특정 길이만큼의 문자값만을 리턴한다. SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4 |
| INSTR | 문자값 중 지정된 문자값의 위치를 숫자로 리턴한다. |
| RPAD | 지정된 길이에서 문자값을 채우고 남은 공간을 우(좌)측부터 특정값으로 채워 리턴한다.LPAD |
| RTRIM | 문자값중에서 우(좌)측으로부터 특정문자와 일치하는 문자값을 제거하여 리턴한다.LTRIM |
| ASCII(char) | 지정한 숫자나 문자를 ASCII 코드값으로 바꾸어 리턴한다.(문자일 경우, 단일인용부호를 사용한다) |
| CHR(n) | ASCII 코드값인 n값을 해당하는 문자로 바꾸어 리턴한다.(CHR(75)='F'임) |
| GREATEST(a1,a2,a3) | 나열한 숫자나 문자중에서 가장 큰 값을 리턴한다. |
| LEAST(a1,a2,a3) | 나열한 숫자나 문자중에서 가장 작은 값을 리턴한다. |
| REPLACE(a1,a2,a3) | a1: 전제문자열 a2: 전체 문자열 a1중에서 바꾸기를 원하는 문자열 a3: 바꾸고자 하는 새로운 문자열 |
| VSIZE(char) | 지정된 문자열의 크기를 숫자값으로 리턴한다. |
날짜함수는 날짜 데이터형의 값에 수행되는 함수이다.
| 함수 | 설명 | 결과 |
|---|---|---|
| SYSDATE | 현재의 날짜와 시간을 리턴한다. | 날짜 |
| ROUND(date) | 정오를 기준으로 날짜를 반올림하여 리턴한다. | 날짜 |
| TRUNC(date) | 날짜에서 시간부분을 절삭하여 00:00으로 바꾸어주는 함수 | 날짜 |
| MONTHS_BETWEEN | 두 개의 날짜간의 달 차이를 리턴하는 함수 | 숫자 |
| ADD_MONTHS | 특정 수의 달을 더한 날짜를 리턴하는 함수 | 날짜 |
| LAST_DAY | 특정 날짜가 속한 달의 가장 마지막 날짜를 리턴하는 함수 | 날짜 |
| NEXT_DAY | 명시된 요일이 돌아오는 가장 최근의 날짜를 리턴하는 함수 | 날짜 |
날짜에 산술 연산을 사용하는 경우, 반환 되는 데이터 타입
| 연산 | 결과 | 의 미 |
|---|---|---|
| 날짜 + 숫자 | 날짜 | 날짜에 일수를 더하여 날짜 계산 |
| 날짜 - 숫자 | 날짜 | 날짜에 일수를 감하여 날짜 계산 |
| 날짜 + 숫자/24 | 날짜 | 날짜에 시간을 더하여 날짜 계산 |
| 날짜 - 날짜 | 일수 | 날짜에 날짜를 감하여 일수 계산 |
datetime함수는 date함수의 확장형태이다.
| 함수 | 설명 |
|---|---|
| TZ_OFFSET | time zone에 대한 시초값을 출력 |
| CURRENT_DATE | 현재의 날짜와 시간을 출력 |
| CURRENT_TIMESTAMP | 현재의 날짜와 밀리초 단위의 시간을 출력 |
| LOCALTIMESTAMP | 현재 session의 날짜와 밀리초 단위의 시간을 출력 |
| DBTIMEZONE | 데이터베이스의 time zone 값을 출력 |
| SESSIONTIMEZONE | 현재 session의 time zone 값을 출력 |
| EXTRACT | 특정 날짜/시간(datetime)값이나 날짜값을 가진 표현식으로부터 원하는 날짜 영역을 추출하여 출력 |
| FROM_TZ | timestamp값을 timestamp with time zone값으로 변환 |
| TO_TIMESTAMP | 문자열을 timestamp값으로 변환 |
| TO_TIMESTAMP_TZ | 문자열을 timestamp with time zone값으로 변환 |
| TO_YMINTERVAL | 문자열을 interval year to month 형으로 변환 |
| TO_DSINTERVAL | 문자열을 interval day to second 형으로 변환 |
• time zone이름은 v$timezone_names 뷰에서 확인된다.
• timestamp의 default 타입은 v$nls_PARAMETERS 뷰나 NLS_SESSION_PARAMETERS 뷰에서 확인된다.
사용자에 의해서 명시적으로 데이터 타입을 변환해주는 함수
| 함수 | 설명 |
|---|---|
| TO_NUMBER | 문자 타입을 숫자 타입으로 변환 |
| TO_CHAR(number) | 숫자, 날짜 타입을 문자 타입으로 변환, TO_CHAR(character),TO_CHAR(datetime) |
| TO_DATE | 숫자, 문자 타입을 날짜 타입으로 변환 |
| CONVERT | 문자열을 한 국가의 언어 형식에서 다른 국가 언어 형식으로 변환하여 실행 |
| HEXTORAW | 16진수 문자열을 2진수로 문자열을 변환 |
그 밖의 일반 함수
| 함수 | 설명 |
|---|---|
| NVL | null 값을 원하는 데이터 값으로 바꾸어 주는 함수 |
| NVL2 | 값이 null인 경우와, null이 아닌 경우에 다르게 반환해주는 함수 |
| NULLIF | 두 개의 값을 비교하여 null값 또는 사용되진 두 값중 하나를 리턴하는 함수 |
| NANVL | NANVL(column, express1)에서 column이 NAN(not a number)이면 express1의 값을, NAN이 아니면 column 값을 반환 |
| COALESCE | 나열해 놓은 값을 순차적으로 체크하여 null이 아닌 값을 리턴하는 함수 |
| DECODE | 여러 개의 조건을 주어 조건에 맞을 경우 해당 값을 리턴하는 함수(IF...THEN...ELSE...처럼) |
| CASE | 여러 개의 조건을 주어 조건에 맞을 경우 해당 값을 리턴하는 함수(DECODE의 확장임) |
정규표현식 함수
| 메타문자와 정규 표현식 함수 | regexp_like() | LIKE 연산자와 유사한 기능의 함수 |
| regexp_instr() | INSTR 연산자와 유사한 기능의 함수 |
| regexp_substr() | SUBSTR 연산자와 유사한 기능의 함수 |
| regexp_replace() | REPLACE 연산자와 유사한 기능의 함수 |
• v$nls_parameters 뷰에서 현재 세션의 매개변수가 한글로 되어 있지 않는 경우, 현재 세션에서만 한글을 사용하려면, alter session set nls_language='KOREAN';을 실행하던가, 매번 부팅때마다 자동으로 한글 세션이 되도록 하려면 login.sql에 포함시키면 된다.
• 그룹당 하나의 결과를 출력한다.
• 그룹 함수는 SELECT절 및 HAVING 절에서 사용할 수 있다.
• GROUP BY절은 행들을 그룹화 한다.
• HAVING 절은 그룹을 제한한다.(비교: WHERE 절은 행(row)을 제한한다.)
| 함수 | 설명 |
|---|---|
| AVG(n) | NULL 값을 제외한 N개 행의 평균값을 리턴한다. |
| COUNT | 행의 갯수를 리턴한다. COUNT(*)은 NULL값을 포함 |
| MAX | 최대값을 리턴한다. |
| MIN | 최소값을 리턴한다. |
| STDDEV(n) | NULL 값을제외한 n의 표준편차를 리턴한다. |
| SUM(n) | NULL을 제외한 n의 합계를 리턴한다. |
| VARIANCE(n) | NULL값을 제외한 n의 분산을 리턴한다. |
• COUNT(*)를 제외한 모든 그룹함수는 NULL을 무시하기 때문에 NVL 함수를 이용하여 NULL값을 다른 값으로 치한시켜 주어야 한다.