저장 프로시저

저장 프로시저 또는 스토어드 프로시저(stored procedure)는 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다. 데이터베이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리 시스템에 저장한(지속성) 것으로, 영구저장모듈(Persistent Storage Module)이라고도 불린다.
개요.
데이터베이스 언어 표준 SQL에서는 SQL / PSM 기준으로 책정되어 있다. 벤더(제조사) 각사 모두 정적, 동적 SQL에 커서 처리 및 제어 구문, 예외 처리 등을 포함한 사양의 확장 언어로 절차를 설명할 수 있는 DBMS를 제공하는 경우가 많다. 또한 C 언어로 작성된 컴파일한 외부 모듈(공유 라이브러리) 및 Java 클래스 라이브러리에서 함수나 클래스 메소드를 호출하는 것으로 실현하는 ‘외부 프로시저’ 기능을 구현하는 것도 있다.
저장프로시저를 사용하여 다음과 같은 장점이 있다.
저장프로시저를 많이 사용하면 다음과 같은 단점이 있다.
DBMS별 예제.
오라클.
다음은 PL/SQL로 구현한 오라클 저장 프로시저의 예이다.
CREATE OR REPLACE PROCEDURE helloworld (str IN VARCHAR2)
 AS
 hw VARCHAR2 (100) : = 'Hello World!';
 BEGIN
 DBMS_OUTPUT. PUT_LINE ( 'Hello World!');
 DBMS_OUTPUT. PUT_LINE ( 'VARIABLE hw ='| | hw);
 DBMS_OUTPUT. PUT_LINE ( 'Parameter str ='| | str);
 END;
MySQL.
MySQL은 버전 5.0 이후 표준 SQL 규격 저장프로시저를 지원하고 있다. 다음은 함수와 프로시저에서 동등한 처리를 하는 예를 보여준다.
함수 예1 (DB 개입 없음).
(1) 정의
drop function if exists DecToNshin; -- 존재한다면 삭제
delimiter // -- 마침 기호의 변경
create function DecToNshin
(dec_num int,
 n_shin tinyint)
 returns varchar(32)
-- 10진수→n진수
begin
 declare ltr char(36) default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
 declare w_dec_num int;
 declare amari int;
 declare w_DecToNshin varchar(32);
 set w_DecToNshin=";
 set w_dec_num=dec_num;
 while w_dec_num&gt;=n_shin do
 set amari=mod(w_dec_num,n_shin);
 set w_DecToNshin=concat(substr(ltr,amari+1,1),w_DecToNshin);
 set w_dec_num=w_dec_num div n_shin;
 end while;
 set w_DecToNshin=concat(substr(ltr,w_dec_num+1,1),w_DecToNshin);
 return w_DecToNshin;
end;
delimiter ; -- 마침기호 취소
(2) 실행
SELECT DecToNshin(100,16); -- 100을 16진수로 하면?
프로시저의 예1 (DB 개입 없음).
(1) 정의
drop procedure if exists DecToNshin; -- 존재한다면 삭제
delimiter // -- 마침기호 변경
create procedure DecToNshin
(in dec_num int,
 in n_shin tinyint,
 out w_DecToNshin varchar(32))
-- 10진수→n진수
begin
 declare ltr char(36) default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
 declare w_dec_num int;
 declare amari int;
 set w_DecToNshin=";
 set w_dec_num=dec_num;
 while w_dec_num&gt;=n_shin do
 set amari=mod(w_dec_num,n_shin);
 set w_DecToNshin=concat(substr(ltr,amari+1,1),w_DecToNshin);
 set w_dec_num=w_dec_num div n_shin;
 end while;
 set w_DecToNshin=concat(substr(ltr,w_dec_num+1,1),w_DecToNshin);
end;
delimiter ; -- 마침 기호 취소
(2) 실행
CALL DecToNshin(100,16,@RSLT); -- 100을 16진수로 하면?
SELECT @RSLT; -- 마침 표시
함수 예제2 (DB 작업 있음).
(1) 테이블 정의 및 데이터
create table gengou
(bgn_date date,
 end_date date,
 gengou_name varchar(4));
insert into gengou values
('1868-01-01','1912-07-30','메이지'),
('1912-07-30','1926-12-25','다이쇼'),
('1926-12-25','1989-01-07','쇼'),
('1989-01-08','2019-04-30','헤이세이');
(2) 정의
drop function if exists cng_gengou; -- 존재한다면 삭제
delimiter // -- 마침기호 변경
create function cng_gengou
 returns varchar(30)
-- 서기→일본력
begin
 declare w_rcnt int default 0; -- 행 line 확인
 declare w_gengou_name varchar(4) default "; -- 연호명
 declare w_bgn_date date; -- 시작일
 declare w_nensuu tinyint default 0; -- 일본력 년
 declare w_rslt varchar(30) default "; -- 결과
 select count(*)
 into w_rcnt
 from gengou
 where bgn_date&lt;=p_seireki_date and end_date&gt;=p_seireki_date;
 if w_rcnt&gt;1 then
 set w_rslt='2행 이상 존재하기 때문에 지원하지 않음';
 else
 select gengou_name,bgn_date
 into w_gengou_name,w_bgn_date
 from gengou
 where bgn_date&lt;=p_seireki_date and end_date&gt;=p_seireki_date;
 if length(w_gengou_name)&gt;0 then
 set w_nensuu=year(p_seireki_date)-year(w_bgn_date)+1;
 set w_rslt=concat(w_gengou_name,cast(w_nensuu as char(2)));
 else
 set w_rslt='change unsuccessful';
 end if;
 end if;
 return w_rslt;
end;
delimiter ; -- 종결기호 취소
(3) 실행
select cng_gengou('2006-07-19');
프로시저 예제2 (DB 작업 있음, 비켜서 작업).
(1) 테이블 정의 및 데이터
(2) 정의
drop procedure if exists cng_gengou; -- 존재한다면 삭제
delimiter // -- 마침기호 변경
create procedure cng_gengou
(in p_seireki_date date,
 out p_rslt varchar(30))
-- 서기→일본력
begin
 declare w_rcnt int default 0; -- 행 line 확인
 declare w_gengou_name varchar(4) default "; -- 연호이름
 declare w_bgn_date date; -- 시작일
 declare w_nensuu tinyint default 0; -- 일본력 년
 select count(*)
 into w_rcnt
 from gengou
 where bgn_date&lt;=p_seireki_date and end_date&gt;=p_seireki_date;
 if w_rcnt&gt;1 then
 set p_rslt='2행 이상 존재하기 때문에 지원하지 않음';
 else
 select gengou_name,bgn_date
 into w_gengou_name,w_bgn_date
 from gengou
 where bgn_date&lt;=p_seireki_date and end_date&gt;=p_seireki_date;
 if length(w_gengou_name)&gt;0 then
 set w_nensuu=year(p_seireki_date)-year(w_bgn_date)+1;
 set p_rslt=concat(w_gengou_name,cast(w_nensuu as char(2)));
 else
 set p_rslt='change unsuccessful';
 end if;
 end if;
end;
delimiter ; -- 마침기호 취소
(3) 실행
call cng_gengou('2006-07-19',@rslt);
select @rslt;
프로시저 예제3 (DB 작업 있음, 커서 작업).
(1) 테이블 정의 및 데이터
(2) 정의
drop procedure if exists cng_gengou; -- 존재한다면 삭제
delimiter // -- 마침기호 변경
create procedure cng_gengou
(in p_seireki_date date,
 out p_rslt varchar(30))
-- 서기→일본력
begin
 declare w_rcnt int default 0; -- 행 수 확인
 declare w_gengou_name varchar(4) default "; -- 연호이름
 declare w_bgn_date date; -- 시작일
 declare w_nensuu tinyint default 0; -- 일본력 년
 declare eod tinyint;
-- 커서 선언
 declare cr1 cursor for
 select gengou_name,bgn_date
 from gengou
 where bgn_date&lt;=p_seireki_date and end_date&gt;=p_seireki_date;
-- 예외 선언
 declare continue handler for not found set eod=1;
 set eod=0;
 open cr1;
 fetch cr1 into w_gengou_name,w_bgn_date;
 while eod=0 do
 set w_rcnt=w_rcnt+1;
 if w_rcnt&gt;1 then
 set p_rslt='2행 이상 존재하기 때문에 지원하지 않음';
 else
 if length(w_gengou_name)&gt;0 then
 set w_nensuu=year(p_seireki_date)-year(w_bgn_date)+1;
 set p_rslt=concat(w_gengou_name,cast(w_nensuu as char(2)));
 else
 set p_rslt='change unsuccessful';
 end if;
 end if;
 fetch cr1 into w_gengou_name,w_bgn_date;
 end while;
 close cr1;
end;
delimiter ; -- 종결기호 취소
(3) 실행
call cng_gengou('2006-08-10',@rslt);
select @rslt;