「ストアドプロシージャ」の版間の差分
Chuuken ken kou (会話 | 投稿記録) |
Chuuken ken kou (会話 | 投稿記録) |
||
205行目: | 205行目: | ||
(1)表定義及びデータ |
(1)表定義及びデータ |
||
「ファンクションの例2(DB操作あり)」と同じ。 |
「ファンクションの例2(DB操作あり)」と同じ。 |
||
(2)定義 |
(2)定義 |
||
<source lang="text"> |
|||
<PRE> |
|||
drop procedure if exists cng_gengou; -- 存在したら削除 |
drop procedure if exists cng_gengou; -- 存在したら削除 |
||
delimiter // -- 終端記号の変更 |
delimiter // -- 終端記号の変更 |
||
250行目: | 251行目: | ||
// |
// |
||
delimiter ; -- 終端記号を元に戻す |
delimiter ; -- 終端記号を元に戻す |
||
</ |
</source> |
||
(3)実行 |
(3)実行 |
2009年8月14日 (金) 14:31時点における版
ストアドプロシージャ (stored procedure) とは、データベースに対する一連の処理をまとめた手続きにして、リレーショナルデータベース管理システムに保存(永続化)したもの。永続格納モジュール(Persistent Storage Module)とも呼ばれる。ストアドプロシージャは実際にはデータベースのデータ辞書に格納されている。
概要
データベース言語標準SQLでは、SQL/PSM規格として策定されている。
ベンダー各社とも、静的・動的SQLにカーソル処理や制御構文・例外処理などを含めた仕様の拡張言語により手続きを記述することができるDBMSを提供している場合が多い。また、C言語で記述しコンパイルした外部モジュール(共有ライブラリなど)やJavaのクラスライブラリから、関数やクラスメソッドを呼び出すことで実現する「外部プロシージャ」機能を実装しているものもある。
ストアドプロシージャを利用することにより次のようなメリットが生まれる。
- ひとつの要求で複数のSQL文を実行できる(ネットワークに対する負荷を軽減できる)
- あらかじめ構文解析や内部中間コードへの変換をすませるため、処理時間が軽減される
- データベーストリガと組み合わせることで、複雑なルールによるデータの参照整合性保持が可能になる。簡単に言えば、アプリケーション側にロジックを持たずとも、データベース内のデータのつじつまが合うようにすることができる。
ストアドプロシージャを多用することによるデメリットには以下のようなものがある。
- データベース製品ごとに、記述する構文の規約がSQL/PSM規格との互換性が低いため、コード資産としての再利用性が悪い。
- ビジネスロジックの一部として利用する場合、業務の仕様変更に際して、外部のアプリケーションとともにストアドプロシージャの定義を変更する必要がある。このとき、よけいな手間や変更ミスによる障害を発生させる可能性がある。
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; -- 行数チェック
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; -- 行数チェック
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;
PostgreSQLのストアドプロシージャ
概要
PostgreSQLのストアドプロシージャは、独自構文で実装されている。このうち、PL/pgSQLでの作成例を、MySQLと同等の例で示す。
ファンクションの例1(DB操作なし)
(1)定義
create or replace function DecToNshin (dec_num int, n_shin int) returns varchar(32) as $$ -- -- 10進数→n進数 -- declare ltr char(36) default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; w_dec_num int; amari int; w_DecToNshin varchar(32); begin w_DecToNshin:=''; w_dec_num:=dec_num; while w_dec_num>=n_shin loop amari:=w_dec_num%n_shin; -- 剰余 w_DecToNshin:=substr(ltr,amari+1,1)||w_DecToNshin; w_dec_num:=w_dec_num/n_shin; end loop; w_DecToNshin:=substr(ltr,w_dec_num+1,1)||w_DecToNshin; return w_DecToNshin; end; $$ language 'plpgsql' ;
(2)実行
select DecToNshin(100,16);
(3)削除
仮に削除する場合も、引数の宣言が必要である。
drop function DecToNshin (dec_num int, n_shin int);
ファンクションの例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','明治'); insert into gengou values('1912-07-30','1926-12-25','大正'); insert into gengou values('1926-12-25','1989-01-07','昭和'); insert into gengou values('1989-01-08','9999-12-31','平成');
(2)定義
create or replace function cng_gengou (p_seireki_date date) returns varchar(30) as $$ -- -- 西暦→和暦 -- declare w_rcnt int default 0; -- 行数チェック w_gengou_name varchar(4) default ''; -- 元号名 w_bgn_date date; -- 開始日 w_nensuu smallint default 0; -- 和暦年 w_rslt varchar(30) default ''; -- 結果 begin -- select into w_rcnt -- 標準SQLと語順が異なっている count(*) from gengou where bgn_date<=p_seireki_date and end_date>=p_seireki_date; if w_rcnt>1 then w_rslt:='2行以上存在するため、未サポート'; else select into w_gengou_name,w_bgn_date -- 標準SQLと語順が異なっている gengou_name,bgn_date from gengou where bgn_date<=p_seireki_date and end_date>=p_seireki_date; if length(w_gengou_name)>0 then w_nensuu := extract(year from p_seireki_date) - extract(year from w_bgn_date) + 1; w_rslt:=w_gengou_name||to_char(w_nensuu,'99'); else w_rslt:='change unsuccessful'; end if; end if; return w_rslt; end; $$ language 'plpgsql' ;
(3)実行
select cng_gengou('2006-07-19');
ファンクションの例3(DB操作あり、カーソル操作)
(1)表定義及びデータ
「ファンクションの例2(DB操作あり、非カーソル操作)」と同じ
(2)定義
create or replace function cng_gengou (p_seireki_date date) returns varchar(30) as $$ -- -- 西暦→和暦 -- declare w_rcnt int default 0; -- 行数チェック w_nensuu smallint default 0; -- 和暦年 w_rslt varchar(30) default ''; -- 結果 eod smallint; cr1 cursor for select gengou_name,bgn_date from gengou where bgn_date<=p_seireki_date and end_date>=p_seireki_date; rec record; begin eod:=0; open cr1; fetch cr1 into rec; while eod=0 loop if not found then raise log 'not fount,rcnt = %',w_rcnt; eod:=1; else w_rcnt:=w_rcnt+1; raise log 'rcnt = %',w_rcnt; if w_rcnt>1 then w_rslt:='2行以上存在するため、未サポート'; else if length(rec.gengou_name)>0 then w_nensuu := extract(year from p_seireki_date) - extract(year from rec.bgn_date) + 1; w_rslt:=rec.gengou_name||to_char(w_nensuu,'99'); else w_rslt:='change unsuccessful'; end if; end if; end if; fetch cr1 into rec; end loop; close cr1; return w_rslt; end; $$ language 'plpgsql' ;
(3)実行
select cng_gengou('2006-08-10');
SQL Serverのストアドプロシージャ
概要
SQL Serverでは、Transact-SQLという独自構文で実装されている。
ファンクションの例1(DB操作なし)
(1)定義
create function dbo.DecToNshin (@dec_num int, @n_shin smallint) returns varchar(32) -- -- 10進数→n進数 -- begin declare @ltr char(36) declare @w_dec_num int declare @amari int declare @w_DecToNshin varchar(32) set @ltr='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' set @w_DecToNshin='' set @w_dec_num=@dec_num while @w_dec_num>=@n_shin begin set @amari=@w_dec_num%@n_shin -- 剰余 set @w_DecToNshin=substring(@ltr,@amari+1,1)+@w_DecToNshin -- +:文字連結 set @w_dec_num=@w_dec_num/@n_shin end set @w_DecToNshin=substring(@ltr,@w_dec_num+1,1)+@w_DecToNshin return @w_DecToNshin end
(2)実行
select dbo.DecToNshin(100,16)
プロシージャの例1(DB操作なし)
(1)定義
if exists (select * from sysobjects where id = object_id('dbo.DecToNshin') and sysstat & 0xf = 4) drop procedure dbo.DecToNshin GO create procedure dbo.DecToNshin @dec_num int, @n_shin smallint, @w_DecToNshin varchar(32) output as -- -- 10進数→n進数 -- declare @ltr char(36) declare @w_dec_num int declare @amari int set @ltr='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' set @w_DecToNshin='' set @w_dec_num=@dec_num while @w_dec_num>=@n_shin begin set @amari=@w_dec_num%@n_shin -- 剰余 set @w_DecToNshin=substring(@ltr,@amari+1,1)+@w_DecToNshin -- +:文字連結 set @w_dec_num=@w_dec_num/@n_shin end set @w_DecToNshin=substring(@ltr,@w_dec_num+1,1)+@w_DecToNshin
(2)実行
declare @out varchar(32) exec dbo.DecToNshin @dec_num=100,@n_shin=16,@w_DecToNshin=@out output select @out
ファンクションの例2(DB操作あり)
(1)表定義及びデータ
create table gengou (bgn_date datetime, end_date datetime, gengou_name varchar(6)) insert into gengou values('1868-01-01','1912-07-30','明治') insert into gengou values('1912-07-30','1926-12-25','大正') insert into gengou values('1926-12-25','1989-01-07','昭和') insert into gengou values('1989-01-08','9999-12-31','平成')
(2)定義
create function dbo.cng_gengou (@p_seireki_date datetime) returns varchar(60) -- -- 西暦→和暦 -- begin declare @w_rcnt int -- 行数チェック declare @w_gengou_name varchar(6) -- 元号名 declare @w_bgn_date datetime -- 開始日 declare @w_nensuu smallint -- 和暦年 declare @w_rslt varchar(60) -- 結果 -- カーソル宣言 declare cr1 cursor for select gengou_name,bgn_date from gengou where bgn_date<=@p_seireki_date and end_date>=@p_seireki_date -- 初期値 set @w_rcnt=0 set @w_gengou_name='' set @w_nensuu=0 set @w_rslt='' -- カーソルオープン open cr1 -- 1行取り出し fetch cr1 into @w_gengou_name,@w_bgn_date while @@fetch_status=0 begin set @w_rcnt=@w_rcnt+1 if @w_rcnt>1 -- thenは書かない set @w_rslt='2行以上存在するため、未サポート' else begin if len(@w_gengou_name)>0 -- thenは書かない begin set @w_nensuu=year(@p_seireki_date)-year(@w_bgn_date)+1 set @w_rslt=@w_gengou_name+cast(@w_nensuu as char(2)) end else set @w_rslt='change unsuccessful' end -- 1行取り出し fetch cr1 into @w_gengou_name,@w_bgn_date end -- カーソルクローズ close cr1 return @w_rslt end
(3)実行
select dbo.cng_gengou('2006-07-19')
プロシージャの例2(DB操作あり、非カーソル操作)
(1)表定義及びデータ
「ファンクションの例2(DB操作あり)」と同じ。
(2)定義
if exists (select * from sysobjects where id = object_id('dbo.cng_gengou') and sysstat & 0xf = 4) drop procedure dbo.cng_gengou GO create procedure dbo.cng_gengou @p_seireki_date datetime, @w_rslt varchar(60) output as -- -- 西暦→和暦 -- declare @w_rcnt int -- 行数チェック declare @w_gengou_name varchar(6) -- 元号名 declare @w_bgn_date datetime -- 開始日 declare @w_nensuu smallint -- 和暦年 -- 初期値 set @w_gengou_name='' set @w_nensuu=0 set @w_rslt='' select @w_gengou_name=gengou_name,@w_bgn_date=bgn_date from gengou where bgn_date<=@p_seireki_date and end_date>=@p_seireki_date if @@rowcount>1 -- thenは書かない set @w_rslt='2行以上存在するため、未サポート' else begin if len(@w_gengou_name)>0 -- thenは書かない begin set @w_nensuu=year(@p_seireki_date)-year(@w_bgn_date)+1 set @w_rslt=@w_gengou_name+cast(@w_nensuu as char(2)) end else set @w_rslt='change unsuccessful' end
(3)実行
declare @out varchar(60) exec dbo.cng_gengou @p_seireki_date='2006-08-10',@w_rslt=@out output select @out
プロシージャの例3(DB操作あり、カーソル操作)
(1)表定義及びデータ
「ファンクションの例2(DB操作あり)」と同じ。
(2)定義
if exists (select * from sysobjects where id = object_id('dbo.cng_gengou') and sysstat & 0xf = 4) drop procedure dbo.cng_gengou GO create procedure dbo.cng_gengou @p_seireki_date datetime, @w_rslt varchar(60) output as -- -- 西暦→和暦 -- declare @w_rcnt int -- 行数チェック declare @w_gengou_name varchar(6) -- 元号名 declare @w_bgn_date datetime -- 開始日 declare @w_nensuu smallint -- 和暦年 -- カーソル宣言 declare cr1 cursor for select gengou_name,bgn_date from gengou where bgn_date<=@p_seireki_date and end_date>=@p_seireki_date -- 初期値 set @w_rcnt=0 set @w_gengou_name='' set @w_nensuu=0 set @w_rslt='' -- カーソルオープン open cr1 -- 1行取り出し fetch cr1 into @w_gengou_name,@w_bgn_date while @@fetch_status=0 begin set @w_rcnt=@w_rcnt+1 if @w_rcnt>1 -- thenは書かない set @w_rslt='2行以上存在するため、未サポート' else begin if len(@w_gengou_name)>0 -- thenは書かない begin set @w_nensuu=year(@p_seireki_date)-year(@w_bgn_date)+1 set @w_rslt=@w_gengou_name+cast(@w_nensuu as char(2)) end else set @w_rslt='change unsuccessful' end -- 1行取り出し fetch cr1 into @w_gengou_name,@w_bgn_date end -- カーソルクローズ close cr1
(3)実行
deallocate cr1 declare @out varchar(60) exec dbo.cng_gengou @p_seireki_date='2006-07-19',@w_rslt=@out output select @out
ORACLEのストアドプロシージャ
この節の加筆が望まれています。 |
概要
Oracleのストアドプロシージャは、PL/SQLという独自構文で実装されている。
ファンクションの例1(DB操作なし)
(1)定義
-- 10進数→n進数 CREATE OR REPLACE FUNCTION DecToNshin( dec_num IN NUMBER, n_shin IN NUMBER) RETURN VARCHAR2(32) IS ltr CHAR(36); w_dec_num NUMBER; amari NUMBER; w_DecToNshin VARCHAR2(32); BEGIN ltr := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; w_DecToNshin := ''; w_dec_num := dec_num; WHILE w_dec_num >= n_shin LOOP amari := mod(w_dec_num, n_shin); -- 剰余 w_DecToNshin := substr(ltr, amari+1, 1) || w_DecToNshin; w_dec_num := w_dec_num / n_shin; END LOOP; w_DecToNshin := substr(ltr, w_dec_num+1, 1) || w_DecToNshin; RETURN w_DecToNshin; END; /
(2)実行
SELECT DecToNshin(100, 16);
Firebirdのストアドプロシージャ
概要
Firebirdのストアドプロシージャは、PSQLという独自構文で実装されている。 元々FirebirdはInterBaseから派生したデータベースという経緯があるため、 両者には高い互換性がある。 特徴として、SUSPEND命令を利用したSELECT可能なプロシージャの作成が容易であることが挙げられる。
ファンクションの例1(DB操作なし)
次の例では、最大公約数を求めて返します。
(定義)
SET TERM ^ ; CREATE PROCEDURE SP_GCD ( M INTEGER, N INTEGER ) RETURNS ( GCD INTEGER ) AS DECLARE VARIABLE MOD_MN INTEGER ; BEGIN IF (N = 0) THEN GCD = M; ELSE BEGIN MOD_MN = MOD(M,N); EXECUTE PROCEDURE SP_GCD :N, :MOD_MN RETURNING_VALUES GCD; END SUSPEND; END^ SET TERM ; ^
(実行)
SQL> EXECUTE PROCEDURE SP_GCD 3,9; GCD ============ 3
ファンクションの例2(DB操作あり)
次の例では、システムに登録されているユーザー定義のテーブルの一覧を返します。
(定義)
SET TERM ^; CREATE PROCEDURE SP_ALL_TABLES RETURNS( TAB_NO INTEGER, TAB_NAME CHAR(31) CHARACTER SET UNICODE_FSS) AS BEGIN TAB_NO = 1; FOR SELECT rdb$relation_name FROM rdb$relations WHERE rdb$flags = 1 AND rdb$view_source IS NULL INTO :TAB_NAME DO BEGIN SUSPEND; TAB_NO = TAB_NO + 1; END END ^ SET TERM ; ^
(実行)
SQL>SELECT p.TAB_NO, p.TAB_NAME FROM SP_ALL_TABLES p; TAB_NO TAB_NAME =============== 1 TBL_DATE_TEST 2 T