SQLD - 과목2. (6) 함수 (FUNCTION)

티스토리 뷰

SQL/SQLD 2과목

SQLD - 과목2. (6) 함수 (FUNCTION)

채애 2021. 3. 16. 20:02

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

함수는 다양한 기준으로 분류된다.

* Built - in Function (내장함수) - 벤더에서 제공하는 함수

* User Defined Function - 사용자가 정의할 수 있는 함수

▷ 내장 함수

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

벤더별로 큰 차이를 보여도, 핵심적인 기능들은 이름, 표현법이 다를 뿐 대부분의 데이터베이스가 공통적으로 제공한다.

내장함수는 단일행 함수 , 다중행 함수로 나뉜다.

※ 단일행 함수 / 다중행 함수

단일행 함수 ( Single - Row Function )	다중행 함수 ( Multi - Row Function )
다시 함수의 입력 값이 단일행 값이 입력되는 함수	여러 행의 값이 입력되는 함수

다중행 함수는 집계함수 , 그룹 함수 , 윈도우 함수로 나뉜다.

※ 집계 함수 / 그룹 함수 / 윈도우 함수

다중행 함수
집계 함수 ( Aggregate Function )	그룹 함수 ( Group Function )	윈도우 함수 ( Window Function )

* 중요한 특징

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

* 입력 인수

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

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

단일행 함수는 처리하는 데이터의 형식에 따라서 함수들을 나눌 수 있다.

< 문자형 / 숫자형 / 날짜형 / 변환형 / NULL 관련 >

Oracle 과 SQL Server 에서 같은 기능이어도 다르게 표현되는 함수들이 있다.

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

- 인자 ( Argumant )

2. 문자형 함수

▷ 문자형 함수

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

몇몇 문자형 함수는 결과를 숫자로 리턴하는 함수가 있다.

< 문자형 함수 종류 >

문자형 함수	함수 설명
LOWER(문자열)	문자열의 알파벳 문자를 소문자로 바꾸어 준다.
UPPER(문자열)	문자열의 알파벳 문자를 대문자로 바꾸어 준다.
ASCII(문자)	문자나 숫자를 ASCII 코드 번호로 바꾸어 준다.
CHR/CHAR(ASCII번호)	ASCII 코드 번호를 문자나 숫자로 바꾸어 준다.
CONCAT(문자열1, 문자열2)	Oracle, My SQL 에서 유효한 함수이며, 문자열1과 문자열2를 연결한다. 합성연산자 ' \| \| ' (Oracle) 나 ' + ' (SQL Server) 와 동일하다.
SUBSTR / SUBSTRING (문자열, m[, n ])	문자열 중 m 위치에서 n개의 문자 길이에 해당하는 문자를 돌려준다. n이 생략되면 마지막 문자까지이다.
LENGTH / LEN (문자열)	문자열의 개수를 숫자값으로 돌려준다.
LTRIM(문자열 [, 지정문자])	문자열의 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자를 제거한다. (지정 문자가 생략되면 공백 값이 디폴트) SQL Server 에서는 LTRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
RTRIM(문자열 [, 지정문자])	문자열의 마지막 문자부터 확인해서 지정 문자가 나타나는 동안 해당 문자를 제거한다. (지정 문자가 생략되면 공백 값이 디폴트) SQL Server 에서는 RTRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
TRIM ([leading \| trailing \| both] 지정문자 FROM 문자열)	문자열의 머리말, 꼬리말, 또는 양쪽에 있는 지정 문자를 제거한다. ( leading \| trailing \| both 가 생략되면 both 가 디폴트 ) SQL Server 에서는 TRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.

* 주 : Oracle 함수 / SQL Server 함수 표시

' / ' 없으면 공통 함수

< 단일행 문자형 함수 사용 예시 >

(예시)

① ' SQL Expert ' 라는 문자형 데이터의 길이를 구하는 문자형 함수?

SELECT LENGTH('SQL Expert') FROM DUAL;

② 선수 테이블에서 "축구선수"라는 문구를 추가한다.

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

# Oracle
SELECT PLAYER_NAME || ' 축구선수' AS 선수명 FROM PLAYER;

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

③ 경기장의 지역번호와 전화번호를 합친 번호의 길이를 구하라.

연결연산자의 결과가 LENGTH/LEN 함수의 인수가 된다.

#Oracle
SELECT LENGTH(DDD||TEL) AS T_LEN FROM STADIUM;

#SQL Server
SELECT LEN(DDD+TEL) AS T_LEN FROM STADIUM;

3. 숫자형 함수

▷ 숫자형 함수

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

< 숫자형 함수 종류 >

숫자형 함수	함수 설명
ABS(숫자)	숫자의 절대값을 돌려준다.
SIGN(숫자)	숫자가 양수인지, 음수인지, 0인지 구별한다.
MOD(숫자1, 숫자2)	숫자1을 숫자2로 나누어 나머지 값을 리턴한다. MOD 함수는 %연산자로도 대체 가능함. ( 숫자1 % 숫자2 )
CEIL/CEILING(숫자)	숫자보다 크거나 같은 최소 정수를 리턴한다.
FLOOR(숫자)	숫자보다 작거나 같은 최대 정수를 리턴한다.
ROUND(숫자 [, m])	숫자를 소수점 m 자리에서 반올림하여 리턴한다. m이 생략되면 디폴트 값은 0이다.
TRUNC(숫자 [, m])	숫자를 소수점 m 자리에서 잘라서 버린다. m이 생략되면 디폴트 값은 0이다. ( SQL Server 에서 TRUNC 함수는 제공되지 않는다. )
SIN, COS, TAN, ...	숫자의 삼각함수 값을 리턴한다.
EXP( ), POWER( ), SQRT( ), LOG( ), LN( )	숫자의 지수, 거듭 제곱, 제곱근, 자연 로그 값을 리턴한다.

< 단일행 숫자형 함수 사용 예시 >

(예시)

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

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

ROUND 함수는 소수점 1 자리에서 반올림하고, TRUNC 함수는 소수점 1 자리에서 잘라서 버린다. (내림)

② 정수 기준으로 반올림 및 올림하여 출력한다.

SELECT ROUND(SAL/12), CEILING(SAL/12) FROM EMP;

ROUND 함수의 m자리가 생략되면서 디폴트값이 0으로 정수만 나온다. (반올림)

CEILING 함수는 숫자보다 크거나 같은 최소 정수를 리턴하므로, 올림이 된다. (올림)

4. 날짜형 함수

▷ 날짜형 함수

: DATE 타입의 값을 연산하는 함수이다.

Oracle 의 TO_NUMBER(TO_CHAR( )) 함수의 경우 변환형 함수로 구분할 수도 있으나,

SQL Server 의 YEAR, MONTH, DAY 함수와 매핑하기 위하여 날짜형 함수로 본다.

EXTRACT/DATEPART 는 같은 기능을 하는 Oracle 내장 함수와 SQL Server 내장 함수를 표현한 것이다.

< 날짜형 함수 종류 >

날짜형 함수	함수 설명
SYSDATE / GETDATE( )	현재 날짜와 시각을 출력한다.
EXTRACT('YEAR' \| 'MONTH' \| 'DAY' from d) / DATEPART(''YEAR' \| 'MONTH' \| 'DAY' , d)	날짜 데이터에서 년/월/일 데이터를 출력할 수 있다. 시간/분/초 도 가능함.
TO_NUMBER(TO_CHAR(d, 'YYYY')) / YEAR(d), TO_NUMBER(TO_CHAR(d, 'MM')) / MONTH(d), TO_NUMBER(TO_CHAR(d, 'DD')) / DAY(d)	날짜 데이터에서 년/월/일 데이터를 출력할 수 있다. TO_NUMBER 함수 제외시 문자형으로 출력됨.

DATE 변수가 데이터베이스에 저장되는 방법은,

데이터베이스는 날짜를 저장할 때 세기(Century), 년(Year), 월(Month), 일(Day), 시(Hours), 분(Minutes), 초(Seconds) 같은 숫자 형식으로 변환하여 저장한다.

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

데이터베이스는 날짜를 숫자로 저장하기 때문에 산술 연산자 (덧셈,뺄셈) 로도 계산이 가능하다.

* 날짜 연산

(예시)

① 현재의 날짜 데이터를 확인하는 함수 사용.

Oracle 의 SYSDATE 함수 / SQL Server 의 GETDATE( ) 함수

# Oracle
SELECT SYSDATE FROM DUAL;

# SQL Server
SELECT GETDATE() AS CURRENTTIME;

② 사원 테이블의 입사일지에서 년도 데이터를 출력한다.

# Oracle
SELECT EXTRACT(YEAR FROM HIREDATE) 입사년도 FROM EMP;
SELECT TO_NUMBER(TO_CHAR(HIREDATE,'YYYY') 입사년도 FROM EMP;

# SQL Server
SELECT DATEPART(YEAR, HIREDATE) 입사년도 FROM EMP;
SELECT YEAR(HIREDATE) 입사년도 FROM EMP;

( 4개의 SQL 문장은 모두 같은 기능을 한다. )

5. 변환형 함수

▷ 변환형 함수

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

< 데이터 유형 변환의 종류 (2가지) >

종류	설명
명시적 (Explicit) 데이터 유형 변환	데이터 변환형 함수로 데이터 유형을 변환하도록 명시해 주는 경우
암시적 (Implicit) 데이터 유형 변환	데이터 베이스가 자동으로 데이터 유형을 변환하여 계산하는 경우 ( 성능 저하 / 에러 가 발생할 수 있음 )

< 변환형 함수의 종류 >

변환형 함수 - ORACLE	함수 설명
TO_NUMBER(문자열)	alphanumeric 문자열을 숫자로 변환한다.
TO_CHAR(숫자 \| 날짜 [, FORMAT])	숫자나 날짜를 주어진 FORMAT 형태로 문자열 타입으로 변환한다.
TO_DATE(문자열 [, FORMAT])	문자열을 주어진 FORMAT 형태로 날짜 타입으로 변환한다.

변환형 함수 - SQL SERVER	함수 설명
CAST (expression AS data_type [(length)])	expression 을 목표 데이터 유형으로 변환한다.
CONVERT (data_type [(length)], expression [, style ])	expression 을 목표 데이터 유형으로 변환한다.

(예시)

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

# Oracle
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') 날짜, TO_CHAR(SYSDATE, 'YYYY.MON, DAY') 문자형 FROM DUAL;

# OUTPUT
2021/03/15
2021.3월, 월요일

# SQL Server
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS CURRENTDATE;

# OUTPUT
2021/03/15

② 금액을 달러와 원화로 표시한다.

# Oracle
SELECT TO_CHAR(123456789/1200, '$999,999,999.99') 환율반영달러,
       TO_CHAR(123456789, 'L999,999,999') 원화
       FROM DUAL;
       
// L은 로칼 화폐 단위를 의미.

6. CASE 표현

▷ CASE 표현

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

CASE 표현을 하기 위한 조건절을 표현하는 방법은 두 가지 이다.

Oracle 은 DECODE 함수를 사용할 수 있다.

CASE 표현은 함수의 성질을 가지고 있어서 다른 함수처럼 중첩 사용이 가능하다.

< 단일행 CASE 표현의 종류 >

CASE 표현	함수 설명
CASE SIMPLE_CASE_EXPRESSION 조건 ELSE 표현절 END	SIMPLE_CASE_EXPRESSION 조건이 맞으면, 조건내의 THEN 절을 수행하고, 조건이 맞지 않으면, ELSE 절을 수행한다.
CASE SEARCHED_CASE_EXPRESSION 조건 ELSE 표현절 END	SEARCHED_CASE_EXPRESSION 조건이 맞으면, 조건내의 THEN 절을 수행하고, 조건이 맞지 않으면, ELSE 절을 수행한다.
DECODE(표현식, 기준값1, 값1 [, 기준값2, 값2, ... , 디폴드 값)	Oracle 에서만 사용되는 함수. 표현식의 값이 기준값1 이면, 값1을 출력하고 기준값2 이면, 값2를 출력한다. 기준값이 없으면 디폴드 값을 출력한다. SIMPLE_CASE_EXPRESSION 조건과 동일하다.

① IF-THEN-ELSE 논리를 구현하는 CASE Expression 은

SIMPLE CASE Expression 과 Searched CASE Expression 두 가지 표현법 중에 하나를 선택해서 사용한다.

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

② SIMPLE CASE Expression )

WHEN 절에서 앞에서 정의한 칼럼이나 표현식과 같은지 아닌지 판단하는 문장으로 = 조건만 사용한다면,

Searched CASE Expression 보다 간단하게 사용할 수 있는 장점이 있다.

Oracle의 DECODE 함수와 기능면에서 동일하다.

CASE EXPR WHEN COMPARISION_EXPR THEN RETURN_EXPR ELSE 표현절 END

(예시)

SIMPLE CASE Expression : 부서 정보에서 부서 위치를 미국의 동부, 중부, 서부로 구분하라.

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;

③ Searched CASE Expression )

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

다음 WHEN 절에서 = 조건 포함 여러 조건 (부등호) 을 이용한 조건절을 사용할 수 있어서

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

CASE WHEN CONDITION THEN RETURN_EXPR ELSE 표현절 END

(예시)

Searched CASE Expression : 사원정보에서 급여가 3000 이상이면 상등급,

1000 이상이면 중등급,

1000 미만이면 하등급으로 분류하라.

SELECT CASE WHEN SAL >= 3000 THEN 'HIGH'
            WHEN SAL >= 1000 THEN 'MID'
            WHEN SAL < 1000 THEN 'LOW'
END AS SALARY_GRADE FROM EMP;

6. NULL 관련 함수

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

① NVL / ISNULL 함수

NVL / ISNULL 함수는 산술적인 계산에서 데이터 값이 NULL 일 경우 유용하게 사용된다.

계산을 수행하려는데 NULL 값이 존재하면 해당 연산 값도 NULL 값이 된다.

이런 경우 NVL 함수를 사용한다.

숫자인 0 으로 변환을 시킨 후 계산을 한다.

< NULL 함수의 종류 >

일반형 함수	함수 설명
NVL(표현식1, 표현식2) / ISNULL (표현식1, 표현식2)	표현식1의 결과값이 NULL 이면 표현식2의 값을 출력한다. ( 결과 데이터 타입이 같아야 한다 )
NULLIF(표현식1, 표현식2)	표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴한다.
COALESCE(표현식1, 표현식2, ... )	임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이면 NULL을 리턴한다.

Oracle 의 경우 NVL 함수 / SQL Server 의 경우 ISNULL 함수를 사용한다.

(예시)

① 선수 테이블에서 K08 소속 선수의 이름과 포지션을 출력하는데, 포지션이 없는 경우는 '없음'으로 표시한다.

# Oracle
SELECT POSITION, NVL(POSITION,'없음') 포지션 FROM PLAYER WHERE TEAM_ID = 'K08';

# SQL Server
SELECT POSITION, ISNULL(POSITION,'없음') 포지션 FROM PLAYER WHERE TEAM_ID = 'K08';

② NVL 함수와 ISNULL 함수를 사용한 SQL 문장은 벤더 공통적으로 CASE 문장으로 표현할 수 있다.

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

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

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

연봉 A : (SAL * 12) + COMM

연봉 B : (SAL * 12) + NVL(COMM, 0)

연봉 A 는, COMM에 NULL값이 있는 경우 COMM 값에 NVL( ) 함수를 사용하지 않아서 계산 결과가 잘못 나온다.

연봉 B 처럼 NVL(COMM, 0) 로 NULL 값을 0으로 변환 하여 연봉을 계산해야 한다.

곱셈을 사용해야 하는 경우에는 NVL(COMM, 1) 을 하면 된다.

② NULL 과 공집합

(a) 일반적인 NVL / ISNULL 함수 사용

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

NVL 함수로 NULL 을 9999 으로 변경했다.

(b) 공집합의 NVL / ISNULL 함수 사용

SELECT MGR FROM EMP WHERE ENAME = 'JSC';

위 코드의 데이터를 찾을 수 없다.

EMP 테이블에 ENAME 이 'JSC' 라는 사람이 없으므로 공집합이 발생한다.

* SELECT 1 FROM DUAL WHERE 1 = 2;

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

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

위 코드의 데이터를 찾을 수 없다.

인수의 값이 공집합인 경우는 NVL / ISNULL 함수를 사용해도 역시 공집합이 출력된다.

( 해당 함수는 NULL값을 다른 값으로 바꾸는 함수일 뿐, 공집합을 대상으로 하지 않는다. )

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

위 코드의 데이터는 빈 칸으로 표시되었지만, 실 데이터는 NULL 이다.

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

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

공집합이면 NVL 함수를 사용해도 공집합이 출력된다.

따라서 그룹함수와 NVL 함수를 같이 사용해서 처리한다.

그룹함수를 NVL 함수의 인자로 사용해서 인수의 값이 공집합인 경우에도 9999 라는 값으로 변환된다.

NVL / ISNULL 함수를 사용하는 경우와, 집계함수를 포함한 NVL / ISNULL 함수를 사용하는 경우를 정확히 알아야 한다.

③ NULLIF

NULLIF (EXPR1, EXPR2)

NULLIF 함수는 EXPR1 과 EXPR2 가 같으면 NULL 을,

같지 않으면 EXPR1 을 리턴한다.

(예시)

① 사원 테이블에서 MGR 와 1000 이 같으면 NULL 을 표시, 같지 않으면 MGR 을 표시한다.

SELECT MGR, NULLIF(MGR, 1000) NUIF FROM EMP;

SELECT MGR, CASE WHEN MGR = 7698 THEN NULL
            ELSE MGR END
FROM EMP;

NULL 이 표시되면, MGR 의 값이 1000 이었던 것이다.

④ 기타 NULL 관련 함수 ( COALESCE )

COALESCE (EXPR1, EXPR2, ...)

COALESCE 함수는 인수의 숫자가 한정되어 있지 않다.

임의의 개수 EXPR 에서 NULL 이 아닌 최초의 EXPR 을 나타낸다.

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

(예시)

① 사원 테이블에서 커미션을 1차 선택값으로, 급여를 2차 선택값으로 선택하되,

두 칼럼 모두 NULL 인 경우는 NULL 로 표시한다.

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

SELECT COMM, SAL, CASE WHEN COMM IS NOT NULL THEN COMM
                  ELSE (CASE WHEN SAL IS NOT NULL THEN SAL
                        ELSE NULL END) COAL
FROM EMP;

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

'SQL > SQLD 2과목' 카테고리의 다른 글

SQLD - 과목2. (8) ORDER BY 절 (0)	2021.03.18
SQLD - 과목2. (7) GROUP BY, HAVING 절 (0)	2021.03.18
SQLD - 과목2. (5) WHERE 절 (0)	2021.03.11
SQLD - 과목2. (4) TCL (0)	2021.03.08
SQLD - 과목2. (3) DML (0)	2021.03.05

공지사항

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday

링크

TAG more

« 2025/04 »
일	월	화	수	목	금	토
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

글 보관함

채애가 최애

티스토리 뷰