MySQL - 7.1 스토어드 프리시저 - 스토어드 프리시저 사용 방법

스토어드 프로시저

스토어드 프로시저는 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있다. ( SQL + 프로그래밍 기능 -> 스토어드 프로시저 )

스토어드 프로시저 사용

• 스토어드 프로시저 만들기 CREATE PROCEDURE : 커피 자판기를 만들었다.

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

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

• 만든 스토어드 프로시저 호출하기 CALL : 커피 뽑기

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

ex)
CALL user_proc();

• 스토어드 프로시저 삭제하기

DROP PROCEDURE user_proc;

스토어드 프로시저 - 매개변수 사용

스토어드 프로시저에서는 실행 시 입력 매개변수를 지정할 수 있다.
입력 매개변수를 쉽게 비유하면 자판기를 사용할 때 동전을 넣고 버튼을 누르는 동작으로 생각하면 된다.

• 입력 매개변수 지정

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

ex)
DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
BEGIN
  SELECT * FROM member WHERE mem_name = userName; 
END $$
DELIMITER ;

(매개변수 - 혼공SQL 교재)

• 입력 매개변수가 있는 스터어드 프로시저를 실행하기 위해서는 다음과 같이 괄호 안에 값을 전달하면 된다.

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

ex)
CALL user_proc1('여자친구');

아래와 같이 매개변수 2개도 사용 가능
DROP PROCEDURE IF EXISTS user_proc2;
DELIMITER $$
CREATE PROCEDURE user_proc2(
    IN userNumber INT, 
    IN userAddr CHAR(10)  )
BEGIN
  SELECT * FROM member 
    WHERE mem_number > userNumber AND addr = userAddr;
END $$
DELIMITER ;

CALL user_proc2(6, '서울');

• 위에서 이렇게 스토어드 프로시저에서 처리된 결과를 출력 매개변수를 통해 얻을 수도 있는데, 출력 매개변수는 커피 자판기에서 미리 준비하고 있는 컵이라고 보면 된다. 비어 있는 컵에는 커피가 담겨서 돌아온다.

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

(매개변수 - 혼공SQL 교재)

=> 출력 매개변수에 값을 대입하기 위해서는 주로 SELECT ~ INTO 문을 사용

• 출력 매개변수가 있는 스토어드 프로시저를 실행

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

만들어 봤는데 잘 실행이 된다.
DROP PROCEDURE IF EXISTS user_proc3;
DELIMITER $$
CREATE PROCEDURE user_proc3(
    IN txtValue CHAR(10), 
    OUT outValue INT     ) -- IN : 입력 매개변수 , OUT : 출력 매개변수
BEGIN
  INSERT INTO noTable VALUES(NULL,txtValue);
  SELECT MAX(id) INTO outValue FROM noTable; 
END $$
DELIMITER ;

오잉 근데 이렇게 확인해보니, noTable을 애초에 안 만들었는데 위의 프로시저 생성은 성공했네?
왜냐하면 프로시저를 생성만 했지 실행은 하지 않았기 때문! 실행하면 에러가 날 것
DESC noTable;

아래와 같이 테이블을 만들어주고
CREATE TABLE IF NOT EXISTS noTable(
    id INT AUTO_INCREMENT PRIMARY KEY, 
    txt CHAR(10)
);

스토어드_프로시저 실행!
: myValue라는 매개변수를 위의 프로시저에 할당시키고, myValue라는 변수에 값이 저장된다!
CALL user_proc3 ('테스트1', @myValue);

=> 이렇게 저장된 값을 확인하기 위한 코드!
SELECT CONCAT('입력된 ID 값 ==>', @myValue);

• 추가 예시 1)

* 멤버수가 5명 이상인지 확인하는 프로시저 생성
DELIMITER $$
CREATE PROCEDURE ifelse_proc(
    IN memName VARCHAR(10)
)
BEGIN
    DECLARE mem_number INT; -- 변수 선언
    SELECT mem_number FROM member
        WHERE mem_name = memName;
    IF (mem_number >= 5) THEN
            SELECT '멤버수가 5명 이상이세요!' AS '메시지';
    ELSE
            SELECT '멤버수가 5명 미만이시네요!'AS '메시지';
    END IF;
END $$
DELIMITER ;

* 프로시저 부르기
CALL ifelse_proc ('오마이걸');

• 추가 예시 2)

* 1부터 100까지 합하는 프로시저 생성
DROP PROCEDURE IF EXISTS while_proc;
DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
    DECLARE hap INT; -- 합계
    DECLARE num INT; -- 1부터 100까지 증가
    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();

• 추가 예시 3) 동적 SQL!
  : SQL문이 고정되어 있지 않고, 계속 바뀌는 것

0) CALL을 통해 프로시저를 실행했을 때
1) tableName을 매개변수로 받고,
2) sqlQuery 이라는 변수를 나중에 받을 파라미터(tableName)와 SELECT * FROM을 concat시켜서 저장해 놓는다. (CALL하는 순간 SELECT문이 실행되게)
3) PRERARE myQuery FROM @sqlQuery 로 준비를 해 놓는다.
4) EXCUTE를 했을 때 쿼리문이 실행된다.
5) DEALLOCATE PREPARE myQuery로 다시 해제해준다.

* 테이블을 부르는 스토어드 프로시저 생성
DROP PROCEDURE IF EXISTS dynamic_proc;
DELIMITER $$
CREATE PROCEDURE dynamic_proc(
    IN tableName VARCHAR(20)
)
BEGIN
  SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);
  PREPARE myQuery FROM @sqlQuery;
  EXECUTE myQuery;
  DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;
* 스토어드 프로시저 실행
CALL dynamic_proc ('member');