<?php
// Store procedure call without params
$MyConnection = new mysqli ("DB_SERVER", "DB_USER", "DB_PASS", "DB_NAME");
mysqli_multi_query ($MyConnection, "CALL MyStoreProcedure") OR DIE (mysqli_error($MyConnection));
while (mysqli_more_results($MyConnection)) {
if ($result = mysqli_store_result($MyConnection)) {
while ($row = mysqli_fetch_assoc($result)) {
// i.e.: DBTableFieldName="userID"
echo "row = ".$row["DBTableFieldName"]."<br />";
....
}
mysqli_free_result($result);
}
mysqli_next_result($conn);
}
?>
*******************************************************************
<?php
// Store procedure call using params
$MyConnection = new mysqli ("DB_SERVER", "DB_USER", "DB_PASS", "DB_NAME");
mysqli_query($MyConnection ,"SET @p0='".$MyParam1."'");
mysqli_query($MyConnection ,"SET @p1='".$MyParam2."'");
mysqli_multi_query ($MyConnection, "CALL MyStoreProcedure (@p0,@p1)") OR DIE (mysqli_error($MyConnection));
while (mysqli_more_results($MyConnection)) {
if ($result = mysqli_store_result($MyConnection)) {
while ($row = mysqli_fetch_assoc($result)) {
// i.e.: DBTableFieldName="userID"
echo "row = ".$row["DBTableFieldName"]."<br />";
....
}
mysqli_free_result($result);
}
mysqli_next_result($conn);
}
?>
ストアドプロシージャ
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
参照
add a note
User Contributed Notes 3 notes
Valverde ¶
4 years ago
paulo dot reis dot rosa at gmail dot com ¶
7 years ago
<?php
/**
* Small function to facilitate call procedure with multiple arguments (supports in/inout/out)
*/
$db = new mysqli('localhost', 'root', 'password', 'database');
$lt_query = callProcedure(
$db,
"stored_procedure",
array(
"in_param1" => "Value1",
"in_param2" => "Value2",
"inout_param3" => "Value3",
"out_param4" => "",
"out_param5" => ""
));
function callProcedure( $po_db, $pv_proc, $pt_args )
{
if (empty($pv_proc) || empty($pt_args))
{
return false;
}
$lv_call = "CALL `$pv_proc`(";
$lv_select = "SELECT";
$lv_log = "";
foreach($pt_args as $lv_key=>$lv_value)
{
$lv_query = "SET @_$lv_key = '$lv_value'";
$lv_log .= $lv_query.";\n";
if (!$lv_result = $po_db->query($lv_query))
{
/* Write log */
return false;
}
$lv_call .= " @_$lv_key,";
$lv_select .= " @_$lv_key AS $lv_key,";
}
$lv_call = substr($lv_call, 0, -1).")";
$lv_select = substr($lv_select, 0, -1);
$lv_log .= $lv_call;
if ($lv_result = $po_db->query($lv_call))
{
if($lo_result = $po_db->query($lv_select))
{
$lt_result = $lo_result->fetch_assoc();
$lo_result->free();
return $lt_result;
}
/* Write log */
return false;
}
/* Write log */
return false;
}
/**
* This will return an array like this:
*
* $lt_query = array(
* 'in_param1' = 'Value1', // Same value as in call
* 'in_param2' = 'Value2', // Same value as in call
* 'inout_param3' = ?, // Value is changed accordingly
* 'out_param4' = ?, // Value is changed accordingly
* 'out_param5' = ? // Value is changed accordingly
* )
*/
?>
Jonathon ¶
10 years ago
<?php
/*
*
*Creating Session Variable with Prepared Statement
*
*for IN/OUT variable to stored procedure
*
*using Data Binding to get the data back
*
*@params
* $mysql: mysqli() instance
* $var: input and output paramater to stored procedure
*/
function stored_procedure($mysql , &$var = 'hello world')
{
$stmt = $mysql->stmt_init(); // initialize statement
if($stmt->prepare("SET @var = ?")) // prepare set command
{
$var = $mysql->real_escape_string($var); // escape input
$stmt->bind_param('s',$var); // bind data
$stmt->execute(); // create session variable
$stmt->free_result(); // free resutl
if($stmt->prepare("CALL stored_procedure(@var)"))//call stored procedure with database server session variable
{
$stmt->execute();
$stmt->free_result();
}
if($stmt->prepare(" SELECT @var AS var ")) // prepare command to select the database server session variable
{
$stmt->execute(); // execure
$out_var = NULL;
$stmt->bind_result($out_var); // bind
$stmt->fetch(); // fetch one row
echo $out_var.'<br />';// display OUT variable
}
}
}
?>
