SQL 프로그래밍 - 스토어드 프로시저, IF, CASE, WHILE, 동적 SQL

# 스토어드 프로시저 

DELIMITER $$
CREATE PROCEDURE myProcedure()
BEGIN
    <SQL 문 ~~>
END $$
DELIMITER ;

CALL myProcedure();

 

• 세미콜론 (;) 으로는 SQL 의 끝인지, 스토어드 프로시저의 끝인지 구별할 수 없어서 $$ 를 사용한다. 

 

* 스토어드 프로시저 매개변수

# 1. IN

- 프로시저에 값을 전달한다.

DELIMITER $$
CREATE PROCEDURE myProc(IN name VARCHAR(255))
BEGIN
    SELECT * FROM Member m
    WHERE m.name = name;
END $$

DELIMITER ;

--
CALL myProc('홍길동');

 

 

 

# 2. OUT

- 프로시저의 값을 반환한다. 

- 초기값은 프로시저 내에서 NULL 이며, 프로시저가 반환될 때 새로운 값이 호출자에게 리턴된다. 

DELIMITER $$
CREATE PROCEDURE myProc(IN name VARCHAR(255), OUT total INT)
BEGIN
    SELECT count(m) 
    INTO total
    FROM Member m
    WHERE m.name = name;
END $$

DELIMITER ;

--
CALL myProc('홍길동', @total);
SELECT @total;		-- @total 값 호출

 

 

 

# 3. INOUT

- 호출자에 의해 하나의 변수가 초기화되고 프로시저에 의해 수정된다. IN + OUT

- 프로시저가 리턴할 때 프로시저가 변경한 사항은 호출자에게 리턴된다. 

 

DELIMITER $$
CREATE PROCEDURE add_counter (INOUT count INT(4), IN x INT(4))
BEGIN
    SET count = count + x;
END $$

DELIMITER ;

--

SET @counter = 10;
CALL add_counter (@counter, 1);		-- SELECT @counter = 11
CALL add_counter (@counter, 2);		-- SELECT @counter = 13
CALL add_counter (@counter, 5);		-- SELECT @counter = 18

 

 

 

# IF 문

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

 

• SQL 문장들이 한 문장이라면 그 문장만 써도 되지만, 두 문장 이상이 처리되어야 할 때는 BEGIN ~ END 로 묶어줘야 한다.

 

* 활용 예제

DROP PROCEDURE IF EXISTS myProc;
DELIMITER $$
CREATE PROCEDURE myProc()
BEGIN 
    DECLARE debutDate DATE;		-- 1
    DECLARE curDate DATE;
    DECLARE days INT;
    
    SELECT debut_date INTO debutDate	-- 2
        FROM member
        WHERE mem_id = 'APN';
	
    SET curDATE = CURRENT_DATE();		-- 3
    SET days = DATEDIFF(curDATE, debutDate);   -- 4
    
    IF (days/365) >= 5 THEN            -- 5
        SELECT CONCAT('데뷔', days, '일');
    ELSE
        SELECT CONCAT('데뷔', days, '일');
    END IF;
END $$
DELIMITER ;

CALL myProc();

• 1. 변수를 3개 준비했다.
• 2. INTO 변수가 붙었다. 이럴 경우 결과를 변수에 저장한다. 
• 3. CURRENT_DATE() 함수로 현재 날짜를 curDate 에 저장한다.
• 4. DATEDIFF() 함수로 날짜 차이를 days 에 저장한다.
• 5. IF ~ ELSE ~ END IF

 

# CASE 문

* 형식

CASE
    WHEN 조건1 THEN
        SQL 문
    WHEN 조건2 THEN
        SQL 문
    ELSE
        SQL 문
END 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;

 

 

 

# WHILE 문

* 형식

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

 

 

* 예제 -  1에서 100까지 값을 모두 더하기

DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE i INT;  	-- 	1에서 100까지 증가할 변수
    DECLARE hap INT;	-- 더한 값을 누적할 변수
    SET i = 1;
    SET hap = 0;
    
    WHILE (i <= 100) DO
        SET hap = hap + i;
        SET i = i + 1;
	END WHILE;
    
    SELECT '합 --> ', hap;
END $$
DELIMITER ;

CALL whileProc();

 

 

* WHILE 문의 응용

• 1에서 100까지 합계에서 4의 배수를 제외시켜보자. 그리고 숫자를 더하는 중간에 합계가 1,000이 넘으면 더하는 것을 그만두자.
• 이럴 떄는 ITERATE 와 LEAVE 문을 사용한다.
  
  • ITERATE [레이블] : 지정한 레이블로 가서 계속 진행한다.  (CONTINUE)
  • LEAVE [레이블] : 지정한 레이블을 빠져나가낟. 즉, WHILE 문이 종료된다.  (BREAK)

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; 	-- 지정한 label 문으로 가서 계속 진행 
        END IF;
        SET hap = hap + i;
        IF (hap > 1000) THEN
            LEAVE myWhile;		-- 지정한 label 문을 떠남. 즉 While 종료
        END IF;
        SET i = i + 1;
    END WHILE;
    
    SELECT '1부터 100까지의 합 (4의 배수 제외), 1000 넘으면 종료 --> ', hap;
END $$
DELIMITER ;
CALL whileProc2();

 

 

 

# PREPARE 와 EXECUTE

• PREPARE 는 SQL 문을 실행하지는 않고 미리 준비만 해놓고, EXECUTE 는 준비한 SQL 을 실행합니다.
• 그리고 실행 후에는 DEALLOCATE PREPARE 로 문장을 해제해주는 것이 바람직하다.

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

 

• 이렇게 미리 SQL을 준비한 후에 나중에 실행하는 것을 동적 SQL 이라고 한다. 

 

* 동적 SQL 의 활용

• PREPARE 문에서는 ? 로 향후에 입력될 값을 비워 놓고, EXECUTE 에서 USING 으로 ? 에 값을 전달할 수 있다.
• 다음 예제는 실무에서 종종 사용된다. 
• 보안이 중요한 출입문에서는 출입한 내역을 테이블에 기록해 놓는다. 
  • 이때 출입증을 태그하는 순간의 날짜와 시간이 INSERT 문으로 만들어져서 입력되도록 한다. 

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;

 

• 결국 이 SQL 을 실행한 시점의 날짜와 시간이 입력된다.