저장 프로시저(Stored Procedure)는 일반적인 컴퓨팅 언어의 관점에서 볼 때, 데이터베이스 안에 저장되어 있는 서브프로그램(subprogram)과 같은 형태의 서브루틴이라고 정의할 수 있습니다. MySQL의 관점에서 말하면, 저장 프로시저란 데이터베이스 카탈로그 내부에 저장된 선언적 SQL 문장들의 집합을 의미합니다.
MySQL에서 저장 프로시저를 작성하기 전에는 반드시 사용 중인 MySQL 버전을 먼저 확인해야 합니다. 저장 프로시저 기능은 MySQL 5.0부터 도입되었기 때문에, 그 이전 버전에서는 사용할 수 없습니다.
저장 프로시저 생성 구문
다음은 MySQL에서 저장 프로시저를 생성할 때 사용하는 기본 구문입니다.
CREATE [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter: [ IN | OUT | INOUT ] param_name type
type:
유효한 MySQL 데이터 타입
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA
| MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
유효한 SQL 루틴 문장
구문의 주요 구성 요소
- DEFINER: 프로시저를 생성하거나 실행할 권한을 가진 사용자를 지정합니다. 생략하면 현재 사용자(CURRENT_USER)가 기본값으로 설정됩니다.
- proc_parameter: 매개변수는 IN(입력용), OUT(출력용), INOUT(입출력 겸용) 세 가지 모드를 가질 수 있으며, 각 매개변수는 유효한 MySQL 데이터 타입이어야 합니다.
- characteristic: COMMENT(주석), LANGUAGE SQL, DETERMINISTIC 또는 NOT DETERMINISTIC(동일 입력에 대한 결과의 결정 여부), CONTAINS SQL·NO SQL·READS SQL DATA·MODIFIES SQL DATA(데이터 접근 특성), SQL SECURITY DEFINER 또는 INVOKER(실행 권한 컨텍스트) 등의 옵션을 포함합니다.
- routine_body: SELECT, INSERT, UPDATE처럼 유효한 SQL 문장으로 구성되며, BEGIN ... END 블록으로 감싸 여러 문장을 포함할 수 있습니다.
저장 프로시저 생성 예제
아래 예제에서는 'student_info' 테이블의 모든 레코드를 조회하는 간단한 프로시저를 만들어 보겠습니다. 먼저 해당 테이블에는 다음과 같은 데이터가 들어 있습니다.
mysql> select * from student_info; +-----+---------+------------+------------+ | id | Name | Address | Subject | +-----+---------+------------+------------+ | 100 | Aarav | Delhi | Computers | | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Jaipur | Literature | | 110 | Rahul | Chandigarh | History | +-----+---------+------------+------------+ 4 rows in set (0.00 sec)
이제 아래 쿼리를 통해 'allrecords()'라는 이름의 저장 프로시저를 생성합니다.
mysql> Delimiter //
mysql> Create Procedure allrecords()
-> BEGIN
-> Select * from Student_info;
-> END//
Query OK, 0 rows affected (0.02 sec)
mysql> DELIMITER ;
DELIMITER 명령이 필요한 이유
MySQL 클라이언트의 기본 문장 구분자는 세미콜론(;)입니다. 그런데 저장 프로시저 본문 내부에도 여러 SQL 문장이 세미콜론으로 구분되어 있기 때문에, 클라이언트가 프로시저 전체를 하나의 문장으로 인식하도록 임시로 구분자를 '//'(또는 '$$' 등)로 변경해야 합니다. 프로시저 생성이 끝나면 DELIMITER ;를 실행하여 원래 구분자로 되돌려 주는 것이 좋습니다.
생성한 저장 프로시저 호출하기
저장 프로시저는 CALL 문을 사용하여 실행할 수 있습니다.
mysql> CALL allrecords(); +-----+---------+------------+------------+ | id | Name | Address | Subject | +-----+---------+------------+------------+ | 100 | Aarav | Delhi | Computers | | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Jaipur | Literature | | 110 | Rahul | Chandigarh | History | +-----+---------+------------+------------+ 4 rows in set (0.00 sec)
참고로 더 이상 필요하지 않은 프로시저는 DROP PROCEDURE allrecords;와 같이 삭제할 수 있으며, 같은 이름의 프로시저가 이미 존재할 경우 발생하는 오류를 피하려면 DROP PROCEDURE IF EXISTS allrecords;를 사용하면 됩니다.