SQLを扱うエンジニアの方でも、ストアドプロシージャに関しては名前を知っているくらいで、実際の使い方はよく分からないといった方も少なくありません。
本記事では、SQLの応用スキルとして「ストアドプロシージャ」の基本的な使い方を解説していきたいと思います。
目次
SQLにおけるストアドプロシージャって何？
データベース(SQL)で利用するストアドプロシージャは簡潔に言うと「複数の命令をまとめた関数」です。
元々複数の命令文を実行しないといけなかったデータベース処理を、1つの関数にまとめることで関数を一度呼び出すだけで処理を完了させることが出来るようになります。
ストアドプロシージャは「戻り値のない」関数であることもポイントです。
ストアドファンクションとの違い
ストアドプロシージャと似た用語として、ストアドファンクションという用語が存在します。
違いは戻り値があるかないかの違いです。
上述した通り、ストアドプロシージャは「戻り値のない」関数で、ストアドファンクションは「戻り値のある」関数です。
MySQLでのストアドプロシージャの基本的な使い方を確認しよう
では実際にMySQLで、ストアドプロシージャの基本的な使い方についてご紹介していきます。
データベース毎にストアドプロシージャの記述方法は異なりますので、ご利用のデータベースに合わせて適宜読み替えをお願いします。
定義
まずは基本となるストアドプロシージャの定義方法です。
基本構文は下記の通りです。
CREATE PROCEDURE プロシージャ名 [SQL文];
CREATE PROCEDUREコマンドにプロシージャ名を入力することで定義することが可能です。
上記の記述方法で指定可能なSQL文は1つだけです。
複数のSQL文を指定する場合には、後述する方法をご利用ください。
一覧確認
定義したストアドプロシージャを確認するには「SHOW」コマンドを利用します。
SHOW PROCEDURE STATUS;
定義内容確認
定義したストアドプロシージャの内容について確認したい場合には、下記のコマンドが利用可能です。
SHOW CREATE PROCEDURE プロシージャ名;
定義済プロシージャの削除
定義済のストアドプロシージャが不要になったため、削除したい場合には「DROP」コマンドが利用可能です。
DROP PROCEDURE プロシージャ名;
複数命令文の定義
実際にストアドプロシージャを定義する際には、複数のSQL文を定義することが多くなります。
複数のSQL文を記述するには下記のように定義します。
DELIMITER // CREATE PROCEDURE プロシージャ名 BEGIN SQL文; SQL文; ... END // DELIMITER ;
SQL文は「;」で定義が完了してしまうので、複数のSQL文を発行したい場合、区切り文字を変更してあげる必要があります。
「DELIMITER」は区切り文字を変更する際に使用するコマンドです。
ストアドプロシージャを定義し終わったあとは再度「DELIMITER」で「;」に戻してあげることを忘れないようにしましょう。
呼び出し
ストアドプロシージャの呼び出しは「CALL」コマンドを利用します。
CALL プロシージャ名;
MySQLでストアドプロシージャを触ってみよう
では実際にMySQLでストアドプロシージャを使ったサンプルをご紹介していきたいと思います。
今回はサンプルとして「user」テーブルを下記のデータで作成しています。
user
+------+--------------+------+------------+ | id | name | age | birthday | +------+--------------+------+------------+ | 1 | 山田太郎 | 30 | 1990-01-01 | | 2 | 山田花子 | 25 | 1995-07-02 | | 3 | 鈴木次郎 | 20 | 2000-05-05 | +------+--------------+------+------------+
プロシージャの作成~実行
まずは簡単にSQL文1つだけのストアドプロシージャを作成してみます。
ちなみにストアドプロシージャには引数を設けることも可能ですので、今回はSQLの条件となる値を引数として定義してみます。
CREATE PROCEDURE test1_procedure(IN arg_age int) SELECT * FROM user WHERE age = arg_age;
サンプルでは「test1_procedure」というプロシージャ名を付与した上で、「arg_age」という引数をint型で定義しています。
ちなみに「IN」というのは入力引数で、「OUT」という出力引数も存在します。
実行するには下記のようにコマンドを入力します。
CALL test1_procedure(25);
結果が下記の通りです。
+------+--------------+------+------------+ | id | name | age | birthday | +------+--------------+------+------------+ | 2 | 山田花子 | 25 | 1995-07-02 | +------+--------------+------+------------+
引数に指定した値が、条件式に反映されたことを確認出来ますね。
プロシージャの作成(応用編)
では次はもう少し複雑なストアドプロシージャを作成したいと思います。
ストアドプロシージャでは、プログラミング言語のようにIF文を利用した条件分岐を使用することも可能です。
今回のサンプルでは、入力された引数によって誕生日として出力されるフォーマットを変更してみたいと思います。
ストアドプロシージャでは返り値はありませんが、引数に「OUT」を指定することで出力結果を保管することが可能となります。
DELIMITER // CREATE PROCEDURE test2_procedure(IN arg_id int, OUT result varchar(30)) BEGIN IF arg_id = 1 THEN SELECT DATE_FORMAT(birthday, '%Y年%m月%d日') INTO result FROM user WHERE id = arg_id; ELSE SELECT DATE_FORMAT(birthday, '%Y/%m/%d') INTO result FROM user WHERE id = arg_id; END IF; END // DELIMITER ;
では引数を設定して動作確認してみましょう。
まずは戻り値用のユーザー定義変数を定義しておきます。
SET @result = '':
続いてプロシージャを呼び出します。
CALL test2_procedure(1, @result);
プロシージャの実行に成功すれば、第二引数に格納されていますので、SELECT文で出力してみましょう。
select @result;
実行した結果が下記の通りです。
+-------------------+ | @result | +-------------------+ | 1990年01月01日 | +-------------------+
次は引数を2に変更して実施してみます。
CALL test2_procedure(2, @result);
SELECT文を実行すると下記の結果となりました。
+------------+ | @result | +------------+ | 1995/07/02 | +------------+
さいごに：ストアドプロシージャの知識を身につけてSQLのスキルを一段階成長させよう！
本記事では、ストアドプロシージャの基本的な情報とMySQLをサンプルとした実際の使い方についてご紹介してきました。
ストアドプロシージャは使い方を理解すれば、複雑な処理でも一度作成してしまえば呼び出しも簡単で、開発効率を大きく挙げられる手法です。
基本的な使い方を把握すれば、他のデータベースの記述方法も比較的簡単に覚えられますので、ぜひこれまで触ったことのない方もチャレンジしてみてください。
