SQL 함수

내장함수(Built-In Function)

 

함수는 다양한 기준으로 분류할 수 있는데,

벤더에서 제공하는 함수인 내장함수(Built-in Function)와
사용자가 정의할 수 있는 함수(User Defined Function)로 나눌 수 있다.

본 절에서는 각 벤더에서 제공하는 데이터베이스를 설치하면 기본적으로 제공되는 SQL 내장 함수에 대해 설명한다.

 

내장함수는 다시 함수의 입력 값이 단일행 값이 입력되는 단일행 함수(Single-Row Function)와

여러 행의 값이 입력되는 다중행 함수(Multi-Row Function)로 나눌 수 있다.

다중행 함수는 다시 집계 함수(Aggregate Function), 그룹 함수(Group Function), 윈도우 함수(Window Function)로 나뉜다.

 

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

단일행 함수의 경우 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 표현될 수 있다.

다중행 함수의 경우도 여러 레코드 값들을 입력 인수로 사용하는 것이다.

 

단일행 함수는 처리하는 데이터의 형식에 따라서 문자형, 숫자형, 날짜형, 변환형, NULL 관련 함수로 나눌 수 있다.

 

단일행 함수의 종류 (Oracle/SQL Server, Common)

종류	내용	함수의 예
문자형 함수	문자를 입력하면 문자나 숫자 값을 반환한다.	LOWER, UPPER, SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII
숫자형 함수	숫자를 입력하면 숫자 값을 반환한다.	ABS, MOD, ROUND, TRUNC, SIGN, CHR/CHAR, CEIL/CEILING, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN
날짜형 함수	DATE 타입의 값을 연산한다.	SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(d,'YYYY'|'MM'|'DD')) / YEAR|MONTH|DAY
변환형 함수	문자, 숫자, 날짜형 값의 데이터 타입을 변환	TO_NUMBER, TO_CHAR, TO_DATE/CAST, CONVERT
NULL 관련 함수	NULL을 처리하기 위한 함수	NVL/ISNULL, NULLIF, COALESCE

 

- 단일행 함수의 중요한 특징은 다음과 같다.

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

---

문자형 함수

 

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

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

-- Oracle

SELECT LENGTH('SQL Expert')
FROM DUAL;

-- Length : 10

 

숫자형 함수

 

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

 

날짜형 함수

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

Oracle의 TO_NUMBER(TO_CHAR()) 함수의 경우 변환형 함수로 구분할 수 있으나

SQL Server의 YEAR, MONTH, DAY 함수와 매핑하기 위하여 날짜형 함수에서 설명한다.

EXTRACT/DATEPART는 같은 기능을 하는 Oracle 내장함수와 SQL Server 내장함수를 표현한 것이다.

 

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

변환하여 저장한다. 날짜는 여러가지 형식으로 출력이 되고 날짜 계산에도 사용되기 때문에 그 편리성을 위해서
숫자형으로 저장하는 것이다. 데이터베이스는 날짜를 숫자로 저장하기 때문에 덧셈, 뺏셈 같은 산술 연산자로도

계산이 가능하다. 즉, 날짜에 숫자 상수를 더하거나 뺄 수 있다.

 

 

• 1일
  SYSDATE + 1
• 1시간
  SYSDATE + 1/24
• 10분
  SYSDATE + 1/24/6
• 1분
  SYSDATE + 1/24/60
• 매일 밤 11시
  TRUNCATE(SYSDATE) + 23/24

 

변환형 함수

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

 

CASE 표현

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

 

NULL 관련 함수

• NVL/ISNULL 함수
• NULL과 공집합
• NULLIF
• 기타 NULL 관련 함수(COALESCE)

---

Summary

 

• 내장함수 (Built-in Function)
  
  • 벤더에서 제공하는 함수인 내장 함수 (Buit-In Function)
  • 사용자가 정의할 수 있는 함수 (User Defined Function)
  • SQL을 더욱 강력하게 해주고 데이터 값을 간편하게 조작하는데 사용
  • 핵심적인 기능들은 이름/표기법이 달라도 대부분의 데이터베이스가 공통적으로 제공
  • 내장함수는 다시 함수의 입력 값에 따라 단일행 함수 / 다중행 함수로 나뉨
  • 함수는 입력값이 아무리 많아도 출력값은 하나라는 M:1 관계라는 중요한 특징을 가짐
  • 단일행 함수: 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 사용
    - 문자형 함수 : LOWER, UPPER, LENGTH, TRIM, CONCAT ...
    - 숫자형 함수: ABS, ROUND, TRUNC, CEIL/CEILING, FLOOR ...
    - 날짜형 함수: SYSDATE/GETDATE ...
    - 변환형 함수: TO_NUMBER, TO_CHAR, TO_DATE...
    - NULL 관련 함수(NULL을 처리하기 위한 함수) : NVL, NULLIF, COASESCE
    
    
    • 추출되는 각 행마다 작업을 수행
    • 각 행마다 하나의 결과를 반환
    • SELECT, WHERE, ORDER BY, UPDATE의 SET 절에 사용가능
    • 데이터 타입 변경 가능
    • 중첩해서 사용 가능
  • 다중행 함수:
    
    • 여러 개의 행이 입력, 하나의 값 반환
    • 그룹(집계) 함수가 다중 행 함수
    • SUM, AVG, MAX, MIN, COUNT 등
      
      
• CASE 표현
  • CASE 표현은 IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성
  • SQL의 비교 연산 기능을 보완하는 역할
  • ANSI/ISO 표준에는 CASE Expression 이라고 표시
  • 함수와 같은 성격을 가지고 있으며 Oracle의 DECODE 함수와 같은 기능
    
    SELECT 칼럼명
                   CASE
                   WHEN 조건
                   THEN 조건이 TRUE일 때 반환
                   ELSE 조건이 FALSE일 때 반환
                   END AS 칼럼명
    FROM 테이블명;
  • "사원 정보에서급여가
    3000 이상이면 상등급으로,
    1000 이상이면 중등급으로,
    1000 미만이면 하등급으로 분류하라."
    
    SELECT ENAME,
    CASE WHEN SAL >= 3000THEN 'HIGH'
               WHEN SAL >= 1000 THEN 'MID'
               ELSE 'LOW'
               END AS SALARY_GRADE
    FROM EMP;
    
    
    
• NULL 관련 함수
  
  1.  NVL / ISNULL 함수
     • 표현식1의 값이 NULL 이면 표현식2 값을 출력한다.
     • 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.
     • NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0(Zero)
     • 문자 유형 데이터인 경우는 Blank(공백) 보다는 'X' 같이 시스템에서 의미없는 문자로 바꾼다.
     • NULL 포함 연산의 결과:
       NULL + 2, NULL - 2, NULL * 2, NULL / 2: NULL에 대한 사칙연산 결과는 모두 NULL 이다.
  2. NULL과 공집합
     
     • SELECT 1 FROM DUAL WHERE 1 = 2; 와 같은 조건이 대표적인 공집합 발생 쿼리이다.
     • 조건에 맞는 데이터가 한 건도 없는 경우를 공집합
     • NULL 데이터와는 또 다르게 이해해야 한다.
  3. NULLIF (표현식1, 표현식2)
     • 표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1 을 리턴
     • "사원 테이블에서 MGR과 7698이 같으면 NULL을 표시하고, 같지않으면 MGR을 표시한다."
       
       SELECT ENAME, EMPNO, MGR,
       NULLIF(MGR, 7698) NUIF
       FROM EMP;
       
       
  4. COALESCE (표현식1, 표현식2 ...)
     • 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식
     • COALESCE 함수는 인수의 숫자가 한정되어있지 않다.
     • 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 나타낸다.
     • 만일 모든 EXP이 NULL이라면 NULL을 리턴
     • "사원 테이블에서
       커미션(COMM)을 1차 선택값으로 ,
       급여(SAL)를 2차 선택값으로 선택하되,
       두 칼럼 모두 NULL인 경우는 NULL로 표시한다."
       
       SELECT ENAME, COMM, SAL,
       COALESCE(COMM, SAL) COAL
       FROM EMP;

---