<?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.
Параметры
Хранимые процедуры могут иметь параметры IN,
INOUT и OUT в зависимости от
версии MySQL. Интерфейс mysqli не делает различий между этими типами
параметров.
Параметр IN
Входные параметры указываются внутри предложения CALL.
При передаче входных параметров важно убедиться, что их значения корректно
экранированы.
Пример #1 Вызов хранимой процедуры
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Не удалось подключиться к MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)")) {
echo "Не удалось создать таблицу: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$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;")) {
echo "Не удалось создать хранимую процедуру: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$mysqli->query("CALL p(1)")) {
echo "Не удалось вызвать хранимую процедуру: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($res = $mysqli->query("SELECT id FROM test"))) {
echo "Запрос SELECT потерпел неудачу: (" . $mysqli->errno . ") " . $mysqli->error;
}
var_dump($res->fetch_assoc());
?>
Результат выполнения данного примера:
array(1) {
["id"]=>
string(1) "1"
}
Параметр INOUT/OUT
Значения параметров INOUT/OUT доступны
через переменные сессии.
Пример #2 Использование переменных сессии
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Не удалось подключиться к MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP PROCEDURE IF EXISTS p") ||
!$mysqli->query('CREATE PROCEDURE p(OUT msg VARCHAR(50)) BEGIN SELECT "Hi!" INTO msg; END;')) {
echo "Не удалось создать хранимую процедуру: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$mysqli->query("SET @msg = ''") || !$mysqli->query("CALL p(@msg)")) {
echo "Не удалось вызвать хранимую процедуру: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($res = $mysqli->query("SELECT @msg as _p_out"))) {
echo "Получить данные не удалось: (" . $mysqli->errno . ") " . $mysqli->error;
}
$row = $res->fetch_assoc();
echo $row['_p_out'];
?>
Результат выполнения данного примера:
Hi!
Разработчики приложений и фреймворков могут предоставить более удобный API, в котором наряду с сессионными переменными используется просмотр каталогов базы данных напрямую. Однако, стоит учитывать, что такой подход снижает быстродействие.
Обработка результирующих наборов
Хранимые процедуры могут возвращать результирующие наборы строк. Таблицы результатов работы хранимой процедуры нельзя корректно извлечь средствами mysqli_query(). Функция mysqli_query() выполняет две операции: запускает запрос и извлекает первый результирующий набор, помещая его в буфер. Хранимые процедуры могут возвращать более одного результирующего набора, но при использовании mysqli_query() все они, кроме первого, станут недоступны пользователю.
Результирующие таблицы хранимых процедур извлекаются функциями
mysqli_real_query() или
mysqli_multi_query(). Обе функции позволяют получить любое
количество результирующих наборов, возвращённых SQL-запросами, таких как
CALL. Если в процессе работы не удаётся извлечь все
доступные результаты вызова хранимой процедуры, будет вызываться ошибка.
Пример #3 Извлечение результатов работы хранимой процедуры
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Не удалось подключиться к MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT)") ||
!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
echo "Не удалось создать таблицу: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$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;')) {
echo "Не удалось создать хранимую процедуру: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$mysqli->multi_query("CALL p()")) {
echo "Не удалось вызвать хранимую процедуру: (" . $mysqli->errno . ") " . $mysqli->error;
}
do {
if ($res = $mysqli->store_result()) {
printf("---\n");
var_dump($res->fetch_all());
$res->free();
} else {
if ($mysqli->errno) {
echo "Не удалось получить результат на клиенте: (" . $mysqli->errno . ") " . $mysqli->error;
}
}
} while ($mysqli->more_results() && $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 поддерживают
подготовку в запросе SQL-выражения CALL.
Пример #4 Хранимые процедуры и подготавливаемые запросы
<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Не удалось подключиться к MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
!$mysqli->query("CREATE TABLE test(id INT)") ||
!$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)")) {
echo "Не удалось создать таблицу: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$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;')) {
echo "Не удалось создать хранимую процедуру: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!($stmt = $mysqli->prepare("CALL p()"))) {
echo "Не удалось подготовить запрос: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->execute()) {
echo "Не удалось выполнить запрос: (" . $stmt->errno . ") " . $stmt->error;
}
do {
if ($res = $stmt->get_result()) {
printf("---\n");
var_dump(mysqli_fetch_all($res));
mysqli_free_result($res);
} else {
if ($stmt->errno) {
echo "Не удалось получить результат на клиенте: (" . $stmt->errno . ") " . $stmt->error;
}
}
} while ($stmt->more_results() && $stmt->next_result());
?>
Само собой, поддерживается привязка результатов к объекту запроса.
Пример #5 Хранимые процедуры и подготавливаемые запросы с использованием привязки результатов
<?php
if (!($stmt = $mysqli->prepare("CALL p()"))) {
echo "Не удалось подготовить запрос: (" . $mysqli->errno . ") " . $mysqli->error;
}
if (!$stmt->execute()) {
echo "Не удалось выполнить запрос: (" . $stmt->errno . ") " . $stmt->error;
}
do {
$id_out = NULL;
if (!$stmt->bind_result($id_out)) {
echo "Не удалось связать результат с объектом запроса: (" . $stmt->errno . ") " . $stmt->error;
}
while ($stmt->fetch()) {
echo "id = $id_out\n";
}
} while ($stmt->more_results() && $stmt->next_result());
?>
Смотрите также
- mysqli::query()
- mysqli::multi_query()
- mysqli_result::next-result()
- mysqli_result::more-results()
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
}
}
}
?>
