Хранимые процедуры

СУБД MySQL поддерживает хранимые процедуры. Под этим термином понимается последовательность операций, хранящаяся как единое целое в каталоге базы данных на сервере. Приложения могут вызывать и запускать хранимые процедуры. Для запуска хранимой процедуры используется SQL выражение CALL.

Параметры

Хранимые процедуры могут иметь параметры IN, INOUT и OUT в зависимости от версии MySQL. Интерфейс 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(). Обе функции позволяют получить любое количество результирующих наборов, возвращённых SQL-запросами, таких как 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 поддерживают подготовку в запросе SQL-выражения 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)
  }
}

Само собой, поддерживается привязка результатов к объекту запроса.

Пример #5 Хранимые процедуры и подготавливаемые запросы с использованием привязки результатов

<?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

Смотрите также