📒 요약 : SQL에서도 스토어드 프로시저를 활용하여 프로그램을 작성할 수 있다. 다른 언어들에서 사용하는 IF, CASE. WHILE문 등의 구문을 이용할 수 있다.

SQL 프로그래밍

1. 스토어드 프로시저

스토어드 프로시저는 MYSQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체다. SQL프로그래밍은 기본적으로 스토어드 프로시저 안에 만들어야 한다. 스토어드 프로시저는 아래와 같은 구조를 갖는다.

DELIMITTER $$ 		#스토어드 프로시저의 이름
CREATE PROCEDURE
BEGIN 				#스토어드 프로시저 시작
	#SQL 프로그래밍 작성
END $$ 				# 스토어드 프로시저 종료
DELIMITER; 			#종료 문자를 세미콜론으로 변경
CALL				#스토어드 프로시저 실행

2. IF 문

IF문의 기본 형식

IF문은 조건문으로 가장 많이 사용되는 프로그래밍 문법 중 하나로, 다양한 조건문을 처리할 수 있다. SQL 프로그래밍에서의 기본 형식은 아래와 같다.

IF <조건식> THEN 
	SQL 문장들
END IF;

만약 SQL 문장들이 한 문장이라면 그 문장만 써도 되지만, 두 문장 이상이 처리되어야 할 때는 BEGIN~END로 묶어줘야 한다. 아래에 간단한 예시가 있다.

DROP PROCEDURE IF EXISTS ifProcol;			 #만약 기존에 ifProcol()을 만든 적이 있다면 삭제한다.
DELIMITER ##								#세미콜론으로는 sql의 끝인지, 스토어드 프로시저의 끝인지 구별할 수 없어서 $$를 사용한다.
CREATE PROCEDURE ifProcol()					#스토어드 프로시저의 이름을 ifProcol()로 지정한다.
BEGIN
	IF 100 = 100 THEN						#조건식으로 100과 100이 같은지 비교한다. 당연히 참이므로 다음 행이 실행된다.
    	SELECT '100은 100과 같습니다.';
    END IF;
END $$
DELIMITER;
CALL ifProcol();							#ifProcol()이 실행된다.

IF~ELSE문

IF~ELSE 문은 조건에 따라 다른 부분을 수행한다. 조건식이 참이라면 바로 다음 행을, 그렇지 않다면 다른 행을 실행한다. 아래의 예시를 살펴보자.

DROP PROCEDURE IF EXISTS ifProc2;			 #만약 기존에 ifProc2()을 만든 적이 있다면 삭제한다.
DELIMITER ##								#세미콜론으로는 sql의 끝인지, 스토어드 프로시저의 끝인지 구별할 수 없어서 $$를 사용한다.
CREATE PROCEDURE ifProc2()					#스토어드 프로시저의 이름을 ifProcol()로 지정한다.
BEGIN
	DECLARE myNum INT;						#DECLARE 예약어를 사용해서 myNum 변수를 선언한다. 변수의 데이터 형식을 INT로 지정한다.
    SET myNum = 200;						#SET 예약어로 myNum 변수에 200d을 대입한다.
    IF myNum = 100 THEN
    	SELECT '100입니다.';
    ELSE
    	SELECT '100이 아닙니다.';
    END IF;
ND $$
DELIMITER;
CALL ifProc2();							#ifProc2()이 실행된다.

3. CASE 문

여러 가지 조건 중에서 선택해야 하는 경우, CASE 문을 사용해서 조건을 설정할 수 있다 . CASE 문은 2가지 이상의 여러 가지 경우일 때 처리가 가능하므로 '다중 분기'라고 부른다. 다른 프로그래밍 언어의 SWITCH~CASE 문과 비슷한 기능을 한다고 생각하면 된다. 아래와 같은 형식을 사용한다.

CASE
	WHEN 조건1 THEN
    	SQL문장들1
    WHEN 조건2 THEN
    	SQL문장들2
    WHEN 조건3 THEN
    	SQL문장들3
	ELSE
    	SQL문장들4
END CASE;

CASE와 END CASE 사이에는 여러 조건을 넣을 수 있다. WHEN 다음에 조건이 나오는데, 조건이 여러 개라면 WHEN을 여러 번 반복한다. 그리고 모든 조건에 해당하지 않으면 마지막 ELSE 구문을 수행한다. CASE문을 활용해보자. 인터넷 마켓 데이터베이스의 회원들은 물건을 구매한다. 회원들의 총 구매액을 계산하여 회원 등급을 다음과 같이 4단계로 나누려고 한다.

총 구매액	회원 등급
1500 이상	최우수고객
1000~1499	우수고객
1~999	일반고객
0 이하(구매한 적 없음)	유령고객

1. 구매 테이블에서 회원별로 총 구매액을 구한다.

SELECT mem_id, SUM(price*amount) "총구매액"
	FROM buy
    GROUP BY mem_id
    ORDER BY SUM(price*amount) DESC;

그림1. 구매 테이블에서 회원 별 총 구매액 구하기

2. 구매 테이블에서 회원의 아이디별로 가격과 수량을 곱해서 총 구매액의 합계를 구한다. ORDER BY로 총 구매액이 많은 순서로 정렬한다.

SELECT mem_id, SUM(price*amount) "총구매액"
	FROM buy
    GROUP BY mem_id
    ORDER BY SUM(price*amount) DESC;

그림2. 구매 테이블에서 회원 별 총 구매액 구하고 정렬하기

3. 회원 테이블의 이름 행과 구매 테이블을 조인하여 회원의 이름 출력하기

SELECT B.mem_id, M.mem_name, SUM(price*amount) "총구매액"
	FROM buy B
		INNER JOIN member M
        ON B.mem_id = M.mem_id
	GROUP BY B.mem_id
	ORDER BY SUM(price*amount) DESC;

그림3. 회원의 이름 출력하기

4. 외부 조인을 활용하여 구매하지 않은 회원의 아이디와 이름 출력하기

구매 테이블에는 4명만 구매했으므로 나머지 6명에 대한 아이디 등의 정보가 없다. 따라서 SELECT에서 회원 테이블의 아이디를 조회하고, GROUP BY 역시 member 테이블을 참조하도록 변경한다.

SELECT M.mem_id, M.mem_name, SUM(price*amount) "총구매액"
	FROM buy B
		RIGHT OUTER JOIN member M
        ON B.mem_id = M.mem_id
	GROUP BY M.mem_id
	ORDER BY SUM(price*amount) DESC;

그림4. 외부 조인을 활용하여 구매하지 않은 회원의 이름 출력하기

5. 총 구매액에 따라 회원 등급 구분하기

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 OUTER JOIN member M
        ON B.mem_id = M.mem_id
	GROUP BY M.mem_id
	ORDER BY SUM(price*amount) DESC;

그림5. 구매액에 따른 회원 등급 출력하기

4. WHILE 문

WHILE 문은 필요한 만큼 계속 같은 내용을 반복한다. 조건식이 참인 동안에 수행하는데, 기본적인 형식은 아래와 같다.

WHILE <조건식> DO
	SQL문장들
END WHILE;

아래는 1에서 100까지의 모두 더하는 간단한 기능을 WHILE 문으로 구현하는 SQL문이다.

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();

그림6. WHILE문을 활용하여 1부터 100까지 더하기

WHILE문 응용하기

ITERATE문과 LEAVE문을 활용하여 WHILE문을 보다 정교하게 이용할 수 있다.

• ITERATE [레이블] : 지정한 레이블로 가서 계속 진행한다. 프로그래밍 언어의 CONTINUE와 비슷한 기능을 한다.
• LEAVE [레이블] : 지정한 레이블을 빠져나간다. WHILE문이 종료된다. 프로그래밍 언어의 BREAK문과 비슷한 기능을 한다.

5. 동적 SQL

SQL 문은 내용이 고정되어 있는 경우가 대부분이지만, 상황에 따라 내용 변경이 필요할 때 동적 SQL을 사용하면 변경되는 내용을 실시간으로 적용시켜 사용할 수 있다.

PREPARE와 EXECUTE

PREPARE는 SQL문을 실행하지는 않고 미리 준비만 해놓고, EXECUTE는 준비한 SQL문을 실행하는 기능이다. 그리고 실행한 뒤에는 DEALLOCATE PREPARE로 문장을 해제해주는 것이 좋다. 아래에 간단한 예시가 있다.

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

PREPARE문에서는 ?로 향후 입력될 값을 비워두고, EXECUTE에서 USING으로 ?에 값을 전달할 수 있다. 그러면 실시간으로 필요한 값들을 전달해서 동적으로 SQL문이 실행된다.