저장 프로시저
저장 프로시저 또는 스토어드 프로시저(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;
외부 링크
[편집]- (영어) MySQL 저장프로시저 FAQ
- (영어) PostgreSQL 프로시저 언어 지원 개요
- (영어) Sybase ASE 저장프로시저 사용
- (영어) 오라클 PL/SQL 프로시저
- (영어) 오라클 데이터베이스 PL/SQL 언어 참조