Как сделать запрос в таблицу указанную как аргумент в хранимой процедуре?

Question

[Мурад Нурмагомедов]

Доброго дня!

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

CREATE PROCEDURE `changeStatus`(IN pid bigint, IN tbl_name char(10))
BEGIN
	SELECT status into result FROM tbl_name where id = pid; 
	select result;
END

Что делаю не так?

Answer 1

[Мурад Нурмагомедов]

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

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

Все примеры будут выполняться на примере учебной базы world, которую Вы можете скачать с сайта MySQL (dev.mysql.com/doc/)
Пусть мы хотим узнать количество округов. Выполнение нашей процедуры:
CALL my_proc('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 my_proc//
CREATE PROCEDURE my_proc(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 my_proc('City', 'District')//
+--------------------------+
| count(DISTINCT District) |
+--------------------------+
| 1367 |
+--------------------------+
1 row in set (0.00 sec)