[MySQL] 스토어드 프로시저(Stored Procedure) 사용법

저장 프로시저(Stored Procedure)

일련의 작업 절차를 정리해 저장한 것으로, SQL문을 미리 정의해두고 요청을 받으면 실행된다.

자주 사용되는 복잡한 작업들을 프로시저를 이용해 간단하게 사용할 수 있다.

MySQL 5.0버전부터 사용 가능하다.

 

Stored Procedure의 장점

- 응용프로그램의 성능 향상에 도움을 준다. 한번 생성하면 저장 프로시저는 데이터베이스에 컴파일되고 저장되나, MySQL의 저장 프로시저는 요구가 있을때 컴파일진 후 모든 단일 연결에 독자적인 저장 프로시저 캐시를 유지한다.

만약 단일 연결에서 저장 프로시저를 여러번 사용하는 어플리케이션이 있다면 프로시저는 쿼리처럼 일을 할 것이므로 컴파일된 버전을 사용해야 한다. 

- 응용프로그램과 데이터베이스 서버 사이에 오가는 트래픽 양을 줄여주는데 도움을 준다. 많은 SQL문을 보내는 대신 저장프로시저의 이름과 매개변수만 보내면 되기 때문이다.

- 저장 프로시저는 재사용이 가능하며, 어떤 응용프로그램에서도 투명하다.

- 개발자들이 함수를 개발하지 않아도 모든 응용프로그램에서 데이터베이스 인터페이스를 노출한다.

- 안전하다. 데이터베이스 관리자는 기본 데이터베이스 테이블에 접근할 수 있는 권한을 아무에게 부여하지않고, 응용프로그램이 저장 프로시저에 접근할 수 있는 적절한 권한을 부여하는 방법을 쓴다.

 

Stored Procedure의 단점

- 많은 저장 프로시저를 사용한다면 모든 연결의 메모리 사용량이 증가한다. 게다가 프로시저 내부의 큰 수의 논리적 연산을 많이 사용한다면, CPU 사용량 또한 증가한다.

- 디버그하는 것이 어렵다. 단 몇개의 데이터베이스 관리 시스템들만 저장 프로시저를 디버그하는 것이 허용된다. MySQL은 저장 프로시저를 디버깅하기 위한 기능을 제공하지않는다.

- 개발하고 유지하는 것이 쉽지 않다. 전문적인 기술을 요구하기때문에 개발, 유지 보수 단계에서 문제가 발생할 수 있다.

 

---

 

# 생성
DELIMITER $$
  CREATE PROCEDURE getAllProducts()
    BEGIN
        SELECT*FROM products;
    END $$
DELIMITER;


# 호출
CALL getAllProducts();

DELIMITER $$

저장 프로시저 구문과는 관련이 없는 명령어로, 표준 구분 기호인 세미콜론(;)을 다른 기호($$ or //등등)로 변경한다.

MySQL도구가 매번 각 문장을 실행하는 것보다 서버에 저장 프로시저를 통과시키는 것이 중요하기때문이다. 

 

END $$

저장 프로시저의 끝을 표시할 때 DELIMITER에서 정의한 구분기호를 사용한다.

 

DELIMITER ;

구분기호를 다시 세미콜론으로 변경하기 위해 사용한다.

 

CREATE PROCEDURE 프로시저명()

(함수는 아니지만 함수 형태로) 새로운 저장 프로시저를 생성한다.

 

BEGIN ~ END

비긴과 엔드 사이에 저장 프로시저의 비즈니스 로직을 처리하기 위한 바디 부분을 호출한다. 

---

참고 및 출처 : blog.duveen.me/11?category=657591