[Mysql] 스토어드 프로시저

스토어드 프로시저(Stored Procedure, 저장 프로시저)란?

-> Mysql에서 제공하고 있는 프로그래밍 기능 중 하나로 쿼리문에 대한 동작을 일괄 처리하기 위한 용도로 사용한다.

쿼리를 모듈화 함으로써 필요할 때마다 호출하는 식으로 사용하여 편리하게 Mysql 동작을 시킬 수 있다.

또한, 클라이언트 측에서 DB로 장황한 쿼리대신 프로시저를 호출하기만 하면 되기에 편리한 기능이라고 할 수 있다.

 

특징

1. 성능 향상 : 프로시저를 CALL 하는 것으로 긴 쿼리를 사용할 수 있다. --> 네트워크 부하 감소

2. 유지관리의 간편함 : 응용 프로그램의 코드에서는 긴 쿼리가 아닌 프로시저만을 호출하기 때문에 프로시저의 내용 수정 등의 유지보수가 간편하다.

3. 모듈식 프로그래밍 가능 : 언제든지 사용이 가능하며, 프로시저 내의 쿼리만 수정, 삭제 하면 되기에 관리가 수월한 점이 모듈식 프로그래밍 언어와 동일한 장점을 갖는다.

4. 보안 강화 : 사용자 별로 TABLE 접근 권한이 아닌 스토어드 프로시저의 접근 권한만 주면 되기에 보안이 강화된다.

 

Mysql WorkBench 사용 예

1. 우선 데이터베이스에 연결 후 왼쪽 스키마 목록에서 'Stored Procedure' 우클릭 - 'Create Stored Procedure...'를 통해

스토어드 프로시저를 생성해주자.

2. 창을 넘어오면 위와같은 DDL 명령어를 작성할 수 있는 공간이 나타나며 간단한 구문이 작성되있다.

 

1. CREATE PROCEDURE 'new_procedure'() : 프로시저 생성 구문으로 ''사이 원하는 프로시저명을 정할 수 있으며, 괄호 안에 내가 사용할 매개변수의 이름 및 자료형을 선언해줄 수 있다.

 

2. BEGIN ~ END : 이 사이에 내가 실행시키고 싶은 쿼리들을 작성 해줄 수 있다. 위에는 이해를 돕기위해 간단한 INSERT문과 SELECT문을 작성해놓았다.

 

이 후 아랫단 'Apply' 버튼을 통해 해당 프로시저를 생성하여 사용할 수 있다.

 

 

 

활용 예제

위 프로시저는 예제용으로 생성한 프로시저이며 간단한 기능들을 넣어두었다.

 

1. CREATE 구문 : 매개변수로 name, major, gender 정보를 VARCHAR(45)형태로 받아올 수 있게 선언하였다.

2. BEGIN ~ END(실질적인 쿼리) : DECLARE를 통해 num 변수를 선언한 뒤 SELECT INTO문을 통해 테이블의 컬럼 수를 파악한 후 +1을 해준 값을 num변수에 넣어주었다.

그러고 난 뒤 매개변수를 활용해 값을 INSERT 해주고, 마지막으로 해당 테이블을 보여주는 SELECT문을 넣어놓았다.

 

위 테이블은 예제를 위해 생성한 테이블 구조이므로 참고하길 바람.

 

 

 

본격적으로 프로시저를 활용하기 위해 CALL 프로시저이름(매개변수) 형식으로 방금 만든 프로시저를 호출하였고,

결과로써 컬럼에 값이 제대로 들어간 걸 확인할 수 있다.

 

이처럼 스토어드 프로시저는 여러 개의 쿼리를 CALL 호출을 통해 한번에 처리가 가능하도록 돕는 기능을 제공하며,

이는 마치 C언어의 함수와 유사한 기능을 하는 것처럼 보인다.