2.1.6 함수

내장 함수 개요

• 함수는 벤더에서 제공하는 내장함수와 사용자가 정의하는 함수로 나눌 수 있다.
• 내장함수는 SQL을 더욱 강력하게 해주고 데이터 값을 간편하게 조작하는 데 사용된다.

종류

• 함수의 입력 값이 단일행 값이 입력되는 단일행 함수
  • SELECT, WHERE, UPDATE의 SET, ORDER BY 절에 사용가능
• 여러 행의 값이 입력되는 다중행 함수
  • 집계함수
  • 그룹함수
  • 윈도우 함수
• 함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1 관계
• 단일행 함수: 단일행 내의 하나의 값 또는 여러 값이 인수로 표현될 수 있음
• 다중행 함수: 여러 레코드의 값들을 입력 인수로 사용함
• 함수명 (칼럼이나 표현식 [, Arg1, Arg2, ...])

문자형 함수

• 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값의 결과를 돌려줌
• LOWER : 소문자로 리턴
• UPPER : 대문자로 리턴
• ASCII : 아스키 코드 값 리턴
• CHR / CHAR : 아스키 값 리턴
• CONCAT, || / + : 문자열 연결 값 리턴
• SUBSTR / SUBSTRING : 문자열 일부 값 리턴
• LENGTH / LEN : 문자열 길이 리턴
• LTRIM : 왼쪽 문자 자른 값 리턴
• RTRIM : 오른쪽 문자 자른 값 리턴
• TRIM : 왼쪽, 오른쪽 문자 자른 값 리턴
  
  
• 예제 ) 경기장의 지역번호와 전화번호를 합친 번호의 길이를 구하시오

Oracle

SELECT STADIUM_ID, DDD||TEL as TEL, LENGTH(DDD||TEL) as T_LEN
FROM STADIUM;

SQL Server

SELECT STADIUM_ID, DDD+TEL as TEL, LEN(DDD+TEL) as T_LEN
FROM STADIUM;

숫자형 함수

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

• CEIL / CEILING : 숫자보다 크거나 같은 최소 정수 리턴

• FLOOR : 숫자보다 작거나 같은 최대 정수 리턴

날짜형 함수

• DATE 타입의 값을 연산하는 함수
  
• 세기, 년, 월, 일, 시, 분, 초와 같은 숫자 형식으로 변환하여 저장
  

변환형 함수

• 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 때 사용
• 명시적 데이터 유형 변환 => 바람직
  • 데이터 변환형 함수로 데이터 유형을 반환하도록 명시해 주는 경우
• 암묵적 데이터 유형 변환
  • 데이터베이스가 자동으로 데이터 유형을 변환하여 계산하는 경우
    

1. Simple Case Expression
   

• 예제) 부서 정보에서 부서 위치를 미국의 동부, 중부, 서부로 구분

SELECT LOC,
    CASE LOC
    WHEN 'NEW YORK' THEN 'EAST'
    WHEN 'BOSTON'   THEN 'EAST'
    WHEN 'CHICAGO'  THEN 'CENTER'
    WHEN 'DALLAS'    THEN 'CENTER'
    ELSE 'ETC'
    END as AREA
    FROM DEPT;

• 예시2

SELECT t.id,
CASE t.color WHEN 1 THEN 'Red'
            WHEN 2 THEN 'Blue'
            WHEN 3 THEN 'Green'
            ELSE 'NoColor' END AS Color
FROM @t t

2. Searched Cased Expression

• 예제) 사원정보에서 급여가 3000 이상이면 상등급으로, 1000이상이면 중등급, 1000미만이면 하등급

SELECT ENAME,  
    CASE WHEN SAL >=3000 THEN 'HIGH'  
        WHEN SAL >=1000 THEN 'MID'  
        ELSE 'LOW'
        END    AS    SALARY_GRADE
    FROM EMP;

• 예시2

SELECT t.id,
CASE WHEN t.color = 1 THEN 'Red'
    WHEN t.color = 2 THEN 'Blue'
    WHEN t.color = 3 THEN 'Green'
    ELSE 'NoColor' END AS color
FROM @t t

• CASE 표현은 함수의 성질을 가지고 있어서, 다른 함수에서 중첩해서 사용가능

ELSE (CASE WHEN SAL>=1000
		THEN 500
        ELSE 0
     END)
   END    as    BONUS
FROM EMP;

NULL 관련 함수

1. NVL / ISNULL 함수 (IS NULL과는 다름)

• NULL 특성
• 결과 값이 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용함.
• NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0으로, 문자 유형 데이터인 경우는 블랭크보다는 'x'라는 임의의 의미 없는 문자로 바꿈
  
• NVL/ISNULL 함수를 유용하게 사용하는 예는 산술적인 계산에서 데이터 값이 NULL일 경우일 때 그 빈칸을 채워주는 역할
  
  
  
• 선수 테이블에서 K08 소속 선수의 이름과 포지션을 출력하는데, 포지션이 없는 경우는 '없음'으로 표시한다.

ORACLE

SELECT PLAYER_NAME 선수명, POSITION, NVL(POSITION,'없음') 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08';

SQL SERVER

SELECT PLAYER_NAME 선수명, POSITION, ISNULL(POSITION,'없음') 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08';

2. NULL과 공집합

• 일반적인 NVL / ISNULL 함수 사용
• 공집합의 NVL / ISNULL 함수 사용
• SELECT 1 FROM DUAL WHERE 1 = 2;
• 위와 같이 조건에 맞는 데이터가 한 건도 없는 경우를 공집합이라고 함(NULL 데이터와는 다름)
• 공집합인 경우 NVL / ISNULL 함수를 이용하여 공집합을 다른 값으로 바꾸고자 시도함

SELECT NVL(MGR, 9999) MGR 
FROM EMP 
WHERE ENAME='JSC';

• BUT 공집합 출력됨
• 인수의 값이 공집합인 경우, 이 함수를 사용해도 역시 공집합 출력
• 적절한 집계함수 찾아서 NVL 대신 적용(아래는 MAX 사용함.)

SELECT MAX(MGR), 9999 MGR 
FROM EMP 
WHERE ENAME='JSC';

• 공집합의 경우는 NVL 함수를 사용해도 공집합이 출력되므로, 그룹함수의 NVL 함수를 같이 사용해서 처리
• 아래 예제는 NVL을 함수의 인자로 사용해서 인수의 값이 공집합인 경우에도 9999로 변환한 사례

SELECT NVL(MAX(MGR), 9999) MGR 
FROM EMP 
WHERE ENAME='JSC';

3. NULLIF

• NULLIF 함수는 EXPR1이 EXPR2와 같으면 NULL을, 같지않으면 EXPR1을 리턴

NULLIF (EXPR1,EXPR2)

• 예제) 사원 테이블에서 MGR과 7698이 같으면 NULL을 표시, 같지 않으면 MGR 표시

SELECT ENAME, EMPNO, MGR, NULLIF(MGR, 7698) NUIF
FROM EMP;

4. 기타 NULL 관련 함수 (COALESCE)

• 인수의 숫자가 한정되어 있지 않으며, 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 나타낸다.
• 만일 모든 EXPR이 NULL이라면 NULL을 리턴한다.

COALESCE (EXPR1, EXPR2, ...)

• 예제) 사원 테이블에서 커미션을 1차 선택값으로, 급여를 2차 선택값으로 선택하되 두 칼럼 모두 NULL인 경우는 NULL로 표시

SELECT ENAME, COMM, SAL, COALESCE(COMM, SAL) COAL
FROM EMP;