저장 프로시저

저장 프로시저 또는 스토어드 프로시저(stored procedure)는 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다. 데이터베이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스 관리 시스템 에 저장한(지속성) 것으로, 영구저장모듈(Persistent Storage Module)이라고도 불린다.

개요

데이터베이스 언어 표준 SQL에서는 SQL / PSM 기준으로 책정되어 있다. 벤더(제조사) 각사 모두 정적, 동적 SQL에 커서 처리 및 제어 구문, 예외 처리 등을 포함한 사양의 확장 언어로 절차를 설명할 수 있는 DBMS를 제공하는 경우가 많다. 또한 C 언어로 작성된 컴파일한 외부 모듈(공유 라이브러리) 및 Java 클래스 라이브러리에서 함수나 클래스 메소드를 호출하는 것으로 실현하는 ‘외부 프로시저’ 기능을 구현하는 것도 있다. 저장프로시저를 사용하여 다음과 같은 장점이 있다.

1. 하나의 요청으로 여러 SQL문을 실행 할 수 있다. (네트워크에 대한 부하를 줄일 수 있다.)
2. 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어든다.
3. 데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 참조무결성 유지가 가능하게 된다. 간단히 말하면 응용프로그램 측 로직을 가지지 않고도 데이터베이스의 데이터 앞뒤가 맞게 될 수 있다.
4. JAVA 등의 호스트 언어와 SQL 문장이 확실하게 분리된 소스 코드의 전망이 좋아지는 것, 또한 웹사이트 등 운용 중에도 저장프로시저의 교체에 의한 수정이 가능하기 때문에 보수성이 뛰어나다.

저장프로시저를 많이 사용하면 다음과 같은 단이 있다.

1. 데이터베이스 제품에 대해 설명하는 구문 규칙이 SQL / PSM 표준과의 호환성이 낮기 때문에 코드 자산으로의 재사용성이 나쁘다.
2. 비즈니스 로직의 일부로 사용하는 경우 업무의 사양 변경 시 외부 응용프로그램과 함께 저장프로시저의 정의를 변경할 필요가 있다. 이때 불필요한 수고와 변경 실수에 의한 장애를 발생시킬 가능성이 있다.

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>=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>=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','9999-12-31','헤이세이');

(2) 정의

drop function if exists cng_gengou;   -- 존재한다면 삭제
delimiter //                          -- 마침기호 변경
create function cng_gengou
(p_seireki_date date)
 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<=p_seireki_date and end_date>=p_seireki_date;
 if w_rcnt>1 then
  set w_rslt='2행 이상 존재하기 때문에 지원하지 않음';
 else
  select gengou_name,bgn_date
   into w_gengou_name,w_bgn_date
   from gengou
   where bgn_date<=p_seireki_date and end_date>=p_seireki_date;
  if length(w_gengou_name)>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 (DB 작업 있습니다)"와 같다.

(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<=p_seireki_date and end_date>=p_seireki_date;
 if w_rcnt>1 then
  set p_rslt='2행 이상 존재하기 때문에 지원하지 않음';
 else
  select gengou_name,bgn_date
   into w_gengou_name,w_bgn_date
   from gengou
   where bgn_date<=p_seireki_date and end_date>=p_seireki_date;
  if length(w_gengou_name)>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 (DB작업 있습니다)"와 같다.

(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<=p_seireki_date and end_date>=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>1 then
   set p_rslt='2행 이상 존재하기 때문에 지원하지 않음';
  else
   if length(w_gengou_name)>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;