[SQL] 함수

May 18, 2024

아래의 명령어들은 모두 Oracle 의 SQL 명령어이다. (SQL Server 명령어의 경우 따로 표시)

👑 SQL 함수

각 DBMS 마다 기본적으로 SQL 내장 함수를 제공한다. 내장 함수를 이용하면 데이터 값을

조작하고 조회하는 데 수월하다. 벤더 별로 이름이나 표현법은 다를 수 있지만 대부분 비슷한

기능들을 공통으로 제공하고 있다.

내장 함수는 입력 값이 단일행인 단일행 함수 (Single-Row Function)과 입력 값이 여러 행인

다중행 함수 (Multi-Row Function)로 나눌 수 있으며,

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

함수는 입력 값에 상관없이 출력은 하나만 된다라는 특징을 가진다. (M : 1 관계)

함수명 (칼럼 or 표현식 [, Arg1, Arg2, ...])

👑 단일행 함수

SELECT, WHERE, ORDER BY 절에 사용이 가능하다.
각 튜플들에 대해 개별적으로 작용함. 각각의 튜플에 대한 조작 결과를 반환한다.
인자가 여러개 일지라도 단 하나의 결과만 반환한다.

다음으로 주어지는 함수들은 Oracle 함수 / SQL Server 함수 의 형태로 주어진다.

💡 문자형 함수

문자 데이터를 매개 변수로 받아 문자나 숫자 결과를 반환하는 함수

LOWER(str) : 알파벳 문자를 소문자로 변환
UPPER(str) : 알파벳 문자를 대문자로 변환
ASCII(c) : 문자 or 숫자를 ASCII 코드 번호로 변환
CHR / CHAR(ASCII NUM) : ASCII 코드 번호를 문자 or 숫자로 변환
CONCAT(str1, str2) : str1, str2 를 연결 (||, + 와 동일함)
SUBSTR / SUBSTRING(str, m, n) : m 위치에서 n개의 문자 길이의 문자를 반환
LENGTH / LEN(str) : 문자열의 개수를 반환
LTRIM(str, 지정 문자) : 문자열의 첫 문자부터 지정 문자가 나타나면 해당 문자 제거
(지정 문자 생략 시 default = 공백, SQL Server 에서는 지정문자 사용 불가)
RTRIM(str, 지정문자) : 문자열의 마지막 문자부터 지정 문자가 나타나면 해당 문자 제거
(LTRIM 과 동일 조건)
TRIM(머리말, 꼬리말, 양쪽) : 문자열에서 머리말, 꼬리말, 또는 양쪽의 지정 문자 제거
(SQL Server 의 경우 지정문자 사용 불가)

💡 DUAL 테이블

Oracle DB에서 사용하는 특수한 테이블로 모든 사용자가 접근 가능한 테이블이다.

Oracle 에서는 SELECT를 수행하기 위해 FROM 절을 필수적으로 사용해야 한다.

따라서 사용자 테이블이 필요하지 않은 SQL 문의 경우에도 필수적으로 테이블이 존재해야만 하는데

이를 위해 사용하는 일종의 DUMMY 테이블이 바로 DUAL 테이블이다.

DUAL 테이블은 DUMMY 라는 VARCHAR2 유형의 칼럼에 X 라는 값이 들어 있는 단일 행, 단일 열

테이블이다. 반면 SQL Server의 경우 SELECT 만으로도 SQL 문장이 수행 가능해 DUAL 테이블이

필요 없다.

-- e.g.

SELECT SYSDATE FROM DUAL;

SELECT 'Hello' || 'World!' FROM DUAL;

SELECT LEN('HELLO') FROM DUAL;

💡 숫자형 함수

숫자를 입력받아 처리한 후 숫자를 반환하는 함수

ABS(n) : 절대값을 반환하는 함수
SIGN(n) : 수가 양수이면 1, 0이면 0, 음수이면 -1 을 반환
MOD(n1, n2) : n1 % n2 를 리턴 (% 연산자와 동일)
CEIL / CEILING(n) : 숫자보다 크거나 같은 최소 정수를 반환
FLOOR(n) : 숫자보다 작거나 같은 최대 정수를 반환
ROUND(n, m) : n을 소수점 m + 1 자리에서 반올림하여 반환 (m의 default는 0)
TRUNC(n, m) : n을 소수 m + 1 자리에서 잘라 버림 (SQL Server 에서 제공 X)
SIN, COS, TAN, ... : 삼각함수 값을 반환
EXP(), POWER(), SQRT(), LOG(), LN() : 지수, 거듭 제곱, 제곱근, 자연 로그 값을 반환

💡 날짜형 함수

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

SYSDATE / GETDATE() : 현재 날짜와 시각을 출력
EXTRACT(YEAR or MONTH or DAY from d) / DATEPART(YEAR or MONTH or DAY, d)
: 날짜 정보에서 년/월/일 데이터를 출력
TO_NUMBER(TO_CHAR(d, 'YYYY')) / YEAR(d) : EXTRACT / DATEPART 와 같은 기능
TO_NUMBER(TO_CHAR(d, 'MM')) / MONTH(d), TO_NUMBER(TO_CHAR(d, 'DD')) / DAY(d)

-- 아래의 SQL 문장은 모두 같은 기능을 한다.

-- Oracle
SELECT BIRTHDAY,
       EXTRACT(YEAR FROM BIRTHDAY) 년,
       EXTRACT(MONTH FROM BIRTHDAY) 월,
       EXTRACT(DAY FROM BIRTHDAY) 일
FROM STUDENT;

-- Oracle
SELECT BIRTHDAY,
       TO_NUMBER(TO_CHAR(BIRTHDAY, 'YYYY')) 년,
       TO_NUMBER(TO_CHAR(BIRTHDAY, 'MM')) 월,
       TO_NUMBER(TO_CHAR(BIRTHDAY, 'DD')) 일
FROM STUDENT;

-- SQL Server
SELECT BIRTHDAY,
       YEAR(BIRTHDAY) 년,
       MONTH(BIRTHDAY) 월.
       DAY(BIRTHDAY) 일
FROM STUDENT;

💡 변환형 함수

특정 자료형을 변환할 경우 사용되는 함수이다.

명시적 자료형 변환 : 데이터 변환형 함수로 자료형을 변환하도록 명시하는 경우
암시적 자료형 변환 : DB가 자동으로 자료형을 변환하여 계산하는 경우

암시적 자료형 변환의 경우 성능 저하가 발생할 수 있고, 에러를 발생시킬 가능성이 있어

명시적 변환 방법을 사용하여 변환해주는 것이 바람직하다.

Oracle

TO_NUMBER(str) : 영문 숫자 문자열을 숫자로 변환
TO_CHAR(num or date, format) : 숫자나 날짜를 주어진 포맷 형태로 문자열 타입으로 변환
TO_DATE(str, format) : 문자열을 주어진 포맷 형태로 날짜 타입으로 변환

SQL Server

CAST(expr AS data_type) : expr을 목표 데이터 타입으로 변환
CONVERT(data_type [ ( length ) ], expression [, style ] )
: expr을 목표 데이터 타입으로 변환 (스타일 코드가 존재)

-- Oracle
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
       TO_CHAR(SYSDATE, 'YYYY. MON, DAY')
FROM DUAL;

-- 다양한 형식 요소들이 존재 ('MON' 은 축약된 월 이름, 'DAY' 는 축약되지 않은 요일 이름을 제공)

-- SQL Server
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS NOWDATE

-- 스타일 코드 111 → YYYY/MM/DD 형식으로 변환

💡 CASE Expression

IF-THEN-ELSE 구문과 유사한 방식의 SQL의 비교 연산 기능을 제공하는 expression 이다.

Simple Case Expression : WHEN 절 앞에 정의한 칼럼 혹은 표현식과 같은지를 판단하는 문장
Searched Case Expression : 여러 조건 (>, >= 등)을 이용한 조건절 사용 가능 (다양한 조건)

-- Simple Case Expression
-- SIMPLE_CASE_EXPRESSION 조건이 맞으면 조건 내의 THEN 절 수행, 아니면 ELSE 절 수행

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

------------------------------------------------------------------------

-- Searched Case Expression
-- SEARCHED_CASE_EXPRESSION 조건이 맞으면 조건 내의 THEN 절 수행, 아니면 ELSE 절 수행

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

------------------------------------------------------------------------

-- DECODE (Oracle 에서만 제공하는 함수)
-- 표현식이 특정 값과 일치할 때 특정 결과를 반환 (SIMPLE_CASE_EXPRESSION 조건과 동일)

DECODE(expr,
       search1, result1,
       search2, result2,
       ...,
       default_result)

💡 NULL 관련 함수

NULL 값을 포함하는 연산의 결과 역시 NULL 이다.

NVL(expr1, expr2) / ISNULL(expr1, expr2)
- expr1의 결과값이 NULL 이면 expr2의 값 출력
- 산술 계산에서 데이터 값이 NULL 일 경우 유용하게 사용된다.
  
  NULL 값이 존재 시 결과 또한 NULL이 되므로 0으로 변환이 필요
- 하지만, 다중행 함수는 NULL인 경우를 제외하고 계산하므로 오히려 Overhead를 발생시킴
  
  NVL 함수 사용 시 주의해야 함
```
SELECT PLAYER_NAME, PLAYER_SALARY, BONUS,
       (PLAYER_SALARY * 12) + NVL(BONUS, 0) 연봉
FROM PLAYER;

-- BONUS 가 NULL 이라면 0으로 바꿔서 계산
```

NULLIF(expr1, expr2)

expr1이 expr2와 같으면 NULL을, 같지 않으면 expr1을 리턴

SELECT PLAYER_NAME, PLAYER_SALARY, BONUS,
       NULLIF(BONUS, PLAYER_SALARY * 0.1) AS FINAL_BONUS
FROM PLAYER;

-- 보너스를 급여의 10% 라고 한다면, 이미 받은 직원의 보너스를 NULL로 설정

COALESCE(expr1, expr2, ⋯, exprN)

임의의 개수의 expr에서 NULL이 아닌 최초의 expr을 리턴
(모든 expr이 NULL 이라면 NULL을 리턴)
컬럼이나 표현식 중 NULL 이 아닌 값을 찾을 때 매우 유용하다.

SELECT
      EMPLOYEE_ID,
      COALESCE(PERSONAL_EMAIL, COMPANY_EMAIL, TEMP_EMAIL) AS PREFERRED_EMAIL
FROM EMPLOYEE;

-- 가장 먼저 PERSONAL_EMAIL을 확인하고, NULL 인 경우 COMPANY_EMAIL 과 TEMP_EMAIL을 차례로 확인
-- 여러 컬럼 중 사용 가능한 첫 번째 유효한 값을 찾는 데 유용하다.

SELECT
      PLAYER_NAME,
      COALESCE(POSITION, 'Unknown') AS POSITION
FROM PLAYER;

-- 선수의 포지션이 NULL인 경우 `Unknown'을 반환
-- 특정 컬럼의 `NULL' 값을 적절한 값으로 변환 가능

[SQL] 함수

👑 SQL 함수

👑 단일행 함수

💡 문자형 함수

💡 DUAL 테이블

💡 숫자형 함수

💡 날짜형 함수

💡 변환형 함수

💡 CASE Expression

💡 NULL 관련 함수

Leave a comment

You may also enjoy

[C++] 백준 10026 - 적록색약

[C++] 백준 16933 - 벽 부수고 이동하기 3

[용어 정리] Annotation이란?

[용어 정리] MVC (Model-View-Controller) 패턴이란?