SQLD - 함수(FUNCTION)

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

- 함수는 다양한 기준으로 분류할 수 있는데, 벤더에서 제공하는 함수인 내장 함수(Built-in Function)와 사용자가 정의할 수 있는 함수(User Defined Function)로 나눌 수 있다.

 

- 내장 함수는 다시 함수의 입력 값이 단일행 값이 입력되는 단일행 함수(Single-Row Function)와 여러 행의 값이 입력되는 다중행 함수(MultiRow Function)로 나눌 수 있다.

 

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

 

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

- 단일행 함수의 경우 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 표현될 수 있다. 다중행 함수의 경우도 여러 레코드의 값들을 입력 인수로 사용하는 것이다.

함수명 (칼럼이나 표현식 (, Args1, Args2, ...))

 

2. 단일행 함수

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

 

1. 종류

2. 특징

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

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

- 여러 인자(Argument)를 입력해도 단 하나의 결과만 리턴한다.

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

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

 

2-1. 문자형 함수

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

[예제] SQL Server SELECT PLAYER_NAME + ' 축구선수' AS 선수명 FROM PLAYER;

[실행 결과] PLAYER_ID 선수명 --------- ------------ 2011075 김성환 축구선수 2012123 가비 축구선
수 2010089 강대희 축구선수 2007051 고종수 축구선수 2012015 고창현 축구선수 2009089 정기범 축구
선수 2009083 정동현 축구선수 2011071 정두현 축구선수 2012025 정준 축구선수 2007040 정진우 축구
선수 2007069 데니스 축구선수 2007274 서정원 축구선수 480 개의 행이 선택되었다.

 

2-2. 숫자형 함수

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

 

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

[실행 결과] 
ENAME ROUND(SAL/12) CEILING(SAL/12) -------- ------------ -------------- SMITH
67 67 ALLEN 133 134 WARD 104 105 JONES 248 248 MARTIN 104 105 BLAKE 238 238 CLARK 204
205 SCOTT 250 250 KING 417 417 TURNER 125 125 ADAMS 92 92 JAMES 79 80 FORD 250 250
MILLER 108 109 14 개의 행이 선택되었다.

 

2-3. 날짜형 함수

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

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

 

- 날짜는 여러 가지 형식으로 출력이 되고 날짜 계산에도 사용되기 때문에 그 편리성을 위해서 숫자형으로 저장하는 것이다.

- 데이터베이스는 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능하다. 즉, 날짜에 숫자 상수를 더하거나 뺄 수 있다.

[예제 및 실행 결과] 
Oracle SELECT SYSDATE FROM DUAL; 

SYSDATE -------- 
12/07/18

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

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


[실행 결과]
ENAME HIREDATE 입사년도 입사월 입사일 ------- ---------- ------- ------ ------
SMITH 1980-12-17 1980 12 17 ALLEN 1981-02-20 1981 2 20 WARD 1981-02-22 1981 2 22 JONES 1981-
04-02 1981 4 2 MARTIN 1981-09-28 1981 9 28 BLAKE 1981-05-01 1981 5 1 CLARK 1981-06-09 1981 6
9 SCOTT 1987-07-13 1987 7 13 KING 1981-11-17 1981 11 17 TURNER 1981-09-08 1981 9 8 ADAMS
1987-07-13 1987 7 13 JAMES 1981-12-03 1981 12 3 FORD 1981-12-03 1981 12 3 MILLER 1982-01-23
1982 1 23 14 개의 행이 선택되었다.

 

2-4. 변환형 함수

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

[예제] 날짜를 정해진 문자 형태로 변형한다.

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

[실행 결과]
TEAM_ID   우편번호합
--------- -------- 
K05 750 K08 592 K03 840 K07 554
K09 359 K04 838 K11 333 K01 742 K10 331 K02 660 K12 869 K06 620 K13 777 K14 1221 K15 1665 15


[sql server예제 및 실행 결과] 
SELECT TEAM_ID, CAST(ZIP_CODE1 AS INT) + CAST(ZIP_CODE2 ASINT) 우편번호합 FROM TEAM;

[실행 결과]
TEAM_ID 우편번호합
------- ------- 
K05 750 K08 592 K03 840 K07 554
K09 359 K04 838 K11 3

 

2-5. CASE 표현

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

- ANSI/ISO SQL 표준에는 CASE Expression 이라고 표시되어 있는데, 함수와 같은 성격을 가지고 있으며 Oracle 의 경우 Decode 함수를 쓸 수도 있다.

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

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

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


실행결과
LOC	       AREA
--------------------
NEWYORK		EAST
DALLAS		CENTER
CHICAGO		CENTER
BOSTON		EAST


예제) 사원 정보에서 급여가 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-6. NULL 관련 함수

1. NULL의 특성

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

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

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

 

 

2. NVL/ISNULL 함수

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

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

 

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

[예제] MySql
mysql> SELECT PLAYER_NAME AS '선수명', IFNULL(HEIGHT,0) AS '키' FROM PLAYER;
+--------+-----+
| 선수명 | 키  |
+--------+-----+
| 유현지 | 400 |
| 이운재 | 400 |
| 기성용 |   0 |
| 박지성 | 400 |
| 이청용 | 400 |
+--------+-----+
5 rows in set (0.00 sec)

 

3. NULL과 공집합

SELECT 1 FROM DUAL WHERE 1 = 2;

- 대표적인 공집합을 발생시키는 쿼리.

- 이와같이 조건에 맞는 데이터가 한 건도 없는 경우를 공집합이라고 한다.

- NVL/ISNULL 함수는 NULL 값을 대상으로 다른 값으로 바꾸는 함수지, 공집합을 대상으로 하는 함수가 아니다.

 

- 집계 함수(MGR)와 Scalar Subquery의 경우 인수의 결과 값이 공집합인 경우에도 NULL을 출력한다.

- 따라서 NVL/ISNULL 함수를 이용해서 공집합을 다루려면 집계 함수를 인수로해서 출력한다.

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

- 개발자들은

 

1. NVL/ISNULL 함수를 사용해야 하는 경우와

2. 집계 함수를 포함한 NVL/ISNULL 함수를 사용해야 하는 경우

3. NVL/ISNULL 함수를 포함한 집계 함수를 사용하지 않아야 될 경우

 

까지 잘 이해해서 NVL/ISNULL 함수를 정확히 사용해야 한다.

 

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

[실행 결과] ENAME EMPNO MGR NUIF ------- ------- ----- ---- SMITH 7369 7902 7902 ALLEN
7499 7698 WARD 7521 7698 JONES 7566 7839 7839 MARTIN 7654 7698 BLAKE 7698 7839 7839
CLARK 7782 7839 7839 SCOTT 7788 7566 7566 KING 7839 TURNER 7844 7698 ADAMS 7876 7788
7788 JAMES 7900 7698 FORD 7902 7566 7566 MILLER 7934 7782 7782 14 개의 행이 선택되었다.

 

5. COALESCE

- 인수의 숫자가 한정되어 있지 않으며, 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 나타낸다.

- 만일 모든 EXPR이 NULL이라면 NULL을 리턴한다.

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 SALELSE NULL END) END COAL FROM EMP;

[실행 결과] ENAME COMM SAL COAL ------- ------- ------ ------ SMITH 800 800 ALLEN 300
1600 300 WARD 500 1250 500 JONES 2975 2975 MARTIN 1400 1250 1400 BLAKE 2850 2850 CLARK
2450 2450 SCOTT 3000 3000 KING 5000 5000 TURNER 0 1500 0 ADAMS 1100 1100 JAMES 950 950
FORD 3000 3000 MILLER 1300 1300 14 개의 행이 선택되었다.