PROFILPELAJAR.COM

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

개요

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

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

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

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

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>=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','2019-04-30','헤이세이');

(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;

PROFILPELAJAR.COM

저장 프로시저

개요

DBMS별 예제

오라클

MySQL

함수 예1 (DB 개입 없음)

프로시저의 예1 (DB 개입 없음)

함수 예제2 (DB 작업 있음)

프로시저 예제2 (DB 작업 있음, 비켜서 작업)

프로시저 예제3 (DB 작업 있음, 커서 작업)

외부 링크

Read other articles:

Pulau Drvenik Veli

Sergio Romero

Julian Brandt

Heaven (Cosmic Baby album)

Unione Sportiva Casertana 1982-1983

Government of British Columbia

Oberon (poem)

Державний комітет телебачення і радіомовлення України

List of British films of 1933

Benguet State University

Sadruddin Aga Khan

Japanese destroyer Yūgiri (1930)

وسم الحقيبة

Thann

Speaker (politics)

Michel Foucault

The Pillars of the Earth (miniseries)

美軍慰安婦

Parody

العبل (الدوادمي)