지구정복

[SQLD] 17. 함수 (Function) 본문

자격증 정복/SQLD

[SQLD] 17. 함수 (Function)

nooh._.jl 2020. 11. 10. 09:13
728x90
반응형

1. 함수

함수는 다음과 같이 나뉠 수 있다.

  1. 내장 함수: 벤더에서 제공되는 함수
    1. 단일행 함수: 함수의 입력 값이 단일행 값이 입력되는 함수(Single-Row Function)
    2. 다중행 함수: 함수의 입력 값이 여러 행 값이 입력되는 함수(Multi-Row Function)
      1. 집계 함수
      2. 그룹 함수
      3. 윈도우 함수
  2. 사용자 정의 함수: 사용자가 정의할 수 있는 함수

2. 단일행 함수

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

가. 문자형 함수

  • 종류
  • 예제
  • LENGTH(Oracle vs Mssql)
    Oracle SELECT LENGTH('SQL Expert') FROM DUAL; result = 10
    Mssql SELECT LEN('SQL Expert') result = 10
  • CONCAT(Oracle vs Mssql)
    Oracle SELECT CONCAT('SQL', ' Expert') FROM DUAL; result = SQL Expert
    Mssql SELECT 'SQL'+ ' Expert' result = SQL Expert
  • LENGTH + CONCAT(Oracle vs Mssql)
    Oracle SELECT LENGTH(CONCAT('SQL', ' Expert')) FROM DUAL; result = 10
    Mssql SELECT LEN('SQL'+ ' Expert') result = 10
  • DUAL 테이블의 특성
  • 사용자 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블이다.
  • SELECT ~ FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블이다.
  • DUMMY라는 문자열 유형의 칼럼에 'X' 라는 값이 들어 있는 행을 1 건 포함하고 있다.
  • mssql에서는 dual 없음*

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

나. 숫자형 함수

  • 종류
  • 예제
  • ROUND(Oracle vs Mssql)
    Oracle SELECT ROUND(33.4567, 3) FROM DUAL; result = 33.4567
    Mssql SELECT ROUND(33.4567, 3) result = 33.457
  • TRUNC(Oracle vs Mssql)
    Oracle SELECT TRUNC(33.4567) FROM DUAL; result = 33
    Mssql SELECT ROUND(33.4567, 0, 3) result = 33
    Mssql SELECT ROUND(33.4567, 0, 1) result = 33
    Mssql SELECT ROUND(33.4567, 0, 0) result = 33
    Mssql SELECT ROUND(33.4567, 1, 1) result = 33.4
  • ROUND 함수의 인자(PARAMETER)중
    첫 번째 인자 : 반올림 / 절삭 하려는 수
    두 번째 인자 : 반올림 변수(편의상 반올림 변수라 함)
    (세 번째 인자 ) : 0인 경우 반올림 처리, 0이 아닌 아무 정수면 절삭처리
    -> 양수이면 그에 해당하는 소수점 아래까지 반올림하고 음수이면 그에 해당하는 소수점 위쪽까지 반올림한다.

다. 날짜형 함수

  • 종류
  • 연산
  • SYSDATE(Oracle vs Mssql)
    Oracle SELECT SYSDATE FROM DUAL result = 13/10/08
    Mssql SELECT GETDATE() result = 2013-10-10 15:05:47.983
  • SYSDATE(Oracle vs Mssql)
    Oracle SELECT TO_NUMBER(TO CHAR(SYSDATE,'yyyy')) year，TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) month，TO_NUMBER(TO CHAR(SYSDATE, 'DD')) day FROM DUAL result = 2013, 10, 10
    Mssql SELECT DATEPART(YEAR, getdate()), DATEPART(MONTH, getdate()), DATEPART(DAY, getdate()) result = 2013, 10, 10

라. 변환형 함수

-> 변환형 함수는 특정 데이터 타입을 다OJ'한 형식으로 출력하고 싶을 경우에 사용되는 함수 이며 변환형 함수는 크게 두 가지 방식이 있다.

  • 변환 유형
  • 종류
  • 특정형태로 날짜 변환(Oracle vs Mssql)
    Oracle SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD')FROM DUAL result = 2013/10/08
    Mssql SELECT CONVERT(VARCHAR(10),GETDATE() ,111) result = 2013/10/08
  • mssql 변환
    함수 결과
    CONVERT(VARCHAR, GETDATE(), 1) 10/10/13
    CONVERT(VARCHAR, GETDATE(), 2) 13.10.10
    CONVERT(VARCHAR, GETDATE(), 3) 10/10/13
    CONVERT(VARCHAR, GETDATE(), 4) 10.10.13
    CONVERT(VARCHAR, GETDATE(), 5) 10-10-13
    CONVERT(VARCHAR, GETDATE(), 8) 11:00:34
    CONVERT(VARCHAR, GETDATE(), 9) 10 10 2013 11:00:39:573AM
    CONVERT(VARCHAR,GETDATE(), 13) 10 10 2013 10:34:27:573
    CONVERT(VARCHAR, GETDATE(), 14) 10:34:48:080
    CONVERT(VARCHAR, GETDATE(), 20) 2013-10-10 10:35:15
    CONVERT(VARCHAR, GETDATE(), 21) 2013-10-10 10:56:37.400
    CONVERT(VARCHAR, GETDATE(), 22) 10/10/13 10:56:53 AM
    CONVERT(VARCHAR, GETDATE(), 23) 2013-10-10
    CONVERT(VARCHAR, GETDATE(), 10) 2013-10-10 10:58:32.180
    CONVERT(VARCHAR, GETDATE(), 101) 10/10/2013
    CONVERT(VARCHAR, GETDATE(), 102) 2013.10.10
    CONVERT(VARCHAR, GETDATE(), 103) 10/10/2013
    CONVERT(VARCHAR, GETDATE(), 104) 10.10.2013
    CONVERT(VARCHAR, GETDATE(), 105) 10-10-2013
    CONVERT(VARCHAR, GETDATE(), 111) 2013/10/10
    CONVERT(VARCHAR, GETDATE(), 112) 20131010
    CONVERT(VARCHAR, GETDATE(), 120) 2013-10-10 10:59:28
    CONVERT(VARCHAR, GETDATE(), 121) 2013-10-10 10:59:35.057

 

마. CASE 표현

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

  1. CASE Expressions
    -> 다음의 두 가지 표현법 중에 하나를 선택해서 사용하게 된다.
  • Simple Case Expression
  • Searched Case Expression
CASE SIMPLE_CASE_EXPRESSION 조건 or SEARCHED_CASE_EXPRESSION 조건 ELSE 표현절 END

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

  • Simple Case Expression 예
    Case CASE LOC WHEN 'NEW YORK' THEN 'EAST'
    ELSE 'ETC'
    END as AREA
  • Decode 예
    Decode DECODE(LOC , 'NEW YORK', 'EAST', 'ETC')
  • Searched Case Expression 예
    Case CASE WHEN SAL > = 3000
    ELSE 'LOW'
    END AS SALARY
  • 종류

바. NULL 관련 함수

NVL/ISNULL 함수

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

    -> Null 연산시에 NVL/ISNULL 함수를 이용하여 변환하여 연산
  • 단일행 Null 관련 함수의 종류
  • Null 함수 (Oracle vs Mssql)
    Oracle NVL (NULL 판단 대상,'NULL일 때 대체값') NVL('Not-Null','NVL-OK')
    Mssql ISNULL (NULL 판단 대상,'NULL일 때 대체값') ISNULL(NULL, 'NVL-OK')

    -> 유사하게 Case문으로 표현가능

NVL 함수를 다중행 함수의 인자로 사용하는 경우는 오히려 불필요한 부하를 발생할 수 있으므로 굳이 NVL 함수를 사용할 필요가 없다.

다중행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서 일부만 NULL인 경우는 다중행 함수의 대상에서 제외한다.
ex) 100명 중 10명의 성적이 NULL 값일 때 평균을 구하는 다중행 함수 AVG를 사용하면 NULL 값이 아닌 90명의 성적에 대해서 평균값을 구하게 된다.

NULL과 공집합

  1. 일반적인 사용 예
    SELECT NVL(MGR,9999) MGR FROM EMP WHERE ENAME='KING'; result = 9999 NVL 함수로 NULL을 0으로 변경
  1. 공집합의 NVL/ISNULL 사용 예
    SELECT MAX(MGR) MGR FROM EMP WHERE ENAME='JSC'; result = 값이 없으나 조회된 data는 NULL (집계함수와 Scalar Subquery의 경우 NULL 출력)
  1. NULLIF 예
    NULLIF (EXPRl, EXPR2) NULLIF(MGR,7698) MGR = 7698이면 NULL 표시 그렇지 않으면 MGR을 표시
  1. 기타 NULL 관련 함수(COALESCE) 예
    COALESCE (EXPR1, EXPR2, ...) COALESCE(COMM, SAL) COMM이 NULL 이면 SAL를 표기

    -> 중첩 case 문으로 변경가능

    CASE WHEN COMM IS NOT NULL THEN COMM ELSE (CA8E WHEN SAL IS NOT NULL THEN SAL ELSE NULL END ) END

 

정리

------단일행함수
----문자형함수
--LENGTH : 문자열 길이 반환
SELECT LENGTH('SQL EXPERT')
FROM DUAL;

--CONCAT : 문자열 합치기
SELECT CONCAT('SQL ', 'EXPERT')
FROM DUAL;

--LENGTH + CONCAT
SELECT LENGTH( CONCAT('SQL ', 'EXPERT') )
FROM DUAL;

----숫자형함수
--ROUND : 특정 위치에서 반올림
SELECT ROUND(33.4567, 3)
FROM DUAL;

--TRUNC : 특정 위치까지만 출력하고 나머지 버림
SELECT TRUNC(33.4567, 3)
FROM DUAL;

----날짜형함수
--SYSDATE
SELECT SYSDATE
FROM DUAL;

SELECT TO_NUMBER( TO_CHAR( SYSDATE, 'YYYY' ) ) YEAR,
    TO_NUMBER( TO_CHAR( SYSDATE, 'MM' ) ) MONTH,
    TO_NUMBER( TO_CHAR( SYSDATE, 'DD' ) ) DAY
FROM DUAL;

----변환형함수
--특정 형태로 날짜 변환
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD')
FROM DUAL;


------CASE 표현
--1. 일반적인 CASE표현
SELECT ENAME, DEPTNO, 
    CASE WHEN DEPTNO = '10' THEN 'NEW YORK'
         WHEN DEPTNO = '20' THEN 'DALLAS'
         ELSE 'UNKNOWN'
    END AS LOC_NAME
FROM EMP
WHERE JOB = 'MANAGER';

--2. ELSE를 생략후 만족하는 조건이 없ㅇ면 NULL리턴
SELECT ENAME, DEPTNO, 
    CASE WHEN DEPTNO = '10' THEN 'NEW YORK'
         WHEN DEPTNO = '20' THEN 'DALLAS'
    END AS LOC_NAME
FROM EMP
WHERE JOB = 'MANAGER';

--3. 비교, 범위 연산자 등 사용
SELECT ENAME, SAL,
    CASE WHEN SAL >= 2900 THEN '1ST'
         WHEN SAL >= 2700 THEN '2ST'
         WHEN SAL >= 2000 THEN '3ST'
    END AS SAL_GRADE
FROM EMP
WHERE JOB = 'MANAGER';

--4.WHERE 절에 사용
SELECT ENAME, SAL,
    CASE WHEN SAL >= 2900 THEN '1ST'
         WHEN SAL >= 2700 THEN '2ST'
         WHEN SAL >= 2000 THEN '3ST'
    END AS SAL_GRADE
FROM EMP
WHERE JOB = 'MANAGER' AND
    (CASE WHEN SAL >= 2900 THEN 1
          WHEN SAL >= 2700 THEN 2
          WHEN SAL >= 2000 THEN 3
     END ) =1;
     
--5.오라클 내장함수를 조건으로 사용
SELECT ENAME, HIREDATE, 
    CASE WHEN TO_CHAR(HIREDATE, 'Q') = '1' THEN '1Q'
         WHEN TO_CHAR(HIREDATE, 'Q') = '2' THEN '2Q'
         WHEN TO_CHAR(HIREDATE, 'Q') = '3' THEN '3Q'
         WHEN TO_CHAR(HIREDATE, 'Q') = '4' THEN '4Q'
    END AS HIRE_QUARTER
FROM EMP
WHERE JOB = 'MANAGER';

--6.THEN 절에서 중첩 CASE문 사용
SELECT ENAME, SAL, DEPTNO,
    CASE WHEN DEPTNO = '10' THEN
            CASE WHEN SAL >= 2000 THEN '1ST'
                 WHEN SAL >= 1500 THEN '2ST'
                 WHEN SAL >= 1000 THEN '3ST'
            END
         WHEN DEPTNO = '20' THEN
            CASE WHEN SAL >= 3000 THEN '1ST'
                 WHEN SAL >= 2500 THEN '2ST'
                 WHEN SAL >= 2000 THEN '3ST'
            END
         WHEN DEPTNO = '30' THEN
            CASE WHEN SAL >= 2500 THEN '1ST'
                 WHEN SAL >= 2000 THEN '2ST'
                 WHEN SAL >= 1500 THEN '3ST'
            END
    END AS SAL_GRADE
FROM EMP
WHERE JOB = 'MANAGER';

----NULL 관련함수
--NVL(표현식1, 표현식2) : 표현식1이 NULL이면 표현식2반환, NULL아니면 표현식1반환
SELECT ENAME, NVL(MGR, 9999) MGR
FROM EMP;

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

--NVL2(표현식1, 표현식2, 표현식3) : 표현식1이 NULL이면 표현식3 반환, 아니면 표현식2반환
SELECT MGR, COMM, NVL2(MGR, 'Y', 'N'), NVL2(COMM, 'Y', 'N')
FROM EMP;


--NULLIF(표현식1, 표현식2) : 표현식1과 표현식2 비교하여 둘이 같으며 NULL반환, 아니면 표현식1반환
SELECT A.ENAME, A.JOB, B.ENAME, B.JOB, NULLIF(A.JOB, B.JOB) JOB_EQAL
FROM EMP A, EMP B;

--COALESCE(A1, A2, A3, A4 ... AN) : A1부터 AN까지 처음으로 NULL이 아닌 값 리턴
--모든 값이 NULL이면 NULL리턴
COALESCE(COMM, SAL) -- COMM이 NULL이면 SAL 리턴

--중첩 CASE 문으로 변경가능
CASE 
    WHEN COMM IS NOT NULL THEN COMM
    ELSE (CASE  
            WHEN SAL IS NOT NULL THEN SAL
            ELSE NULL
         END)
END

 

 

 

[출처]

wiki.gurubee.net/pages/viewpage.action?pageId=27427487

728x90
반응형
저작자표시 동일조건

'자격증 정복 > SQLD' 카테고리의 다른 글

[SQLD] 19. 조인 (JOIN)  (0) 2020.11.10
[SQLD] 18. GROUP BY, HAVING 절  (0) 2020.11.10
[SQLD] 16. WHERE절  (0) 2020.11.09
[SQLD] 15. TCL (Transaction Control Language)  (0) 2020.11.09
[SQLD] 14. DML (Data Manipulation Language)  (0) 2020.11.09
'자격증 정복/SQLD' Related Articles more
Comments