혼공스 19일차(스토어드 프로시저)

스토어드 프로시저란?

• MySQL의 스토어드 프로시저는 SQL에 프로그래밍 기능을 추가해서
  일반 프로그래밍 언어와 비슷한 효과를 낼 수 있습니다.
• 스토어드 프로시저는 쿼리문의 집합으로도 볼 수 있습니다.
• 어떠한 동작을 일괄 처리하기 위한 용도로도 사용합니다.
• 아래는 가장 많이 사용되는 필수적인 형식만 표시한 것입니다.

DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름( IN 또는 OUT 변수)
BEGIN
	이 부분에 SQL 프로그래밍 코드를 작성
END $$
DELIMITER ;

 

 

스토어드 프로시저를 호출하는 형식은 아래와 같습니다.

CALL 스토이드_프로시저_이름();

 

 

스토어드 프로시저의 생성

• 계속 사용했던 market_db 파일을 예시로 생성해 보겠습니다.

USE market_db;
DROP PROCEDURE IF EXISTS pro_example;

DELIMITER $$
CREATE PROCEDURE pro_example()
BEGIN
    SELECT * FROM member;
END $$
DELIMITER ;

CALL pro_example();

 

 

스토어드 프로시저의 삭제

• 내용을 삭제하기 위해 DROP PROCEURE를 사용하면 됩니다.
• 주의할 점으로 괄호를 붙이면 안 됩니다.

DROP PROCEDURE pro_example;

 

 

매개변수 사용방법

• 스토어드 프로시저에는 실행 시 입력 매개변수를 지정할 수 있습니다.
• 매개변수는 다른 용어로 파라미터라고 부릅니다.

 

입력 매개변수를 지정하는 형식은 아래와 같습니다.

IN 입력_매개변수_이름 데이터_형식

 

입력매개변수가 있는 프로시저를 실행하는 방법은 아래와 같습니다.

CALL 프로시저_이름(전달_값);

 

출력 매개변수의 형식은 다음과 같습니다.

OUT 출력_매개변수_이름 데이터_형식

 

출력 매개변수가 있는 프로시저를 실행하는 방법은 아래와 같습니다.

CALL 프로시저_이름(@변수명);
SELECT @변수명;

 

 

입력매개변수 활용

• 아래 코드는 인원이 4명 이상 평균 키가 150 이상인 그룹을 조회하는 코드입니다.

DELIMITER $$
CREATE PROCEDURE pro_example(
    IN uNumber INT, 
    IN uHeight INT  )
BEGIN
  SELECT * FROM member 
    WHERE mem_number > uNumber AND height > uHeight;
END $$
DELIMITER ;

CALL pro_example(4, 150);

 

 

출력매개변수 활용

• 스토어드 프로시저를 만드는 시점에는 존재하지 않는 테이블을 사용해도 됩니다.
• 하지만 CALL로 실행하는 시점에는 사용한 테이블이 있어야 합니다.

 

먼저 테이블 하나를 만들어주겠습니다. (id열과 txt열을 만들어주었습니다.)

CREATE TABLE IF NOT EXISTS Table1(
    id INT AUTO_INCREMENT PRIMARY KEY, 
    txt CHAR(10)
);

 

아래 코드는 Table1에서 받은 값을 입력하고 id열의 최댓값을 알아내는 기능을 하는 코드입니다.

DROP PROCEDURE IF EXISTS pro_example2;
DELIMITER $$
CREATE PROCEDURE pro_example2(
    IN txtValue CHAR(10),
    OUT outValue INT     )
    
BEGIN
  INSERT INTO Table1 VALUES(NULL,txtValue);
  SELECT MAX(id) INTO outValue FROM Table1; 
END $$
DELIMITER ;

 

한번 실행을 해보겠습니다.

(코드를 계속 실행하면 값이 증가하는 것을 확인할 수 있습니다.)

CALL pro_example2 ('테스트1', @myValue);
SELECT CONCAT('ID 값 ==>', @myValue);

 

 

 

SQL프로그래밍 활용

• 인원수가 5명 이상이면 "인원이 많네요"를 5명 미만이면 "인원이 적네요"를 출력하는 코드입니다.
• 마마무(인원 4명)로 테스트한 결과입니다.

DROP PROCEDURE IF EXISTS proc_example4;
DELIMITER $$
CREATE PROCEDURE proc_example4(
    IN memName VARCHAR(10)
)
BEGIN
    DECLARE memNumber INT;
    SELECT mem_number into memNumber FROM member
        WHERE mem_name = memName;
    IF (memNumber >= 5) THEN
            SELECT '인원이 많네요' AS '메시지';
    ELSE
            SELECT '인원이 적어요'AS '메시지';
    END IF;
END $$
DELIMITER ;

CALL proc_example4('마마무');

 

 

동적 SQL

• 동적 SQL은 이름 그대로 다이내믹하게 SQL이 변경됩니다.
• 아래 코드는 테이블 이름을 매개변수로 전달받아서 해당 테이블을 조회하는 코드입니다.
• 넘겨받은 테이블 이름을 @sqlQuery 변수에 SELECT 문으로 문자열을 생성해 놓은 것입니다.
  (SELECT * FROM member가 생성됩니다.)

DROP PROCEDURE IF EXISTS proc_example6;
DELIMITER $$

CREATE PROCEDURE proc_example6(
    IN tName VARCHAR(20)
)
BEGIN
  SET @sqlQuery = CONCAT('SELECT * FROM ', tName);
  PREPARE myQuery FROM @sqlQuery;
  EXECUTE myQuery;
  DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;

CALL proc_example6('member');