ストアドプロシージャ
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'];
アプリケーションやフレームワークの開発者は、
セッションの値やデータベースカタログを調べることによって、
もっと便利な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
参照
- mysqli::query
- mysqli::multi_query
- mysqli::next_result
- mysqli::more_results