by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

제6절 함수(FUNCTION)




제6절  함수(FUNCTION)


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

  다양한 기준으로 분류를 할수 있는데 밴더에서 제공하는 함수인 내장 함수(Built-in Function)와 사용자가 정의할 수 있는 함수(User Defined Function)로 나눌 수 있다.
내장 함수는 다시 함수의 입력 값이 단일행 값이 입력되는 단일행 함수(Single-Row Function)와 여러 행의 값이 입력되는 다중행 함수(Multi-Row Function)로 나눌 수 있다. 다중행 함수는 다시 집계 함수(Aggregate Function), 그룹 함수(Group Function), 윈도우 함수(Window Function)로 나눌 수 있다.

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


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

 

 리턴되는 값을 예를 들어 설명한 사례 
 
 

  SQL Expert'라는 문자형 데이터의 길이를 구하는 문자형 함수를 사용한다. 

예제)--오라클
SELECT LENGTH('SQL Expert')
FROM     DUAL;
LENGTH('SQL Expert')
----------------------------------
10

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

 

DUAL 테이블 특징

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

3. 숫자형 함수

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

 
 

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

 

 데이터베이스는 날짜를 저장할 때 내부적으로 세기(Century), 년(Year), 월(Month), 일(Day), 시(Hours), 분(Minutes), 초(Seconds)와 같은 숫자 형식으로 변환하여 저장한다.
덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능하다.


 

예제)
SELECT SYSDATE FROM DUAL; => 12/07/18
SELECT GETDATE() AS CURRENTTIME; => 2012-07-18 13:10:02.047
오라클 함수
SELECT EXTRACT(YEAR FROM HIREDATE)     입사년도
                ,EXTRACT(MONTH FROM HIREDATE) 입사월
                ,EXTRACT(DAY FROM HIREDATE)        입사일
FROM     EMP;
SQL SERVER
SELECT DATEPART(YEAR, HIREDATE)     입사년도
                ,DATEPART(MONTH, HIREDATE) 입사월
                ,DATEPART(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



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

결과) 2012/07/19



금액을 달러와 원화로 표시)
--Oracle
  SELECT TO_CHAR(123456789/1200,'$999,999,999.99') 환율반영달러
                 , TO_CHAR(123456789,'L999,999,999')                 원화       --칼럼의 L999에서 L은 로칼 화폐 단위를 의미한다.
  FROM DUAL; 환율반영달러 원화

결과) $102,880.66 , 123,456,789

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

데이터)
  1. ZIP_CODE1 데이터: 700, 500, 800
  2. ZIP_CODE2 데이터: 50, 92, 40
--Oracle
   SELECT TEAM_ID
                 , TO_NUMBER(ZIP_CODE1,'999') + TO_NUMBER(ZIP_CODE2,'999') 우편번호합
    FROM    TEAM;

결과) 750, 592, 840

--SQL Server
   SELECT TEAM_ID
                  , CAST(ZIP_CODE1 AS INT) + CAST(ZIP_CODE2 AS INT) 우편번호합
   FROM TEAM; 결과) 750, 592, 840 



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

 

6-1) SIMPLE_CASE_EXPRESSION 표현방식
        CASE 다음에 바로 조건에 사용되는 칼럼이나 표현식을 표시( EQUI(=) 조건을 사용 하지 않음)

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



6-2) SEARCHED_CASE_EXPRESSION 표현방식
CASE 다음에 바로 조건에 사용되는 칼럼이나 표현식을 표시 하지 않고  EQUI(=) 조건을 사용함

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


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

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


 

7.  NULL관련 함수

    가) NVL/ISNULL 함수

     NULL 특성
    - 값이 정해져 있지 않으므로 0 , 공백, ""와는 다르다.(0:숫자, 공백:문자, "":문자)
    -테이블 생성시 PK 혹은 NOT NULL 컬럼 이외에는 모든 데이터 유형은 NULL 값을 가질수 있다.
    -NULL값을 포함한 연산을 할 경우 결과 값도 NULL이다.
    -NULL 값이 아닌 값을 얻고자 할때는 NVL, ISNULL 함수를 사용 해야 한다.
   

    NULL을 포함한 데이터 연산

  

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

예)--ORACLE
SELECT PLAYER_NAME 선수명
               , POSITION
               , NVL(POSITION,'없음') 포지션
FROM     PLAYER
WHERE TEAM_ID = 'K08'
--SQLSERVER
SELEC PLAYER_NAME 선수명
            , POSITION
            , ISNULL(POSITION,'없음') 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08'

CASE 표현)
NVL 함수와 ISNULL 함수를 사용한 SQL 문장은 벤더 공통적으로 CASE 문장으로 표현

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

  

 나) NULL과 공집합
      데이터 조건이 한건도 없는 경우를 공집합이라고 하지만 NULL값 데이터와는 다르게 이해해야 한다.

예) SELECT MGR FROM EMP WHERE ENAME='KING';
위의 쿼리에서 ENAME이 KING인 사람은 존재를 하나 MGR의 데이터의 값이 없는 경우 공집합에 해당하지 않는다.

공집합의 NVL/ISNULL 함수 사용

STEP1
    SELECT MGR FROM EMP WHERE ENAME='KANKAN';
    위의 데이터를 검색시 KANKAN의 이름을 가진 사람이 없는 경우(데이터를 찾을 수 없는 경우)이는 공집합에 해당한다.

STEP2
   SELECT NVL(MGR,'9999') FROM EMP WHERE ENAME='KANKAN';
    KANKAN의 이름을 가진 사람이 존재 하지 않으므로 데이터 내용은 출력 되지 않는다.(ISNULL/NVL은 값의 대상을 바꾸는 값이므로..)

STEP3
   SELECT NVL(MAX(MGR),'9999') FROM EMP WHERE ENAME='KANKAN';
   MAX의 값으로 검색을 할 경우 ENAME에서 KANKAN의 이름을 가진 사람이 없더라도 MAX 그룹 함수를 이용하여 NULL값을 출력 하였다.
   (1개의 레코드를 출력 하였다) 그 후 NULL 값을 NVL로 이용하여 9999로 치완하였으며 레코드 한건인 '9999' 가 출력 되었다.

Oracle의 SQL*PLUS 같이 화면에서 데이터베이스와 직접 대화하는 환경이라면, 화면상에서 "데이터를 찾을 수 없다."라는 문구로 공집합을 구분할 수 있지만, 다른 개발 언어 내에 SQL 문장이 포함된 경우에는 NULL과 공집합을 쉽게 구분하기 힘들다.

다)NULLIF
    NULLIF 함수는 EXPR1이 EXPR2와 같으면 NULL을, 같지 않으면 EXPR1을 리턴한다.
 
   예) SELECT ENAME, EMPNO, MGR, NULLIF(MGR,7698) NUIF FROM EMP;

         MGR의 데이터 값이 7698과 같은 경우는  NULL값을 출력 한다.

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

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

     COMM의 값이 NULL이면 SAL을 비교하며 SAL값도 NULL인 경우 NULL를 리턴한다. 단 NULL값이 아닌 경우는 해당 컬럼의 값을 리턴한다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 3월 21, 2013

    배성환 says:

    내장함수의역활같은거도 나올수도있겠죠??설마?? D라면 가능할뜻.. D의의지는 루피인데??? 나는 해적왕이될꺼야~!! 멘붕!!

    내장함수의역활같은거도 나올수도있겠죠??설마?? D라면 가능할뜻..
    D의의지는 루피인데??? 나는 해적왕이될꺼야~!!
    멘붕!!

    1. 3월 22, 2013

      이재현 says:

      NULLIF 나오더라

      NULLIF 나오더라

      1. 3월 22, 2013

        이가혜 says:

        다음에 쭉~~~ 한번 정리해서 알려 줘요^^ 모모 나왔었는지

        다음에 쭉~~~ 한번 정리해서 알려 줘요^^
        모모 나왔었는지

  2. 3월 22, 2013

    임상준 says:

    널이랑 결과값이 없는거랑 구분이 잘 안되는군요 프로그램에선... 첨알았네...

    널이랑 결과값이 없는거랑 구분이 잘 안되는군요 프로그램에선...
    첨알았네...

    1. 3월 22, 2013

      배성환 says:

      젤오해하기쉬운케이스.. null 로 처리했는데 왜 안나오지?? 했는데char(10) 형태로 되어있어 ' ' 이러한값이 들어가있는.. 오래된 사...

      젤오해하기쉬운케이스..
      null 로 처리했는데 왜 안나오지??
      했는데char(10) 형태로 되어있어
      ' ' 이러한값이 들어가있는..

      오래된 사이트든 이런케이스들이 꽤마늘뜻..
      특히 연계관련해서는 저렇게 강제로하기도하니..

    2. 3월 22, 2013

      안길환 says:

      NULL은 값자체가 없는 정보의 부제입니다. 그러나 ""는 값이 없는게 아닌 문자입니다. 단지 빈값으로 초기화 할분이죠. 프로그래머 개발자도 ...

      NULL은 값자체가 없는 정보의 부제입니다.
      그러나 ""는 값이 없는게 아닌 문자입니다.
      단지 빈값으로 초기화 할분이죠.
      프로그래머 개발자도 모르고 넘어가는 사람들이 많습니다.