[SQLD 2-1-3] SQL 기본

데이터베이스(DB)/SQLD

[SQLD 2-1-3] SQL 기본 - 함수

송기동 2024. 5. 16. 18:08

728x90

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

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

내장 함수는 다시 함수의 입력 값이 단일행 값이 입력되는 단일행 함수와 여러 행의 값이 입력되는 다중행 함수로 나눌 수 있다.

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

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

# 단일행 함수의 특징

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

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

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

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

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

2. 문자형 함수

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

3. 숫자형 함수

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

4. 날짜형 함수

- DATE 타입의 값을 연산하는 함수

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

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

5. 변환형 함수

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

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

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

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

6. 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 관련 함수

(NULL과 관련해서는 지난 포스팅에서 다룬적 있기 때문에 참고하시 좋을 것 같습니다!)

[SQLD 1-2-4] 데이터 모델과 SQL - Null 속성의 이해

위의 주문모델을 보면 주문금액과 주문최소금액이 Null 허용인 것을 알 수 있다.Null 값을 가질 수 있는 속성은 몇가지 특성이 존재한다. 1. Null 값의 연산은 언제나 Null이다.Null값은 공백이나 숫자

thdrlehd.tistory.com

# NVL/ISNULL 함수

- NULL 값은 아직 정의되지 않은 값으로 0또는 공백과 다르다. 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 일 때 대체값)

# NULL 과 공집합

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

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

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

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

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

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

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

SELECT NVL(MAX(MGR), 7777) MGR FROM EMP WHERE ENAME = 'ABC';

# NULLIF

NULLIF (EXPR1, EXPR2)

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

# 기타 NULL 관련 함수 (COALESCE)

COALESCE (EXPR1, EXPR2, ...)

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

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

출저 : SQL 전문가 가이드

728x90