제6절_함수(FUNCTION)

조회 수 7293 추천 수 0 2013.09.05 09:42:10
suspace *.171.240.111

과목2_1_6_함수

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

벤더별로 가장 큰 차이를 보이는 부분이지만, 핵심적인 기능들은 이름이나 표현법이 다르더라도 대부분의 데이터베이스가 공통적으로 제공
단일행 함수(Single-Row Function)와 다중행 함수(Multi-Row Function)로 나눌 수 있다.

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

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

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

2. 문자형 함수

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

  • 문자형 함수들이 적용되었을 때 리턴되는 값 예

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

SELECT LENGTH('SQL Expert') FROM DUAL;

LENGTH('SQL Expert')
---------------
10

- Oracle
SELECT 절과 FROM 절 두 개의 절을 SELECT 문장의 필수 절로 지정하였으므로 사용자 테이블이 필요 없는 SQL 문장의 경우에도 필수적으로 DUAL이라는 테이블을 FROM 절에 지정

* DUAL 테이블이란?

- DUAL테이블은 데이터 딕셔너리와 함께 Oracle에 의해 자동으로 생성되는 테이블 입니다.
- DUAL
테이블은 사용자 SYS의 스키마에 있지만 모든 사용자는 DUAL이라는 이름으로 엑세스 할 수 있습니다.
- DUAL
테이블은 VARCHAR2(1)으로 정의된 DUMMY라는 하나의 열이 있으며 값을 가지는 하나의 행도 포함되어 있습니다.
- DUAL
테이블은 사용자가 계산이나 사용자 함수등을 실행하고자 할 경우에 유용 합니다.

* DUAL 테이블의 특성

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

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

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

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

PLAYER_ID       선수명
---------               ------------
2011075            
김성환 축구선수

……
* Oracle
'||' 합성 연산자와 같은 기능 (SELECT PLAYER_NAME || ' 축구선수' AS 선수명 FROM PLAYER; )

l  특별한 제약 조건이 없다면 함수는 여러 개 중첩하여 사용 가능. 함수 내부에 다른 함수를 사용하며 안쪽에 위치해 있는 함수부터 실행되어 그 결과 값이 바깥쪽의 함수에 인자(Argument)로 사용되는 것이다.

함수3 (함수2 (함수1 (칼럼이나 표현식 , Arg1) , Arg2) , Arg3)

예제 경기장의 지역번호와 전화번호를 합친 번호의 길이를 구하시오.
연결연산자의 결과가 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;


STADIUM_ID      TEL                    T_LEN
---------                 ----------               -----
D03                        063273-1763       11
....

3. 숫자형 함수

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

  • 숫자형 함수들이 적용되었을 때 리턴되는 값 예

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

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

 

ENAME     ROUND(SAL/12,1)     TRUNC(SAL/12,1)
--------        -------------                    -------------
 SMITH       66.7                             66.6
ALLEN       133.3                          133.3
 WARD       104.2                          104.1
JONES        247.9                          247.9
MARTIN    104.2                          104.1
BLAKE       237.5                         237.5
CLARK       204.2                         204.1
SCOTT        250                            250
KING          416.7                          416.6
TURNER     125                            125
ADAMS       91.7                           91.6
JAMES          79.2                          79.1
FORD             250                          250
MILLER        108.3                       108.3
14
개의 행이 선택되었다.

4. 날짜형 함수

- 날짜를 저장할 때 내부적으로 세기(Century), (Year), (Month), (Day), (Hours), (Minutes), (Seconds)와 같은 숫자 형식으로 변환하여 저장
-
날짜는 여러 가지 형식으로 출력이 되고 날짜 계산에도 사용되기 때문에 그 편리성을 위해서 숫자형으로 저장
-
덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능하다. , 날짜에 숫자 상수를 더하거나 뺄 수 있다.

예제 사원(EMP) 테이블의 입사일자에서 년, , 일 데이터를 각각 출력한다. 아래 4개의 SQL 문장은 같은 기능을 하는 SQL 문장이다.

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


SELECT ENAME, HIREDATE,
TO_NUMBER(TO_CHAR(HIREDATE,'YYYY'))
입사년도, TO_NUMBER(TO_CHAR(HIREDATE,'MM')) 입사월,
TO_NUMBER(TO_CHAR(HIREDATE,'DD'))
입사일 FROM EMP;
cf) TO_NUMBER
함수 제외시 문자형으로 출력됨 (ex: 01,02,03,...)

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

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

….

5. 변환형 함수

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

** 암시적 데이터 유형 변환의 경우 성능 저하가 발생할 수 있으며, 자동적으로 데이터베이스가 알아서 계산하지 않는 경우가 있어 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환 방법을 사용하는 것이 바람직하다.

** 변환형 함수를 사용하여 출력 형식을 지정할 때, 숫자형과 날짜형의 경우 상당히 많은 포맷이 벤더별로 제공된다.

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

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/1

6. CASE 표현

CASE 표현은 IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완하는 역할을 한다.
ANSI/ISO SQL
표준에는 CASE Expression이라고 표시되어 있는데, 함수와 같은 성격을 가지고 있으며 Oracle Decode 함수와 같은 기능을 하므로 단일행 내장 함수에서 같이 설명을 한다.

예제 일반 프로그램의 IF-THEN-ELSE-END 로직과 같다.

IF SAL > 2000
    THEN REVISED_SALARY = SAL
    ELSE REVISED_SALARY = 2000
END-IF.


SELECT ENAME,
CASE WHEN SAL > 2000 THEN SAL ELSE 2000 END REVISED_SALARY
FROM EMP;

 

ENAME

REVISED_SALARY

SMITH

2000

ALLEN

2000

WARD

2000

JONES

2975

MARTIN

2000

BLAKE

2850

CLARK

2450

SCOTT

3000

KING

5000

TURNER

2000

ADAMS

2000

JAMES

2000

FORD

3000

MILLER

2000

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

CASE Expressions Simple Case Expression Searched Case Expression 두 가지 표현법

l  Simple Case Expression

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

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

 

LOC

AREA

NEW YORK

EAST

DALLAS

CENTER

CHICAGO

CENTER

BOSTON

EAST

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

 

ENAME

SALARY_GRADE

SMITH

LOW

ALLEN

MID

WARD

MID

JONES

MID

MARTIN

MID

BLAKE

MID

CLARK

MID

SCOTT

HIGH

KING

HIGH

TURNER

MID

ADAMS

MID

JAMES

LOW

FORD

HIGH

MILLER

MID

* CASE 표현은 함수의 성질을 가지고 있으므로, 다른 함수처럼 중첩해서 사용할 수 있다.

. NVL/ISNULL 함수

NULL에 대한 특성

- 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. 0은 숫자이고, 공백은 하나의 문자이다.
-
테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함할 수 있다
.
-
널 값을 포함하는 연산의 경우 결과 값도 널 값이다. 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는 데이터인 것과 같다
.
-
결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0(Zero)으로, 문자 유형 데이터인 경우는 블랭크보다는 ‘x’ 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많다.

 

 

l  Oracle의 경우 NVL 함수를 사용한다.

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

SELECT NVL(NULL, 'NVL-OK') NVL_TEST FROM DUAL;

 

NVL_TEST

NVL-OK


SELECT NVL('Not-Null', 'NVL-OK') NVL_TEST FROM DUAL;

NVL_TEST

Not-Null

l  SQL Server의 경우 ISNULL 함수를 사용한다.

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

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

ISNULL_TEST
 ---------
 NVL-OK

 

 

예제 선수 테이블에서 성남 일화천마(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 문장으로 표현할 수 있다

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


선수명   POSITION  포지션
--------       ---------     ------
차경복        DF           DF
정학범                       없음
안익수                       없음
차상광                       없음
권찬수         GK           GK
정경두         GK           GK
정해운         GK           GK
양영민         GK           GK
가이모토      DF            DF
정두영          DF             DF
정명휘         DF             DF
정영철         DF             DF
곽치국        MF             MF
정상식        MF            MF
서관수        FW            FW
김성운        FW            FW
김정운        FW            FW
장동현        FW             FW
45
개의 행이 선택되었다.

 

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

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

사원명

월급

커미션

연봉A

연봉B

SMITH

800

 

 

9600

ALLEN

1600

300

19500

19500

WARD

1250

500

15500

15500

JONES

2975

 

 

35700

MARTIN

1250

1400

16400

16400

BLAKE

2850

 

 

34200

CLARK

2450

 

 

29400

SCOTT

3000

 

 

36000

KING

5000

 

 

60000

TURNER

1500

0

18000

18000

ADAMS

1100

 

 

13200

JAMES

950

 

 

11400

FORD

3000

 

 

36000

MILLER

1300

 

 

15600

실행 결과에서 월급에 커미션을 더해서 연봉을 계산하는 산술식이 있을 때 커미션에 NULL 값이 있는 경우 커미션 값에 NVL() 함수를 사용하지 않으면 연봉A의 계산 결과가 NULL이 되어서 잘못 계산한 결과를 확인할 수 있다. 따라서 연봉B 결과와 같이 NVL(COMM,0)처럼 NULL 값을 0으로 변환하여 연봉을 계산해야 하는 것이다.

. NULL과 공집합
  • 일반적인 NVL/ISNULL 함수 사용

예제 매니저에 NULL이 들어있는 KING의 매니저를 출력한다.

SELECT MGR FROM EMP
WHERE ENAME='KING';

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

 

  • 공집합의 NVL/ISNULL 함수 사용

SELECT 1 FROM DUAL WHERE 1 = 2; 와 같은 조건이 대표적인 공집합을 발생시키는 쿼리이며, 위와 같이 조건에 맞는 데이터가 한 건도 없는 경우를 공집합이라고 하고, NULL 데이터와는 또 다르게 이해해야 한다.

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

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

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

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

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

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

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

MGR
-----
1
개의 행이 선택되었다.
빈 칸으로 표시되었지만 실 데이터는 NULL이다.
다른 함수와 달리 집계 함수와 Scalar Subquery의 경우는 인수의 결과 값이 공집합인 경우에도 NULL을 출력한다.

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

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

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

Oracle SQL*PLUS 같이 화면에서 데이터베이스와 직접 대화하는 환경이라면, 화면상에서데이터를 찾을 수 없다.”라는 문구로 공집합을 구분할 수 있지만, 다른 개발 언어 내에 SQL 문장이 포함된 경우에는 NULL과 공집합을 쉽게 구분하기 힘들다. 개발자들은 NVL/ISNULL 함수를 사용해야 하는 경우와, 집계 함수를 포함한 NVL/ISNULL 함수를 사용해야 하는 경우와, 1 7절에서 설명할 NVL/ISNULL 함수를 포함한 집계 함수를 사용하지 않아야 될 경우까지 잘 이해해서 NVL/ISNULL 함수를 정확히 사용해야 한다.

. NULLIF

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

NULLIF (EXPR1, EXPR2)

. 기타 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;


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

 

 

 

 

 

  • SQL 전문가 (bysql.net 2012년 1차 스터디)
  • 작성자: 이주영 (suspace)
  • 최초작성일: 2012년 03월 11 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^