[MariaDB] 내장 함수② - 제어흐름/정보 관련

Geehyun(장지현)·2024년 2월 20일
MariaDBsql천재교육 에듀테크 풀스택4기학습노트
1

MariaDB

목록 보기
6/12
post-thumbnail

함수

특정 기능을 수행하는 작은 프로그램으로 다양한 프로그래밍 언어에서 사용되는 개념입니다.
SQL에서도 해당 개념을 사용하며 다양한 내장 함수를 제공하고 있어 이를 이용할 수 있습니다.

함수명(인자, 인자,,,)

형식으로 사용할 수 있습니다.

인자로 넣어야하는 값들은 함수마다 다르니 아래의 MariaDB에서 주로 사용하는 함수 리스트를 보고 활용해보세요!

내장함수의 경우 양이 많아 두 개로 나눠서 작성했습니다.
➡️ MariaDB 내장 함수① - 문자/시간/수치 관련

제어흐름함수

다른 프로그래밍 언어에서 제어문 처럼 MariaDB에서도 특정 조건에 따라 결과를 달리 표시해주는 함수 또는 연산자를 제공하고 있습니다.

  • CASE 연산자
  • DECODE_ORACLE() 함수
  • IF() 함수
  • NULL 관련 함수

CASE 연산자

CASE 연산자의 경우 조건을 체크하여 결과를 표시하는 연산자로 아래 두 가지 방법으로 사용할 수 있습니다.

사용법 ①

특정 값일 경우 WHEN절과 비교하여 해당하는 WHEN절의 THEN 결과값으로 출력하는 방식으로 JAVA의 SWITCH문과 비슷합니다.

-- 사용법
CASE value
	WHEN 비교값 THEN 결과
	WHEN 비교값 THEN 결과
	WHEN 비교값 THEN 결과
 	ELSE 다 안맞으면 나오는 결과
END

-- 사용예시
SELECT memberId, NAME, addr1,
CASE jobCode
	WHEN '01' THEN '학생'
	WHEN '02' THEN '회사원'
	WHEN '03' THEN '공무원'
	WHEN '04' THEN '교사'
	ELSE '기타' 
END AS job
FROM tbl_member;

사용법 ②

WHEN절에 조건절을 작성 후 해당 조건에 일치하면 THEN 결과값으로 출력하는 방식으로 JAVA의 IF문과 비슷합니다.

-- 사용법
CASE
	WHEN 조건절 THEN 결과
	WHEN 조건절 THEN 결과
	WHEN 조건절 THEN 결과
	ELSE 다 아니면 나오는 결과
END

-- 사용예시
SELECT
orderNo, orderDate, memberId, orderAmount,
CASE WHEN cancelYN = 'N' THEN orderAmount ELSE 0 END AS normal,
CASE WHEN cancelYN = 'Y' THen orderAmount ELSE 0 END AS cancel 
FROM tbl_orderinfo
ORDER BY orderNo
;

DECODE_ORACLE() 함수

DECODE_ORACLE()함수의 경우 기존 ORACLE에서 사용하던 함수였으나, 사용성이 편리하여 MariaDB에서도 사용할 수 있게 되었습니다.

  • CASE문의 사용법①과 동일한 기능을 하며, 작성법만 조금 다릅니다.
  • CASE문과 달리 비교값, 결과값을 콤마로만 구분되서 작성 시 주의가 필요합니다.
  • 쭉 줄줄이 한줄로 이어서 써도 되지만 위의 이유로 인해 줄넘김을 작성해주는게 좋습니다. 
-- 사용법
DECODE_ORACLE(값,
	비교값, 결과
	비교값, 결과
	비교값, 결과
	나머지 결과)
    
-- 사용예시
SELECT
	memberId, NAME, jumin, addr1,
    DECODE_ORACLE(jobcode,
        '01', '학생',
        '02', '회사원',
        '03', '공무원',
        '04', '교사',
        '기타' ) AS jobname
FROM tbl_member
ORDER BY memberId
;

IF() 함수

IF함수는 조건절 입력 후 해당 조건이 참일 경우 출력결과, 거짓일 경우 출력결과를 입력하는 방식으로 EXCEL에서의 IF()함수와 그 사용법이 유사합니다.

-- 사용법
IF(조건문, 참일 때 결과, 거짓일 때 결과);

-- 사용예시 ①
SELECT if(1<2, 'true', 'false');    # 결과 : true

-- 사용예시 ②
SELECT
    orderNO, orderDate, memberId, orderAmount, cancelYN,
    if(cancelYN = 'Y', '주문취소', '정상주문') AS orderStatus
FROM tbl_orderinfo
ORDER BY orderNo;

💡 CASE/IF/DECODE_ORACLE 서로 막 바꿔써보기!
위 각 제어 연산자 또는 제어함수는 서로 바꿔서 이용할 수 있습니다.
어떤 것을 사용하든 상황에 맞는 방식으로 사용할 수 있어야 합니다.

SELECT
  orderNO, orderDate, memberId, orderAmount, cancelYN,
  # CASE 버전
  CASE WHEN cancelYN = 'Y' THEN '주문취소' ELSE '정상주문' END AS orderStatus2,
  # DECODE_ORACLE 버전
  DECODE_ORACLE(cancelYN, 'Y', '주문취소', 'N', '정상주문') AS orderStatus3, 
  # IF 버전
  if(cancelYN = 'Y', '주문취소', '정상주문') AS orderStatus1
FROM tbl_orderinfo
ORDER BY orderNo;

NULL 관련 함수

위 조건체크 관련 함수를 사용하다보니 NULL 여부를 확인하는 구조를 많이 사용하게되어 아예 NULL값 체크를 위한 함수가 따로 만들어지게되었습니다.

NULL값을 체크해서 특정 결과를 표시할 수 있는 함수로 대표적으로 아래 4가지가 있습니다.

  • IFNULL(대상, NULL일 경우 결과)
    NVL(대상, NULL일 경우 결과)
    대상 값을 비교하여 해당 값이 NULL일 경우 특정 결과로 대체하여 나오게 하고, NULL이 아닐 경우 대상 값 그대로 출력하는 함수 입니다.
  • NULLIF(대상1, 대상2)
    대상1, 대상2를 비교하여 두 대상이 같을 시 NULL을 출력, 다를 경우 대상1을 출력하는 함수 입니다.
  • NVL2(대상, 값1, 값2)
    대상의 값을 비교하여 해당 값이 NULL이 아닐 경우 값1로, NULL일 경우 값2로 출력하는 함수 입니다.
-- 사용법
IFNULL(대상, 대상이 NULL일 경우 결과)
NVL(대상, 대상이 NULL일 경우 결과)
NULLIF(대상1, 대상2)    => 대상1이 대상2가 같으면 NULL반환 / 아니면 대상1을 반환
NVL2(대상, NULL이 아닐때 결과, NULL일 때 결과)

-- 사용예시
SELECT
	memberId, NAME, jumin, addr1,
	IFNULL(addr2, '빈주소') AS addr2,     # addr2열에서 null값인 데이터는 '빈주소'로 출력 / null이 아닐 경우 기존 데이터 그대로 출력
	IFNULL(addr2, '') AS addr3,          # addr2열에서 null값인 데이터는 '' 공란으로 표시 / null이 아닐 경우 기존 데이터 그대로 출력
	NVL(addr2, '-') AS addr4,            # addr2열에서 null값인 데이터는 '-'으로 표시 / null이 아닐 경우 기존 데이터 그대로 출력
	NULLIF(addr1, addr2) AS addr12,      # addr1열과 addr2열의 값이 동일할 경우 null로 표시 / null이 아닐 경우 addr1값으로 출력
	NULLIF(addr2, NULL) AS addr22,       # NULLIF를 ISNULL과 같은 용도로 쓰는 법
	NVL2(addr2, addr2, '빈주소') AS addr23   # Java 삼항연산자와 유사 (조건) ? true일 경우 실행 : false일 경우 실행;
FROM tbl_member
ORDER BY memberId;

정보함수

정보함수는 데이터베이스 내의 각종 정보를 제공해주는 함수입니다!

No함수명설명
1ROWNUM()- 현재 조회하는 쿼리의 결과셋에 대해 행번호를 부여하는 함수입니다.
- 행번호 부여 후 Order by로 정렬하게 될 경우 행번호가 부여된 상태에서 정렬이되게 됩니다.
2ROW_COUNT()- 쿼리문이 실행되면서 영향을 미친 행 수를 반환합니다. (INSERT, UPDATE, DELETE를 통해)
- 쿼리문이 실행되어도 값이 사실상 기존 값과 같다면 0이 나옵니다. (MariaDB의 경우 UPDATE문 실행 시 실제로 값이 변경되는게 없으면 실제로 실행되지 않기 때문입니다.)
- 쿼리 실행기에 따라 실행 옵션을 '일괄보내기' 등으로 수정한 후 실행할 쿼리 + ROW_COUNT()쿼리 함께 실행해줘야 올바른 결과를 확인할 수 있습니다.
3DEFAULT(열 이름)- 해당 열에 설정된 기본값(Default)을 반환해줍니다.
- CURRENT_TIMESTAMP() 함수를 사용할 경우 조회하는 기준 현재일시를 반환해줍니다.
- AUTO_INCREMENT 속성을 사용할 경우 0을 반환해줍니다.
- 기본값이 설정되지 않은 열에 사용 시 오류메시지가 발생합니다.
4LAST_INSERT_ID()- 마지막 INSERT된 데이터의 AUTO_INCREMENT 열의 값을 반환해줍니다.
- AUTO_INCEREMENT를 쓰는 열이 없으면 사용할 수 없습니다.
5USER()- 현재 DB를 사용중인 사용자 정보 표시해줍니다.
6CURRENT_USER()- 현재 DB를 사용중인 사용자 정보 표시해줍니다.
7DATABASE()- 현재 사용 중인 데이터베이스 명을 표시해줍니다.
8VERSION()- 데이터베이스 시스템의 버전 정보를 표시해줍니다.
9JSON_OBJECT(키1, 값1, 키2, 값2,,,)- JSON객체 형태로 변한해줍니다.
- {"키1" : "값1", "키2" : "값2",,,}의 형태로 출력됩니다.
-- 1. ROWNUM() : 현재 조회하는 쿼리의 결과셋에 대해 행번호를 부여하는 함수입니다.
SELECT rownum() AS rowNo, memberID, NAME, jumin
FROM tbl_member
ORDER BY memberId ASC;
# ROWNUM() 으로 행번호를 부여한 다음 DESC 정렬이되서 rowNo도 역순으로 나옴!
SELECT rownum() AS rowNo,memberID, NAME, jumin
FROM tbl_member
ORDER BY memberId DESC;    

-- 2. ROW_COUNT() : 쿼리문이 실행되면서 영향을 미친 행 수를 반환합니다.
# 쿼리문이 실행되어도 값이 사실상 기존 값과 같다면 0이 나옵니다. (MariaDB의 경우 UPDATE문 실행 시 실제로 값이 변경되는게 없으면 실제로 실행되지 않기 때문입니다.)
# (heidiSQL 기준) 
#  heidiSQL 설정에서 실행 옵션을 '일괄보내기'로 변경 후 위 두 쿼리를 한번에 실행해야 정상적인 결과를 받을 수 있습니다.
#  실행 옵션 => 상단 메뉴에서 '▶' 버튼 우측 하단 화살표 클릭 '일괄보내기'로 변경
UPDATE tbl_member SET addr2 = 'addr2' WHERE memberID = 'gee1';
SELECT ROW_COUNT(); 

-- 3. DEFAULT(열 이름) : 해당 열에 설정된 기본값(Default)을 반환해줍니다.
# CURRENT_TIMESTAMP() 함수를 사용할 경우 조회하는 기준 현재일시를 반환해주며, 
# AUTO_INCREMENT 속성을 사용할 경우 0을 반환해줍니다.
# 기본값이 설정되지 않은 열에 사용할 경우 'Field '열이름' doesn't have a default value' 오류 메시지 발생
SELECT
DEFAULT(mileage),
DEFAULT(memberState),
DEFAULT(leaveDate)
FROM tbl_member 
WHERE memberID = 'gee1';

-- 4. LAST_INSERT_ID() : 마지막 INSERT된 데이터의 AUTO_INCREMENT 열의 값을 반환해줍니다.
# AUTO_INCEREMENT를 쓰는 열이 없으면 사용할 수 없습니다.
INSERT INTO tbl_test (title_no, title)
VALUES ('0011', '테스트');
SELECT LAST_INSERT_ID();
# DELETE문과 응용 : 마직막으로 들어온 행 삭제하기!
DELETE FROM tbl_test
WHERE idx = LAST_INSERT_ID();

-- 5. USER() : 현재 DB를 사용중인 사용자 정보 표시해줍니다.
-- 6. CURRENT_USER() : 위와 동일
SELECT USER()
SELECT CURRENT_USER();

-- 7. DATABASE() : 현재 사용 중인 데이터베이스 명을 표시해줍니다.
SELECT DATABASE();

-- 8. VERSION() : 데이터베이스 시스템의 버전 정보를 표시해줍니다.
SELECT VERSION();

-- 9. JSON_OBJECT(키1, 값1, 키2, 값2,,,) : JSON객체 형태로 변한해줍니다.
# {"키1" : "값1", "키2" : "값2",,,}의 형태로 출력됩니다.
# 키와 값을 ,(쉼표)로만 구분해주므로 작성 시 주의가 필요합니다.
# 위와 같은 이유로 줄줄이 나열식으로 작성하는 것 보다 아래처럼 줄넘김을 이용하는게 읽기 편할 수 있습니다.
SELECT
JSON_OBJECT(
	'memberId', memberId
	, 'name', NAME
	, 'jumin', jumin
	, 'addr1', addr1
	, 'addr2', addr2
	, 'birthdaty', birthday
	, 'jobCode', jobCode
	, 'mileage', mileage
	, 'memberState', memberState
	, 'regDate', regDate
	, 'leaveDate', leaveDate
	) AS json_member
FROM tbl_member
ORDER BY memberId;

참고

MariaDB로 따라 하며 배우는 SQL프로그래밍 데이터베이스 기초에서 실무까지 - 나익수, 서연경 지음
위 책을 공부하며 작성하고 있습니다!

profile
Geehyun(장지현)
개발자를 꿈꾸는 병아리 (블로그 이전 준비중 입니다.)
이전 포스트

[MariaDB] 내장 함수① - 문자/시간/수치 관련

다음 포스트

[MariaDB] JOIN

0개의 댓글