저장 프로시저

**저장 프로시저** 또는 **스토어드 프로시저**(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; 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; 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; 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; 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; \-- 예외 선언 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;