[SQLD : Ⅲ. SQL 기본] 6. 함수 FUNCTION

* 내장함수 BUILT IN FUNCTION 개요

 - 함수는 벤더에서 제공하는 함수인 내장 함수와 사용자가 정의할 수 있는 함수로 나눔

 - 내장 함수는 단일행 함수와 다중행 함수로 나눔

 - 다중행 함수는 집계함수, 그룹함수, 윈도우 함수로 나눔

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

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

 - 단일행 함수 특징 

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

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

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

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

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

 

* 문자형 함수

  문자형 함수는 문자 데이터를 매개 변수로 문자나 숫자 값의 결과를 돌려주는 함수

  몇몇 문자형 함수의 경우는 결과를 숫자로 리턴하는 함수

[예제] ‘SQL Expert’라는 문자형 데이터의 길이를 구하는 문자형 함수를 사용한다.

Oracle 
SELECT LENGTH('SQL Expert') 
FROM DUAL; 
LENGTH('SQL Expert') --------------- 10

 - 사용자 테이블이 필요 없는 SQL 문장에는 FROM 절에 DUAL이라는 테이블 필수적으로 지정

 - DUAL 테이블의 특성

   사용자 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블이다

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

   DUMMY 라는 문자열 유형의 컬럼에 X라는 값이 들어 있는 행을 1건 포함

Oracle DESC DUAL; 
칼럼 NULL 가능 데이터 유형 
---------------- -------- ----------- 
DUMMY VARCHAR2(1)

 

Oracle SELECT * 
FROM DUAL; 
DUMMY ----- X 1개의 행이 선택되었다.

 - Sybase나 SQL server 경우 SELECT 절만으로도 SQL 문장이 수행 가능하도록 정의해서 DUAL 필요 없음

 - 하지만 사용자 테이블 컬럼을 사용할 때는 FROM 절이 필수적으로 사용되어야 함

[예제] ‘SQL Expert’라는 문자형 데이터의 길이를 구하는 문자형 함수를 사용한다.

Oracle SELECT LEN('SQL Expert') AS ColumnLength; ColumnLength ---------- 10

[예제] 선수 테이블에서 CONCAT 문자형 함수를 이용해 축구선수란 문구를 추가한다.

SELECT CONCAT(PLAYER_NAME, ' 축구선수') 선수명 
FROM PLAYER; 
CONCAT 함수는 Oracle의 '||' 합성 연산자와 같은 기능이다.

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

 

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

[예제] 경기장의 지역번호와 전화번호를 합친 번호의 길이를 구하시오.

연결연산자의 결과가 LENGTH(SQL Server는 LEN 사용) 함수의 인수가 된다.

[예제] 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;

 

* 숫자형 함수

[예제] 소수점 이하 한 자리까지 반올림 및 내림하여 출력한다.

[예제] SQL Server SELECT ENAME, ROUND(SAL/12,1), TRUNC(SAL/12,1) FROM EMP;

[예제] 정수 기준으로 반올림 및 올림하여 출력한다.

[예제] SQL Server SELECT ENAME, ROUND(SAL/12), CEILING(SAL/12) FROM EMP;

 

* 날짜형 함수

[예제] Oracle의 SYSDATE 함수와 SQL Server의 GETDATE( ) 함수를 사용하여

데이터베이스에서 사용하는 현재의 날짜 데이터를 확인한다.

날짜 데이터는 시스템 구성에 따라 다양하게 표현될 수 있으므로 사용자마다 다른 결과가 나올 수 있다.

[예제 및 실행 결과] Oracle SELECT SYSDATE FROM DUAL; SYSDATE -------- 12/07/18
[예제 및 실행 결과] SQL Server SELECT GETDATE() AS CURRENTTIME; CURRENTTIME ----------------------- 2012-07-18 13:10:02.047

[예제] 사원(EMP) 테이블의 입사일자에서 년, 월, 일 데이터를 각각 출력한다.

아래 4개의 SQL 문장은 같은 기능을 하는 SQL 문장이다.

[예제] Oracle 함수 SELECT EXTRACT(YEAR FROM HIREDATE) 입사년, 
                          EXTRACT(MONTH FROM HIREDATE) 입사월, 
                          EXTRACT(DAY FROM HIREDATE) 입사일 
                   FROM EMP;
                 
[예제] Oracle 함수 SELECT ENAME, HIREDATE, TO_NUMBER(TO_CHAR(HIREDATE,'YYYY')) 입사년도, 
                          TO_NUMBER(TO_CHAR(HIREDATE,'MM')) 입사월, 
                          TO_NUMBER(TO_CHAR(HIREDATE,'DD')) 입사일 
                   FROM EMP; TO_NUMBER 함수 제외시 문자형으로 출력됨 (ex: 01,02,03,...)
                   
[예제] SQL Server 함수 SELECT ENAME, HIREDATE, DATEPART(YEAR, HIREDATE) 입사년도, 
                              DATEPART(MONTH, HIREDATE) 입사월, DATEPART(DAY, 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; 
날자 문자형 
--------- ---------------- 
2012-07-19 2012. 7월 , 월요일

[예제 및 실행 결과] SQL Server SELECT CONVERT(VARCHAR(10),GETDATE(),111) 
                               AS CURRENTDATE CURRNETDATE 
---------- 2012/07/19

[예제] 금액을 달러와 원화로 표시한다.

[예제 및 실행 결과] Oracle SELECT TO_CHAR(123456789/1200,'$999,999,999.99') 환율반영달러, 
                                  TO_CHAR(123456789,'L999,999,999') 원화 
                           FROM DUAL; 
환율반영달러 원화 
------------ ---------- 
$102,880.66 \123,456,789 두 번째 칼럼의 L999에서 L은 로칼 화폐 단위를 의미한다.

[예제] 팀(TEAM) 테이블의 ZIP 코드1과 ZIP 코드2를 숫자로 변환한 후 두 항목을 더한 숫자를 출력한다.

[예제] Oracle SELECT TEAM_ID, 
                     TO_NUMBER(ZIP_CODE1,'999') + TO_NUMBER(ZIP_CODE2,'999') 우편번호합 
              FROM TEAM;
              
[예제] SQL Server SELECT TEAM_ID, 
                         CAST(ZIP_CODE1 AS INT) + CAST(ZIP_CODE2 AS INT) 우편번호합 
                  FROM TEAM;

 

* CASE 표현

[예제] 일반 프로그램의 IF-THEN-ELSE-END 로직과 같다. 
IF SAL > 2000 THEN REVISED_SALARY = SAL ELSE REVISED_SALARY = 2000 END-IF.

[예제] 같은 기능을 하는 CASE 표현이다. 
SELECT ENAME, CASE WHEN SAL > 2000 THEN SAL ELSE 2000 END REVISED_SALARY FROM EMP;

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

SIMPLE_CASE_EXPRESSION은 CASE 다음에 바로 조건에 사용되는 컬럼이나 표현식을 표시하고

다음 WHEN절에서 앞에서 정의한 컬럼이나 표현식과 같은지 아닌지 판단하는 문장으로 EQUI 조건만 사용하면 SEARCHED_CASE_EXPRESSION 보다 간단하게 사용할 수 있음

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;

SEARCHED_CASE_EXPRESSION은 CASE 다음에는 칼럼이나 표현식을 표시하지 않고,

다음 WHEN 절에서 EQUI(=) 조건 포함 여러 조건(>, >=, <, <=)을 이용한 조건절을 사용할 수 있기 때문에 SIMPLE_CASE_EXPRESSION보다 훨씬 다양한 조건을 적용할 수 있음

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;

 

* NVL/ISNULL 함수

 - 오라클의 경우 NVL 함수 사용

NVL (NULL 판단 대상,‘NULL일 때 대체값’)

Oracle SELECT NVL(NULL, 'NVL-OK') NVL_TEST 
       FROM DUAL; 
       NVL_TEST 
       ------- 
       NVL-OK 1개의 행이 선택되었다.
       
Oracle SELECT NVL('Not-Null', 'NVL-OK') NVL_TEST 
       FROM DUAL; 
       NVL_TEST 
       ------- 
       Not-Null 1개의 행이 선택되었다.

 - SQL 서버의 경우 ISNULL 함수 사용

ISNULL (NULL 판단 대상,‘NULL일 때 대체값’)

SQL Server SELECT ISNULL(NULL, 'NVL-OK') ISNULL_TEST ; 
ISNULL_TEST
---------
NVL-OK 1개의 행이 선택되었다.

SQL Server SELECT ISNULL('Not-Null', 'NVL-OK') ISNULL_TEST ; 
ISNULL_TEST 
--------- 
Not-Null 1개의 행이 선택되었다.

[예제] 선수 테이블에서 성남 일화천마(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'

[예제] NVL 함수와 ISNULL 함수를 사용한 SQL 문장은 벤더 공통적으로 CASE 문장으로 표현할 수 있다

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

[예제] 급여와 커미션을 포함한 연봉을 계산하면서 NVL 함수의 필요성을 알아본다.

SELECT ENAME 사원명, SAL 월급, COMM 커미션, 
       (SAL * 12) + COMM 연봉A, (SAL * 12) + NVL(COMM,0) 연봉B 
FROM EMP;

연봉 계산 산술식이 있을 때 커미션에 NULL 값이 있는 경우 커미션 값에 NUL()함수를 사용하지 않으면

연봉A의 계산 결과가 NULL이 되어서 잘못 계산한 결과를 확인할 수 있음

연봉B 결과와 같이 NVL(COMM,0)처럼 NULL 값을 0으로 변환하여 연봉을 계산

곱셈을 사용해야 하는 경우에는 NVL(COMM,1)

NVL 함수를 다중행 함수의 인자로 사용하는 경우는 오히려 불필요한 부하를 발생. 다중행 함수의 대상에서 제외

 

* NULL과 공집합

 - 정상적으로 매니저 정보를 가지고 있는 SCOTT의 매니저를 출력한다.

SELECT MGR
FROM EMP 
WHERE ENAME='SCOTT'; 
MGR 
----- 
7566 1개의 행이 선택되었다. ☞ 'SCOTT'의 관리자(MGR=Manager)는 7566 사번을 가진 JONES이다.

SELECT MGR 
FROM EMP 
WHERE ENAME='KING'; 
MGR 
----- 
1개의 행이 선택되었다. ☞ 빈 칸으로 표시되었지만 실 데이터는 NULL이다. 
☞ 'KING'은 EMP 테이블에서 사장이므로 MGR(관리자) 필드에 NULL이 입력되어 있다.

SELECT NVL(MGR,9999) MGR 
FROM EMP 
WHERE ENAME='KING'; 
MGR 
----- 
9999 1개의 행이 선택되었다. ☞ NVL 함수로 NULL을 0으로 변경한다.

 - 공집합을 발생시키기 위해 사원 테이블에 존재하지 않는 'JSC'라는 이름으로 데이터를 검색한다.

SELECT MGR 
FROM EMP 
WHERE ENAME='JSC'; 
데이터를 찾을 수 없다. ☞ EMP 테이블에 ENAME이‘JSC’란 사람은 없으므로 공집합이 발생한다.

 - NVL/ISNULL 함수를 이용해 공집합을 9999로 바꾸고자 시도한다.

SELECT NVL(MGR, 9999) MGR 
FROM EMP 
WHERE ENAME='JSC'; 
데이터를 찾을 수 없다. ☞ 많은 분들이 공집합을 NVL/ISNULL 함수를 이용해서 처리하려고 하는데, 
인수의 값이 공집합인 경우는 NVL/ISNULL 함수를 사용해도 역시 공집합이 출력된다. 
☞ NVL/ISNULL 함수는 NULL 값을 대상으로 다른 값으로 바꾸는 함수이지 공집합을 대상으로 하지 않는다.

 - 적절한 집계 함수를 찾아서 NVL 함수 대신 적용한다.

SELECT MAX(MGR) MGR 
FROM EMP 
WHERE ENAME='JSC'; 
MGR
----- 
1개의 행이 선택되었다. ☞ 빈 칸으로 표시되었지만 실 데이터는 NULL이다. 
☞ 다른 함수와 달리 집계 함수와 Scalar Subquery의 경우는 인수의 결과 값이 공집합인 경우에도 NULL을 출력한다.

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

SELECT NVL(MAX(MGR), 9999) MGR 
FROM EMP 
WHERE ENAME='JSC'; 
MGR 
----- 
9999 1개의 행이 선택되었다. 
☞ 공집합의 경우는 NVL 함수를 사용해도 공집합이 출력되므로, 
그룹함수와 NVL 함수를 같이 사용해서 처리한다.
예제는 그룹함수를 NVL 함수의 인자로 사용해서 인수의 값이 공집합인 경우에도
원하는 9999라는 값으로 변환한 사례이다.

 

* NULLIF

 EXPR1이 EXPR2와 같으면 NULL을, 같지 않으면 EXPR1 리턴

NULLIF (EXPR1, EXPR2)

[예제] 사원 테이블에서 MGR와 7698이 같으면 NULL을 표시하고, 같지 않으면 MGR를 표시한다.

NULLIF 함수를 CASE 문장으로 표현할 수 있다. 
SELECT ENAME, EMPNO, MGR, CASE WHEN MGR = 7698 THEN NULL 
                               ELSE MGR END NUIF  
FROM EMP;

 

* 기타 NULL 관련 함수 (COALESCE)

임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 나타냄

COALESCE (EXPR1, EXPR2, …)

[예제] 사원 테이블에서 커미션을 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;