PHP расширение mysqli - хранимые процедуры

• Обзор
• Асинхронные запросы
• Подготовленные выражения
• Хранимые процедуры
• Множественные запросы

Хранимая процедура — это SQL-процедура расположенная в базе данных. От функции она отличается тем, что:

1. процедура не возвращает значения, но может изменять свои параметры;
2. процедура может возвращать множество строк результата (но не через параметр, а в результате SELECT-запросов в теле процедуры).

Хранимые процедуры (и функции) могут быть полезны в следующих случаях:

1. Если есть какой-то набор действий, который нужно выполнять одинаково с разных клиентов. Да даже и на одном клиенте иногда надёжнее создать sql-программу. Например если при изменении счёта пользователя нужно также отредактировать поля «приход», «уход» и дополнительно сделать запись в соответствующую действию таблицу транзакций.
2. Если безопасность на первом месте. Вся работа с базой данных может быть организована через процедуры и функции, при этом доступ к таблицам БД может быть закрыт.

Для кода примеров я использовал следующую процедуру:

delimiter #
 
DROP PROCEDURE IF EXISTS EXEC#
 
CREATE PROCEDURE EXEC(OUT msg VARCHAR(50))
BEGIN
 
    SET msg = "zzz";
    SELECT * FROM `test` LIMIT 5;
 
END;#
 
delimiter ;

Вызов процедуры выглядит следующим образом:

$mysqli = new mysqli('host', 'user', 'pass', 'database');
$result = $mysqli->query('CALL exec(@msg)') OR
    exit('Error: (' . $mysqli->errno . ') ' . $mysqli->error);

@msg – это выходной параметр, который в результате выполнения процедуры получает значение «zzz». Выбор результата SELECT-запроса из процедуры происходит также, как и с обычным запросом:

while ($row = $result->fetch_assoc())
{
    print_r($row);
    echo '<br />';
}
 
$result->free();

Но чтобы выбрать результаты других SELECT-запросов из тела процедуры (а также выходных параметров) или выполнить какой-либо другой запрос (не связанный с процедурой), необходимо выбрать все результаты подготовленные БД:

$mysqli->next_result();
$result = $mysqli->store_result();

Теперь можно получить значение переменной:

$result = $mysqli->query('SELECT @msg') OR
    exit('Error: (' . $mysqli->errno . ') ' . $mysqli->error);
 
$row = $result->fetch_assoc();
 
$result->free();
$mysqli->close();

С хранимыми процедурами можно также использовать подготовленные выражения:

$mysqli = new mysqli('host', 'user', 'pass', 'database');
 
$stmt = $mysqli->prepare('CALL exec(@msg)') OR
    exit('Prepare failed: (' . $mysqli->errno . ') ' . $mysqli->error);
 
$stmt->execute() OR
    exit('Execute failed: (' . $mysqli->errno . ') ' . $mysqli->error);
 
while ($stmt->more_results())
{
    $result = $stmt->get_result();
 
    if (is_object($result))
    {
        while ($row = $result->fetch_assoc())
        {
            print_r($row);
            echo '<br />';
        }
 
        $result->free();
    }
 
    $stmt->next_result();
}
 
$result = $mysqli->query('SELECT @msg') OR
    exit('Error: (' . $mysqli->errno . ') ' . $mysqli->error);
 
$row = $result->fetch_assoc();
print_r($row);
echo '<br />';
 
$mysqli->close();

Можно даже привязывать результаты запросов:

while ($stmt->more_results())
{
    $col_a = $col_b = 0;
 
    $stmt->bind_result($col_a, $col_b);
 
    while ($stmt->fetch())
    {
        echo 'col_a = ' . $col_a . '; ';
        echo 'col_b = ' . $col_b . '<br />';
    }
 
    $stmt->next_result();
}

Но вот привязать перменную к параметру процедуры у меня не получилось ни к входному, ни к выходному.