[SQLD 도전기] SQL 기본(6)

데이터베이스/SQLD

[SQLD 도전기] SQL 기본(6) - 함수(FUNCTION)

썬키 2022. 10. 18. 13:31

본문 내용은 모두 필자가 공부하며 직접 정리한 내용입니다.

Kdata 데이터자격검정의 SQL 개발자(SQLD) 가이드를 참고하였습니다.

─지난 글 보기─

[SQLD 도전기] SQL 기본(4) - TCL(Transaction Control Language)

본문 내용은 모두 필자가 공부하며 직접 정리한 내용입니다. Kdata 데이터자격검정의 SQL 개발자(SQLD) 가이드를 참고하였습니다. ─지난 글 보기─ [SQLD 도전기] SQL 기본(3) - DML(Data Manipulation Language)

itsmesunky.tistory.com

과목 2 SQL 기본 및 활용

제 1장 SQL 기본

제 6절 함수(FUNCTION)

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

ㆍ데이터베이스를 설치하면 기본적으로 제공

ㆍSQL을 더욱 강력하게 해주고 데이터 값을 간편하게 조작하는데 사용

ㆍ단일행 함수(Single-Row Function) : 다시 함수의 입력 값이 단일행 값이 입력

ㆍ다중행 함수(Multi-Row Function) : 여러 행의 값이 입력

예) 집계 함수, 그룹 함수, 윈도우 함수

ㆍ함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1 관계

ㆍ단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 표현

ㆍ다중행 함수의 경우, 여러 레코드의 값들을 입력 인수로 사용하는 것

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

- 단일행 함수의 특징

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

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

ㆍ여러 인자(Argument)를 입력해도 단 하나의 결과만 리턴한다.
ㆍ함수의 인자로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있다.

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

2. 문자형 함수

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

ㆍ몇몇 문자형 함수의 경우, 결과를 숫자로 리턴하는 함수도 있다.

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

 [예제 및 실행 결과] Oracle 
 SELECT LENGTH('SQL Expert') 
 FROM DUAL; 

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

- DUAL 테이블의 특성

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

ㆍSELECT - FROM ~ 의 형식을 갖추기 위한 일종의 DUMMY 테이블이다.

ㆍDUMMY라는 문자열 유형의 칼럼에 'X'라는 값이 들어 있는 행을 1건 포함하고 있다.

3. 숫자형 함수

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

4. 날짜형 함수

ㆍDATE 타입의 값을 연산하는 함수

5. 변환형 함수

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

ㆍ암시적 데이터 유형 변환의 경우 성능 저하가 발생할 수 있으며, 자동적으로 데이터베이스가 알아서

계산하지 않는 경우가 있어 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환 방법을 사용하는 것이 바람직하다.

ㆍ명시적 데이터 유형 변환에 사용되는 대표적인 변환형 함수는 다음과 같다.

6. CASE 표현

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

[예제]
 일반 프로그램의 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 표현을 하기 위해서는 조건절을 표현하는 두 가지 방법이 있고, Oracle의 경우 DECODE 함수를 사용할 수 있다.

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; 

[실행 결과]
 LOC             AREA
 ---------     --------
 NEW YORK     EAST
 DALLAS        CENTER
 CHICAGO       CENTER
 BOSTON        EAST
 4개의 행이 선택되었다.

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

다음 WHEN절에서 EQUI(=) 조건 포함 여러 조건을 이용한 조건절을 사용할 수 있기 때문에

SIMPLE_CASE_EXPRESSION 보다 훨씬 다양한 조건을 적용할 수 있는 장점이 있다.

CASE
     WHEN CONDITION THEN RETURN_EXPR
     ELSE 표현절
 END

[예제]
 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
 14개의 행이 선택되었다.

7. NULL 관련 함수

가. NVL/ISNULL 함수

- NULL에 대한 특성

ㆍ널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다.

0은 숫자이고, 공백은 하나의 문자이다.

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

ㆍ널 값을 포함하는 연산의 경우 결과 값도 널 값이다.

모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는 데이터인것과 같다.

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

ㆍ칼럼 간 계산을 수행하는 경우 NULL 값이 존재하면 해당 연산 결과가 NULL 값이 되므로 원하는 결과를 얻을 수 없는 경우가 발생

ㆍ이런 경우는 NVL 함수를 사용하여 숫자인 0(Zero)으로 변환을 시킨 후 계산을 해서 원하는 데이터를 얻는다.

나. NULL과 공집합

- 일반적인 NVL/ISNULL 함수 사용

[예제] 매니저가 NULL인 경우 빈칸이 아닌 9999로 출력하기 위해 NVL/ISNULL 함수를 사용한다.

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

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

ㆍSELECT 1 FROM DUAL WHERE 1 = 2; 와 같은 조건이 대표적인 공집합을 발생시키는 쿼리

ㆍ위와 같이 조건에 맞는 데이터가 한 건도 없는 경우를 공집합이라고 하고, NULL 데이터와는 또 다르게 이해해야 한다.

[예제 및 실행 결과]
 SELECT MGR
 FROM EMP
 WHERE ENAME='JSC';
 데이터를 찾을 수 없다.
 ☞ EMP 테이블에 ENAME이‘JSC’란 사람은 없으므로 공집합이 발생한다.
 
 [예제 및 실행 결과]
 SELECT NVL(MGR, 9999) MGR
 FROM EMP
 WHERE ENAME='JSC';
 데이터를 찾을 수 없다.
 ☞ 많은 분들이 공집합을 NVL/ISNULL 함수를 이용해서 처리하려고 하는데, 인수의 값이 공집합인 경우는 NVL/ISNULL 함수를 사용해도 역시 공집합이 출력된다.
 ☞ NVL/ISNULL 함수는 NULL 값을 대상으로 다른 값으로 바꾸는 함수이지 공집합을 대상으로 하지 않는다.
 
 [예제 및 실행 결과]
 SELECT MAX(MGR) MGR FROM EMP WHERE ENAME='JSC';
 MGR
 -----

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

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

다. NULLIF

ㆍNULLIF 함수는 EXPR1이 EXPR2와 같으면 NULL을, 같지 않으면 EXPR1을 리턴한다.

ㆍ특정 값을 NULL로 대체하는 경우에 유용하게 사용할 수 있다.

NULLIF (EXPR1, EXPR2)

라. 기타 NULL 관련 함수 (COALESCE)

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

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

다음에는 GROUP BY, HAVING 절에 관한 내용을 정리하여 포스팅하겠습니다.

저작자표시