함수(FUNCTION)

song4·2023년 7월 30일

SQLD - SQL 기본

목록 보기

6/9

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

함수는 다양한 기준으로 분류할 수 있다.
- 내장 함수(Built-in Function)
  - 단일행 함수(Single-Row Function)
  - 다중행 함수(Multi-Row Function)
    - 집계 함수(Aggregate Function)
    - 그룹 함수(Group Function)
    - 윈도우 함수(Window Function)
- 사용자 정의 함수(User Defined Function)

단일행 함수
이 함수들은 각 행마다 따로 적용되며, 각 행에서 단일 결과 값을 생성합니다. 즉, 이들은 각각의 입력 행에 대해 하나의 출력 결과를 반환합니다. 예를 들어, 문자열, 날짜, 수치 등의 데이터를 변환하거나 조작하는 함수들이 여기에 해당합니다. (UPPER(), LOWER(), TRIM(), SUBSTRING() 등)

다중행 함수
함수들은 여러 행에 걸친 데이터를 기반으로 결과를 생성합니다. 즉, 다수의 입력 행에 대해 하나의 출력 결과를 반환합니다. 집계 함수라고도 불리며, COUNT(), SUM(), AVG(), MAX(), MIN() 등이 이에 해당합니다.

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

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

2. 문자형 함수

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

몇몇 문자형 함수의 경우는 결과를 숫자로 리턴하는 함수도 있다.

단일행 문자형 함수의 종류 단일행 문자형 함수 사례

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

3. 숫자형 함수

숫자형 함수는 숫자 데이터를 입력받아 처리하고 숫자를 리턴하는 함수이다.
단일행 숫자형 함수 종류 단일행 숫자형 함수 사례

4. 날짜형 함수

날짜형 함수는 DATE 타입의 값을 연산하는 함수이다.
단일행 날짜형 함수 종류

DATE 변수가 데이터베이스에 어떻게 저장되는지 살펴보면, 데이터베이스는 날짜를 저장할 때 내부적으로 세기(Century), 년(Year), 월(Month), 일(Day), 시(Hours), 분(Minutes), 초(Seconds)와 같은 숫자 형식으로 변환하여 저장한다.

날짜는 여러 가지 형식으로 출력이 되고 날짜 계산에도 사용되기 때문에 그 편리성을 위해서 숫자형으로 저장하는 것이다. 그렇기 때문에 덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능하다.
단일행 날짜형 데이터 연산

5. 변환형 함수

변환형 함수는 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수이다. 변환형 함수는 크게 두 가지 방식이 있다.
데이터 유형 변환의 종류

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

단일행 변환형 함수의 종류

6. CASE 표현

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

단일행 CASE 표현의 종류

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

SEARCHED_CASE_EXPRESSION
CASE 다음에는 칼럼이나 표현식을 표시하지 않고, 다음 WHEN 절에서 EQUI(=) 조건 포함 여러 조건(>, >=, <, <=)을 이용한 조건절을 사용할 수 있기 때문에 SIMPLE_CASE_EXPRESSION보다 훨씬 다양한 조건을 적용할 수 있는 장점이 있다.

CASE 표현은 함수의 성질을 가지고 있으므로 중첩해서 사용할 수 있다.

7. NULL 관련 함수

가. NVL/ISNULL 함수

NULL에 대한 특성
- 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다.
- 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함할 수 있다.
- 널 값을 포함하는 연산의 경우 결과 값도 널 값이다.
- 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.

NULL 포함 연산의 결과

NVL/ISNULL 함수를 유용하게 사용하는 예는 산술적인 계산에서 데이터 값이 NULL일 경우이다.
칼럼 간 계산을 수행하는 경우 NULL 값이 존재하면 해당 연산 결과가 NULL 값이 되므로 원하는 결과를 얻을 수 없는 경우가 발생한다. 이런 경우는 NVL 함수를 사용하여 숫자인 0(Zero)으로 변환을 시킨 후 계산을 해서 원하는 데이터를 얻는다.
관계형 데이터베이스의 중요한 데이터인 NULL을 처리하는 주요 함수는 다음과 같다.
단일행 NULL 관련 함수의 종류