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

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

第6回:ストアドプロシージャ
著者:NTTデータ   藤塚 勤也   2007/7/25
前のページ  1  2  3  次のページ
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データ 藤塚 勤也
著者プロフィール
株式会社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

サーバやOSの保守期限、VMware環境の見直しといった節目に考える、Oracle AI Databaseの次期基盤。オンプレ継続・AWS・OCIそれぞれの特性と留意点を整理するWebセミナー 11月19日 16:00 AIエージェントの“見えないリスク”に備えてアイデンティティを「関係」で統制する 11月17日 6:25 AI生成コードをどう安全に使うか――決定論的検証とAutoFixで築く品質保証ワークフロー 11月16日 6:30 デジタル主権時代、データとモデルの持ち方は企業が主体的に定める 11月13日 6:25 ClickHouse Cloudが実現した広告運用SaaS「ATOM」のデータ基盤革新、パフォーマンスを向上しながら、運用負荷とコストを削減 11月11日 6:20 AIで開発が加速する時代、品質保証はどう変わる? Tricentisが示すAIエージェント活用の実像 11月9日 6:45 悪用までの猶予が「1分」に迫るフロンティアAI時代に開発と本番を貫く脆弱性管理が必要 10月29日 8:30 欧州CRA対応とSBOM運用。AI生成コードのOSS混入問題の対策とは ――スニペット単位で検出する「FossID」「Insignary Clarity」によるSBOM運用 9月16日 6:40 PostgreSQLからTiDBへ、稼働中SaaSで進める段階的DB移行の実践と再設計への展望 9月7日 6:30 機能安全開発におけるGoogleTestの活用を支援!「Parasoft C/C++test CT」で実現する効率化&AI連携 4月14日 6:30