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

СУБД 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

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

add a note add a note

User Contributed Notes 3 notes

up
7
Valverde
4 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);

}
?>
up
-5
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
    * )
    */
?>
up
-41
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
           
}
        }
    }
?>
To Top