PHP 8.0.24 Released!

ストアドプロシージャ

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 2 notes

up
5
Valverde
3 years ago
<?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);

}
?>
up
-8
paulo dot reis dot rosa at gmail dot com
5 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
    * )
    */
?>
To Top