webew
Войти » Регистрация
 
MySQL/MariaDB :: хранимые процедуры

Использование PREPARE в хранимых процедурах

3 февраля 2008, 14:29

Часто возникает необходимость передавать имя таблицы или колонки в виде параметра процедуры. Однако напрямую это сделать нельзя. Если в качестве имени таблицы, столбца и т.п. использовать переменную (неважно локальную или входной параметр), то сервер при выполнении запроса использует имя переменной, а не её значение.

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

DROP PROCEDURE IF EXISTS count_unique;
delimiter//
CREATE PROCEDURE count_unique(table_name CHAR(64), column_name char(64))
BEGIN
SELECT count(DISTINCT column_name) FROM table_name;
END;
//

Все примеры будут выполняться на примере учебной базы world, которую Вы можете скачать с сайта MySQL.

Пусть мы хотим узнать количество округов. Выполнение нашей процедуры:
CALL count_unique('City', 'District')// Приведет к попытке посчитать уникальные значения столбца column_name из таблицы table_name, которой скорее всего не существует, что приведет к ошибке. Те данные, которые мы передали в качестве параметров, использованы не будут.

Преодолеть данное затруднение можно за счет использования пользовательских переменных и подготовленных выражений.

Пользовательские переменные (user variable).

Пользовательские переменные записываются как @var_name, действуют в течении сессии, не чувствительны к регистру.

Пользовательские переменные могут быть определены следующими способами:

  1. В выражениях SET с помощью операторов ' =' или ' := '
    test >set @v1 = 'one', @v2 := 'two';
    Query OK, 0 rows affected (0.00 sec)
    
    test >select @v1, @v2;
    +------+------+
    | @v1  | @v2  |
    +------+------+
    | one  | two  |
    +------+------+
    1 row in set (0.00 sec)
    
  2. В выражениях select с помощью оператора ' := '
    test >select @v1 := 55;
    +-----------+
    | @v1 := 55 |
    +-----------+
    |        55 |
    +-----------+
    
  3. С помощью выражений select .. from .. into (эквивалентно select .. into .. from)
    test >select col_int into @av from aa limit 1;
    Query OK, 1 row affected (0.00 sec)
    
    test >select @aV;
    +------+
    | @aV  |
    +------+
    | 1990 |
    +------+
    1 row in set (0.00 sec)
    

Если переменная не была определена явно, то принимает NULL значение.

test >select @x;
+------+
| @x   |
+------+
| NULL |
+------+

Пользовательской переменной может быть присвоен результат выполнения функции. Например:
world >SET @var = CONCAT('SELECT count(DISTINCT ', 'District', ') FROM ', 'City');
Query OK, 0 rows affected (0.00 sec)

world >SELECT @var;
+-------------------------------------------+
| @var                                      |
+-------------------------------------------+
| SELECT count(DISTINCT District) FROM City |
+-------------------------------------------+
1 row in set (0.00 sec)

Подготовленные выражения (prepared statement)

Подготовленные выражения (prepared statement) отличаются от обычных запросов тем, что не выполняются по завершению парсинга выражения, а хранятся на сервере. Выполняются в момент их вызова командой "EXECUTE имя_выражения" (возможен многократный вызов). Обычно применяются, когда необходимо выполнить ряд одинаковых (или незначительно отличающихся) запросов. Например, многократно выполняем запрос указывая различную временную величину. Вследствии того, что подготовленные выражения хранятся на сервере, они уменьшают трафик и время на парсинг (выражение разбирается один раз).

Существуют только в течении сессии. Если создается новое с уже существующим именем, старое автоматически удаляется (даже если новое содержит ошибку и не будет создано).

Имеет следующий синтаксис:
PREPARE имя_выражения_какое_хотите FROM какой_нибудь_запрос;
EXECUTE имя_выражения_какое_хотите;

Запрос можно вписать сразу, например:
world >PREPARE zxc FROM 'SELECT count(DISTINCT District) FROM City';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

world >EXECUTE zxc;
+--------------------------+
| count(DISTINCT District) |
+--------------------------+
|                     1367 |
+--------------------------+
1 row in set (0.03 sec)


Или сначала определить переменную, а потом приготовить выражение из нее:
world >SET @query = 'SELECT count(DISTINCT District) FROM City';
Query OK, 0 rows affected (0.00 sec)

world >PREPARE zxc FROM @query;
Query OK, 0 rows affected (0.38 sec)
Statement prepared

world >EXECUTE zxc;
+--------------------------+
| count(DISTINCT District) |
+--------------------------+
|                     1367 |
+--------------------------+
1 row in set (0.38 sec)

Возможность создать подготовленное выражение, используя переменную, позволяет программисту формировать запрос динамически.

Кроме того, можно выполнять запросы с различными параметрами, например
PREPARE имя_выражения FROM 'SELECT count(*) FROM table_name WHERE column1= ?';
SET @var = '54';
EXECUTE имя_выражения USING @var;
вернет количество строк в таблице table_name, у которых column1=54.

Применительно к нашему случаю решение будет выглядеть следующим образом:

DROP PROCEDURE IF EXISTS count_unique//
CREATE PROCEDURE count_unique(table_name CHAR(64), column_name CHAR(64))
BEGIN
SET @var = CONCAT('SELECT count(DISTINCT ', column_name, ') FROM ', table_name);
PREPARE zxc FROM @var;
EXECUTE zxc;
END;
//

world >call count_unique('City', 'District')//
+--------------------------+
| count(DISTINCT District) |
+--------------------------+
|                     1367 |
+--------------------------+
1 row in set (0.00 sec)

© Все права на данную статью принадлежат порталу webew.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.
Добавить комментарий
© 2008—2017 webew.ru, связаться: x собака webew.ru
Сайт использует Flede и соответствует стандартам WAI-WCAG 1.0 на уровне A.
Rambler's Top100

Реклама: