Контакты

Достижения

Все достижения (4)

Наибольший вклад в теги

Все теги (26)

Лучшие ответы пользователя

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

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

    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)
    Ответ написан
    Комментировать

Лучшие вопросы пользователя

Все вопросы (46)