함수 개념 잡기
DBMS는 SQL 작성에 유용한 다양한 함수(FUNCTION)를 제공한다. 함수는 값을 입력 받아 계산이나 일정한 처리를 한 후에 결과 값을 돌려준다.
아래 SQL은 ABS라는 절대값을 구하는 함수의 사용 예제다. SQL을 실행하면 -100의 절대값인 100이 출력된다.
-- [SQL-3-10-1]
SELECT ABS(-100) FUN_ABS FROM DUAL;
SQL
복사
아래는 UPPER라는 함수다. 입력된 문자를 모두 대문자로 변환해 돌려주는 함수다.
-- [SQL-3-10-2]
SELECT UPPER('abcD') FUN_UPPER FROM DUAL;
SQL
복사
Tip. DUAL
위 SQL에서는 FROM 절에 DUAL이라는 특별한 테이블을 사용했다. DUAL은 데이터를 입력할 수는 없는 특별한 기능의 테이블이다. SELECT 시에 한 레코드로 이루어진 값을 출력하거나 돌려 받고 싶을때 주로 사용한다. 오라클에서 주로 사용하며 MySQL은 FROM DUAL을 생략하고 SELECT 절만 사용해도 같은 기능이 동작한다. 예를 들어 아래와 같이 SQL을 실행해도 FROM DUAL을 사용한 것과 같은 결과가 나온다.
SELECT UPPER('abcd') FUN_UPPER;
ABS와 UPPER 함수를 통해 함수란 어떤 것인지 간단하게 살펴봤다. 이처럼 함수는 값을 입력 받아 값에 대한 처리나 연산을 수행한 결과를 출력해준다. DBMS는 다양한 기본 함수를 제공하며, 필요하다면 사용자가 함수를 추가 개발할 수도 있다. DBMS가 제공하는 기본 함수의 사용법을 모두 알 필요는 없다. 아래의 레퍼런스를 참고해 어떠한 함수들이 있는지 한 번씩만 살펴보기 바란다. 어떤 함수가 있는지 대략적으로 기억한 후에 필요할 때 사용법을 찾아보자.
DBMS 별로 비슷한 기능의 함수를 제공하지만, 함수 명이나 사용법은 약간씩 다르다. 아래는 오라클의 함수가 정리된 레퍼런스 URL이다.
자주 사용하게 되는 함 수 몇 개만 간단히 살펴보자.
문자열 처리 함수
아래는 REPLACE라는 함수다. 특정 문자열을 특정 값으로 치환(대체 변환)하기 위해 사용한다. Item 테이블에서 상품명(ItemNm)의 Iced를 'Very Cold'로 대체해서 출력한다.
-- [SQL-3-10-3]
SELECT T1.ItemId ,T1.ItemNm ,REPLACE(T1.ItemNm,'Iced','Very Cold') NewItemNm
FROM startdb.Item T1
WHERE T1.ItemCat = 'COF'
AND T1.ItemNm LIKE '%Cafe%'
ORDER BY T1.ItemID;
ItemId ItemNm NewItemNm
------ ------------------ -----------------------
CLB Cafe Latte(B) Cafe Latte(B)
CLR Cafe Latte(R) Cafe Latte(R)
ICLB Iced Cafe Latte(B) Very Cold Cafe Latte(B)
ICLR Iced Cafe Latte(R) Very Cold Cafe Latte(R)
SQL
복사
SUBSTR은 문자열을 정해진 위치에서 정해진 길이만큼 잘라내는 함수다. 아래와 같이 사용해볼 수 있다. 제법 자주 사용하게 되는 함수다.
-- [SQL-3-10-4]
-- 문자열을 정해진 길이만큼 잘라내는 함수,
-- SUBSTR(문자열, 시작위치, 자를길이)
SELECT T1.MobileNo ,T1.NickNm
,SUBSTR(T1.MobileNo,6,4) 핸드폰번호뒷자리
FROM startdb.Member T1
WHERE T1.NickNm= 'Air';
MobileNo NickNm 핸드폰번호뒷자리
--------- ------ ----------------
0001-0001 Air 0001
SQL
복사
CONCAT은 여러 문자열을 결합하기 위해 사용하는 함수다. CONCAT 함수 안에 결합할 문자열을 차례대로 콤마(,)로 구분해 입력해주면 된다. 오라클은 CONCAT 대신에 문자열 사이에 ||(파이프 두개 연속)를 사용해 문자열을 결합한다. (오라클도 CONCAT을 제공하지만, 오라클의 CONCAT은 두 개의 문자열만 결합할 수 있습니다.)
-- [SQL-3-10-5]
-- 문자열을 결합하는 함수, CONCAT(문자열1, 문자열2, 문자열3, ...)
SELECT T1.ItemId ,T1.ItemNm
,CONCAT(T1.HotColdCd,'-',T1.ItemSizeCd,'-',T1.ItemId) NewItemId
FROM startdb.Item T1
WHERE T1.ItemCat = 'BEV'
AND T1.ItemSizeCd = 'REG'
ORDER BY T1.ItemID;
ItemId ItemNm NewItemId
------ ----------------- -------------
CITR Yuzu Ade(R) COLD-REG-CITR
HCHR Hot Chocolate(R) HOT-REG-HCHR
LEMR Lemonade(R) COLD-REG-LEMR
ZAMB Grapefruit Ade(R) COLD-REG-ZAMB
SQL
복사
함수는 중첩해서 사용할 수 있다. 함수의 결과를 함수의 입력값으로 다시 사용하는 것이다. 아래는 SUBSTR 함수의 처리 결과를 CONCAT 함수의 입력 값으로 사용하고 있다.
-- [SQL-3-10-6]
SELECT T1.MobileNo ,T1.NickNm
,CONCAT('010-****-',SUBSTR(T1.MobileNo,6,4)) MaskMobile
FROM startdb.Member T1
WHERE T1.NickNm = 'Air';
MobileNo NickNm MaskMobile
--------- ------ -------------
0001-0001 Air 010-****-0001
SQL
복사
소수점 처리 함수
아래는 ROUND, CEIL, FLOOR라는 소수점을 처리하는 함수다. CEIL이나 FLOOR는 사용할 경우가 잘 없다. 하지만 소수점이 나오는 데이터를 처리하다 보면 종종 사용하게 되니 알아 놓기 바란다.
-- [SQL-3-10-7]
SELECT ROUND(1.2,0) COL_ROUND
,CEIL(1.2) COL_CEIL
,FLOOR(1.2) COL_FLOOR
FROM DUAL;
-- [SQL-3-10-8]
SELECT ROUND(1.9,0) COL_ROUND
,CEIL(1.9) COL_CEIL
,FLOOR(1.9) COL_FLOOR
FROM DUAL;
SQL
복사
문자를 날짜로 변환하는 함수
마지막으로 STR_TO_DATE라는 문자를 날짜로 변환하는 함수는 살펴보자. 여기서는 사용법만 간단히 살펴본다. (자세한 설명은 뒤에서 날짜 관련 함수와 같이 할 예정이다.)
아래는 회원(Member) 테이블에서 2022년 7월 21일에 가입한 회원을 조회하는 SQL이다. STR_TO_DATE 함수를 사용해 JoinDtm(가입일시)에 조건 값을 주고 있다.
-- [SQL-3-10-9]
SELECT T1.MemberId ,T1.NickNm ,T1.JoinDtm
FROM startdb.Member T1
WHERE T1.JoinDtm = STR_TO_DATE('20220721','%Y%m%d')
ORDER BY T1.MemberId ASC;
MemberId NickNm JoinDtm
-------- -------- -------------------
M9800 Wind195 2022-07-21 00:00:00
M9912 Fire198 2022-07-21 00:00:00
M9926 Moon198 2022-07-21 00:00:00
M9968 Green199 2022-07-21 00:00:00
M9982 Pink199 2022-07-21 00:00:00
SQL
복사
회원(Member) 테이블의 가입일시(JoinDtm) 컬럼은 DATETIME 자료형이다. DATETIME은 년, 월, 일과 시, 분, 초까지 저장할 수 있는 날짜 자료형이다. 이와 같은 날짜 자료형의 컬럼에 조건 값을 줄 때는 STR_TO_DATE(String To Date) 함수를 사용해 문자 값을 날짜 형태로 변환해 처리해야 한다.
STR_TO_DATE의 기본 사용법은 아래와 같다.
•
STR_TO_DATE('20220721','%Y%m%d'): 20220721이라는 문자 값을 2022년 7월 21일이라는 날짜 형태로 변환
◦
이때, 패턴문자인 %Y%m%d 에서 Y는 대문자로 m과 d는 소문자로 처리해야 한다.
위 SQL은 원래 STR_TO_DATE를 생략해도 정상 작동한다.(각자 환경에 따라 작동이 안될 수도 있다.) 그런데 왜 STR_TO_DATE를 사용해야 하는지는 조금 복잡하면서 중요한 이야기다. 이와 관련해서는 뒤에서 별도로 설명한다. 우선은 "날짜 자료형의 컬럼에 조건을 처리할 때는 STR_TO_DATE로 조건 값을 변환해야 한다"라고 외우기 바란다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 3-10-1
아래 링크를 참고해 함수의 기능을 적으시오.
•
LENGTH:
•
LPAD:
•
RPAD:
•
REPEAT:
•
RIGHT:
•
TRIM:
BOOSTER QUIZ 3-10-2
기존의 ItemId를 총 왼쪽부터 0으로 채운 10자리의 문자열로 만들어서 조회해주세요.
•
대상 테이블: 상품(Item)
•
조회 조건: 모든 상품 조회
•
조회 컬럼: ItemId, NewItemId, ItemNm
•
추가 조건:
: NewItemId는 기존의 ItemId를 왼쪽부터 문자 '0'으로 채운 10자리의 신규 ItemId입니다.
◦
Ex) AMB -> 0000000AMB, BGLR-> 000000BGLR
◦
이와 같은 처리를 위해 LPAD 함수를 사용합니다.
ItemId NewItemId ItemNm
------ ---------- -------------------
AMB 0000000AMB Americano(B)
AMR 0000000AMR Americano(R)
BGLR 000000BGLR Bagel(R)
BMFR 000000BMFR Blueberry Muffin(R)
CITR 000000CITR Yuzu Ade(R)
CLB 0000000CLB Cafe Latte(B)
CLR 0000000CLR Cafe Latte(R)
... 생략 ...
SQL
복사
