{info} 함수는 입력되는 값이 많아도 출력은 하나만 된다는 M:1관계라는 특징을 지니고 있으며
SQL 사용에 있어서 더욱 강력하고 간편하게 조작을 가능하게 하는 특징을 지닌다. {info}

함수

함수는 다음과 같이 나뉠 수 있다.

내장 함수: 벤더에서 제공되는 함수
1. 단일행 함수: 함수의 입력 값이 단일행 값이 입력되는 함수(Single-Row Function)
2. 다중행 함수: 함수의 입력 값이 여러 행 값이 입력되는 함수(Multi-Row Function)
  1. 집계 함수
  2. 그룹 함수
  3. 윈도우 함수
사용자 정의 함수: 사용자가 정의할 수 있는 함수

단일행 함수

단일행 함수의 종류 및 특징

SELECT, WHERE, ORDER BY 절에 사용 가능하다.
각 행(Row)들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.
여러 인자(Argument)를 입력해도 단 하나의 결과만 리턴한다.
함수의 인자(Arguments)로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있다.
특별한 경우가 아니면 함수의 인자(Arguments)로 함수를 사용하는 함수의 중첩이 가능하다

문자형 함수

종류
예제

LENGTH(Oracle vs Mssql)

Oracle	SELECT LENGTH('SQL Expert') FROM DUAL;	result = 10
Mssql	SELECT LEN('SQL Expert')	result = 10

CONCAT(Oracle vs Mssql)

Oracle	SELECT CONCAT('SQL', ' Expert') FROM DUAL;	result = SQL Expert
Mssql	SELECT 'SQL'+ ' Expert'	result = SQL Expert

LENGTH + CONCAT(Oracle vs Mssql)

Oracle	SELECT LENGTH(CONCAT('SQL', ' Expert')) FROM DUAL;	result = 10
Mssql	SELECT LEN('SQL'+ ' Expert')	result = 10

{tip}* DUAL 테이블의 특성
- 사용자 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블이다.
- SELECT ~ FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블이다.
- DUMMY라는 문자열 유형의 칼럼에 'X' 라는 값이 들어 있는 행을 1 건 포함하고 있다.
* mssql에서는 dual 없음* {tip}

{info} 특별한 제약 조건이 없다면 함수는 여러 개 중첩하여 사용이 가능하다.
함수 내부에 다른 함수를 사용하며 안쪽에 위치해 있는 함수부터 실행되어 그 결과 값이 바깥쪽의 함수에 인자(Argument)로 사용되는 것이다.

함수3 (함수2 (함수1 (칼럼이나 표현식 , Arg1) , Arg2) , Arg3 )

|

숫자형 함수

종류
예제

ROUND(Oracle vs Mssql)

Oracle	SELECT ROUND(33.4567, 3) FROM DUAL;	result = 33.4567
Mssql	SELECT ROUND(33.4567, 3)	result = 33.457

TRUNC(Oracle vs Mssql)

Oracle	SELECT TRUNC(33.4567) FROM DUAL;	result = 33
Mssql	SELECT ROUND(33.4567, 0, 3)	result = 33
Mssql	SELECT ROUND(33.4567, 0, 1)	result = 33
Mssql	SELECT ROUND(33.4567, 0, 0)	result = 33
Mssql	SELECT ROUND(33.4567, 1, 1)	result = 33.4

{tip}* ROUND 함수의 인자(PARAMETER)중
첫 번째 인자 : 반올림 / 절삭 하려는 수
두 번째 인자 : 반올림 변수(편의상 반올림 변수라 함)
(세 번째 인자 ) : 0인 경우 반올림 처리, 0이 아닌 아무 정수면 절삭처리
-> 양수이면 그에 해당하는 소수점 아래까지 반올림하고 음수이면 그에 해당하는 소수점 위쪽까지 반올림한다. {tip}

날짜형 함수

종류
연산

SYSDATE(Oracle vs Mssql)

Oracle	SELECT SYSDATE FROM DUAL	result = 13/10/08
Mssql	SELECT GETDATE()	result = 2013-10-10 15:05:47.983

SYSDATE(Oracle vs Mssql)

Oracle	SELECT TO_NUMBER(TO CHAR(SYSDATE,'yyyy')) year，TO_NUMBER(TO_CHAR(HIREDATE, 'MM')) month，TO_NUMBER(TO CHAR(HIREDATE, 'DD')) day FROM DUAL	result = 2013, 10, 10
Mssql	SELECT DATEPART(YEAR, getdate()), DATEPART(MONTH, getdate()), DATEPART(DAY, getdate())	result = 2013, 10, 10

변환형 함수

-> 변환형 함수는 특정 데이터 타입을 다OJ'한 형식으로 출력하고 싶을 경우에 사용되는 함수 이며 변환형 함수는 크게 두 가지 방식이 있다.

변환 유형
종류

특정형태로 날짜 변환(Oracle vs Mssql)

Oracle	SELECT TO_CHAR(SYSDATE, 'YYYY/MM!DD') DUAL	result = 2013/10/08
Mssql	SELECT CONVERT(VARCHAR(10),GETDATE() ,111)	result = 2013/10/08

{tip} * mssql 변환	함수	결과
CONVERT(VARCHAR, GETDATE(), 1)	10/10/13
CONVERT(VARCHAR, GETDATE(), 2)	13.10.10
CONVERT(VARCHAR, GETDATE(), 3)	10/10/13
CONVERT(VARCHAR, GETDATE(), 4)	10.10.13
CONVERT(VARCHAR, GETDATE(), 5)	10-10-13
CONVERT(VARCHAR, GETDATE(), 8)	11:00:34
CONVERT(VARCHAR, GETDATE(), 9)	10 10 2013 11:00:39:573AM
CONVERT(VARCHAR,GETDATE(), 13)	10 10 2013 10:34:27:573
CONVERT(VARCHAR, GETDATE(), 14)	10:34:48:080
CONVERT(VARCHAR, GETDATE(), 20)	2013-10-10 10:35:15
CONVERT(VARCHAR, GETDATE(), 21)	2013-10-10 10:56:37.400
CONVERT(VARCHAR, GETDATE(), 22)	10/10/13 10:56:53 AM
CONVERT(VARCHAR, GETDATE(), 23)	2013-10-10
CONVERT(VARCHAR, GETDATE(), 10)	2013-10-10 10:58:32.180
CONVERT(VARCHAR, GETDATE(), 101)	10/10/2013
CONVERT(VARCHAR, GETDATE(), 102)	2013.10.10
CONVERT(VARCHAR, GETDATE(), 103)	10/10/2013
CONVERT(VARCHAR, GETDATE(), 104)	10.10.2013
CONVERT(VARCHAR, GETDATE(), 105)	10-10-2013
CONVERT(VARCHAR, GETDATE(), 111)	2013/10/10
CONVERT(VARCHAR, GETDATE(), 112)	20131010
CONVERT(VARCHAR, GETDATE(), 120)	2013-10-10 10:59:28
CONVERT(VARCHAR, GETDATE(), 121)	2013-10-10 10:59:35.057

CASE 표현

-> CASE 표현은 IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완하는 역할을 한다.
ANSI/ISO SQL 표준에는 CASE Expression이라고 표시되어 있는데，함수와 같은 성격을 가지고 있으며 Oracle 의 Decode 함수와 같은 기능을 수행

CASE Expressions
-> 다음의 두 가지 표현법 중에 하나를 선택해서 사용하게 된다.

Simple Case Expression
Searched Case Expression

CASE SIMPLE_CASE_EXPRESSION 조건 or SEARCHED_CASE_EXPRESSION 조건 ELSE 표현절 END

첫 번째 SIMPLE_CASE_EXPRESSION은 CASE 다음에 바로 조건에 사용되는 칼럼이나 표현식을 표시하고, 다음 WHEN 절에서 앞에서 정의한 칼럼이나 표현식과 같은지 아닌지 판단하는 문장으로 EQUI(=) 조건만 사용한다면 SEARCHED_CASE_EXPRESSION보다 간단하게 사용할 수 있는 장점이 있다. Oracle의 DECODE 함수와 기능면에서 동일하다.

Simple Case Expression 예

Case	CASE LOC WHEN 'NEW YORK' THEN 'EAST' ELSE 'ETC' END as AREA

Decode 예

Decode

DECODE(LOC , 'NEW YORK', 'EAST', 'ETC')

Searched Case Expression 예

Case	CASE WHEN SAL > = 3000 ELSE 'LOW' END AS SALARY

종류

NULL 관련 함수

NVL/ISNULL 함수

널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. 0은 숫자이고, 공백은 하나의 문자이다.
테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함할 수 있다.
널 값을 포함하는 연산의 경우 결과 값도 널 값이다. 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는 데이터인 것과 같다.
결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0(Zero)으로, 문자 유형 데이터인 경우는 블랭크보다는 'x' 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다.

Null 연산의 결과

-> Null 연산시에 NVL/ISNULL 함수를 이용하여 변환하여 연산

단일행 Null 관련 함수의 종류

Null 함수 (Oracle vs Mssql)

Oracle	NVL (NULL 판단 대상,'NULL일 때 대체값')	NVL('Not-Null','NVL-OK')
Mssql	ISNULL (NULL 판단 대상,'NULL일 때 대체값')	ISNULL(NULL, 'NVL-OK')

-> 유사하게 Case문으로 표현가능

{tip} NVL 함수를 다중행 함수의 인자로 사용하는 경우는 오히려 불필요한 부하를 발생할 수 있으므로 굳이 NVL 함수를 사용할 필요가 없다. {tip}
다중행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서 일부만 NULL인 경우는 다중행 함수의 대상에서 제외한다.
ex) 100명 중 10명의 성적이 NULL 값일 때 평균을 구하는 다중행 함수 AVG를 사용하면 NULL 값이 아닌 90명의 성적에 대해서 평균값을 구하게 된다.

NULL과 공집합

일반적인 사용 예

SELECT NVL(MGR,9999) MGR FROM EMP WHERE ENAME='KING';

result = 9999

NVL 함수로 NULL을 0으로 변경

공집합의 NVL/ISNULL 사용 예

SELECT MAX(MGR) MGR FROM EMP WHERE ENAME='JSC';

result =

값이 없으나 조회된 data는 NULL (집계함수와 Scalar Subquery의 경우 NULL 출력)

NULLIF 예

NULLIF (EXPRl, EXPR2)

NULLIF(MGR,7698)

MGR = 7698이면 NULL 표시 그렇지 않으면 MGR을 표시

기타 NULL 관련 함수(COALESCE) 예

COALESCE (EXPR1, EXPR2, ...)

COALESCE(COMM, SAL)

COMM이 NULL 이면 SAL를 표기

-> 중첩 case 문으로 변경가능

CASE WHEN COMM IS NOT NULL THEN COMM ELSE (CA8E WHEN SAL IS NOT NULL THEN SAL ELSE NULL END ) END

문서에 대하여

이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 한국 데이터베이스 진흥원에서 출간한 'SQL 전문가 가이드'를 참고하였습니다.*

6.함수(FUNCTION) (by alstjs1008) [2013.10.10]

함수