MySQLのストアドプロシージャとストアドファンクション

概要

MySQLのストアドプロシージャとストアドファンクションについて調べてみたのでメモ

用語の整理

• MySQLでは、以下のストアド〇〇という用語が存在する
  • https://dev.mysql.com/doc/refman/8.0/ja/stored-objects.html

ストアドオブジェクトには、ストアドプログラムおよびビューが含まれます。
ストアドプログラムには、ストアドルーチン、トリガー、およびイベントが含まれます。
ストアドルーチンには、ストアドプロシージャーとストアドファンクションが含まれます。

事前準備

• ストアドプロシージャと、ストアドファンクションはデータベースに対して作成されるため、試すためのデータベースとテーブルをあらかじめ作成してする
• ここで主キーとなるidと、あまり意味がnumberをという適当な数値用のカラムを作成する

/* dbおよび、テーブルの作成 */
mysql> create database sample character set utf8mb4;
mysql> create table users (id int auto_increment primary key, number int, index(number));

ストアドプロシージャとは

• DBの処理をあらかじめ定義して、関数のように呼び出しを行って使用できるもの。引数、戻り値も設定可能
• プロシージャ内部でINSERT/UPDATE/DELETEなどの一連の流れをまとめた処理を作成する
• トランザクション処理も可能
• エラーを例外にて捕捉でき、必要に応じて処理をROLLBACKすることが可能
• プロシージャの中でプロシージャの処理の呼び出しも可能

メリット

• https://dev.mysql.com/doc/refman/8.0/ja/stored-routines.html

• ストアドプロシージャを利用することで、一連の処理をアプリケーションサーバと、DBサーバ間の通信を行わずに処理を行うことができるようになるため、パフォーマンスの改善が可能になる

ストアドルーチンは、サーバーとクライアント間で送信する必要のある情報が少なくなるので、パフォーマンスを改善できます。 そのトレードオフでは、これによりサーバー側で行われる作業が増え、クライアント (アプリケーション) 側で行われる作業が少なくなるので、データベースサーバーでのロードが増大します。 1 台または少数のデータベースサーバーだけで多数のクライアントマシン (Web サーバーなど) にサービスを提供している場合にはこれを検討してください。

• アプリケーションサーバの開発言語に依存しない、処理の共通化が可能になる（DBには依存することになる）

クライアントアプリケーションが異なる言語で作成されているか、異なるプラットフォームで動作しているが、同じデータベース操作を実行する必要がある場合。

• 処理を実行できるユーザを管理できる

セキュリティーが最重要である場合。 たとえば、銀行では、すべての一般的な操作に対してストアドプロシージャーおよびストアドファンクションを使用します。 これにより一貫したセキュアな環境が得られ、ルーチンによってそれぞれの操作が正しく記録されるようになります。 このようなセットアップでは、アプリケーションおよびユーザーはデータベーステーブルに直接アクセスできませんが、特定のストアドルーチンだけを実行できます。

下記はOracleの資料だが、参考としてメモ

• https://docs.oracle.com/cd/E82638_01/jjdev/stored-procedure-advantages.html#GUID-B6F864FC-5C87-45DC-BBEC-2CEE469A939C

SQL文をグループ化することによって、ストアド・プロシージャではそれらを1回のコールで処理できます。この結果、ネットワークの通信量が減少し、ラウンドトリップの応答時間が短縮されます。

ストアド・プロシージャは、一度コンパイルされて実行可能な形式で格納されています。そのため、プロシージャ・コールが迅速かつ効率的に処理されます。実行可能コードは自動的にキャッシュされ、ユーザー間で共有されます。この結果、メモリー要件や起動時のオーバーヘッドが低減します。

作成

• create procedure ステートメントを使用してプロシージャを作成する

  • https://dev.mysql.com/doc/refman/8.0/ja/create-procedure.html

• cli上でストアドププシージャを作製する場合、MySQLでは区切り文字にセミコロンを使用しているが、ストアドプロシージャの区切り文字についてもセミコロンを使用しているため、delimiter を使用して一時的に区切り文字を変更する必要がある

/* 同名のプロシージャが存在する場合は削除 */
mysql> drop procedure if exists create_test_data;

/* 区切り文字の変更 */
mysql> delimiter //

/* テストデータを挿入するためのプロシージャを作成 引数 i を定義している */
mysql> create procedure create_test_data(in i int)
    -> begin
    ->   declare cnt int default 0; /* 変数宣言 */
    ->   while cnt < i do
    ->     set cnt = cnt + 1;
    ->     insert into users(number) values(cnt); /* テストデータを挿入 */
    ->   end while;
    -> end
    -> //

/* 区切り文字を ; に戻す*/
mysql> delimiter ;

以下のコマンドで、作成したプロシージャの名前を指定することで中身の確認ができる

mysql> show create procedure create_test_data \G
*************************** 1. row ***************************
           Procedure: create_test_data
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `create_test_data`(in i int, out cnt int)
begin
  declare cnt int default 0;
  while cnt < i do
    set cnt = cnt + 1;
    insert into users(number) values(cnt);
  end while;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

実行

• call ステートメントを使用して、コマンドラインからプロシージャの呼び出しが可能
  • https://dev.mysql.com/doc/refman/8.0/ja/call.html

/* call で処理の呼び出しが可能 */
> call sample.create_test_data(100);

/* テストデータを確認 */
> select count(*) from users;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

ストアドファンクション

• ファンクション内では、INSERT/UPDATE/DELETEを行わないのが一般的で、パラメータにて渡された値の判定、変換処理、値の抽出を行うために使用する。引数と戻り値を設定できる
• SQL内で使用する、関数を作成することが可能。SUM関数のような使い方ができる

作成

/* 同名のファンクションが存在する場合は削除 */
mysql> drop procedure if exists create_test_data;
mysql> delimiter //

/* ファンクションの作成、引数および戻り値を定義している */
mysql> create function hello (s char(20)) returns char(50) deterministic
    -> begin
    ->   return concat('Hello, ',s,'!'); 
    -> end
    -> //
mysql> delimiter ;

以下のコマンドで、作成したファンクションの名前を指定することで中身の確認ができる

mysql> show create function hello\G
*************************** 1. row ***************************
            Function: hello
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `hello`(s CHAR(20)) RETURNS char(50) CHARSET utf8mb4
    DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!')
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

実行

• SQLを実行する際に、作成したファンクションを利用することが可能。もちろんストアドプロシージャ中で利用が可能

mysql> SELECT hello('world');

参考

• https://qiita.com/setsuna82001/items/e742338eb93e3a48ba46