ストアドプロシージャ

MySQL データベースは、ストアドプロシージャをサポートしています。 ストアドプロシージャは、 データベースカタログに保存されたサブルーチンです。 アプリケーションは、ストアドプロシージャを呼び出し、実行できます。 ストアドプロシージャを実行するには、SQL ステートメント CALL を使います。

ストアドプロシージャへの引数

MySQL のバージョンによっては、 ストアドプロシージャで IN, INOUT, OUT という引数をとることができるものがあります。 mysqli インターフェイスは、 引数の違いについて、特別な考慮を行いません。

IN パラメータ

CALL ステートメントに渡す入力パラメータに使います。 値が適切にエスケープされていることを必ず確認するようにして下さい。

例1 ストアドプロシージャを呼び出す

<?php

mysqli_report
(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com""user""password""database");

$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");

$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query("CREATE PROCEDURE p(IN id_val INT) BEGIN INSERT INTO test(id) VALUES(id_val); END;");

$mysqli->query("CALL p(1)");

$result $mysqli->query("SELECT id FROM test");

var_dump($result->fetch_assoc());

上の例の出力は以下となります。

array(1) {
  ["id"]=>
  string(1) "1"
}

INOUT/OUT パラメータ

INOUT/OUT パラメータに渡した値は、 セッションの値を使ってアクセスできます

例2 セッションの値を使う

<?php

mysqli_report
(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com""user""password""database");

$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p(OUT msg VARCHAR(50)) BEGIN SELECT "Hi!" INTO msg; END;');

$mysqli->query("SET @msg = ''");
$mysqli->query("CALL p(@msg)");

$result $mysqli->query("SELECT @msg as _p_out");

$row $result->fetch_assoc();
echo 
$row['_p_out'];

上の例の出力は以下となります。

Hi!

アプリケーションやフレームワークの開発者は、 セッションの値やデータベースカタログを調べることによって、 もっと便利なAPIを提供できます。 しかし、カタログを調べることをベースにしたカスタムのやり方は、 パフォーマンスに影響がある可能性があることに注意して下さい。

結果セットを扱う

ストアドプロシージャは、 結果セットを返すことができます。 ストアドプロシージャから返された結果セットは、 mysqli::query() を使っても正しく取得できません。 mysqli::query() は、ステートメントを実行し、 バッファリングされた結果セットから、存在する場合にだけ、 最初の結果セットを返すものです。 しかし、 mysqli::query() は、 ストアドプロシージャが返す追加の結果セットを隠してしまうため、 ユーザが期待する結果セットを返すことに失敗してしまうのです。

ストアドプロシージャから返される結果セットは、 mysqli::real_query()mysqli::multi_query() を使うと取得できます。 これらの関数は、 CALL のような、 任意の数の結果セットを返すステートメントから結果を取得できます。 ストアドプロシージャによって返される、 結果セットが全部取得できない場合は、エラーが発生します。

例3 ストアドプロシージャから、結果を取得する

<?php

mysqli_report
(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com""user""password""database");

$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");

$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');

$mysqli->multi_query("CALL p()");

do {
    if (
$result $mysqli->store_result()) {
        
printf("---\n");
        
var_dump($result->fetch_all());
        
$result->free();
    }
} while (
$mysqli->next_result());

上の例の出力は以下となります。

---
array(3) {
  [0]=>
  array(1) {
    [0]=>
    string(1) "1"
  }
  [1]=>
  array(1) {
    [0]=>
    string(1) "2"
  }
  [2]=>
  array(1) {
    [0]=>
    string(1) "3"
  }
}
---
array(3) {
  [0]=>
  array(1) {
    [0]=>
    string(1) "2"
  }
  [1]=>
  array(1) {
    [0]=>
    string(1) "3"
  }
  [2]=>
  array(1) {
    [0]=>
    string(1) "4"
  }
}

プリペアドステートメントを使う

上に示した、同じストアドプロシージャから結果を取得する場合に、 プリペアドステートメントを使う場合であっても、 特別な操作は必要ありません。 プリペアドステートメントと、 それを用いないインターフェイスは似ています。 全てのバージョンの MySQL サーバが、 CALL ステートメントを準備することをサポートしているわけではないことに注意して下さい。

例4 ストアドプロシージャとプリペアドステートメント

<?php

mysqli_report
(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com""user""password""database");

$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");

$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');

$stmt $mysqli->prepare("CALL p()");

$stmt->execute();

do {
    if (
$result $stmt->get_result()) {
        
printf("---\n");
        
var_dump($result->fetch_all());
        
$result->free();
    }
} while (
$stmt->next_result());

上の例の出力は以下となります。

---
array(3) {
  [0]=>
  array(1) {
    [0]=>
    int(1)
  }
  [1]=>
  array(1) {
    [0]=>
    int(2)
  }
  [2]=>
  array(1) {
    [0]=>
    int(3)
  }
}
---
array(3) {
  [0]=>
  array(1) {
    [0]=>
    int(2)
  }
  [1]=>
  array(1) {
    [0]=>
    int(3)
  }
  [2]=>
  array(1) {
    [0]=>
    int(4)
  }
}

もちろん、 値を取得するためのバインドAPIもサポートしています。

例5 バインドAPIを使って、プリペアドステートメントとストアドプロシージャを実行する

<?php

mysqli_report
(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com""user""password""database");

$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");

$mysqli->query("DROP PROCEDURE IF EXISTS p");
$mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');

$stmt $mysqli->prepare("CALL p()");

$stmt->execute();

do {
    if (
$stmt->store_result()) {
        
$stmt->bind_result($id_out);
        while (
$stmt->fetch()) {
            echo 
"id = $id_out\n";
        }
    }
} while (
$stmt->next_result());

上の例の出力は以下となります。

id = 1
id = 2
id = 3
id = 2
id = 3
id = 4

参照

関連キーワード:  array, セット, mysqli, 取得, query, string, int, OUT, プリペアドステートメント, ステートメント