[SQLD] SQL 기본 1-6. 함수

1. 내장 함수
내장 함수Bulit-in Function 와 사용자가 정의할 수 있는 함수 UDF

내장 함수 : 데이터 값을 간편하게 조작하는데 사용

단일행 함수
다중행 함수

다중행 함수
집계 함수
그룹 함수
윈도우 함수

-> M:1 관계
-> 단일행 함수 : 하나의 값 또는 여러 값이 입력 인수로 표현
-> 다중행 함수 : 여러 레코드의 값들을 입력 인수로 사용

단일행 함수 :
문자형 함수
문자를 입력시 문자나 숫자 값을 반환
LOWER, UPPER, SUBSTR, LENGTH, LEN, LTRIM, RTRIM, TRIM, ASCII

숫자형 함수
숫자 입력시 숫자 값 반환
ABS, MOD, ROUND, TRUNC, SIGN, CHR, CEIL, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN

날짜형 함수
DATE 타입의 값을 연산
SYSDATE EXTRACT TO_NUMBER(TO_CHAR(d, ‘YYYY’ | ‘MM’| ‘DD’))

변환형 함수
문자, 숫자 날짜형 값의 데이터 타입을 변환
TO_NUMBER, TO_CHAR, TO_DATE, CONVERT

NULL 관련 함수
NULL을 처리하기 위한 함수
NVL, NULLIF, COALESCE

단일행 함수 특징
-> SELECT WHERE ORDER BY절에 사용 가능
-> 각 행에 대해 개별적으로 작용해 데이터 값 조작하고 조작 결과 리턴
-> 여러 인자 입력시에도 하나의 결과만 리턴
-> 함수의 인자로 상수, 변수, 표현식 사용가능, 하나의 인수나 여러 개의 인수를 가질 수 있다.
-> 함수의 인자로 함수를 사용하는 함수 중첩 가능

2. 문자형 함수
문자 데이터를 매개 변수로 문자나 숫자 값의 결과를 리턴하는 함수

LOWER
UPPER
ASCII
CHR
CONCAT
SUBSTR
LENGTH
LTRIM
RTRIM
TRIM
RTRIM


LOWER
: LOWER(‘SQL Expert’) -> ‘sql expert’
UPPER
: UPPER (‘SQL Expert’) -> ‘SQL EXPERT’

ASCII
: ASCHII(‘A’) -> 65
CHR
: CHAR(65) -> ‘A’

CONCAT [Concatenation]
:CONCAT(‘RDBMS’, ‘SQL’) -> ‘RDBMS SQL’
SUBSTR
: SUBSTR(‘SQL Expert’, 5,3) -> ‘Exp’
LENGTH
:LENGTH(‘SQL Expert’) -> 10

LTRIM
: LTRIM(‘xxxYYZZxYZ’,’x’) -> ‘YYZZxYZ’
RTRIM
:RTRIM(‘XXYYZzXYzz’, ‘z’) -> ‘XXYYzzXY’
TRIM
:TRIM(‘x’ FROM ‘XxYYZZxYZxx’) -> ‘XXYYZZXYZ’
RTRIM
:RTRIM(‘XXYYZZXYZ      ‘) -> ‘XXYYZZXYZ’
-> 공백 제거 및 CHAR와 VARCHAR 데이터 유형 비교시 유용

(1) 문자형 데이터의 길이
SELECT LENGTH(‘SQL Expert’)
FROM DUAL

LENGTH(‘SQL Expert’)

DESC DUAL;

SELECT* FROM DUAL;

———————————————
SELECT LEN(‘SQL Expert’) AS ColumnLength;

(2) 문자열 합성
SELECT CONCAT(PLAYER_NAME, ‘축구선수’) 선수명
FROM PLAYER;

———————————————

SELECT PLAYER_NAME || ‘축구선수’ AS 선수명
FROM PLAYER;

=> 실제적으로 함수가 모든 행에 적용 되어 ‘~축구선수’라는 각각의 결과로 출력
즉, 일반적으로 함수는 여러 개 중첩하여 사용이 가능하며
함수 내부에 다른 함수를 사용해 안쪽에 위치해 있는 함수부터 실행
-> 그 결과 값이 바깥쪽의 함수에 인자로 사용
함수3 (함수2 (함수1 (칼럼이나 표현식 [, Arg1]) [, Arg2]) [, Arg3 ])

(3) 숫자 합성
SELECT STADIUM_ID, DDD || TEL as TEL, LENGTH(DDD || TEL) as T_LEN FROM STADIUM;

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

단일행 숫자형 함수
ABS(숫자)
: 절대값
SIGN(숫자)
: 양수, 음수, 0
MOD(숫자1, 숫자2) [modulo] = [%]
:나머지

CEIL(숫자) [ceiling]
: 올림
FLOOR(숫자) [floor]
: 내림
ROUND(숫자 [, m])
: 반올림
TRUNC(숫자 [, m]) [Truncate]
: 소수점

SIN, COS, TAN
: 사인, 코사인, 탄젠트
EXP(), POWER(), SQRT(), LOG(), LN()
: 지수, 거듭 제곱, 제곱근, 자연 로그

ABS
:ABS(-15) -> 15
SIGN
:SIGN(-20) -> -1
:SIGN(0) -> 0
:SIGN(+20) -> 1
MOD
:MOD(7,3) -> 1
:7%3 -> 1

CEIL
:CEIL(38, 123) ->  39
:CEIL(-39, 123) -> -39
FLOOR
:FLOOR(38, 123) -> 38
:FLOOR(-38, 123) -> -39
ROUND
:ROUND(38, 5235, 3) -> 38, 524
:ROUND(38, 5235, 1) -> 38, 5
:ROUND(38, 5235, 0) -> 39
:ROUND(38, 5235) -> 39 (인수 0이 Default)
TRUNC
:TRUNC(38, 5235, 3) -> 38, 523
:TRUNC(38, 5235, 1) -> 38, 5
:TRUNC(38, 5235, 0) -> 38
:TRUNC(38, 5235) -> 38 (인수 0이 Default)


4. 날짜형 함수
DATE 타입의 값을 연산하는 함수
SYSDATE
EXTRACT(‘YEAR’ | ‘MONTH’ | ‘DAY’ from d)
TO_NUMBER(TO_CHAR(d, ‘YYYY’))
TO_NUMBER(TO_CHAR(d, ‘MM’))
TO_NUMBER(TO_CHAR(d, ‘DD’))
-> TO_NUMBER가 없으면 문자형으로 출력

단일행 날짜형 데이터 연산
날짜 + 숫자 : 날짜 -> 숫자만큼 날수를 날짜에 더한다.
날짜 - 숫자 : 날짜 -> 숫자만큼 날수를 날짜에서 뺀다.
날짜1 - 날짜2 : 날짜수 -> 다른 하나의 날짜에서 하나의 날짜를 빼면 일수가 나온다.
날짜 + 숫자/24 : 날짜 -> 시간을 날짜에 더한다.

(1) 현재 날짜 데이터 확인
SELECT SYSDATE
FROM DUAL;

(2) 사원(EMP) 테이블의 입사일짜에서 년월일 데이터를 각각 출력
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;

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

명시적 데이터 유형 변환 : 데이터 변환형 함수로 데이터 유형을 변환하도록 명시해주는 경우
암시적 데이터 유형 변환 : 데이터베이스가 자동으로 데이터 유형을 변환하여 계산하는 경우
(암시적 데이터 유형 변환의 경우 성능저하나, 에러가 발생할 확률이 높다)

명시적 데이터 유형 변환 함수
TO_NUMBER
TO_CHAR(숫자 | 날짜[ ,FORMAT])
TO_DATE([문자열, FORMAT])

(1) 날짜를 정해진 문자 형태로 변환
SELECT TO_CHAR(SYSDATE, ‘YYYY/MM/DD’) 날짜,
TO_CHAR(SYSDATE, ‘YYYY, MON, DAY’) 문자형
FROM DUAL;

(2) 금액을 달러와 원화로 표시
SELECT TO_CHAR(123456789/1200, ‘$999,999,999.99’) 환율반영달러,
TO_CHAR(123456789, ‘L999.999.999’) 원화
FROM DUAL;

(3) 팀 테이블의 ZIP 코드1과 ZIP 코드2를 숫자로 변환한 후 두 항목을 더한 숫자 출력
SELECT TEAM_ID,
TO_NUMBER(ZIP_CODE1, ‘999’)+ TO_NUMBER(ZIP_CODE2, ‘999’)우편번호합
FROM TEAM;

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

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;

CASE 표현은 조건절을 표현하는 2가지 방법이 있으며, DCODE 함수를 사용하는 방법도 있다.

단일행 CASE 표현의 종류
CASE
SIMPLE_CASE_EXPRESSION 조건
ELSE 표현절
END
: 조건이 맞으면 THEN / 맞지 않으면 ELSE
—————————————————————
CASE
SEARCHED_CASE_EXPRESSION 조건
ELSE 표현절
END
: 조건이 맞으면 THEN / 맞지 않으면 ELSE
—————————————————————
DECODE(표현식, 기준값1, 값1
[, 기준값2, 값2, … , 디폴트값])
: 표현식의 값=기준값1 -> 값1 출력, 기준값2 -> 값2 출력, 기준값이 X -> Default 출력
(=SIMPLE_CASE_EXPRESSION 조건과 동일)

(1) SIMPLE_CASE_EXPRESSION
: 조건에 사용되는 칼럼이나 표현식을 표시하고, 다음 WHEN절에서 앞에서 정의한 칼럼이나 표현식과 같은지 안진지 판단하는 문장으로 EQUI(=) 조건만 사용한다면 SEARCHED_CASE_EXPRESSION보다 간단하게 사용가능

CASE
EXPR WHEN COMPARISION_EXPR
THEN RETURN_EXPR
ELSE 표현절
END

(Ex) 부서 정보에서 부서 위치를 미국의 동부, 중부, 서부로 구분
SELECT LOC,
CASE LOC
WHEN ‘NEW YORK’ THEN ‘EAST’
WHEN ‘BOSTON’ THEN ‘EAST’
WHEN ‘CAHICAGO’ THEN ‘CENTER’
WHEN ‘DALLAS’ THEN ‘CENTER’
ELSE ‘ETC’
END as AREA
FROM DEPT;

(2) SEARCED_CASE_EXPRESSION
: CASE 다음에 칼럼이나 표현식 표시 없이, WHEN 절에서 EQUI(=) 조건 포함 여러 조건을 이용한 조건절을 사용하여 다양한 조건 적용 가능

CASE
WHEN CONDITION THEN RETURN_EXPR
ELSE 조건절
END

(Ex 1) 사원 정보에서 급여가 3000이상이면 상등급, 1000 이상이면 중등급, 1000 미만이면 하등급으로 분류
SELECT ENAME
CASE WHEN SAL >= THEN ‘HIGH’
WHEN SAL >= THEN ‘MID’
ELSE ‘LOW’
END AS SALARY_GRADE
FROM EMP;

CASE 표현은 함수의 성질을 가지며, 함수처럼 중첩해 사용 가능

(Ex 2) 사원 정보에서 급여가 2000 이상이면 보너스를 1000, 1000 이상이면 5000, 1000 미만이면 0으로 계산

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 함수
NVL (NULL 판단 대상, ‘NULL일 때 대체값’)
널 값 포함 불가 : NOT NULL, PRIMARY KEY
널 값 포함 연산 결과 : NULL -> 결과 값을 다른 값으로 : NVL / ISNULL 함수 사용

산술 계산에서 데이터 값이 NULL인 경우 NVL/ISNULL 함수를 사용
칼럼 간 계산일 경우 NULL 값이 존재하는 칼럼을 NVL 함수를 사용해 0으로 변환 후 계산

단일행 NULL 관련 함수 종류
NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2)
: 표현식1의 값이 NULL이면 표현식2의 값을 출력
(단, 표현식1과 표현식2의 결과 데이터 타입이 같아야 한다.
NULLIF(표현식1, 표현식2)
: 표현식1과 표현식2이 같으면 NULL을, 같지 안흥면 표현식1을 리턴한다.

COALESE(표현식1, 표현식2, …)
: 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다.
모든 표현식이 NULL이면 NULL을 리턴

(Ex 1)
SELECT NVL(NULL, ‘NVL-OK’) NVL_TEST
FROM DUAL;
————————————————————————
SELECT NVL(‘Not-Null, ‘NVL-OK’) NVL_TEST
FROM DUAL;

(Ex 2) 선수 테이블에서 성남 일화천마(K08) 소속 선수의 이름과 포지션을 출력하는데, 포지션이 없는 경우는 ‘없음’으로 표시
SELECT PLAYER_NAME 선수명, POSITION,
CASE WHEN POSITION IS NULL
THEN ‘없음’
ELSE POSITION
END AS 포지션
FROM PLAYER
WHERE TEAM_ID=‘K08’

(Ex 3) NVL 함수와 ISNULL 함수를 사용한 SQL 문장은 CASE 문장으로 표현 가능
SELECT PLYAER_NAME 선수명, POSITION,
CASE WHEN POSITION IS NULL
THEN ‘없음’
ELSE POSITION
END AS 포지션
FROM AS 포지션
FROM PLAYER
WHERE TEAM_ID = ‘K08’

(Ex 4) 급여와 커미션을 포함한 연봉을 계산하면서 NVL 함수의 필요성
SELECT ENAME 사원명, SAL 월급, COMM 커미션,
(SAL * 12) + COMM 연봉A, (SAL * 12) + NVL(COMM, 0) 연봉 B
FROM EMP;

—————————————————————————————————
실행 결과 : 월급 + 커미션 = 연봉을 계산하는 산술식이 있을 때 커미션에 NULL 값이 있는경우
커미션 값에 NVL() 함수를 사용해 NVL(COMM, 0)처럼 NULL 값을 0으로 변환해 연봉을 계산한다.
곱셈의 경우 NVL(COMM, 1)을 한다.

NVL 함수를 다중행 함수의 인자로 사용하는 경우 불필요한 부하를 발생시킨다.

다중행 함수의 경우엔 입력 값으로 전체 건수가 NULL 값인 경우에만 결과가 NULL이며, 일부만 NULL인 경우 다중행 함수 대상에서 제외

나. NULL과 공집합
(1) 일반적인 NVL/ISNULL 함수 사용

STEP1. 정상적으로 매니저 정보를 가지고 있는 SCOTT의 매니저 출력
SELECT MGR FROM EMP WHERE ENAME=‘SCOTT’;

STEP2. 매니저에 NULL이 들어있는 KING의 매니저를 출력
SELECT MGR FROM EMP WHERE ENMAE=‘KING’;

STEP3. 매니저가 NULL인 경우 빈칸이 아닌 9999로 출력하기 위해 NVL/ISNULL 함수를 사용
SELECT NVL(MGR, 9999) MGR FROM EMP FROM WHERE ENAME=‘KING’;

(2) 공집합의 NVL/ISNULL 함수 사용
조건에 맞는 데이터가 한 건도 없는 경우에 NULL데이터와는 다른 이해가 필요

STEP1. 공집합을 발생하기 위해 존재하지 않은 ‘JSC’ 데이터 검색
SELECT MGR
FROM EMP
WHERE ENAME=‘JSC’;

STEP2. NVL/ISNULL 함수를 이용 공집합을 9999로 바꾸고자 시도
SELECT NVL(MGR, 9999) MGR
FROM EMP
WHERE ENAME=‘JSC’;

STEP3. 적절한 집계함수를 찾아서 NVL 함수 대신 적용
SELECT MAX(MGR) MGR FROM EMP WHERE ENAME=‘JSC’;
-> 집계함수와 Scalar Subquerry는 인수의 결과 값이 공집합이어도 NULL 출력

STEP4. 집계 함수를 인수로 한 NVL/ISNULL 함수를 이용해 공집합인 경우에도 9999로 출력
SELECT NVL(MAX(MGR), 9999) MGR
FROM EMP
WHERE ENAME=‘JSC’;
-> 그룹함수와 NVL 함수를 같이 사용해서 처리
-> 그룹함수를 NVL 함수의 인자로 사용해서 인수의 값이 공집합인 경우에도 9999로 변환

(#) 공집합과 NULL을 구분하기 힘들기 때문에
(1) NVL/ISNULL 함수를 사용해야 하는 경우
(2) 집계함수를 포함한 NVL/ISNULL 함수를 사용하는 경우
(3) NVL/ISNULL 함수를 포함한 집계 함수를 사용하지 않아야 될 경우
로 구분해 사용

다. NULLIF
: EXPR1이 EXPR2와 같으면 NULL / 같지 않으면 EXPR1을 리턴
: 특정 값을 NULL로 대체하는 경우 사용

NULLIF (EXPR1, EXPR2)

(1) 사원 테이블에서 MGR와 7698이 같으면 NULL을 표시 같지 않으면 MGR을 표시
SELECT ENAME, EMPNO, MGR, NULLIF(MGR, 7698) NUIF
FROM EMP;
———————————————————————————————————————
SELECT ENAME, EMPNO, MGR
CASE WHEN MGR = 7698
THEN NULL
ELSE MGR
END NUIF
FROM EMP;
-> NULLIF함수를 CASE로 표현
-> MGR의 값이 7698이란 상수가 같은경우 NUIF칼럼에 NULL 표시
-> KING이 속한 행의 NUIF 칼럼에 NULOL이 표시된 것은 원래 MGR데이터가 NULL

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

(1) 사원 테이블에서 커미션을 1차 선택값으로, 급여를 2차 선택값으로 선택하되 두 칼럼 모두 NULL인 경우 NULL로 표시
SELECT ENAME, COMM, SAL, COALESCE(COMM, SAL) COAL
FROM EMP;
—————————————————————————————————————————————
SELECT ENAME, COMM, SAL,
CASE WHEN COMM IS NOT NULL
TEHN COMM
ELSE (CASE WHEN SAL IS NOT NULL
THEN SAL
ELSE NULL
END)
END COAL
FROM EMP;