[SQLD] #017 SQL 기본 - 함수

목차

1. 내장 함수 개요

2. 문자형 함수

3. 숫자형 함수

4. 날짜형 함수

5. 변환형 함수

6. CASE 표현

7. NULL 관련 함수

 

 

---

 

 

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

함수는 벤더에서 제공하는 함수인 내장 함수와 사용자가 정의할 수 있는 함수로 나눌 수 있다. 

 

내장 함수는 데이터 값을 간편하게 조작하는데 사용된다. 내장 함수는 다시 함수의 입력 값이 단일행 값이 입력되는 단일행 함수와 여러 행의 값이 입력되는 다중행 함수로 나눌 수 있다. 

다중행 함수는 다시 집계 함수, 그룹 함수, 윈도우 함수로 나눌 수 있다. 

 

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

 

 

단일행 함수의 특징은 아래와 같다. 

- SELECT, WHERE, ORDER BY 절에 사용 가능하다. 

- 각 행들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다. 

- 여러 인자를 입력해도 단 하나의 결과만 리턴한다. 

- 함수의 인자로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있다. 

- 특별한 경우가 아니면 함수의 인자로 함수를 사용하는 함수의 중첩이 가능하다. 

 

 

 

 

 

 

 

 

 

 

 

 

2. 문자형 함수

문자형 함수는 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값의 결과를 돌려주는 함수

 

예제) 'SQL Expert' 라는 문자형 데이터의 길이를 구하는 문자형 함수를 사용한다. 

SELECT LENGTH('SQL Expert') FROM DUAL; LENGTH('SQL Expert')

-- 결과 : 10

위와 같이 사용자 테이블이 필요없는 SQL 문장의 경우에도 필수적으로 DUAL 이라는 테이블을 FROM 절에 지정한다. 

DUAL 테이블의 특성은 다음과 같다. 

 

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

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

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

 

 

 

 

 

 

 

 

 

3. 숫자형 함수

숫자형 함수는 숫자 데이터를 입력받아 처리하고 숫자를 리턴하는 함수이다.

 

 

 

 

 

 

 

 

 

 

 

 

4. 날짜형 함수

날짜형 함수는 DATE 타입의 값을 연산하는 함수이다. 

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

날짜를 숫자 형식으로 저장하기 때문에 뎃셈, 뺄셈 등 산술 연산자로 연산이 가능하다.

 

 

 

 

 

 

 

 

 

 

5. 변환형 함수

변환형 함수는 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수이다. 

변환형 함수는 크게 두 가지 있다. 

 

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

 

명시적 데이터 유형 변환에 사용되는 대표적인 변환형 함수는 다음과 같다.

 

 

 

 

 

 

 

 

 

6. CASE 표현

CASE 표현은 IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완하는 역할을 한다. 

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

 

 

IF-THEN-ELSE 논리를 구현하는 CASE Expressions는 Simple Case Expression과 Searched Case Expression 두 가지 표현법 중에 하나를 선택해서 사용하게 된다. 

CASE 
 SIMPLE_CASE_EXPRESSION or SEARCHED_CASE_EXPRESSION 
 ELSE 표현절 
END

 

-- SEARCHED_CASE_EXPRESSION 문장 사례
SELECT LOC,
    CASE WHEN LOC = 'NEW YORK' THEN 'EAST'
        ELSE 'ETC'
    END as AREA
FROM DEPT;


-- SIMPLE_CASE_EXPRESSION 문장 사례
SELECT LOC,
    CASE LOC WHEN 'NEW YORK' THEN 'EAST'
        ELSE 'ETC'
    END as AREA
FROM DEPT;

 

 

 

 

 

 

 

 

 

 

7. NULL 관련 함수

1. NVL/ISNULL 함수

NULL 의 특성

- NULL 값은 아직 정의되지 않은 값으로 0또는 공백과 다르다. 

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

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

- 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는 데이터인 것과 같다. 

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

 

 

NVL/ISNULL 함수는 산술적인 계산에서 데이터 값이 NULL 일 경우에 유용하게 쓰인다. 

칼럼간 계산을 수행하는 경우 NULL 값이 존재하면 해당 연산 결과가 NULL 값이 되므로 원하는 결과를 얻을 수 없는 경우가 발생한다. 이런 경우는 NVL 함수를 사용하여 0으로 변환시킨 후 계산해서 원하는 데이터를 얻는다. 

 

 

-- Oracle 의 경우
NVL (NULL 판단 대상, NULL 일 때 대체값)

-- SQL Server 의 경우
ISNULL (NULL 판단 대상, NULL 일 때 대체값)

 

 

 

 

 

 

2. NULL 과 공집합

-- 공집합을 발생시키기 위해 사원테이블에 존재하지 않은 'JSC'라는 이름으로 데이터 검색
SELECT MGR FROM EMP WHERE ENAME='JSC';

위의 쿼리문이 대표적인 공집합을 발생시키는 쿼리이며, 위와 같이 조건에 맞는 데이터가 한 건도 없는 경우를 공집합이라고 하고, NULL 데이터와는 다르게 이해해야 한다.

 

많은 사람들이 공집합을 NVL/ISNULL 함수를 이용해서 처리하려고 하는데, 인수의 값이 공집합인 경우는 NVL/ISNULL 함수를 사용해도 역시 공집합이 출력된다. 왜냐하면 공집합은 NULL 값과 다르기 때문이다. 

 

따라서 그럴땐 아래와 같이 NVL 함수 대신 적절한 집계 함수를 찾아서 적용한다. 

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

 

위 쿼리를 적용하면 결과가 빈칸으로 표시되지만 실 데이터는 NULL 이다. 

 

그리고 집계 함수를 인수로 한 NVL/ISNULL 함수를 이용해서 공집합인 경우에도 빈칸이 아닌 9999로 출력하게 한다.

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

 

 

 

 

 

 

 

 

3. NULLIF

NULLIF (EXPR1, EXPR2)

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

 

 

 

 

 

 

 

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

COALESCE (EXPR1, EXPR2, ...)

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

만일 모든 EXPR이 NULL 이라면 NULL을 리턴한다.

 

 

 

 

 

 

 

 

 

 

---

출처
이 글의 내용은 모두 한국데이터베이스진흥원이 출판한 SQL 전문가 가이드 2013 Edition을 기본으로 한다.