스토어드 프로시저(stored procedure)의 개념과 사용 방법

스토어드 프로시저는 SQL문과 프로그래밍 기능이 합쳐진 것이 스토어드 프로시저이다.

 

 

스토어드 프로시저의 형식은 아래와 같다.($$ 또는 ##을 많이 쓴다.)

 

커피에 비유를 해보면 스토어드 프로시저 생성은 커피 자판기를 만드는 것이고 스토어드 프로시저 호출은 커피를 뽑는 것이다.

아래 CALL과정이 커피를 뽑는 작업이라 보면된다.

 

 

프로시저를 생성해주고

DROP PROCEDURE IF EXISTS user_proc;
DELIMITER $$
CREATE PROCEDURE user_proc()
BEGIN
	SELECT * FROM member; -- 스토어드 프로시저 내용
END $$
DELIMITER ;

실행을 시킨다. 여러 번 실행시킬 수가 있다.

CALL user_proc();

 

프로시저를 삭제를 해보자.

DROP PROCEDURE user_proc;​

 

 

입력 매개변수를 넣어보자.

USE market_db;
DROP PROCEDURE IF EXISTS user_proc1;
DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
BEGIN

  -- 여기서 SELECT * FROM member WHERE mem_name = 에이핑크 라고 넘어간다.
  SELECT * FROM member WHERE mem_name = userName; 
END $$
DELIMITER ;

CALL user_proc1('에이핑크');

 

 

다른 매개변수를 넣어보자.

DROP PROCEDURE IF EXISTS user_proc2;
DELIMITER $$
CREATE PROCEDURE user_proc2(
    IN userNumber INT, 
    IN userHeight INT  )
BEGIN
  SELECT * FROM member 
    WHERE mem_number > userNumber AND height > userHeight;
END $$
DELIMITER ;

CALL user_proc2(6, 165);

 

 

 

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


-- noTable이 있는지 없는지 확인
DESC noTable;

noTable은 프로시저를 생성하는 시점 noTable이 있는지 없는지 확인을 안하고 프로시저를 실행하는 시점에 noTable이 있는지 학인한다.

 

CREATE로 noTable을 만들어보자.

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

 

@를 붙여서 변수명을 넘기고 SELECT 해주면 아래와 같다.

여러번 눌러주면 여러번 증가한다.

CALL user_proc3 ('테스트1', @myValue);
SELECT CONCAT('입력된 ID 값 ==>', @myValue);

 

 

프로시저를 만들고 내용을 출력해보자.

DROP PROCEDURE IF EXISTS ifelse_proc;
DELIMITER $$
CREATE PROCEDURE ifelse_proc(
    IN memName VARCHAR(10)
)
BEGIN
    DECLARE debutYear INT; -- 변수 선언
    -- 대뷔연도만 추출하게 된다.
    SELECT YEAR(debut_date) into debutYear FROM member
    -- 오마이걸의
        WHERE mem_name = memName;
        
    -- 2015년보다 크면 ~~~
    IF (debutYear >= 2015) THEN
            SELECT '신인 가수네요. 화이팅 하세요.' AS '메시지';
    ELSE
            SELECT '고참 가수네요. 그동안 수고하셨어요.'AS '메시지';
    END IF;
END $$
DELIMITER ;


CALL ifelse_proc ('오마이걸');

 

 

반복문을 써보자.

DROP PROCEDURE IF EXISTS while_proc;
DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
	DECLARE hap INT;
    DECLARE num INT;
    SET hap = 0;
    SET num = 1;
    
    WHILE (num <= 100) DO -- 100까지 반복
		SET hap = hap + num;
        SET num = num + 1; -- 숫자증가
        
	END WHILE;
    SELECT hap AS '1~100합계';
END $$
DELIMITER ;

CALL while_proc();

 

 

동적 SQL을 살펴보자.

DROP PROCEDURE IF EXISTS dynamic_proc;
DELIMITER $$
CREATE PROCEDURE dynamic_proc(
-- 테이블 이름이 만들어진 쿼리문이
    IN tableName VARCHAR(20)
)
BEGIN
-- 동적으로 바뀐다.
  SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);
-- query문을 준비하고
  PREPARE myQuery FROM @sqlQuery;
-- query문을 실행
  EXECUTE myQuery;
  DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;

CALL dynamic_proc ('member');
CALL dynamic_proc ('buy');

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<출처 한빛미디어: 스토어드 프로시저(stored procedure)의 개념과 사용 방법(입출력 매개변수의 활용 방법)> 

https://www.youtube.com/watch?v=cw1wGN0ZdFA&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=19