SQLD - SQL 기본 [함수]

1.   내장 함수(BUILT-IN FUNCTION) 개요

-       내장 함수는 SQL을 더욱 강력하게 해주고 데이터 값을 간편하게 조작하는데 사용된다.

-       내장 함수는 벤더별로 가장 큰 차이를 보이는 부분이지만, 핵심적인 기능들은 이름이나 표현법이 다르더라도 대부분의 데이터베이스가 공통적으로 제공하고 있다.

-       내장 함수는 다시 함수의 입력 값이 단일행 값이 되는 경우, 단일행 함수(Single-Row Function)와 여러 행의 값이 입력되는 다중행 함수(Multi-Row Fuction)로 나눌 수 있다.

-       함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1 관계라는 중요한 특징을 가지고 있다.

-       단일행 함수의 경우 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 표현될 수 있다.

-       다중행 함수의 경우도 여러 레코드의 값들을 입력 인수로 사용하는 것이다.

종류	내용	함수의 예
문자형 함수	문자를 입력하면 문자나 숫자 값을 반환한다.	LOWER, UPPER,SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII
숫자형 함수	숫자를 입력하면 숫자 값을 반환한다.	ABS, MOD, ROUND, TRUNC, SIGN, CHR/CHAR, CEIL/CEILING, FLOOR, EXP, LOG, LN,POWER, SIN, COS, TAN
날짜형 함수	DATE 타입의 값을 연산한다.	SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(d, ’YYYY’|’MM’|’DD’))/ YEAR|MONTH/DAY
변환형 함수	문자, 숫자, 날짜형 값의 데이터 타입을 변환한다.	TO_NUMBER, TO_CHAR, TO_DATE/CAST, CONVERT
NULL 관련 함수	NULL을 처리하기 위한 함수	NVL/ISNULL, NULLIF, COALESCE

l  Oracle함수/SQL Server 함수 표시, ‘/’ 없는 것은 공통 함수

 

2.   문자형 함수

문자형 함수	함수 설명
LOWER(문자열)	문자열의 알파벳 문자를 소문자로 바꾸어 준다.
UPPER(문자열)	문자열의 알파벳 문자를 대문자로 바꾸어 준다.
ASCII(문자열)	문자나 숫자를 ASCII 코드 번호로 바꾸어 준다.
CHR/CHAR(ASCII번호)	ASCII 코드 번호를 문자나 숫자로 바꾸어 준다.
CONCAT (문자열1, 문자열2)	Oracle, My SQL에서 유효한 함수이며 문자열1과 문자열2를 연결한다. 합성연산자 ‘||’(Oracle)나 ‘+’(SQL Server)와 동일하다.
SUBSTR/SUBSTRING (문자열, m[, n])	문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자를 반환한다. N이 생략되면 마지막 문자까지이다.
LENGTH/LEN(문자열)	문자열의 개수를 숫자값으로 반환한다.
LTRIM (문자열 [, 지정문자])	문자열의 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자를 제거한다. (지정 문자가 생략되면 공백 값이 Default) SQL Server에서는 LTRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
RTRIM (문자열 [, 지정문자])	문자열의 마지막 확인해서 지정 문자가 나타나면 해당 문자를 제거한다. (지정 문자가 생략되면 공백 값이 Default) SQL Server에서는 RTRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
TRIM ([leading | trailing | both] 지정문자 FROM 문자열)	문자열에서 머리말, 꼬리말 또는 양쪽에 있는 지정문자를 제거한다. (leading | trailing | both 가 생략되면 both가 Default) SQL Server에서는 TRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.

-       DUAL 테이블이란?

n  DUAL 테이블은 데이터 딕셔너리와 함께 Oracle에 의해 자동으로 생성되는 테이블이다.

n  DUAL 테이블은 사용자 SYS의 스키마에 있지만 모든 사용자는 DUAL이라는 이름으로 엑세스할 수 있다.

n  DUAL 테이블은 VARCHAR2(1)으로 정의된 DUMMY라는 하나의 열이 있으며 값을 가지는 하나의 행도 포함되어 있다.

n  DUAL 테이블은 사용자가 계산이나 사용자 함수 등을 실행하고자 할 경우에 유용하다.

-       DUAL 테이블의 특성

n  사용자 SYS가 소유하며 모든 사용자가 엑세스 가능한 테이블이다.

n  SELECT ~ FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블이다.

n  DUMMY라는 문자열 유형의 칼럼에 ‘X’라는 값이 들어있는 행을 1건 포함하고 있다.

 

l  특별한 제약조건이 없다면 함수는 여러 개 중첩하여 사용이 가능하다. 함수 내부에 다른 함수를 사용하며 안쪽에 위치해 있는 함수부터 실행되어 그 결과 값이 바깥쪽의 함수에 인자(Argument)로 사용되는 것이다.
함수3 (함수2 (함수1 (칼럼이나 표현식, Arg1), Arg2), Arg3)

 

3.   숫자형 함수

숫자형 함수	함수 설명
ABS(숫자)	숫자의 절대값을 반환한다.
SIGN(숫자)	숫자가 양수인지, 음수인지 0인지를 구별한다.
MOD(숫자1, 숫자2)	숫자1을 숫자2로 나누어 나머지 값을 반환한다. MOD 함수는 % 연산자로도 대체 가능하다.
CEIL/CEILING(숫자)	숫자보다 크거나 같은 최소 정수를 반환한다.
FLOOR(숫자)	숫자보다 작거나 같은 최대 정수를 반환한다.
ROUND(숫자 [, m])	숫자를 소수점 m자리에서 반올림하여 반환한다. m이 생략되면 Default 값은 0이다.
TRUNC(숫자 [, m])	숫자를 소수 m자리에서 잘라서 버린다. m이 생략되면 Default 값은 0이다. SQL Server에서 TRUNC 함수는 제공되지 않는다.
SIN, COS, TAN, …	숫자의 삼각함수 값을 반환한다.
EXP(), POWER(), SQRT(), LOG(),LN()	숫자의 지수, 거듭 제곱, 제곱근, 자연 로그 값을 반환한다.

 

4.   날짜형 함수

날짜형 함수	함수 설명
SYSDATE / GETDATE()	현재 날짜와 시각을 출력한다.
EXTRACT(‘YEAR’ | ‘MONTH’ | ‘DAY’ from d) / DATEPART(‘YEAR’ | ‘MONTH’ | ‘DAY’, d)	날짜 데이터에서 년/월/일 데이터를 출력할 수 있다. 시간/분/초도 가능함
TO_NUMBER(TO_CHAR(d,’YYYY’)) / YEAR(d) TO_NUMBER(TO_CHAR(d,’MM)) / MONTH(d) TO_NUMBER(TO_CHAR(d,’DD)) / DAY(d)	날짜 데이터에서 년/월/일 데이터를 출력할 수있다. Oracle EXTRACT YEAR/MONTH/DAY 옵션이나 SQL Server DEPART YEAR/MONTH/DAY 옵션과 같은 기능이다. TO_NUMBER 함수 제외시 문자형으로 출력된다.

 

-       데이터베이스는 날짜를 저장할 때 내부적으로 세기(Century), 년(Year), 월(Month), 일(Day), 시(Hours), 분(Minutes), 초(Seconds)와 같은 숫자 형식으로 변환하여 저장한다.

-       데이터베이스는 날짜를 숫자로 저장하기 때문에 산술연산자로도 계산이 가능하다.

연산	결과	설명
날짜 + 숫자	날짜	숫자만큼의 날수를 날짜에 더한다.
날짜 – 숫자	날짜	숫자만큼의 날수를 날짜에서 뺀다.
날짜1 – 날짜2	날짜수	다른 하나의 날짜에서 하나의 날짜를 빼면 일수가 나온다.
날짜 + 숫자/24	날짜	시간을 날짜에 더한다.

 

5.   변환형 함수

종류	설명
명시적(Explicit) 데이터 유형 변환	데이터 변환형 함수로 데이터 유형을 변환하도록 명시해 주는 경우
암시적(Implicit) 데이터 유형 변환	데이터베이스가 자동으로 데이터 유형을 변환하여 계산하는 경우

-       암시적 데이터 유형 변환의 경우 성능 저하가 발생할 수 있으며, 자동적으로 데이터베이스가 알아서 계산하지 않는 경우가 있어 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환 방법을 사용하는 것이 바람직하다.

변환형 함수 – Oracle	함수 설명
TO_NUMBER(문자열)	Alphanumeric 문자열을 숫자로 변환한다.
TO_CHAR(숫자 | 날짜 [, FORMAT])	숫자나 날짜를 주어진 FORMAT 형태로 문자열 타입을 변환한다.
TO_DATE(문자열 [, FORMAT])	문자열을 주어진 FORMAT 형태로 날짜 타입으로 변환한다.

 

변환형 함수 – SQL Server	함수 설명
CAST(expressiong AS data_type [(length)])	expression을 목표 데이터 유형으로 변환한다.
CONVERT(data_type [(length)], Expressiong [, style] )	expression을 목표 데이터 유형으로 변환한다.

 

 

6.   CASE 표현

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

-       CASE 표현을 하기 위해서는 조건절을 표현하는 두 가지 방법이 있고,
Oracle의 경우 DECODE 함수를 사용할 수도 있다.

CASE 표현	함수 설명
CASE SIMPLE_CASE_EXPRESSION 조건 ELSE 표현절 END	SIMPLE_CASE_EXPRESSION 조건이 맞으면 SIMPLE_CASE_EXPRESSION 조건내의 THEN 절을 수행하고, 조건이 맞지 않으면 ELSE절을 수행한다.
CASE SEARCHED_CASE_EXPRESSION 조건 ELSE 표현절 END	SEARCHED_CASE_EXPRESSION 조건이 맞으면 SEARCHED_CASE_EXPRESSION 조건내의 THEN 절을 수행하고, 조건이 맞지 않으면 ELSE절을 수행한다.
DECODE(표현식, 기준값1, 값1 [, 기준값2, 값2, … , 디폴트값)	Oracle에서만 사용되는 함수로, 표현식의 값이 기준값1 이면 값1을 출력하고, 기준값2이면 값2를 출력한다. 그리고 기준값이 없으면 디폴트 값을 출력한다. CASE표현의 SIMPLE_CASE_EXPRESSION 조건과 동일하다.

 

7.   NULL 관련 함수

-       NULL에 대한 특성

n  NULL 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다.
0은 숫자이고, 공백은 하나의 문자이다.

n  테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 NULL 값을 포함할 수 있다.

n  NULL 값을 포함하는 연산의 경우 결과 값도 NULL 값이다.

n  결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.

연산	연산의 결과
NULL + 2, 2 + NULL	NULL
NULL - 2, 2 - NULL	NULL
NULL * 2, 2 * NULL	NULL
NULL / 2, 2 / NULL	NULL

 

가.  NVL/ISNULL 함수

일반형 함수	함수 설명
NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2)	표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다. 단, 표현식1과 표현식2의 결과 데이터 타입이 같아야 한다. NULL관련 가장 많이 사용되는 함수이므로 상당히 중요하다.
NULLIF(표현식1, 표현식2)	표현식1이 표현식2와 같으면 NULL을, 같이 않으면 표현식1을 반환한다.
COALESCE(표현식1, 표현식2, ……)	임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 타나낸다. 모든 표현식이 NULL이라면 NULL을 반환한다.

 

 

나.  NULL과 공집합

-       일반적인 NVL/ISNULL 함수 사용

 

다.  NULLIF

-       NULLIF 함수는 EXPR1이 EXPR2와 같으면 NULL을 같지 않으면 EXPR1을 반환한다.
특정 값을 NULL로 대체하는 경우에 유용하게 사용할 수 있다.

-       NULLIF(EXPR1, EXPR2)

 

라.  기타 NULL 관련 함수(COALESCE)

-       COALESCE 함수는 인수의 숫자가 한정되어 있지 않으며, 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 나타낸다.

-       만일 모든 EXPR이 NULL이라면 NULL을 반환한다.

-       COALESCE(EXPR1, EXPR2, …)