[SQLD 시험 대비] 2과목. SQL 기본 및 활용 : 1장. SQL 기본 - 6. 함수(Fuction)

함수(Fuction)

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

• 함수는 크게 벤더에서 제공하는 함수인 내장 함수(Built-in Function)와 사용자가 정의할 수 있는 함수(User Defined Function)로 분류
• 내장함수 : SQL을 더욱 강력하게 해주고 데이터 값을 간편하게 조작하는데 사용
  • 단일행 함수(Single-Row Function) : 함수의 입력 값으로 단일행 값이 입력됨
  • 다중행 함수(Multi-Row Function) : 함수의 입력 값으로 여러 행의 값이 입력됨
    • 집계 함수(Aggregate Function), 그룹 함수(Group Function), 윈도우 함수(Window Function)

이번 글에서는 내장함수 중에서 단일행 함수에 대해 알아보겠다.

단일행 함수

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

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

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

문자형 함수

• 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값의 결과를 돌려주는 함수이다.
• 몇몇 문자형 함수의 경우는 결과를 숫자로 리턴하는 함수도 있다.
  
• 문자형 함수들이 적용되었을 때 리턴되는 값
  

예제 1

• 선수 테이블에서 CONCAT 문자형 함수를 이용해 축구선수 문구를 추가해보자.

SELECT CONCAT(PLAYER_NAME, ' 축구선수') 선수명 FROM PLAYER;

• Oracle

SELECT PLAYER_NAME || ' 축구선수' AS 선수명
FROM PLAYER;

• SQL Server

SELECT PLAYER_NAME + ' 축구선수' AS 선수명 
FROM PLAYER;

예제 2

• 경기장의 지역번호와 전화번호를 합친 번호의 길이를 구해보자.
• Oracle

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

• SQL Server

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

숫자형 함수

• 숫자 데이터를 입력받아 처리하고 숫자를 리턴하는 함수
  
• 숫자형 함수들이 적용되었을 때 리턴되는 값
  

날짜형 함수

• DATE 타입의 값을 연산하는 함수
  
• 데이터베이스는 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능하다.
  
• Oracle의 SYSDATE 함수와 SQL Server의 GETDATE( ) 함수를 사용하여 데이터베이스에서 사용하는 현재의 날짜 데이터를 확인해보자.

// Oracle

SELECT SYSDATE 
FROM DUAL;
SYSDATE

// SQL Server

SELECT GETDATE() AS CURRENTTIME;
CURRENTTIME

[참고] DUAL 테이블

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

예제

• 사원(EMP) 테이블의 입사일자에서 년, 월, 일 데이터를 각각 출력해보자.
• Oracle

SELECT ENAME, HIREDATE,
       EXTRACT(YEAR FROM HIREDATE) 입사년도,
       EXTRACT(MONTH FROM HIREDATE) 입사월, 
       EXTRACT(DAY FROM HIREDATE) 입사일
FROM EMP;

• SQL Server

SELECT ENAME, HIREDATE, 
       YEAR(HIREDATE) 입사년도, 
       MONTH(HIREDATE) 입사월,
       DAY(HIREDATE) 입사일 
FROM EMP;

변환형 함수

• 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수
• 암시적 데이터 유형 변환의 경우 성능 저하가 발생할 수 있으며, 자동적으로 데이터베이스 가 알아서 계산하지 않는 경우가 있어 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환 방법을 사용하는 것이 바람직하다.
  
• 명시적 데이터 유형 변환에 사용되는 대표적인 변환형 함수는 다음과 같다.
  

예제

• 날짜를 정해진 문자 형태로 변형해보자.
• Oracle

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') 날짜, 
	   TO_CHAR(SYSDATE, 'YYYY. MON, DAY') 문자형
FROM DUAL;

• SQL Server

SELECT CONVERT(VARCHAR(10),GETDATE(),111) AS CURRENTDATE 
CURRNETDATE

CASE 표현

• IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완하는 역할
  
• CASE Expressions은 Simple Case Expression과 Searched Case Expression 두 가지 표현법이 있다.

CASE
  	SIMPLE_CASE_EXPRESSION 조건 or SEARCHED_CASE_EXPRESSION 조건 
	ELSE 표현절
END

SIMPLE_CASE_EXPRESSION

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

CASE
	EXPR WHEN COMPARISON_EXPR THEN RETURN_EXPR
	ELSE 표현절
END

예제

• 부서 정보에서 부서 위치를 미국의 동부, 중부, 서부로 구분해보자.

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;

• 실행 결과

LOC 	   AREA 
--------- -------- 
NEW YORK  EAST 
DALLAS    CENTER 
CHICAGO   CENTER 
BOSTON    EAST 
4개의 행이 선택되었다.

SEARCHED_CASE_EXPRESSION

• CASE 다음에는 칼럼이나 표현식을 표시하지 않고, 다음 WHEN 절에서 EQUI(=) 조건 포함 여러 조건(>, >=, <, <=)을 이용한 조건절을 사용

CASE
    WHEN CONDITION THEN RETURN_EXPR 
    ELSE 표현절
END

예제

• 사원 정보에서 급여가 3000 이상이면 상등급으로, 1000 이상이면 중등급으로, 1000 미만이면 하등급으로 분류해보자.

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

• CASE 표현은 함수의 성질을 가지고 있으므로, 다른 함수처럼 중첩해서 사용할 수 있다.
  • 사원 정보에서 급여가 2000 이상이면 보너스를 1000으로, 1000 이상이면 5000으로, 1000 미만이면 0으로 계산해보자.

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

NULL 관련 함수

NULL 특성

• 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. 0은 숫자이고, 공백은 하나의 문자이다.

• 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함할 수 있다.

• 널 값을 포함하는 연산의 경우 결과 값도 널 값이다.

  • 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는 데이터인 것과 같다.
    

• 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.

• NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0(Zero)으로, 문자 유형 데이터인 경우는 블랭크보다는 ‘x’ 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다.

NVL / ISNULL

• 산술적인 계산에서 데이터 값이 NULL일 경우 사용
• 칼럼 간 계산을 수행하는 경우 NULL 값이 존재하면 해당 연산 결과가 NULL 값이 되므로 원하는 결과를 얻을 수 없는 경우가 발생
  • 이런 경우에 NVL 함수를 사용하여 숫자인 0(Zero)으로 변환을 시킨 후 계산을 해서 원하는 데이터를 얻는다.

예제

• 선수 테이블에서 성남 일화천마(K08) 소속 선수의 이름과 포지션을 출력하는데, 포지션이 없는 경우는 '없음'으로 표시해보자.
• Oracle

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

• SQL Server

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

• NVL 함수와 ISNULL 함수를 사용한 SQL 문장은 벤더 공통적으로 CASE 문장으로 표현할 수도 있다.

SELECT PLAYER_NAME 선수명, POSITION, 
	   CASE WHEN POSITION IS NULL
            THEN '없음'
            ELSE POSITION END AS 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08'

공집합의 NVL/ISNULL 함수 사용

• 조건에 맞는 데이터가 한 건도 없는 경우를 공집합이라고 한다. (NULL과는 다름)
  • SELECT 1 FROM DUAL WHERE 1 = 2; 와 같은 조건이 대표적인 공집합을 발생시키는 쿼리
• 공집합을 NVL/ISNULL 함수를 이용해서 처리하면 공집합이 출력된다.
• 다른 함수와 달리 집계 함수와 Scalar Subquery의 경우는 인수의 결과 값이 공집합인 경우에도 NULL을 출력한다.
  • 집계 함수를 인수로 한 NVL/ISNULL 함수를 이용해서 공집합인 경우에도 빈칸이 아닌 9999로 출력하게 할 수 있다.

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

NULLIF

• EXPR1이 EXPR2와 같으면 NULL을, 같지 않으면 EXPR1을 리턴한다.
• 특정 값을 NULL로 대체하는 경우에 유용하게 사용할 수 있다.

NULLIF (EXPR1, EXPR2)

예제

• 사원 테이블에서 MGR와 7698이 같으면 NULL을 표시하고, 같지 않으면 MGR를 표시해보자.

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

• NULLIF 함수를 CASE 문장으로 표현할 수도 있다.

SELECT ENAME, EMPNO, MGR,
	   CASE WHEN MGR = 7698 
            THEN NULL
            ELSE MGR 
       END NUIF
FROM EMP;

기타 NULL 관련 함수 (COALESCE)

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

예제

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

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

• COALESCE 함수는 두개의 중첩된 CASE 문장으로 표현할 수 있다.

SELECT ENAME, COMM, SAL,
       CASE WHEN COMM IS NOT NULL 
            THEN COMM
            ELSE (CASE WHEN SAL IS NOT NULL 
                       THEN SAL
                       ELSE NULL END)
                  END COAL
FROM EMP;