第6回：ストアドプロシージャ (2/3)

TOP＞サーバ構築・運用＞ MySQLのストアドプロシージャ

徹底比較!! PostgreSQL vs MySQLパート2

第６回：ストアドプロシージャ
著者：NTTデータ藤塚勤也 2007/7/25

MySQLのストアドプロシージャ

   前述した通り、MySQLのストアドプロシージャには、ストアドファンクションとストアドプロシージャの2種類があります。この2種類を総称して「ストアドルーチン」と呼ぶこともあります。ストアドファンクションとストアドプロシージャには、大きく分けて2つの違いがあります。

   1つは処理結果の値を呼び出し元へ返却するための記述方法の違いです。ストアドファンクションは、RETURNS句を使いますが、ストアドプロシージャはパラメータのIN、OUT、INOUT句を使用します。

   ストアドファンクションを作成する際のCREATE句の抜粋は以下のようになります。


		create function ファンクション名 () returns varchar(10)

また、ストアドプロシージャを作成する際のCREATE句の抜粋は以下のようになります。


create procedure プロシージャー名 (out para varchar(10))

もう1つの違いは、呼び出し方法です。ストアドファンクションはPostgreSQLのユーザ定義関数と同様にSELECTなどのSQL文の中に組み込んで呼び出しますが、ストアドプロシージャはCALL文を使用して呼び出します。

ストアドプロシージャの具体例

ではここで、同一処理内容のストアドプロシージャをPostgreSQLのユーザ定義関数、MySQLのストアドファンクション、ストアドプロシージャのそれぞれで作成した例を説明します。なお、PostgreSQLのユーザ定義関数はPL/pgSQLにて作成することとします。

まず、処理対象のテーブルとして図1のようにTABLE01があり、COL01とCOL02の2つのカラムを用意します。

図1：処理対象テーブルの状態

   各カラムの初期状態は図1にある通りで、COL02の値がすべて「100」の4レコードを格納しているものとします。このTABLE01に対して処理を行うストアドプロシージャとして、以下のような内容のものを作成します。

   作成するストアドプロシージャは、名称を「sp_test()」とし、char型の入力パラメータを1つ持ちます。sp_test()は、まず与えられたパラメータ値をTABLE01のCOL01の条件値として使用し、該当するレコードを検索します。

   検索結果が0件の場合は、COL01＝入力パラメータ値、COL02＝0としたレコードを1件挿入します。1件でも該当するレコードが存在した場合は、そのレコードのCOL02の値を0に更新します。そして、レコードを挿入した場合は'INSERT'という文字列を、レコードを更新した場合は'UPDATE'という文字列を返却値としてsp_test()は終了します。

   この内容で3種類のsp_test()を作成します。

   まず、以下はPostgreSQLのユーザ定義関数で記述したものです。


		create function sp_test (para01 char) returns varchar(10)

		as $$

		declare 

		  p_cnt int;

		begin

		  select into p_cnt count(*)  from table01 where col01=para01;  

		  if p_cnt = 0 then

		    insert into table01 (col01,col02) values (para01,0);

		    return 'INSERT';

		  else

		    update table01 set col02=0 where col01=para01;

		    return 'UPDATE';

		  end if;

		end;

		$$ language plpgsql;

続いて、MySQLのストアドファンクションで作成したものです。


		create function sp_test (para01 char) returns varchar(10)

		begin

		  declare p_cnt int;

		  select count(*) into p_cnt from table01 where col01=para01;  

		  if p_cnt = 0 then

		    insert into table01 (col01,col02) values (para01,0);

		    return 'INSERT';

		  else

		    update table01 set col02=0 where col01=para01;

		    return 'UPDATE';

		  end if;

		end;

最後は、MySQLのストアドプロシージャで作成したものです。


		create procedure sp_test (in para01 char,out para02 varchar(10))

		begin

		  declare p_cnt int;

		  select count(*) into p_cnt from table01 where col01=para01;  

		  if p_cnt = 0 then

		    insert into table01 (col01,col02) values (para01,0);

		    set para02 = 'INSERT';

		  else

		    update table01 set col02=0 where col01=para01;

		    set para02 = 'UPDATE';

		  end if;

		end;

いかがでしょうか。この程度の処理内容だと3種類とも、ほとんど同じ内容にみえると思います。しかし、いくつか特徴的な違いもありますのでその部分について説明します。

前のページ 1 2 3 次のページ

著者プロフィール
株式会社NTTデータ藤塚勤也
基盤システム事業本部オープンソース開発センタシニアスペシャリスト。
日本タンデムコンピューターズ（現日本HP）を経て、2003年よりNTTデータにてOSS分野に参画。日頃はオリジナルOSSの開発や、OSSを用いたシステム構築への技術支援に従事。「RDBMS解剖学」（翔泳社）を共著。

INDEX
第6回：ストアドプロシージャ
	今はMySQLでもストアドプロシージャが使える！
	MySQLのストアドプロシージャ
	PostgreSQLのユーザ定義関数とMySQLのストアドファンクションの相違

徹底比較!! PostgreSQL vs MySQLパート2
第1回	同じようで違うデータ型
第2回	拡張部分によって違いがでてくるSQL文
第3回	実は差があるキャラクターセットの違い
第4回	PostgreSQLとMySQLに対応したGUIツールを使いこなす
第5回	バックアップとリストア
第6回	ストアドプロシージャ
第7回	トリガ
第8回	それぞれが持つビュー機能

タイプ

連載

特集

人気記事トップ10

新着記事

企画広告も役立つ情報バッチリ！ Sponsored