[MySQL] 프로시저 (Stored Procedure)

학원 내 모의 프로젝트에서

1. 회원 탈퇴의 경우 일정 기간이 지나면 자동으로 테이블에서 삭제 처리
2. 통계 테이블에 해당 날짜의 자정이되면 자동으로 insert처리

 

위와 같은 DB 자동화를 구축해 보고 싶었고 강사님께 여쭈어봤더니 CI/CD ?? Jenkins 같은 것들을 학습하면 구현하는 데 도움이 될 거라고 하셨는데 너무 생소한 개념이기도 하고 학습하는데 시간이 다 가서 프로젝트 수행을 못할 것 같아 방법을 찾아보던 중 프로시저를 활용해 MySQL 내에 이벤트를 구현해 간단한 자동화??아닌 자동화를 구현한 적이 있다.

 

그 때 당시 이해했던 프로시저는 Java의 메서드와 같은 역할을 하는 것인줄 알았다.

함수를 생성하여 이벤트 스케쥴러를 통해 프로시저를 호출했기 때문이다.

 

 

지금와서 지난 쿼리를 보니 잘못 사용했다는 생각이 든다.

DELIMITER $$
CREATE PROCEDURE paidDelAuto()
BEGIN
	DELETE FROM PAID WHERE PAID_DATE < (select date_format(date_sub(now(), interval 7 day), '%Y-%m-%d'));
END $$
DELIMITER ;

찾아보니 프로시저는 여러 쿼리문을 하나로 합쳐서 사용하기 위해 주로 사용된다고 한다.

굳이 프로시저가 아니라 이벤트 생성시 단일쿼리문을 넣어서 사용했으면 그냥 돌아갔을 것 같다 ㅋㅋ;;

 

정확히 포스팅해서 다음부터는 올바르게 사용할 수 있도록 하자.

 

 

 

프로시저

---

MySQL에서는 스토어드 프로시저 (Stored Procedure)라 한다.

장점

1. 하나의 프로시저로 여러 SQL문의 실행이 가능하다. (모듈화)
2. 호스팅언어와 SQL문이 분리되어 보수가 용이하다.
3. 네트워크 소요 시간을 줄일 수 있다. (구문 분석 및 코드 변환을 미리 진행하여 처리 시간이 단축됨)

단점

1. 재사용성이 낮다. (DB 버전 별 구문 규칙과 호환성이 다름)
2. 처리 성능이 좋지않다. (MySQL 스토어드의 프로그램 처리 성능이 낮다)

함수(Function)와의 차이점

함수 : 클라이언트에서 처리하며, 리턴 값이 단 하나이고 꼭 값을 가져와야 한다.
프로시저 : 서버로 보내 처리하며, 리턴값을 여러개 반환 가능하다.

 

 

사용하기

---

생성

DELIMITER $$
CREATE PROCEDURE paidDelAuto()
BEGIN
	DELETE FROM PAID WHERE PAID_DATE < (select date_format(date_sub(now(), interval 7 day), '%Y-%m-%d'));
END $$
DELIMITER ;

DELIMITER(구문 문자)

프로시저 내부에 세미콜론이 많을 경우, SQL문이 나눠져 서버로 보내지게 되어 프로시저가 어디까지인지 모른다.
따라서 DELIMITER $$를 선언하고, 프로시저 작성이 끝날 경우 END $$로 프로시저의 끝을 알려야 한다.
END $$로 끝나는 부분을 표시하고, 마지막에 DELIMITER를 세미콜론으로 바꿔준다.

 

호출

CALL paidDelAuto();

---

활용하기

 

답변 테이블에서 원본글인지 답변글인지 판별하고, 답변여부에 따라 삭제여부 UPDATE 혹은 DELETE

DELIMITER $$ 
DROP PROCEDURE IF EXISTS deleteReboard $$ #같은 이름이 있다면 지우기
CREATE PROCEDURE deleteReboard #저장 프로시저 생성 
( 
	#변수 선언 
    m_no INT, 
    m_step INT, 
    m_groupNo INT 
) 
BEGIN #SQL 프로그래밍 부분 시작 
DECLARE cnt INT; #답변 변수 설정 
SET cnt=0; #변수 초기화 
/*답변이 달린 원본 글인 경우에는 삭제하지 말고 delFlag를 Y 로 update하자*/ 
IF m_step=0 THEN /*원본글인 경우*/ 
  /*답변이 달렸는지 확인*/ 
  SELECT COUNT(*) INTO cnt FROM reboard WHERE groupno=m_groupNo; 
  IF cnt >1 THEN /*답변이 달린 경우*/ 
    UPDATE reboard SET delflag='Y' WHERE NO=m_no; 
  ELSE /*답변이 안 달린 경우*/ 
      DELETE FROM reboard WHERE NO=m_no; 
  END IF; 
ELSE /*답변글인 경우*/ 
	DELETE FROM reboard WHERE NO=m_no; 
END IF; 
END$$ 
DELIMITER ;

변수는 프로시저에 선언한 순서대로 입력할 것

CALL deleteReboard(4, 0, 4);

 

 

 

 

추가 정리

---

목록 확인

SHOW PROCEDURE STATUS;

 

내용 확인

SHOW CREATE PROCEDURE 프로시저명;

 

삭제

DROP PROCEDURE 프로시저명;

 

호출

CALL 프로시저명();

 

 

 

참조

https://wakestand.tistory.com/518
https://spiderwebcoding.tistory.com/7
https://heestory217.tistory.com/18