SQL ストアドプロシージャ

※MySQLを前提に記載しています。

ストアドの作成

create procedure ストアド名()

select * from テーブル名;

作成済みのストアドの定義文の確認

show create procedure ストアド名;

ストアドの削除

drop procedure ストアド名;

登録されているストアド一覧の確認

「information_schema.ROUTINES」テーブルに格納されている。

select ROUTINE_NAME, ROUTINE_TYPE from information_schema.ROUTINES;

⇒ROUTINE_TYPE = PROCEDUREのものが該当

複数行のストアド処理を登録する。

1.デリミタの変更

通常、「;」を入力すると処理が走る。
この「;」をデリミタ(DELIMITER)と呼ぶ。 (delimit = 区切り）

ストアドで複数行処理を登録する場合は、プロセス内に「;」を複数回含める必要があるが、1度目の「;」で処理が実行されてしまうため、ストアドの登録時のみ一時的にデリミタを変更して登録を行う。

一般的にデリミタを「;」から「//」に変更する。

DELIMITER //

登録完了後、デリミタを元に戻す。

DELIMITER ;

2.ストアドの登録

複数行登録するときは、「BEGIN」と「END;」ではさむ

> DELIMITER //

> create procedure ストアド名()
-> BEGIN
-> 処理1;
-> 処理2;
-> 処理3;
-> END;
-> //

> DELIMITER ;

ストアドの入出力（引数、返り値）

入力引数をIN、出力引数をOUTで定義する。型を指定する必要あり。

create procedure ストアド名(IN 引数1 CHAR(10), OUT 引数2 CHAR(10)）
BEGIN
  select 出力カラム名
  into 引数2
  from テーブル名
  where 条件カラム名 = 引数1;
END;
//

例えば、以下のようなテーブルがあったとして、

・テーブル「name_table」

ID	NAME
1	Pochi
2	Tama

ここから、IDで検索してNAMEを取得する場合は、

・ストアド

create procedure find_name (IN in_id CHAR(4), OUT out_name CHAR(16))
    --入力にIDを指定して、出力で結果の名前を受け取る

BEGIN
  select NAME
  into out_name    --NAMEカラムから取得した結果を、出力変数のout_nameに格納する
  from name_table
  where ID = in_id;    --where条件に、入力したIDを指定して絞り込む
END;
//

・呼び出し

>call find_name('1', @name);
    --入力で、ID=1を指定、結果を受け取る変数を@nameとしている。（変数は@で宣言）

>select @name;    --結果を表示する

@name
Pochi

てみブログ

個人的な覚書用です