1. 내장 함수 개요
- 함수 (Function)은 다양한 기준으로 분류할 수 있는데, 벤더에서 제공하는 함수인 내장 함수 (Built-in Function)와 사용자가 정의할 수 있는 함수 (User Defined Function)로 나눌 수 있음
- 내장 함수는 SQL을 더욱 강력하게 해 주고 데이터 값을 간편하게 조작하는 데 사용됨
- 벤더별로 가장 큰 차이를 보이는 부분이지만, 핵심적인 기능들은 이름이나 표현법이 다르더라도 대부분의 데이터베이스가 공통적으로 제공함
- 내장 함수는 다시 함수 입력 값이 단일행 값이 입력되는 단일행 함수 (Single-Row Function)와 여러 행의 값이 입력되는 다중행 함수 (Multi-Row Function)로 나눌 수 있음
- 다중행 함수는 다시 집계 함수 (Aggregate Function), 그룹 함수 (Group Function), 그리고 윈도우 함수 (Window Function)로 나눌 수 있음
- 함수는 입력된 값이 아무리 많아도 출력은 하나만 되는 M:1 관계라는 중요한 특징을 갖고 있음
- 단일행 함수의 경우 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 표현될 수 있으며
- 다중행 함수의 경우 여러 레코드의 값들을 입력 인수로 사용
함수명 (칼럼이나 표현식 [, Arg1, Arg2, ... ])
- 단일행 함수는 처리하는 데이터의 형식에 따라서 문자형, 숫자형, 날짜형, 변환형, NULL 관련 함수로 나눌 수 있음
- 단일행 함수의 중요한 특징은 다음과 같음
- SELECT, WHERE, ORDER BY 절에 사용 가능
- 각 행들에 대해 개별적으로 작용해 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 반환함
- 여러 인자 (Argument)를 입력해도 단 하나의 결과만 반환함
- 함수의 인자로 상수, 변수, 그리고 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수 있음
- 특별한 경우가 아니면 함수의 인자로 함수를 사용하는 함수의 중첩이 가능함
2. 문자형 함수
- 문자형 함수는 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값의 결과를 돌려주는 함수
- Oracle은 SELECT 절과 FROM 절 두 개의 절을 SELECT 문장의 필수 절로 지정했으므로, 사용자 테이블이 필요 없는 SQL 문장의 경우에도 필수적으로 DUAL이라는 테이블을 FROM 절에 지정
- DUAL 테이블의 특성은 다음과 같음
- 사용자 SYS가 소유하며 모든 사용자가 접근 가능한 테이블
- SELECT ~ FROM ~의 형식을 갖추기 위한 일종의 DUMMY 테이블
- DUMMY라는 문자열 유형의 칼럼에 'X'라는 값이 들어 있는 행 1건을 포함하고 있음
- 반면, Sybase나 SQL Server는 SELECT 절만으로도 SQL 문장이 수행 가능하도록 정의했기 때문에 DUAL이란 DUMMY 테이블이 필요 없음
- 그러나 Sybase나 SQL Server에서 사용자 테이블의 칼럼을 사용할 때는 FROM 절을 필수적으로 적용해야 함
3. 숫자형 함수
- 숫자형 함수는 숫자 데이터를 입력받아 처리하고 숫자를 반환하는 함수
4. 날짜형 함수
- 날짜형 함수는 DATE 타입의 값을 연산하는 함수
- 데이터베이스는 날짜를 저장할 때 내부적으로 세기 (Century), 연 (Year), 월 (Month), 일 (Day), 시 (Hours), 분 (Minutes), 초 (Seconds)와 같은 숫자 형식으로 변환해 저장함
- 날짜는 여러 가지 형식으로 출력되고 날짜 게산에도 사용되기 때문에 편리성을 위해 숫자형으로 젖아
- 데이터베이스는 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능함
5. 변환형 함수
- 변환형 함수는 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우 사용
- 변환형 함수는 크게 두 가지 방식으로 구분 가능
- 명시적 (Explicit) 데이터 유형 변환: 데이터 변환형 함수를 사용해 데이터 유형을 변환하도록 명시해 주는 케이스
- 암시적 (Implicit) 데이터 유형 변환: DB가 자동으로 데이터 유형을 변환해 계산하는 케이스
- 자동으로 DB가 알아서 계산하지 않는 경우가 있어 에러를 발생시킬 수 있기 때문에 암시적 데이터 유형 변환의 경우 성능 저하가 발생할 수 있음
- 명시적인 데이터 유형 변환 방법을 사용하는 것을 권장
6. CASE 표현
- CASE 표현은 IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해 SQL의 비교 연산 기능을 보완하는 역할
- ANSI/ISO SQL 표준에는 CASE Expression이라고 표시돼 있는데, 함수와 같은 성격을 갖고 있으며 Oracle의 Decode 함수와 같은 기능을 하므로 단일행 내장 함수에서 같이 설명함
7. NULL 관련 함수
가. NVL/ISNULL 함수
- NULL에 대한 특성을 정리하면 다음과 같음
- 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다름 (0은 숫자고, 공백은 하나의 문자)
- 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 NULL 값을 포함할 수 있음
- NULL 값을 포함하는 연산의 경우 결과 값도 NULL 값, 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모른 데이터인 것과 같음
- 결과 값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용 (NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0으로, 문자 유형 데이터인 경우는 블랭크보다는 'x' 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많음)
- NVL/ISNULL 함수를 유용하게 사용하는 예는 산술적인 계산에서 데이터 값이 NULL일 경우
- 칼럼 간 계싼을 수행할 때 NULL 값이 존재하면 해당 연산 결과가 NULL 값이 되므로 원하는 결과를 얻을 수 없는 경우가 발생
- 이런 경우는 NVL 함수를 사용해 숫자인 0으로 변환을 시킨 후 계산을 해서 원하는 데이터를 얻음
- 단, 다중행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고, 전체 건수 중에서 일부만 NULL인 경우는 다중행 함수의 대상에서 제외되는데 이때 NVL 함수를 인자로 사용하는 경우 오히려 불필요한 부하를 발생시킬 수 있음
- 관계형 데이터베이스의 중요한 데이터인 NULL을 처리하는 주요 함수는 다음과 같음
나. NULL과 공집합
- 공집합은 NULL 데이터와는 또 다르게 이해해야 하며 조건에 맞는 데이터가 한 건도 없는 경우를 공집합이라고 함
- 많은 경우 공집합을 NVL/ISNULL 함수를 이용해 처리하려고 하는데, 인수의 값이 공집합인 경우는 NVL/ISNULL 함수를 사용해도 역시 공집합이 출력됨
- NVL/ISNULL 함수는 NULL 값을 대상으로 다른 값으로 바꾸는 함수이지 공집합을 대상으로 하지 않음
- 단, 집계함수와 Scalar Subquery의 경우는 인수의 결과 값이 공집합인 경우에도 NULL을 출력하기 때문에 NVL/ISNULL 함수를 사용하면 원하는 결과를 얻을 수 있음
다. NULLIF
- NULLIF 함수는 EXPR1이 EXPR2와 같으면 NULL을, 같지 않으면 EXPR1을 반환함
- 특정 값을 NULL로 대체하는 경우에 유용하게 사용할 수 있음
- 둘 중 하나가 이미 NULL일 경우 NULL을 반환
라. 기타 NULL 관련 함수 (COALESCE)
- COALESCE 함수는 인수의 숫자가 한정돼 있지 않으며, 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 나타냄
- 만일 모든 EXPR이 NULL이라면 NULL을 반환
참고
SQL 전문가 가이드 2020 개정판 - 한국데이터산업진흥원
'DB > SQL 전문가 가이드' 카테고리의 다른 글
| [과목 II 1장 5절] GROUP BY, HAVING 절 (0) | 2025.03.23 |
|---|---|
| [과목 II 1장 4절] WHERE 절 (0) | 2025.03.23 |
| [과목 II 1장 2절] SELECT 문 (0) | 2025.03.22 |
| [과목 II 1장 1절] 관계형 데이터베이스 개요 (0) | 2025.03.22 |
| [과목 I 2장 5절] 본질식별자 vs. 인조식별자 (0) | 2025.03.17 |