Хранимые процедуры
СУБД 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()
Коментарии
<?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
* )
*/
?>
<?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);
}
?>