ストアドプロシージャ

概要

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','平成'),
('2019-05-01','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のストアドプロシージャは、独自構文で実装されている。このうち、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','2019-04-30','平成');
insert into gengou values('2019-05-01','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では、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','2019-04-30','平成')
insert into gengou values('2019-05-01','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のストアドプロシージャは、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のストアドプロシージャは、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