데이터베이스 SQL - Function

함수 : 특정한 기능을 수행하는 단위 또는 특정한 기능을 수행하기 위한 일련의 연산들의 집합

 

함수의 유형

> 생성 주체에 따라

  - 사용자 정의 함수 : 사용자가 정의한다.

  - 내장 함수 : 벤더가 정의한다.

> 적용 범위에 따라

  - 단일 행 함수 : 문자형 함수, 숫자형 함수, 날짜형 함수, 제어 함수, 변환 함수,  NULL 함수

  - 다중 행 함수 : 그룹 함수, 윈도우 함수

 

단일행 함수의 특징

- 각 행에 대해 개별적으로 적용하고 그 결과를 반환한다.

- 함수 중첩이 가능하다.

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

 

Oracle의 단일행 내장 함수

문자형 함수 (문자형 변수 처리) : CONCAT, SUBSTR, LENGTH, TRIM 등..

숫자형 함수 (숫자형 변수 처리) : MOD, ROUND 등..

변환 함수 (문자, 숫자, DATE형 값의 타입 변환) : TO_CHAR, TO_NUMBER, TO_DATE 등..

날짜형 함수 (DATE 타입의 변수 처리) : SYSDATE 등..

제어 함수 (논리값에 따른 값의 처리) : CASE 등..

NULL 관련 함수 (NULL 처리) : NVL, NULLIF 등..

 

아스키 코드표

0 : NULL

48~57 : 0~9

65~ : A~

97 : a~

 

문자형 함수 사용 예시들

SELECT LOWER('SQL Expert') FROM DUAL; -- sql expert
SELECT UPPER('SQL Expert') FROM DUAL; -- SQL EXPERT
SELECT ASCII('A'), ASCII('5') FROM DUAL; -- 65, 53 : 문자를 아스키코드로
SELECT CHR(65), CHR(53) FROM DUAL; -- A, 5 : 아스키코드를 문자로

SELECT CONCAT('daram','G') FROM DUAL; -- daramG
SELECT SUBSTR('SQL Expert', 5) FROM DUAL; -- Expert : char에서 5위치부터 끝까지 반환
SELECT SUBSTR('SQL Expert', 5, 3) FROM DUAL; -- Exp : char에서 5위치부터 길이3만큼 반환
SELECT LENGTH('SQL Expert') FROM DUAL; -- 10 : 공백도 길이에 포함된다.

-- LTRIM은 문자열 왼쪽부터 시작
SELECT LTRIM('GGOOODdaramG', 'G') FROM DUAL; -- OOODdaramG : 다른 문자 만날 때 까지 지정문자 G를 제거한다.
SELECT LTRIM('  GGOOODdaramG') FROM DUAL; -- daramG : 지정문자 생략 시 공백값이 디폴트
-- RTRIM은 문자열 오른쪽부터 시작
SELECT RTRIM('GGOOODdaramG', 'G') FROM DUAL; -- GGOOODdaram
-- TRIM은 문자열 양쪽에서 시작
SELECT TRIM('G' FROM 'GGOOODdaramG') FROM DUAL; -- OOODdaram

 

문자열 함수 QUIZ

Q. PLAYER 테이블에서 PLAYER_NAME의 맨 마지막 문자를 '*'로 대체한 '비식별화' 칼럼을 출력하시오.

A.

SELECT PLAYER_NAME, CONCAT(SUBSTR(PLAYER_NAME, 1, LENGTH(PLAYER_NAME)-1), '*') AS 비식별화 FROM PLAYER;

결과 )

 

 

숫자형 함수 사용 예시들

SELECT ABS(-15) FROM DUAL; -- 15
SELECT SIGN(-20), SIGN(0), SIGN(10) FROM DUAL; --  -1, 0, 1 : 표현식이 음수, 0, 양수일 때
SELECT MOD(7, 3) FROM DUAL; -- 1 :  7을 3으로 나누었을 때 나머지를 반환
SELECT CEIL(38.123), CEIL(-38.123) FROM DUAL; -- 39, -38 : 올림 함수
SELECT FLOOR(38.123), FLOOR(-38.123) FROM DUAL; -- 38, -39 : 내림 함수
SELECT ROUND(38.5235, 3), ROUND(38.5235) FROM DUAL; -- 38.524, 39 : 반올림 함수
SELECT TRUNC(38.5235, 3), TRUNC(38.5235) FROM DUAL; -- 38.523, 38 : 버림

 

숫자형 함수 QUIZ

Q. PLAYER 테이블에서 PLAYER_ID를 활용하여 전체 선수를 4개의 그룹(0~3)에 배정하시오.

A.

SELECT PLAYER_ID, PLAYER_NAME, MOD(PLAYER_ID, 4) AS 그룹 FROM PLAYER;

결과 )

 

 

변환형 함수

-- TO_CHAR : 숫자, 날짜를 문자열로
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') AS 타입1, -- 2022/11/30
	TO_CHAR(SYSDATE, 'YYYY/MM/DD.HH24.MI.SS') AS 타입2, -- 2022/11/30.11.29.58
	TO_CHAR(SYSDATE) AS 타입3 FROM DUAL; -- 22/11/30

-- TO_NUMBER : 문자열을 숫자로
SELECT '1' + '1' AS 계산 FROM DUAL; -- 2 : 암시적 변환
SELECT TO_NUMBER('1') + TO_NUMBER('1') AS 계산 FROM DUAL; -- 2 : 명시적 변환

-- TO_DATE : 문자열을 날짜로 변환
SELECT EXTRACT (YEAR FROM TO_DATE('20221130', 'YYYY/MM/DD')) AS 연도 FROM DUAL; -- 2022
--SELECT EXTRACT (YEAR FROM '20221130') AS 연도 FROM DUAL; - 오류 발생

 

명시적 데이터 타입 변환 : 함수를 이용해 명시적으로 데이터 타입을 변환한다.

암시적 데이터 타입 변환 : 시스템이 자동으로 데이터 타입을 변환한다. 성능 저하 및 에러 발생 가능성이 존재한다.

 

 

날짜형 함수

-- SYSDATE : 현재 날짜와 시각
SELECT SYSDATE FROM DUAL; -- 22/11/30
SELECT TO_CHAR(SYSDATE, 'YYYY_MM_DD HH24:MI') FROM DUAL; -- 2022_11_30 11:44

-- EXTRACT('YAER'|'MONTH'|'DAY' FROM 날짜) : 날짜 데이터에서 년/월/일 추출
SELECT EXTRACT(YEAR FROM SYSDATE) AS "추출한 년도" FROM DUAL; -- 2022
SELECT EXTRACT(MONTH FROM BIRTH_DATE) AS "BIRTH_MONTH" FROM PLAYER;

-- TRUNC(날짜, 'DD') : 날짜 데이터에서 시/분/초를 잘라냄
SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD.HH24.MI.SS') FROM DUAL; -- 2022.11.30.11.50.59
SELECT TRUNC(SYSDATE, 'DD') FROM DUAL; -- 22/11/30
SELECT TO_CHAR(TRUNC(SYSDATE, 'DD'), 'YYYY.MM.DD.HH24.MI.SS') FROM DUAL; -- 2022.11.30.00.00.00
SELECT TO_CHAR(TRUNC(SYSDATE, 'MM'), 'YYYY.MM.DD.HH24.MI.SS') FROM DUAL; -- 2022.11.01.00.00.00
SELECT TO_CHAR(TRUNC(SYSDATE, 'HH24'), 'YYYY.MM.DD.HH24.MI.SS') FROM DUAL; -- 2022.11.30.11.00.00

 

날짜형 함수 QUIZ

Q. PLAYER 테이블에서 PLAYER_NAME, BIRTH_DATE와 함께 태어난 날부터 오늘까지 지난 날수를 출력하시오.

A.

SELECT PLAYER_NAME, BIRTH_DATE, ROUND((SYSDATE - BIRTH_DATE), 0) AS DAY_PASSED FROM PLAYER;
// ROUND(SYSDATE - BIRTH_DATE) 해도 됨

 

다음과 같이 출력하기

SELECT PLAYER_NAME, BIRTH_DATE,
    EXTRACT(YEAR FROM BIRTH_DATE) AS 생년,
    EXTRACT(MONTH FROM BIRTH_DATE) AS 생월,
    EXTRACT(DAY FROM BIRTH_DATE) AS 생일
FROM PLAYER;
-- 위, 아래 결과 같음
SELECT PLAYER_NAME, BIRTH_DATE,
    TO_NUMBER(TO_CHAR(BIRTH_DATE, 'YYYY')) AS 생년,
    TO_NUMBER(TO_CHAR(BIRTH_DATE, 'MM')) AS 생월,
    TO_NUMBER(TO_CHAR(BIRTH_DATE, 'DD')) AS 생일
FROM PLAYER;

// 문자형 00/00/00을 주고 저렇게 연/월/일 나눠서 추출하라고 나중에 문제 내보자

 

 

CASE Expression

표현식이지만 함수의 성격을 가지고 있다.

SELECT PLAYER_NAME,
    CASE WHEN HEIGHT >= 180
        THEN HEIGHT
        ELSE 180
    END AS NEW_HEIGHT
FROM PLAYER;

 

Searched Case Expression  vs  Simple Case Expression

SELECT DISTINCT POSITION FROM PLAYER; -- MF, DF, FW, (null), GK

-- Searched Case Expression
SELECT PLAYER_NAME, -- 여기서 ',' 실수로 누락해서 계속 오류발생함 이거 퀴즈로 내자
    CASE
        WHEN POSITION = 'MF' THEN '미드필더'
        WHEN POSITION = 'DF' THEN '수비수'
        WHEN POSITION = 'FW' THEN '공격수'
        WHEN POSITION = 'GK' THEN '골키퍼'
        --WHEN POSITION IS NULL THEN '나는 어디감'
        ELSE '나는 누구'
    END AS 포지션
FROM PLAYER;
-- Searched Case 는 다양한 조건 사용이 가능하고 표현식이 복잡하다.


-- Simple Case Expression
SELECT PLAYER_NAME,
    CASE POSITION
        WHEN 'MF' THEN '미드필더'
        WHEN 'DF' THEN '수비수'
        WHEN 'FW' THEN '공격수'
        WHEN 'GK' THEN '골키퍼'
        ELSE '나는 누구'
    END AS 포지션
FROM PLAYER;
-- Simple Case는 동등 비교에만 사용한다. 표현식이 명료하다.

 

앞서 말했듯이 Searched Case Expression은 Simple Case와 달리 동등 이외의 조건을 표현할 수 있다.

-- PLAYER 테이블에서 HEIGHT가 185 이상은 '장신' 175이상 185미만은 '평균',
-- 나머지는 '구웃' 으로 표현한 테이블을 출력하여라
-- 테이블에 PLAYER_NAME과 장신,평균,단신 여부표시 (이름은 키 상태)
SELECT PLAYER_NAME, HEIGHT,
    CASE
        WHEN HEIGHT >= 185 THEN '장신'
        WHEN HEIGHT >= 175 AND HEIGHT < 185 THEN '평균'
        ELSE '단신'
    END AS "키 상태" -- 이거 Alias에 작은따옴표 주고 퀴즈 내야겠다
FROM PLAYER;

 

CASE 표현식의 중첩도 가능하다

SELECT PLAYER_NAME,
    CASE
        WHEN HEIGHT > 185 THEN '장신'
        ELSE (
            CASE
                -- 175이상 185이하
                WHEN HEIGHT BETWEEN 175 AND 185 THEN '평균'
                ELSE '단신'
            END
        )
    END AS 신장
FROM PLAYER;

 

DECODE

- 오라클에서만 사용되는 함수

- 기준 값이 없으면 디폴트 값을 출력한다.

SELECT PLAYER_NAME,
    DECODE(POSITION,
    'GK','골키퍼','DF','수비수','MF','미드필더','FW','공격수','그 외') AS POSITION
FROM PLAYER;

 

 

NULL

- NULL이란 비어있는 값이다. 공백, 0과는 다른 의미이다.

- NULL을 포함하는 모든 산술 연산의 결과는 NULL이다.

- NULL과 공집합도 역시 다른 의미이다.

 

 

NULL 관련 함수

-- NVL : 표현식 값이 NULL이면 대체값을 반환해준다.(아니면 표현식 값 반환)
SELECT PLAYER_NAME, POSITION, NVL(POSITION, '없음') AS 포지션 FROM PLAYER;

-- NULLIF : 두 식이 같으면 NULL을 반환한다.(아니면 표현식 값 반환)
SELECT PLAYER_NAME, POSITION, NULLIF(POSITION, 'GK') AS "GK면 NULL" FROM PLAYER;

-- COALESCE : 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식 반환 (모두 NULL이면 NULL 반환)
SELECT E_PLAYER_NAME, NICKNAME, PLAYER_NAME,
COALESCE(E_PLAYER_NAME, NICKNAME, PLAYER_NAME) FROM PLAYER;

 

NVL

NULL값을 특정 값으로 변환할 때 사용한다.