[SQL 기본과 활용] Function

"특정한 기능을 수행하는 단위"

함수의 유형

 - 생성 주체

     사용자 정의 함수(User Defined Function) - 사용자가 정의

     내장 함수(Built-in Function) - 벤더가 정의

 

 - 적용 범위

     단일 행 함수(Single-Row Function)

          문자형 함수, 숫자형 함수, 날짜형 함수 (데이터타입에 따라서 구분)

          제어함수, 변환함수, NULL 관련 함수(역할과 사용법 정리 필수!)**

     다중 행 함수(Multi-Row Function)

          그룹함수(Group Function)

                 집계 함수(Aggregate Function) 포함

     윈도우 함수(Window Function)

 

단일행 함수의 특징

     예)

     SELECT PLAYER_NAME, LENGTH(PLAYER_NAME) AS 길이

     FROM PLAYER;

 

 - 각 행(Row)에 대해 개별적으로 작용하고 그 결과를 반환함

     단일 행 내에 있는 하나 또는 복수의 값을 인수로 사용

     여러 행에 걸친 값을 사용할 수 없음

 - 함수 중첩(함수의 인자로 함수를 사용)이 가능함

 - SELECT, WHERE, ORDER BY에 사용 가능

 

 

Oracle의 단일행 내장 함수

ASCII 코드표(아스키코드)

문자형 함수

SELECT FUNCTION_NAME FROM DUAL;

문자형 함수	함수 사용 예
LOWER(문자열)	LOWER(‘SQL Expert’)
UPPER(문자열)	UPPER(‘SQL Expert’)
ASCII(문자) *아스키코드를 찾는 함수	ASCII(‘A’), ASCII(‘5’)
CHR(ASCII 코드) *ASCII코드로부터 문자를 찾는 함수 *ASCII 함수와 반대	CHR(65), CHR(53)
CONCAT(문자열1, 문자열2) *두 문자열 합치기	CONCAT(‘RDBMS’, ‘ SQL’) Cf) RDBMS’ || ‘SQL’
SUBSTR(문자열, m) SUBSTR(문자열, m, n) *문자열 중 일부를 뽑아내는 기능	SUBSTR(‘SQL Expert’, 5) *5번째부터 뒤를 뽑아라는 의미 SUBSTR(‘SQL Expert’, 5, 3) *5번째부터 3개 뽑아라
LENGTH(문자열) *문자열의 길이 출력	LENGTH(‘SQL Expert’)
LTRIM(문자열) LTRIM(문자열, 지정문자)	문자열의 왼쪽부터 시작해서 다른 문자를 만나기 전까지 지정 문자를 제거한다. (지정문자가 생략되면 공백 값이 디폴트) (예) LTRIM(‘ xxxYYYZZxYZ’) > ‘xxxYYYZZxYZ’ (예) LTRIM(‘xxxYYYZZxYZ’, ‘x’) > ‘YYYZZxYZ’
RTRIM(문자열) RTRIM(문자열, 지정문자)	문자열의 오른쪽부터 시작해서 다른 문자를 만나기 전까지 지정 문자를 제거한다. (지정문자가 생략되면 공백 값이 디폴트) (예) RTRIM(‘xxxYYYZZxYZ’, ‘Z’) > ‘xxxYYYZZxY’
TRIM(문자열) TRIM(지정문자 FROM 문자열)	문자열의 양쪽에서 시작해서 다른 문자를 만나기 전까지 지정 문자를 제거한다.(지정문자와 FROM 이 생략되면 공백 값이 디폴트) (예) TRIM(‘x’ FROM ‘xxYYZZxYZxx’ > ‘YYZZxYZ’ (예) TRIM(‘ xxYYZZxx ‘) > ‘xxYYZZxx’

“함수와 연산자는 매우 비슷하다!”

 

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

      HINT) CONCAT, SUBSTR, LENGTH 함수 사용

SELECT CONCAT(SUBSTR(PLAYER_NAME, 1, LENGTH(PLAYER_NAME)-1), ‘*’) AS 비식별화

FROM PLAYER;

 

숫자형 함수

SELECT FUCTION_NAME FROM DUAL;

숫자형 함수	함수 사용 예
ABS(숫자) *절대값	ABS(-15)
SIGN(숫자) *방향을 나타내는 함수 *음수면 -1, 0, 양수면 +1	SIGN(-20), SIGN(0), SIGN(10)
MOD(숫자1, 숫자2) *숫자1을 숫자2로 나눴을 때 나머지	MOD(7, 3)
CEIL(숫자) *숫자 이상의 최소 숫자	CEIL(38.133), CEIL(-38.133)>38 >-38
FLOOR(숫자) *숫자 이하의 최대 숫자	FLOOR(38.5235, 3), FLOOR(-38.5235>38 >-39
ROUND(숫자), ROUND(숫자, m) *소수점 첫쨰 자리에서 반올림 > 정수만 표시 *소수점 아래 m까지만 표시(m+1번째 자리에서 반올림)	ROUND(38.5235, 3), ROUND(38.5235)
TRUNC(숫자), TRUNC((숫자, m) *소수점 버림 > 정수만 표시 *소수점 아래 m까지만 표시(뒤는 버림)	TRUNC(38.5235, 3), TRUNC((38.5235)
그 외 SIN(숫자) EXP(숫자) 지수 함수..? POWER(숫자1, 숫자2) 숫자1의 숫자2 승 SQRT(숫자) 제곱? LOG(숫자1, 숫자2) 로그함수 LN(숫자) 자연로그 함수

 

변환형 함수

*YEAR가 날짜 데이터에만 쓸 수 있기 때문에 '문자열'에서 EXTRACT할 수 없음

 

데이터 타입 변환

 - 명시적(Explicit) 데이터 타입 변환

      함수를 사용하여 명시적으로 데이터 타입을 변환

 - 암시적(Implicit) 데이터 타입 변환

      시스템이 자동으로 데이터타입 변환

            예) MOD(PLAYER_ID, 4) > 문자열을 숫자로 변환성능 저하 및 에러 발생의 가능성 존재(안하는걸 권장)

            *TO_DATE는 EXTRACT랑 거의 같이 쓰임 > 문자 데이터를 날짜 데이터로 바꿔서 추출하기 위해

 

날짜형 함수

*EXTRACT = TO_NUMBER(TO_CHAR( ))

 

 

CASE Expression ( if 문이랑 비슷)

 - 표현식이지만 함수의 성격을 갖고 있음

 - IF ~ THEN~ ELSE 논리

 

 - Searched Case Expression 만 가능한 예

      동등(=)이의외 조건은 Simple Case Expression 또는 Decode 함수요 표현 불가

      ex)

"WHEN 이 먼저 나오면 Searched Case Expression 변수가 먼저 나오면 Simple Case Expression"

 

 - CASE 표현식의 중첩

 

 - DECODE (Simple case 에서만 사용)

      Oracle에서만 사용되는 함수

      DECODE(표현식, 기준값1, 출력값1 [, 기준값2, 출력값2, ... , 디폴트값])

      표현식의 값이 기준값1이면 값1을 출력 기준값2이면 값2를 출력

      기준값이 없으면 디폴트값을 출력

 

NULL 이란?

"CASE문으로 바꼈을 때 어떤건지 알아야 함!!"

 - 비어있는 값

 - 공백(SPACE), 0과는 다른 의미

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

      NULL + 0, NULL - 1, NULL *0, NULL/0 → NULL

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

"NULL은 데이터! 공집합은 데이터가 없는 것!"

 

NULL 관련 함수

NULL 관련함수	함수 설명
NVL (표현식, 대체값)	표현식의 값이 NULL이면 대체값을, NULL이 아니면 표현식의 값을 반환 표현식의 값과 대체값의 데이터 타입이 같아야 함 SELECT PLAYER_NAME, POSITION, NVL(POSITION, '업음') AS 포지션 FROM PLAYER;
NULLIF (표현식1, 표현식2)	두 식이 같으면 NULL, 같지 않으면 표현식1의 값을 반환 SELECT PLAYER_NAME, POSITION, NULLIF(POSITION, 'GK') FROM PLAYER; "NULL을 반환해라 만약에 표현식1과 표현식2가 같으면!"
COALESCE (표현식1, 표현식2)	"우선순위가 정해져있음" 임의의 개수의 표현식에서 NULL이 아닌 최초의 표현식을 반환 모든 표현식이 NULL이라면 NULL을 반환 SELECT E_PLAYER_NAME, NICKNAME, PLAYER_NAME, COALESCE(E_PLAYER_NAME, NICKNAME, PLAYER_NAME) FROM PLAYER;

 

- NVL

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

(예) 선수명과 포지션을 출력하되, 포지션이 없는 경우 '없음'으로 출력하라

SELECT PLAYER_NAME, POSITION, NVL(POSITION, '없음') AS 포지션 FROM PLAYER;

위의 문장을 IS NULL과 CASE 구문으로 표현하면?

SELECT PLAYER_NAME, POSITION, CASE WHEN POSITION IS NULL THEN '없음' ELSE POSITION END AS 포지션

FROM PLAYER;

 

"사칙연산에 들어가는 FIELD들은 NULL값 체크를 반드시 해줘야 하고! NVL활용해서 다른 값이나 문자열 값으로 대체해야!"

      덧셈의 경우 0, 곱셈의 경우1 사용(항등원 : 곱해서 자기 자신이 나오는 것)

 

 - NULLIF

      특정 값을 NULL로 변환할 떄 사용

      (예) 선수명과 포지션을 출력하되, 포지션이 'GK'인 경우는 NULL로 출력하라

SELECT PLAYER_NAME, POSITION, NULLIF(POSITION, 'GK') FROM PLAYER;

=

SELECT PLAYER_NAME, POSITION,

CASE WHEN POSITION = 'GK'

THEN NULL

ELSE POSITION

END AS 포지션

FROM PLAYER;

 - COALESCE

      임의의 개수의 표현식에서 NULL이 아닌 최초의 표현식을 반환할 때 사용

SELECT E_PLAYER_NAME, NICKNAME, PLAYER_NAME, COALESCE(E_PLAYER_NAME, NICKNAME, PLAYER_NAME)

FROM PLAYER;

=

SELECT E_PLAYER_NAME, NICKNAME, PLAYER_NAME,

CASE WHEN E_PLAYER_NAME IS NOT NULL

THEN E_PLAYER_NAME

ELSE(

CASE WHEN NICKNAME IS NOT NULL

THEN NICKNAME

ELSE NULL

END)

END AS 명칭

FROM PLAYER;