[혼공SQL] SQL 프로그래밍

 

  스토어드 프로시저

MySQL에서 프로그래밍 기능이 필요할 때 사용하는 데이터베이스 개체를 의미합니다.

 

스토어드 프로시저의 형식은 아래와 같습니다.

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름()
BEGIN
	-- SQL 프로그래밍 구문들
END $$
DELIMITER ; 

CALL 스토어드_프로시저_이름(); -- 스토어드 프로시저 호출

 

여기서 DELIMITER를 사용하는 것을 알 수 있습니다. DELIMITER는 문장을 구분자를 지정하는 키워드입니다. 만약 우리가 스토어드 프로시저에서 문장 구분자를 변경하지 않는다면, 스토어드 프로시저 내에서 작성하는 SQL문이 서버로 끊어져서 전송되게 됩니다.

 

만약 문장 구분자를 변경하여 진행한다면, 스토어드 프로시저의 전체를 한번에 보낼 수 이쓰므로 우리는 DELIMITER를 통해서 스토어드 프로시저 선언 전 문장 구분자를 변경하여 전송하는 것입니다. 이후 스토어드 프로시저의 선언이 종료되었다면, 다시 구분자를 원래대로 세미콜론(;)으로 변경해주면 됩니다.

 

 

  IF문

조건문의 기능을 하는 키워드입니다. 형식은 아래와 같습니다.

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

 

'SQL 문장들'이라고 선언되어 있는 부분은 SQL문이 하나일 수도 있지만 여러개일 수도 있습니다. 그럴 때는 BEGIN ~ END로 묶어서 진행해야 합니다.

 

아래 간단한 예를 확인해봅시다.

DELIMITER $$
CREATE PROCEDURE ifProc1()
BEGIN
	IF 100 = 100 THEN -- 항상 참
    	SELECT '100은 100과 같습니다.'; -- 출력
    END IF;
END $$
DELIMITER ; 

CALL ifProc1();

 

SQL에서는 동등비교연산자(==)는 등호(=)입니다. 또한 SELECT 뒤에 문자가 나오면 해당 구문이 출력됩니다. print 처럼 말이죠.

 

 

또한 IF만 쓰이는 것은 아니고 IF ~ ELSE도 쓰입니다. 아래의 예제로 바로 확인해봅시다.

DELIMITER $$
CREATE PROCEDURE ifProc2();
BEGIN
	DECLARE myNum INT;
	SET myNum = 200;
	IF myNum = 100 THEN
		SELECT '100입니다.';
	ELSE
		SELECT '100이 아닙니다.';
	END IF;
END $$
DELIMITER ;

CALL ifProc2();

 

 

  CASE 문

IF문은 위에서 보았듯이 이중 분기만 가능합니다. CASE 문은 다중 분기가 가능합니다.

바로 기본 형식을 확인해보시죠.

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

 

예제로 바로 살펴봅시다. 아래의 예제는 점수에 따라 학점을 부여해주는 기능의 SQL문입니다.

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

 

 

  WHILE 문

우리가 사용하는 반복문과 동일합니다. 형식은 아래와 같습니다.

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

 

그럼 while문을 이용하여 1부터 100까지의 모든 수를 더해보는 SQL문을 짜봅시다.

DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
	DECLARE i INT;
	DECLARE sum INT;
	SET i = 1;
	SET sum = 0;

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

	SELECT '1부터 100까지의 합 : ', sum;
END $$
DELIMITER ;

CALL whileProc();

 

 

또한 WHILE문 내에서는 ITERATE와 LEAVE를 사용할 수 있습니다. 두 키워드의 기능은 아래와 같습니다.

• ITERATE [레이블] : 지정한 레이블로 가서 계속 진행합니다.(continue와 같음)
• LEAVE [레이블] : 지정한 레이블을 빠져나갑니다. (break와 같음)

 

여기서 레이블이 나오는데, 아래의 예시를 통해서 바로 레이블이 무엇인지 확인해봅시다.

 

해당 예제는 1부터 100까지의 합을 계산하는 SQL문에서 아래의 두 조건이 추가되었습니다.

• 4의 배수는 더하지 않는다.
• 덧셈 과정에서 sum의 값이 1000을 넘으면 종료한다.

해당 조건을 만족하는 SQL문을 짜봅시다.

DELIMITER $$
CREATE PROCEDURE whileProc2()
BEGIN
	DECLARE i INT;
	DECLARE sum INT;
	SET i = 1;
	SET sum = 0;

	myWhile: -- WHILE에 myWhile이라는 라벨 지정
	WHILE (i <= 100) DO
		IF (i%4 = 0) THEN
			SET i = i + 1;
			ITERATE myWhile; -- myWhile 라벨의 시작으로 이동
		END IF;

		SET sum = sum + i;

		IF(sum > 1000) THEN
			LEAVE myWhile; -- myWhile 라벨을 탈출
		END IF;

		SET i = i + 1;
	END WHILE:

	SELECT '1부터 100까지의 합(4의 배수 제외), 1000 넘으면 종료 : ', sum;
END $$
DELIMITER ;

CALL whileProc2();

이렇게 라벨을 지정하여 LEAVE문과 ITERATE 문도 사용할 수 있습니다.

 

 

  동적 SQL

동적 SQL이란 미리 SQL문을 준비한 후에 나중에 실행하는 것을 의미합니다. 이름과 비슷하게 매 쿼리마다 고정적으로 실행하는 것이 아닌 어떠한 변수의 값이 변하면서 쿼리의 결과도 변동되는 SQL을 의미하죠.

 

 

앞서 우리는 잠깐 PREPARE와 EXECUTE를 배웠습니다. 이것이 동적 SQL의 대표적인 키워드입니다. PREPARE 키워드를 통해서 쿼리문을 준비하고 EXECUTE를 통해서 해당 SQL을 실행합니다.

 

이 때, PREPARE로 선언한 SQL문이 모두 사용되었다면 DEALLOCATE PREPARE <쿼리명>; 으로 선언된 쿼리를 삭제해주는 것이 좋습니다.

 

또 다른 예시로 동적 SQL을 이용해 출입문에 출입증이 찍힐 때, 시간을 기록하는 SQL문을 짜봅시다. 아래와 같이 작성할 수 있습니다.

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

SET @curDate = CURRENT_TIEMSTAMP(); -- 현재 날짜 및 시간

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

SELECT * FROM get_table;

해당 SQL은 매 시간마다 다른 SQL문이 실행됩니다. 이런 식으로 동적 SQL문을 생성할 수 있습니다.

 

ㄷ