MySQL - ストアドプロシージャのソースを表示

== 概要 ==
MySQLのストアドプロシージャ (Stored Procedure) は、一連のSQL文をデータベース内に保存し、名前を付けて呼び出せるようにするプログラム単位である。<br>
複雑な処理をカプセル化し、再利用性とメンテナンス性を向上させることができる。<br>
<br>
ストアドプロシージャは、パラメータを受け取り、複雑な制御フロー構文 (<code>IF</code>、<code>CASE</code>、<code>LOOP</code>、<code>WHILE</code>、<code>REPEAT</code>) を使用して処理を実行できる。<br>
また、カーソルを使用して結果セットを行単位で処理したり、ハンドラーを使用してエラーや例外を処理することもできる。<br>
<br>
<u>ストアドプロシージャは、<code>CALL</code> 文で呼び出され、IN (入力)、OUT (出力)、INOUT (入出力) の3種類のパラメータをサポートする。</u><br>
<u>OUT および INOUT パラメータを使用することで、呼び出し側に値を返すことができる。</u><br>
<br>
ストアドプロシージャの主なメリットには、ネットワークトラフィックの削減、セキュリティの向上、コードの一元管理がある。<br>
アプリケーション側で複数のSQL文を個別に実行する代わりに、サーバ側で一括処理できるため、パフォーマンスが向上する。<br>
<br>
ストアドプロシージャは、データベース内に永続化されるため、<code>SHOW CREATE PROCEDURE</code> 文や <code>INFORMATION_SCHEMA.ROUTINES</code> テーブルから定義を確認できる。<br>
プロシージャ本体を変更する場合は、DROP + CREATE による再作成が必要である。<br>
<br>
<u>ストアドプロシージャと類似した機能として、ストアドファンクション (Stored Function) がある。</u><br>
<u>ファンクションはRETURN文で単一の値を返し、SELECT文内で使用できるが、プロシージャは <code>CALL</code> 文で呼び出し、OUT / INOUTパラメータで複数の値を返す点で異なる。</u><br>
<br><br>

== ストアドプロシージャの作成 ==
==== 基本構文 ====
ストアドプロシージャを作成するには、<code>CREATE PROCEDURE</code> 文を使用する。<br>
<br>
完全な構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] <プロシージャ名> ([<パラメータ>[,...]])
    [characteristic ...]
    routine_body
 
 proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
 characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 }
 
 routine_body:
    Valid SQL routine statement
 </syntaxhighlight>
<br>
基本的な例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE simple_proc()
 BEGIN
    SELECT 'Hello, World!';
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
<code>DELIMITER</code> 文は、ストアドプロシージャ内でセミコロン (;) を使用するため、一時的にデリミタを変更する必要がある。<br>
<code>DELIMITER //</code> で変更し、プロシージャ定義の最後に <code>DELIMITER ;</code> で元に戻す。<br>
<br>
IF NOT EXISTS句を指定すると、同名のプロシージャが既に存在する場合にエラーにならず、警告のみが出力される。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE IF NOT EXISTS get_user_count()
 BEGIN
    SELECT COUNT(*) FROM users;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
==== パラメータの種類 ====
ストアドプロシージャのパラメータには、IN、OUT、INOUTの3種類がある。<br>
<br>
<center>
{| class="wikitable"
|+ パラメータの種類
! パラメータ !! 説明
|-
| IN
|
* 入力パラメータ (デフォルト)
* 呼び出し側からプロシージャに値を渡す
* プロシージャ内での変更は呼び出し側に影響しない
|-
| OUT
|
* 出力パラメータ
* プロシージャから呼び出し側に値を返す
* プロシージャ開始時にNULLで初期化される
|-
| INOUT
|
* 入出力パラメータ
* 呼び出し側からの入力値を受け取り、プロシージャ内で変更した値を呼び出し側に返す
|}
</center>
<br>
各パラメータの使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE calc_total(
    IN price DECIMAL(10,2),
    IN quantity INT,
    OUT total DECIMAL(10,2)
 )
 BEGIN
    SET total = price * quantity;
 END//
 
 DELIMITER ;
 
 -- 呼び出し例
 CALL calc_total(100.50, 3, @result);
 SELECT @result;  -- 結果: 301.50
 </syntaxhighlight>
<br>
INOUTパラメータの使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE double_value(INOUT num INT)
 BEGIN
    SET num = num * 2;
 END//
 
 DELIMITER ;
 
 -- 呼び出し例
 SET @value = 10;
 CALL double_value(@value);
 SELECT @value;  -- 結果: 20
 </syntaxhighlight>
<br>
==== 変数の宣言と代入 ====
ストアドプロシージャ内では、<code>DECLARE</code> 文で変数を宣言し、<code>SET</code> 文や <code>SELECT INTO</code> 文で値を代入できる。<br>
<br>
変数宣言の構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DECLARE <変数名> [, <変数名>] ... <データ型> [DEFAULT <デフォルト値>]
 </syntaxhighlight>
<br>
変数への代入には、以下の2つの方法がある。<br>
<br>
 <syntaxhighlight lang="mysql">
 SET <変数名> = <式> [, <変数名> = <式>] ...
 
 SELECT <列名> [, ...] INTO <変数名> [, ...] FROM <テーブル名> WHERE ...
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE variable_example()
 BEGIN
    DECLARE user_count INT DEFAULT 0;
    DECLARE max_price DECIMAL(10,2);
    DECLARE product_name VARCHAR(100);
 
    -- SET文による代入
    SET user_count = 100;
 
    -- SELECT INTO文による代入
    SELECT MAX(price), name
    INTO max_price, product_name
    FROM products
    WHERE price = (SELECT MAX(price) FROM products);

    SELECT user_count, max_price, product_name;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
変数のスコープは、宣言されたBEGIN...ENDブロック内に限定される。<br>
変数は、宣言された順序で初期化される。<br>
<br><br>

== ストアドプロシージャの実行 ==
ストアドプロシージャを実行するには、<code>CALL</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 CALL <プロシージャ名>([<パラメータ>[, ...]]);
 </syntaxhighlight>
<br>
* パラメータなしのプロシージャを呼び出す例
*: <syntaxhighlight lang="mysql">
 CALL simple_proc();
 </syntaxhighlight>
*: <br>
* INパラメータを持つプロシージャを呼び出す例
*: <syntaxhighlight lang="mysql">
 CALL get_user_info(123);
 </syntaxhighlight>
*: <br>
* OUT / INOUTパラメータを持つプロシージャを呼び出す場合は、ユーザ定義変数 (<code>@変数名</code>) を使用する。
*: <syntaxhighlight lang="mysql">
 CALL calc_total(100.50, 3, @result);
 SELECT @result;
 </syntaxhighlight>
*: <br>
* 複数のOUTパラメータを持つプロシージャの例
*: <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE get_user_stats(
    OUT total_users INT,
    OUT active_users INT,
    OUT inactive_users INT
 )
 BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
    SELECT COUNT(*) INTO active_users FROM users WHERE status = 'active';
    SELECT COUNT(*) INTO inactive_users FROM users WHERE status = 'inactive';
 END//
 
 DELIMITER ;
 
 -- 呼び出し例
 CALL get_user_stats(@total, @active, @inactive);
 SELECT @total, @active, @inactive;
 </syntaxhighlight>
<br><br>

== 制御フロー構文 ==
==== IF文 ====
IF文は、条件に応じて処理を分岐させる。<br>
<br>
 <syntaxhighlight lang="mysql">
 IF <条件式> THEN
    <処理>
 [ELSEIF <条件式> THEN
    <処理>] ...
 [ELSE
    <処理>]
 END IF
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE check_price(IN price DECIMAL(10,2), OUT category VARCHAR(20))
 BEGIN
    IF price < 100 THEN
       SET category = '安価';
    ELSEIF price < 1000 THEN
       SET category = '中価格';
    ELSE
       SET category = '高価';
    END IF;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
==== CASE文 ====
CASE文には、単純CASEと検索CASEの2種類がある。<br>
<br>
単純CASE文の構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 CASE <判定値>
    WHEN <値> THEN <処理>
    [WHEN <値> THEN <処理>] ...
    [ELSE <処理>]
 END CASE
 </syntaxhighlight>
<br>
検索CASE文の構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 CASE
    WHEN <条件式> THEN <処理>
    [WHEN <条件式> THEN <処理>] ...
    [ELSE <処理>]
 END CASE
 </syntaxhighlight>
<br>
単純CASE文の使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE get_day_type(IN day_num INT, OUT day_type VARCHAR(20))
 BEGIN
    CASE day_num
       WHEN 1 THEN SET day_type = '月曜日';
       WHEN 2 THEN SET day_type = '火曜日';
       WHEN 3 THEN SET day_type = '水曜日';
       WHEN 4 THEN SET day_type = '木曜日';
       WHEN 5 THEN SET day_type = '金曜日';
       WHEN 6 THEN SET day_type = '土曜日';
       WHEN 7 THEN SET day_type = '日曜日';
       ELSE SET day_type = '不明';
    END CASE;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
検索CASE文の使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE grade_score(IN score INT, OUT grade CHAR(1))
 BEGIN
    CASE
       WHEN score >= 90 THEN SET grade = 'A';
       WHEN score >= 80 THEN SET grade = 'B';
       WHEN score >= 70 THEN SET grade = 'C';
       WHEN score >= 60 THEN SET grade = 'D';
       ELSE SET grade = 'F';
    END CASE;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
==== LOOP文 ====
LOOP文は、無限ループを実装する。<br>
LEAVE文でループを抜け、ITERATE文でループの先頭に戻る。<br>
<br>
 <syntaxhighlight lang="mysql">
 [<ラベル>:] LOOP
    <処理>
 END LOOP [<ラベル>]
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE loop_example()
 BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE sum INT DEFAULT 0;
 
    my_loop: LOOP
       SET counter = counter + 1;
       SET sum = sum + counter;
 
       IF counter >= 10 THEN
          LEAVE my_loop;
       END IF;
    END LOOP my_loop;
 
    SELECT sum;  -- 結果: 55
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
ITERATE文を使用した例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE iterate_example()
 BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE even_sum INT DEFAULT 0;
 
    my_loop: LOOP
       SET counter = counter + 1;
 
       IF counter > 10 THEN
          LEAVE my_loop;
       END IF;
 
       IF counter MOD 2 = 1 THEN
          ITERATE my_loop;  -- 奇数はスキップ
       END IF;
 
       SET even_sum = even_sum + counter;
    END LOOP my_loop;
 
    SELECT even_sum;  -- 結果: 30 (2+4+6+8+10)
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
==== WHILE文 ====
WHILE文は、条件がTRUEの間、処理を繰り返す。<br>
<br>
 <syntaxhighlight lang="mysql">
 [<ラベル>:] WHILE <条件式> DO
    <処理>
 END WHILE [<ラベル>]
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE while_example()
 BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE factorial INT DEFAULT 1;
 
    WHILE counter <= 5 DO
       SET factorial = factorial * counter;
       SET counter = counter + 1;
    END WHILE;
 
    SELECT factorial;  -- 結果: 120 (5の階乗)
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
==== REPEAT文 ====
REPEAT文は、条件がTRUEになるまで処理を繰り返す。<br>
WHILE文とは異なり、最低1回は処理が実行される。<br>
<br>
 <syntaxhighlight lang="mysql">
 [<ラベル>:] REPEAT
    <処理>
 UNTIL <条件式>
 END REPEAT [<ラベル>]
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE repeat_example()
 BEGIN
    DECLARE counter INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;
 
    REPEAT
       SET sum = sum + counter;
       SET counter = counter + 1;
    UNTIL counter > 10
    END REPEAT;
 
    SELECT sum;  -- 結果: 55
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br><br>

== カーソル ==
==== カーソルの基本 ====
カーソルは、SELECT文の結果セットを行単位で処理するための機能である。<br>
<br>
カーソルの基本的な構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DECLARE <カーソル名> CURSOR FOR <SELECT文>;
 OPEN <カーソル名>;
 FETCH <カーソル名> INTO <変数名> [, <変数名>] ...;
 CLOSE <カーソル名>;
 </syntaxhighlight>
<br>
カーソルの宣言は、変数宣言の後、ハンドラー宣言の前に行う必要がある。<br>
SELECT文にはINTO句を使用できない。<br>
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE cursor_example()
 BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_name VARCHAR(50);
    DECLARE user_email VARCHAR(100);
    DECLARE cur CURSOR FOR SELECT username, email FROM users;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
    OPEN cur;
 
    read_loop: LOOP
       FETCH cur INTO user_name, user_email;
       IF done THEN
          LEAVE read_loop;
       END IF;
 
       -- 各行に対する処理
       SELECT user_name, user_email;
    END LOOP;
 
    CLOSE cur;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
==== カーソルと繰り返し処理 ====
カーソルは、<code>NOT FOUND</code> ハンドラーと組み合わせて使用する。<br>
カーソルの終端に達すると、NOT FOUND条件が発生する。<br>
<br>
<u>カーソルは、使用後に必ず <code>CLOSE</code> 文で閉じる必要がある。</u><br>
<br>
実用的な例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE curdemo()
 BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE a CHAR(16);
    DECLARE b, c INT;
    DECLARE cur1 CURSOR FOR SELECT id, data FROM test.t1;
    DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
    OPEN cur1;
    OPEN cur2;
 
    read_loop: LOOP
       FETCH cur1 INTO a, b;
       FETCH cur2 INTO c;
       IF done THEN
          LEAVE read_loop;
       END IF;
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a, b);
       ELSE
          INSERT INTO test.t3 VALUES (a, c);
       END IF;
    END LOOP;
 
    CLOSE cur1;
    CLOSE cur2;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
複数のカーソルを同時に使用する場合は、上記の例のように個別に開閉する。<br>
<br><br>

== ハンドラー ==
ハンドラーは、エラーや例外が発生した際の処理を定義する。<br>
<br>
 <syntaxhighlight lang="mysql">
 DECLARE handler_action HANDLER
    FOR <条件値> [, <条件値>] ...
    statement;
 
 handler_action: { CONTINUE | EXIT | UNDO }
 
 condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
 }
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ ハンドラーアクション
! アクション !! 説明
|-
| CONTINUE || ハンドラー実行後、処理を続行する
|-
| EXIT || ハンドラー実行後、現在のBEGIN...ENDブロックを終了する
|-
| UNDO || 未サポート
|}
</center>
<br>
<center>
{| class="wikitable"
|+ 条件値
! 条件値 !! 説明
|-
| SQLWARNING || '01'で始まるSQLSTATEの短縮形
|-
| NOT FOUND || '02'で始まるSQLSTATEの短縮形 (カーソルの終端等)
|-
| SQLEXCEPTION || '00', '01', '02'以外のSQLSTATEの短縮形
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE handler_example()
 BEGIN
    DECLARE duplicate_key INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR 1062
       SET duplicate_key = 1;
 
    INSERT INTO users (user_id, username) VALUES (1, 'test');
 
    IF duplicate_key = 1 THEN
       SELECT 'Duplicate key error occurred';
    ELSE
       SELECT 'Insert successful';
    END IF;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
* SQLSTATE値を使用した例
*: <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE sqlstate_handler()
 BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
       SELECT 'Integrity constraint violation';
 
    INSERT INTO orders (order_id, customer_id) VALUES (1, 999);
 END//
 
 DELIMITER ;
 </syntaxhighlight>
*: <br>
* 複数の条件を処理する例
*: <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE multi_handler()
 BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
       ROLLBACK;
       SELECT 'An error occurred, transaction rolled back';
    END;
 
    DECLARE CONTINUE HANDLER FOR SQLWARNING
       SELECT 'Warning occurred';
 
    START TRANSACTION;
    -- SQL文
    COMMIT;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br><br>

== ストアドプロシージャの変更と削除 ==
ストアドプロシージャの本体 (routine_body) を変更する場合は、<code>DROP PROCEDURE</code> と <code>CREATE PROCEDURE</code> を組み合わせて再作成する必要がある。<br>
<br>
<u><code>ALTER PROCEDURE</code> 文は、プロシージャの特性 (characteristics) のみを変更できる。</u><br>
<u>変更可能な特性は、COMMENT、LANGUAGE SQL、SQL SECURITY、その他のcharacteristicsである。</u><br>
<br>
 <syntaxhighlight lang="mysql">
 ALTER PROCEDURE <プロシージャ名>
    [characteristic ...]
 
 characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | SQL SECURITY { DEFINER | INVOKER }
 }
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DROP PROCEDURE IF EXISTS get_user_count;
 </syntaxhighlight>
<br>
IF EXISTS句を指定すると、プロシージャが存在しない場合でもエラーにならず、警告のみが出力される。<br>
<br>
* コメントを変更する例
*: <syntaxhighlight lang="mysql">
 ALTER PROCEDURE get_user_count
    COMMENT '全ユーザ数を取得するプロシージャ';
 </syntaxhighlight>
*: <br>
* ストアドプロシージャを削除するには、<code>DROP PROCEDURE</code>文を使用する。
*: <syntaxhighlight lang="mysql">
 DROP PROCEDURE [IF EXISTS] <プロシージャ名>
 </syntaxhighlight>
<br><br>

== ストアドプロシージャの確認 ==
ストアドプロシージャの定義を確認するには、<code>SHOW CREATE PROCEDURE</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW CREATE PROCEDURE <プロシージャ名>;
 </syntaxhighlight>
<br>
データベース内の全てのプロシージャ一覧を確認するには、<code>SHOW PROCEDURE STATUS</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW PROCEDURE STATUS;
 
 -- 特定のデータベースのプロシージャのみを表示
 SHOW PROCEDURE STATUS WHERE Db = 'database_name';
 
 -- パターンマッチング
 SHOW PROCEDURE STATUS LIKE 'get_%';
 </syntaxhighlight>
<br>
<code>INFORMATION_SCHEMA.ROUTINES</code> テーブルから詳細情報を取得することもできる。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION
 FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_SCHEMA = '<データベース名>'
   AND ROUTINE_TYPE = 'PROCEDURE';
 </syntaxhighlight>
<br>
プロシージャのパラメータ情報を確認する場合は、<code>INFORMATION_SCHEMA.PARAMETERS</code> テーブルを使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT PARAMETER_NAME, PARAMETER_MODE, DATA_TYPE
 FROM INFORMATION_SCHEMA.PARAMETERS
 WHERE SPECIFIC_SCHEMA = '<データベース名>'
   AND SPECIFIC_NAME = '<プロシージャ名>'
 ORDER BY ORDINAL_POSITION;
 </syntaxhighlight>
<br><br>

== トランザクション制御 ==
ストアドプロシージャ内で、トランザクション制御を行うことができる。<br>
<br>
 <syntaxhighlight lang="mysql">
 START TRANSACTION;
 -- SQL文
 COMMIT;
 -- または
 ROLLBACK;
 </syntaxhighlight>
<br>
トランザクションを使用した例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE transfer_money(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
 )
 BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
       ROLLBACK;
       SELECT 'Transaction failed, rolled back';
    END;
 
    START TRANSACTION;
 
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
 
    COMMIT;
    SELECT 'Transaction successful';
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
SAVEPOINTを使用した例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE savepoint_example()
 BEGIN
    START TRANSACTION;
 
    INSERT INTO log_table (message) VALUES ('Start');
 
    SAVEPOINT sp1;
 
    INSERT INTO log_table (message) VALUES ('Step 1');
 
    SAVEPOINT sp2;
 
    INSERT INTO log_table (message) VALUES ('Step 2');
 
    -- sp2までロールバック
    ROLLBACK TO SAVEPOINT sp2;
 
    COMMIT;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
ストアドプロシージャ内でトランザクションを使用する場合、エラーハンドラーと組み合わせることで、エラー時の自動ロールバックを実装できる。<br>
<br><br>

== ストアドファンクションとの違い ==
ストアドプロシージャとストアドファンクションの主な違いを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ ストアドプロシージャとストアドファンクションの比較
! 項目 !! ストアドプロシージャ !! ストアドファンクション
|-
| 呼び出し方法 || <code>CALL</code>文 || SELECT文内で使用
|-
| 戻り値 || OUT / INOUTパラメータで複数の値を返す || RETURN文で単一の値を返す
|-
| RETURNS句 || 不要 || 必須
|-
| パラメータ || IN / OUT / INOUT || INのみ
|-
| トランザクション制御 || 可能 || 制限あり (COMMIT / ROLLBACK不可)
|-
| DML文 || 使用可能 || 使用可能
|-
| 結果セットを返すSELECT文 || 使用可能 || 使用不可
|-
| DETERMINISTIC / NO SQL / READS SQL DATA || オプション || 必須 (いずれか1つ)
|}
</center>
<br>
ストアドファンクションの例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE FUNCTION calc_tax(price DECIMAL(10,2))
 RETURNS DECIMAL(10,2)
 DETERMINISTIC
 BEGIN
    RETURN price * 0.10;
 END//
 
 DELIMITER ;
 
 -- 使用例
 SELECT product_name, price, calc_tax(price) AS tax
 FROM products;
 </syntaxhighlight>
<br>
ストアドファンクションは、DETERMINISTIC または NO SQL / READS SQL DATAの指定が必須である。<br>
これにより、MySQLはファンクションが決定的 (同じ入力に対して常に同じ出力を返す) かどうかを判断できる。<br>
<br>
ストアドプロシージャは、複雑な処理や複数の値を返す場合に適している。<br>
ストアドファンクションは、単一の計算結果を返し、SELECT文内で使用する場合に適している。<br>
<br><br>

== サンプルクエリ ==
==== ページネーション処理 ====
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE get_users_paginated(
    IN page_num INT,
    IN page_size INT,
    OUT total_count INT
 )
 BEGIN
    DECLARE offset_val INT;
 
    SET offset_val = (page_num - 1) * page_size;
 
    SELECT COUNT(*) INTO total_count FROM users;
 
    SELECT user_id, username, email, created_at
    FROM users
    ORDER BY user_id
    LIMIT page_size OFFSET offset_val;
 END//
 
 DELIMITER ;
 
 -- 呼び出し例: 2ページ目を取得 (1ページあたり10件)
 CALL get_users_paginated(2, 10, @total);
 SELECT @total AS total_users;
 </syntaxhighlight>
<br>
==== 条件付き集計処理 ====
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE get_sales_summary(
    IN start_date DATE,
    IN end_date DATE
 )
 BEGIN
    SELECT
       COUNT(*) AS total_orders,
       SUM(total_amount) AS total_sales,
       AVG(total_amount) AS avg_order_value,
       MAX(total_amount) AS max_order_value,
       MIN(total_amount) AS min_order_value
    FROM orders
    WHERE order_date BETWEEN start_date AND end_date;
 
    SELECT
       product_id,
       SUM(quantity) AS total_quantity,
       SUM(quantity * unit_price) AS total_revenue
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_date BETWEEN start_date AND end_date
    GROUP BY product_id
    ORDER BY total_revenue DESC
    LIMIT 10;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
==== データ検証と挿入 ====
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE PROCEDURE insert_order(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_status VARCHAR(50)
 )
 BEGIN
    DECLARE v_stock INT;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_total DECIMAL(10,2);
 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
       ROLLBACK;
       SET p_status = 'エラーが発生しました';
       SET p_order_id = NULL;
    END;
 
    -- 在庫確認
    SELECT stock, price INTO v_stock, v_price
    FROM products
    WHERE product_id = p_product_id;
 
    IF v_stock < p_quantity THEN
       SET p_status = '在庫不足';
       SET p_order_id = NULL;
    ELSE
       START TRANSACTION;
 
       SET v_total = v_price * p_quantity;
 
       INSERT INTO orders (customer_id, order_date, total_amount)
       VALUES (p_customer_id, NOW(), v_total);
 
       SET p_order_id = LAST_INSERT_ID();
 
       INSERT INTO order_items (order_id, product_id, quantity, unit_price)
       VALUES (p_order_id, p_product_id, p_quantity, v_price);
 
       UPDATE products
       SET stock = stock - p_quantity
       WHERE product_id = p_product_id;
 
       COMMIT;
       SET p_status = '注文が完了しました';
    END IF;
 END//
 
 DELIMITER ;
 
 -- 呼び出し例
 CALL insert_order(123, 456, 5, @order_id, @status);
 SELECT @order_id, @status;
 </syntaxhighlight>
<br><br>

== 関連ページ ==
* [[MySQL - トリガー]]
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,MySQL,Stored Procedure,ストアドプロシージャ,CREATE PROCEDURE,DROP PROCEDURE,ALTER PROCEDURE,CALL,Database,データベース,SQL,パラメータ,IN,OUT,INOUT,制御フロー,IF,CASE,LOOP,WHILE,REPEAT,カーソル,CURSOR,ハンドラー,HANDLER,トランザクション,Stored Function,ストアドファンクション,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - MySQLのストアドプロシージャの作成、実行、制御フロー、カーソル、ハンドラー、トランザクション制御に関する総合ガイド | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]