Wiki: ページの作成:「== 概要 == MySQLのストアドプロシージャ (Stored Procedure) は、一連のSQL文をデータベース内に保存し、名前を付けて呼び出せるようにするプログラム単位である。
複雑な処理をカプセル化し、再利用性とメンテナンス性を向上させることができる。

ストアドプロシージャは、パラメータを受け取り、複雑な制御フロー構文 (`IF`、`CASE…」`


2026-02-15T09:02:43Z
ページの作成:「== 概要 == MySQLのストアドプロシージャ (Stored Procedure) は、一連のSQL文をデータベース内に保存し、名前を付けて呼び出せるようにするプログラム単位である。<br> 複雑な処理をカプセル化し、再利用性とメンテナンス性を向上させることができる。<br> <br> ストアドプロシージャは、パラメータを受け取り、複雑な制御フロー構文 (<code>IF</code>、<code>CASE…」
新規ページ
== 概要 ==

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]]
MySQL - ストアドプロシージャ - 版の履歴
