SQL 고급 문법 (3)

prefer·2024년 12월 28일

SQL 기초

목록 보기

7/15

스토어드 프로시저

스토어드 프로시저는 MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체이다. SQL 프로그래밍은 기본적으로 스토어드 프로시저 내부에 만들어야 한다.

사용 방법을 알아보도록 하자.

DELIMITER $$
CREATE PROCEDURE 스토어드 프로시저 이름()
BEGIN
 프로그래밍 코딩
END $$
DELIMITER ;
CALL 스토어드 프로시저 이름()

DELIMITER는 구분 문자를 재정의하는 예약어이다. 즉 스토어드 프로시저 내부의 쿼리문에서 사용되는 여러개의 ;을 하나의 프로시저로 인식하기 위해 구분 문자를 ;를 제외한 다른 구분 문자로 임시적으로 바꾸고, 스토어드 프로시저 정의를 한다. 그리고 다시 ;로 복구하면 구분자를 정상적으로 사용할 수 있다.

일반적으로 구분 문자(DELIMITER)는 $$를 많이 사용하나 /, &, @를 사용해도 무관하다. 사용할 때는 다른 기호와 중복될 수 있으므로 문자를 2개 연속 사용하는 것을 권장한다.

스토어드 프로시저의 시작과 끝은 BEGIN ~ END (구분문자)로 구분할 수 있다. 그리고 정의한 스토어드 프로시저는 CALL (스토어드 프로시저 이름)으로 실행할 수 있다.

IF 문

IF문은 일반적인 프로그래밍 언어에서 사용되는 개념과 같게 사용된다.

IF 조건식 THEN
	쿼리문들
END IF;

IF문은 IF 뒤에 나오는 (조건식)이 참이라면 쿼리문들을 실행한다. IF로 열어주고 쿼리문을 모두 작성하면 반드시 END IF로 닫아줘야 한다.

DROP PROCEDURE IF EXISTS ifProc1;
DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
	IF 100 = 100 TEHN
		SELECT '100은 100과 같습니다';
	END IF;
END $$
DELIMITER ;
CALL ifProc1();

위는 스토어드 프로시저를 활용한 예시인데, 우선 DROP PROCEDURE를 사용하여 기존에 ifProc1 프로시저가 존재한다면 제거한다.

세미콜론으로는 SQL의 끝인지 스토어드 프로시저의 끝인지 구분이 힘드므로 구분 문자로 $$ 사용하였고, 스토어드 프로시저의 이름은 ifProc1()로 지정하였다. 이때 IF문 내부의 조건식은 반드시 참이므로 SELECT 쿼리문이 실행된다.

참고로 SELECT 뒤에 문자가 나오면 해당 문자를 화면에 출력한다.

IF ~ ELSE 문

SQL에서도 다른 프로그래밍 언어와 마찬가지로 else를 지원한다. 다른 프로그래밍 언어와 마찬가지로 IF문의 조건문에 해당되지 않으면 ELSE의 쿼리문이 실행된다.

DROP PROCEDURE IF EXISTS ifProc2;
DELIMITER $$
CREATE PROCEDURE ifProc2()
BEGIN
	DECLARE myNum INT;
	SET myNum = 20;
	IF myNum = 100 TEHN
		SELECT '100은 100과 같습니다';
	ELSE
		SELECT '100이 아닙니다.';
	END IF;
END $$
DELIMITER ;
CALL ifProc1();

해당 스토어드 프로시저에서 DECLARE가 사용됐는데, DECLARE 예약어로 변수 선언을 할 수 있다. SET을 사용하여 변수에 값을 할당했고, myNum이 100일 경우 IF 아래의 쿼리문들 실행하고 아닐 경우 ELSE 아래의 쿼리문들 실행한다.

DROP PROCEDURE IF EXISTS ifProc3;
DELIMITER $$
CREATE PROCEDURE ifProc3()
BEGIN
	DECLARE debutDate DATE;
	DECLARE curDate DATE;
	DECLARE days INT;

	SELECT debut_date INTO debutDate
	FROM market_db.member
	WHERE mem_id = 'APN';
	
	SET curDATE = CURRENT_DATE();
	SET days = DATEDIFF(curDATE, debutDate);
	
	If (days/365) >= 5 THEN
		SELECT CONCAT('데뷔한 지 ', days, '일이나 지났습니다. 핑순이들 축하합니다!');
	ELSE
		SELECT '데뷔한 지' + days + '일밖에 안되었네요. 핑순이들 화이팅~';
	END IF;
END $$
DELIMITER ;
CALL ifProc3();

이번에는 INTO (변수 이름)을 사용하여 SELECT절의 결과를 변수에 저장했다. CURRENT_DATE() 함수는 현재 날짜 반환하는 함수이고, CURRENT_TIMESTAMP() 함수는 현재 날짜와 시간을 반환하는 함수이다.

DATEDIFF(날짜1, 날짜2) 함수는 날짜2부터 날짜1까지가 일수로 몇일인지 반환하는 함수이다. 예를 들어 날짜1이 2021-12-31이고 날짜2가 2021-12-30이라면 1을 반환하고, 날짜2가 더 최신이라면 음수를 반환한다.

CASE 문

IF문은 참 또는 거짓의 경우만 존재하므로 2중 분기라고 부르나, CASE 문은 2가지 이상의 경우를 처리 가능하므로 다중 분기라고 한다.

CASE
	WHEN 조건1 THEN
	 쿼리문들
	WHEN 조건2 THEN
	 쿼리문들
	WHEN 조건3 THEN
	 쿼리문들
	ELSE
	 쿼리문들
END CASE;

WHEN 다음에는 조건을 지정하는데 조건이 여러 개라면 WHEN을 여러 번 반복하여 사용한다. 만약 모든 WHEN의 조건에 해당하지 않으면 ELSE가 실행된다.

<실행>

DROP PROCEDURE IF EXISTS caseProc;
DELIMITER $$
CREATE PROCEDURE caseProc()
BEGIN
	DECLARE point INT;
	DECLARE credit CHAR(1);
	SET point = 88;
	
	CASE
		WHEN point >= 90 THEN
			SET credit = 'A';
		WHEN point >= 80 THEN
			SET credit = 'B';
		WHEN point >= 70 THEN
			SET credit = 'C';
		WHEN point >= 60 THEN
			SET credit = 'D';
		ELSE
			SET credit = 'F';
	END CASE;
	SELECT CONCAT('취득점수==>', point), CONCAT('학점==>', credit);
END $$
DELIMITER ;
CALL caseProc();

<결과>

해당 스토어드 프로시저는 point에 따라서 credit에 학점을 부여하는 프로시저이다. 만약 90점 이상, 80점 이상, 70점 이상, 60점 이상과 같은 조건들에 해당하지 않으면 F학점이 부여된다.

이번에는 CASE문을 조금 더 활용해보도록 하자.

<실행>

SELECT M.mem_id, M.mem_name, SUM(price*amount) "총구매액",
	CASE
		WHEN (SUM(price*amount) >= 1500) THEN '최우수고객'
		WHEN (SUM(price*amount) >= 1000) THEN '우수고객'
		WHEN (SUM(price*amount) >= 1) THEN '일반고객'
		ELSE '유령고객'
	END "회원등급"
FROM buy B
RIGHT JOIN member M
on B.mem_id = M.mem_id
GROUP BY M.mem_id
ORDER BY SUM(price*amount) DESC;

<결과>

회원들의 총 구매액을 계산해서 회원의 등급을 나눠 추출하는 쿼리문이다. GROUP BY로 회원의 아이디 별로 총 구매액을 계산하고, ORDER BY로 총 구매액이 많은 순서대로 정렬한다.

조회할 때 주의할 점은 RIGHT JOIN으로 구매한 적이 없는 회원도 조회해야 한다.

END CASE 대신에 END (열 이름)과 같은 형식으로 열 이름을 지정할 수 있다.

WHILE 문

WHILE문도 다른 프로그래밍 언어에서 사용되는 것과 마찬가지로 조건식이 만족될 동안 반복시키고 싶을 때 사용되는 구문이다.

WHILE 조건식 DO
 쿼리문들
END WHILE;

이 WHILE문은 조건식이 참인 동안에 쿼리문들을 계속해서 반복한다.

<실행>

DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
	DECLARE i INT;
	DECLARE hap INT;
	SET i = 1;
	SET hap = 0;

	WHILE (i <= 100) DO
		SET hap = hap + i;
		SET i = i + 1;
	END WHILE;

	SELECT '1부터 100까지의 합 ==>', hap;
END $$
DELIMITER ;
CALL whileProc();

<결과>

해당 WHILE문은 1부터 100까지의 합을 구한다. SET을 이용하여 숫자를 지속적으로 증가시키기 때문에 WHILE문이 정상적으로 종료된다.

이번에는 기존 프로그래밍 언어에 있던 continue, break와 같은 기능을 하는 ITERATE와 LEAVE를 알아보자. 해당 기능을 사용하기 위해서는 반드시 레이블을 지정해야 한다.

<실행>

DROP PROCEDURE IF EXISTS whileProc2;
DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
	DECLARE i INT;
	DECLARE hap INT;
	SET i = 1;
	SET hap = 0;
	
	myWhile:
	WHILE (i <= 100) DO
		IF (i%4 = 0) THEN
			SET i = i + 1;
			ITERATE myWhile;
		END IF;

		SET hap = hap + i;

		IF (hap > 1000) THEN
			LEAVE myWhile;
		END IF;

		SET i = i + 1;
	END WHILE;
	
	SELECT '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 ==>', hap;
END $$
DELIMITER ;
CALL whileProc2()'

<결과>

우선 WHILE문 바깥에 (레이블명):으로 WHILE문에 myWhile이라는 레이블을 지정해주었다. ITERATE (레이블명)은 지정한 레이블로 가서 계속 진행한다는 뜻이다. 그리고 LEAVE (레이블명)은 지정한 레이블을 탈출, 즉 WHILE문을 종료한다는 뜻이다.

따라서 위 스토어드 프로시저는 i가 4의 배수라면 i를 1 증가시키고 ITERATE를 만나서 myWhile, 즉 WHILE문 다시 진행한다. 그리고 hap이 1000을 초과하면 LEAVE를 만나서 myWhile 레이블 탈출, 즉 WHILE문을 종료한다.

동적 SQL

SQL 문은 작성된 내용이 고정되어 있으나, 필요할 때마다 작성된 SQL의 내용을 변경해야할 때가 있다. 이럴 때 동적 SQL을 사용하여 변경되는 내용을 실시간으로 적용할 수 있다.

즉 동적 SQL이란 미리 SQL을 준비한 후 나중에 실행하는 것을 말한다.

USE market_db;
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK"';
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;

PREPARE (쿼리문)은 쿼리문을 실행하지 않고 준비하다가 EXECUTE (쿼리문)이 실행되면 준비한 쿼리문을 실행한다. 실행 완료한 쿼리문은 DEALLOCATE PREPARE (쿼리문)으로 해제한다.

해당 예시는 매우 간단하게 작성된 예시라 내용의 변경이 없지만, 쿼리문 내부에 ?를 사용하여 ?에 원하는 값을 실행 시점에 주입할 수 있다. 자세한 사용법은 아래 예시를 확인해보자.

<실행>

DROP TABLE IF EXISTS gate_table;
CREATE TABLE gate_table(id INT AUTO_INCREMENT PRIMARY KEY, entry_time DATETIME);

SET @curDate = CURRENT_TIMESTAMP();

PREPARE myQuery FROM 'INSERT INTO gate_table VALUES(NULL, ?)';
EXECUTE myQuery USING @curDate;
DEALLOCATE PREPARE myQuery;

SELECT * FROM gate_table;

<결과>

스토어드 프로시저에서 변수는 DECLARE로 선언이 필요한데, 이를 제외한 변수는 @변수명 사용이 가능하며 @변수명은 별도의 선언이 필요없다. 여기서는 ?를 사용하여 입력값을 비워놓고 USING문으로 해당 동적 SQL을 실행할 때 현재 날짜와 시간을 주입하여 gate_table에 삽입하였다.

출처

혼자 공부하는 SQL(우재남 저, 한빛미디어)

prefer

기술적 의사결정에 객관성을 가지는 Back-End 개발자 이선호입니다.

SQL 고급 문법 (3)

SQL 기초

스토어드 프로시저

IF 문

IF ~ ELSE 문

CASE 문

WHILE 문

동적 SQL

출처

SQL 고급 문법 (2)

테이블 생성과 제약 조건

0개의 댓글