PHP: ストアドプロシージャ

ストアドプロシージャ

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

down

Valverde ¶

5 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);

}
?>

down

-10

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
    * )
    */
?>

add a note