[SQLD] 17. 함수 (Function)

제 6절 함수(FUNCTION)

1. 내장 함수(Built-in function) 개요

  함수는 벤더에서 제공하는 함수인 내장함수와 사용자가 정의할 수 있는 함수(User Defined Function)로 나눌 수 있다. 본 절에서는 SQL 내장함수에 대해서 설명하기로 한다. 내장함수는 SQL을 더욱 강력하게 해주고 데이터 값을 간편하게 조작하는데  사용된다. 

  내장함수는 다시 함수의 입력 값이 단일행 값이 입력되는 단일행함수(Single-Row Function)와 여러 행의 값이 입력되는 다중행 함수(Multi-Row Function)로 나눌 수 있다. 다중행 함수는 다시 집계 함수(Aggregate Function), 그룹함수(Group Function), 윈도우 함수(Window Function)로 나눌 수 있다. 이들에 대해서는 다음 절에서 공부할 수 있다. 

  함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1 관계라는 중요한 특징을 가지고 있다. 단일행 함수의 경우 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 표현될 수 있다. 다중행 함수의 경우도 여러 레코드의 값들을 입력 인수로 사용하는 것이다.

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

  단일행 함수는 처리하는 데이터의 형식에 따라 문자형, 숫자형, 날짜형, 변환형, NULL 관련 함수로 나눌 수 있다. 벤더에서 제공하는 내장함수는 무지 많으므로 Oracle과 SQL Server에서 공통으로 사용하는 중요 함수 위주로 설명할 것이다. 

단일행 함수의 중요한 특징

- SELECT, WHERE, ORDER BY 절에 사용 가능

- 각 행들에 대해 개별적으로 작용하여 데이터 값들을 조작, 각각의 행에 대한 조작 결과를 리턴

- 여러 인자를 입력해도 단 하나의 결과만 리턴

- 함수의 인자로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있음

- 특별한 경우가 아니면 함수의 인자로 함수를 사용하는 함수의 중첩이 가능

2. 문자형 함수

  문자형 함수는 문자 데이터를 매개 변수로 받아들여서 문자나 숫자 값의 결과를 돌려주는 함수이다. 몇몇 문자형 함수의 경우는 결과를 숫자로 리턴하는 함수도 있다. (ASCII, CHR/CHAR, LENGTH/LEN)

- LOWER : 소문자로 리턴

- UPPER : 대문자로 리턴

- ASCII : 아스키 코드 값 리턴

- CHR / CHAR : 아스키 값 리턴

- CONCAT, || / + : 문자열 연결 값 리턴

- SUBSTR / SUBSTRING : 문자열 일부 값 리턴

- LENGTH / LEN : 문자열 길이 리턴

- LTRIM : 왼쪽 문자 자른 값 리턴

- RTRIM : 오른쪽 문자 자른 값 리턴

- TRIM : 왼쪽, 오른쪽 문자 자른 값 리턴 

이 트림 함수는 문자열을 자르는데, 예를 들어 LTRIM은 왼쪽부터 x라는 문자를 찾고 더이상 x라는 문자가 나오지 않으면 자름을 중단하고 결과를 리턴한다.

SELECT LENGTH('SQL Expert') FROM DUAL;

이러한 SQL 구문에서 DUAL 이라는 테이블은 일종의 DUMMY 테이블이다. Oracle 에서는 SELECT 절과 FROM 절 두 개의 절을 SELECT 문장의 필수 절로 지정하였기 때문에 테이블이 없더라도 일시적으로 SELECT 절과 FROM 절을 지정해주어야한다. DUAL 테이블의 특성은 다음과 같다.

- 사용자 SYS가 소유, 모든 사용자가 엑세스 가능

- SELECT ~ FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블

- DUMMY 라는 문자열 유형의 칼럼에 'X'라는 값이 들어있는 행을 1건 포함하고 있음

SELECT * FROM DUAL;

DUMMY

------

  X

1개의 행이 선택되었다.

라는 결과를 얻을 수 있다. 반면 Sysbase나 SQL Server의 경우에는 SELECT 절만으로도 SQL 문장 수행이 가능하도록 정의되어있기 때문에 DUMMY 테이블이 필요없다. 그러나 사용자 테이블의 칼럼을 사용할 때는 FROM 절이 필수적으로 사용되어야 한다. 

SELECT LEN('SQL Expert') AS ColumnLength;

예제 ) 경기장의 지역번호와 전화번호를 합친 번호의 길이를 구하시오. 연결 연산자의 결과가 LENGTH / LEN 함수의 인수가 된다.

<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;

3. 숫자형 함수

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

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

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

4. 날짜형 함수

  날짜형 함수는 DATE 타입의 값을 연산하는 함수이다. Oracle의 TO_NUMBER(TO_CHAR()) 함수의 경우 변환형 함수로 구분할 수도 있으나 SQL Server의 YEAR, MONTH, DAY 함수와 매핑하기 위하여 날짜형 함수에서 설명한다. 

DATE 변수가 데이터베이스에 어떻게 저장되는지 내부적으로 보면

세기, 년, 월, 일, 시,분, 초와 같은 숫자 형식으로 변환하여 저장하게 된다. 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능하다. 즉, 날짜에 숫자를 더하거나 뺄 수 있다. 

5. 변환형 함수 

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

 

  CASE 표현을 하기 위해서는 조건절을 표현하는 두 가지 방법이 있고, Oracle에 경우 DECODE 함수를 사용할 수도 있다. 

IF-THEN-ELSE 논리를 구현하는 CASE Expressions은 Simple Case Expression과 Searched Cased Expression 두 가지 표현법 중에 하나를 선택해서 사용하게 된다. 

1. Simple Case Expression

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

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

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. Searched Cased Expression

 두번째 표현법은 칼럼이나 표현식을 표현하지 않고, 다음 WHEN 절에서 EQUL(=) 조건을 포함한 여러 조건(>, >=, .. 등) 을 이용한 조건절을 사용할 수 있기 때문에 앞전의 표현식 보다 훨씬 다양한 조건을 적용할 수 있다는 장점이있다.

예제) 사원정보에서 급여가 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 표현은 함수의 성질을 가지고 있어서, 다른 함수처럼 중첩해서 사용할 수 있음

ELSE (CASE WHEN SAL>=1000

THEN 500

ELSE 0

END)

END    as    BONUS

FROM EMP;

7. NULL 관련 함수

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

NULL의 특성

- 널 값은 아직 정의되지 않은 값으로 0 또는 공백과는 다름, 0은 숫자, 공백은 하나의 문자

- 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함 가능

- 널 값을 포함하는 연산의 경우 결과 값도 널값, 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지

- 결과 값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL / ISNULL 함수를 사용함. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0으로, 문자 유형 데이터인 경우는 블랭크보다는 'x' 라는 임의의 의미없는 문자로 바꾸는 경우가 많음

  NVL / ISNULL 함수를 유용하게 사용하는 예는 산술적인 계산에서 데이터 값이 NULL일 경우일 때 그 빈칸을 채워주는 역할을 하게 된다. 칼럼 간 계산을 수행하는 경우 NULL 값이 존재하면 해당 연산 결과가 NULL 값이 되므로 원하는 결과를 얻을 수 없게 된다. 이 때 이 함수를 이용하여 숫자인 0으로 변환을 시킨 후 계산을 해서 원하는 데이터를 얻을 수 있다.

  관계형 데이터베이스의 중요한 데이터인 NULL을 처리하는 주요 함수는 다음과 같다.

예제들)

<Oracle>

SELECT NVL(NULL, 'NVL-OK') NVL_TEST

FROM DUAL;

NVL_TEST

-------

NVL-OK

<SQL Server>

SELECT ISNULL(NULL, 'NVL-OK') ISNULL_TEST;

ISNUL_TEST

---------

NVL-OK

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

SELECT PLAYER_NAME 선수명, POSITION, NVL(POSITION,'없음') 포지션

FROM PLAYER

WHERE TEAM_ID = 'K08';

SELECT PLAYER_NAME 선수명, POSITION, ISNULL(POSITION,'없음') 포지션

FROM PLAYER

WHERE TEAM_ID = 'K08';

나. 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';

이 경우에도 공집합이 출력된다. 많은 사람들이 공집합을 이러한 함수를 이용해서 처리하려고 하는데, 인수의 값이 공집합일 경우는 이 함수를 사용해도 역시 공집합이 출력된다. 이러한 경우 적절한 집계함수를 찾아서 NVL 대신 적용하기도 한다.

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

집계함수를 인수로 한 NVL / ISNULL 함수를 이용해서 공집합인 경우에도 빈칸이 아닌 9999로 출력함

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

공집합의 경우는 NVL 함수를 사용해도 공집합이 출력되므로, 그룹함수의 NVL 함수를 같이 사용해서 처리한다. 예제는 그룹함수를 NVL 함수의 인자로 사용해서 인수의 값이 공집합인 경우에도 원하는 9999라는 값으로 변환한 사례이다.

다. NULLIF

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

NULLIF (EXPR1,EXPR2)

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

SELECT ENAME, EMPNO, MGR, NULLIF(MGR, 7698) NUIF

FROM EMP;

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

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

COALESCE (EXPR1, EXPR2, ...)

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

SELECT ENAME, COMM, SAL, COALESCE(COMM, SAL) COAL

FROM EMP;