IISにOSSをインストールするための情報サイト [IIS de OSS 64bit]

IIS de OSS 64bit > MySQL > MySQLストアドプロシージャ入門

本ページではMySQLのストアドプロシージャとファンクション、トリガーについて記載します。

ストアドプロシージャの特徴

(一般論と自論が混ざってます)

一般的にストアドプロシージャと言うと、返り値が存在しない「プロシージャ」と返り値が存在する「ファンクション」の両方を意味する。

  1. 「プロシージャ」は主に以下の用途で用いる
    1. DBデータの変更(insert, update, delete)
    2. DB接続のセッション変数の設定
  2. 「ファンクション」は主に以下の用途で用いる
    1. DBデータの参照(select)

開発環境

MySQL標準の開発ツールだと何故かよく不正終了してしまう。 最終的に私はCSEというツールを使っています。

権限付与

ストアドプロシージャを作るにしてもまずは権限付与が必要だが、公式サイトに提示されているCREATE ROUTINE, ALTER ROUTINE, EXECUTEを設定してもSUPER権限が必要と怒られる。
とりあえず、管理者権限(root)でログインし、ストアドプロシージャを作る事はできるので、それで。

詳細は公式サイトを参照。

まずは作成の構文から

プロシージャ作成(引数ありの場合)

こんなかんじ

create procedure プロシージャ名 (
  引数1 引数型,
  引数2 引数型,
  ...
  引数x 引数型
) begin
  本文
end;

プロシージャ作成(引数なしの場合)

こんなかんじ

create procedure プロシージャ名 ()
begin
  本文
end;

create procedure test ()
begin
  update boo set foo = woo where hoge = fugo;
end;

何もしない場合

Oracleではbegin~endの中で何もしない場合は「null」を入れる事でエラーとならないようにしていたが、MySQLの場合は逆のこの「null」があるとエラーになってしまう。
begin~endの中に何も記述しなければそれでOK。

create procedure プロシージャ名 ()
begin
end;

宣言

宣言は「declare」を使います。以下は宣言の例たち。

declare v_done int default 0;
declare v_id int;
declare v_tagt_ymd date;
declare v_cur cursor for
  select now() from dual
;
declare continue handler for sqlstate '02000' set v_done = 1;
  • 「declare」ははじめにまとめて行わなければいけないみたいです
  • 「declare」はローカル変数、カーソル、ハンドラの順に宣言を行わないとエラーになるようです

代入

代入は「SET」を使います。代入先の変数は「DECLARE」で宣言されている必要があります。

  1. 以下では変数bの値を変数aに代入します。
    set a = b;
  2. 以下では変数aに数字の5を代入します。
    set a = 5;
  3. 以下では変数aに変数bを、変数cに変数dをそれぞれ代入します。
    set a = b, c = d;

配列

配列は単純な宣言では扱えないようである。変わりに一時テーブルを利用している例文がたくさん存在する。

create temporary table tmp_test (
    itm_id int,
    itm_nm varchar2(4000 char)
) type innodb;
(処理)
drop temporary table if exists tmp_test; 

フロー制御

IF文

  1. 単発のif文
    if a = b then
      set x = y;
    end if;
  2. elseifのあるif文
    if a = b then
      set x = y;
    elseif a = c then
      set x = z;
    end if;
  3. 更にelseもあるif文
    if a = b then
      set x = y;
    elseif a = c then
      set x = z;
    else
      set x = 0;
    end if;

procedureやfunctionを置き換える

一旦dropしたあと再度createする必要がある。

#置き換えるためのreplaceなどの便利な文が無いようです。

drop procedure プロシージャ名;
create procedure プロシージャ名 ()
begin
end;

select文の中身を回す

プロシージャ内でselect文を発行し、ループさせて処理する方法。 いわゆる「for loop」のような構文は無いので、カーソルを宣言してopen, fetch, closeを使うことで実装する。 fetchした際の最後の行の検出方法はエラーコードをハンドラによって取得する方法がオススメらしい(だって公式サイトに書いてあるんだもん♪)

create procedure tmp_1 ()
begin
 -- 宣言部
 -- ハンドラで利用する変数 v_done を宣言
 declare v_done int default 0;
 -- フェッチした値を格納する変数 v_id を宣言
 declare v_id int;
 -- カーソル宣言
 declare v_cur cursor for
   select 1 ID from dual
 ;
 -- SQLステートが02000の場合にv_doneを1にするハンドラを宣言
 declare continue handler for sqlstate '02000' set v_done = 1;

 -- カーソルを開く 
 open v_cur;

 -- repeat関数で繰り返えさせる
 repeat
   -- カーソル v_cur から値を取り出し v_id に格納
   fetch v_cur into v_id;
   -- エラーか判断
   if not v_done then
     -- メイン処理。お好きな処理を書いてね
     select v_id from dual;
   end if;
 -- エラーの場合はループ終了
 until v_done
 end repeat;

 -- 最後にカーソルを閉じる 
 close v_cur;
end;

独自シーケンスを作成する

TESTテーブルというテーブル用にシーケンスを作成し、その値を割り当てる為のストプロ。 TESTテーブルの主キーはTEST_IDとしている。

  1. シーケンス用テーブルを作成
    create table TEST_SQ01(ID bigint(16) not null);
    insert into TEST_SQ01 values (0);
  2. insertトリガーを作成
    delimiter //
    create trigger TEST_TR01 before insert on TEST for each row
    begin
    if new.TEST_ID is null or new.TEST_ID = 0 then
        update TEST_SQ01 set ID = last_insert_id(ID + 1);
        set new.TEST_ID = last_insert_id();
    end if;
    end;
    //
    delimiter ;

ソート対象のカラムを動的に変更する

「order by」句に「case」句を使う事で実現できます。

例) v_orderby_paramでソートしたい列名を受け取る場合

select A, B, C, D, E, F, G
from HOGE
order by case
    when v_orderby_param = 'A' then A
    when v_orderby_param = 'B' then B
    when v_orderby_param = 'C' then C
    when v_orderby_param = 'D' then D
    when v_orderby_param = 'E' then E
    when v_orderby_param = 'F' then F
    when v_orderby_param = 'G' then G
    when v_orderby_param = 'H' then H
end desc

リンク

コメント


トップ   差分 バックアップ リロード   一覧 単語検索 最終更新   ヘルプ
Last-modified: 2010-02-21 (日) 09:30:05 (3137d)