Руководство для начинающих по хранению процедур с MySQL?

Ответ 1

Считайте это нежным введением в хранимые процедуры в MySQL: http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

Вы можете создавать/управлять хранимыми процедурами в phpMyAdmin.

Ответ 2

Введение

"Хранимая процедура - это набор операторов SQL, которые могут храниться на сервере.

Хранимая процедура - это сегмент декларативных операторов SQL, хранящихся в каталоге базы данных. Хранимая процедура может быть вызывается триггерами, другими хранимыми процедурами и такими приложениями, как Java, Python, PHP, и т.д.

Хранимая процедура - это метод инкапсуляции повторяющихся задач. Они допускают объявления переменных, управление потоком и другие полезные методы программирования.

Pros

• Поделитесь логикой с другими приложениями.

• Хранимые процедуры инкапсулируют функциональность; это гарантирует, что доступ к данным и их манипулирование согласованы между различными приложениями.

• Изолировать пользователей из таблиц данных. Это дает вам возможность предоставить доступ к хранимым процедурам, которые управляют данными, но не напрямую к таблицам.

• Предоставить механизм безопасности. Учитывая предыдущий элемент, если вы можете получить доступ только к данным, используя указанные хранимые процедуры, никто другой не может выполнить инструкцию DELETE SQL и удалить ваши данные.

• Повысить производительность, поскольку это снижает сетевой трафик. С помощью хранимой процедуры несколько вызовов могут быть объединены в один.

против

• Увеличенная нагрузка на сервер базы данных - большая часть работы выполняется на стороне сервера и меньше на стороне клиента.
• Там есть приличная кривая обучения. Вам нужно изучить синтаксис операторов MySQL для записи хранимых процедур.
• Вы повторяете логику своего приложения в двух разных местах:
• код вашего сервера и код хранимых процедур, что затрудняет работу. Перенос на другую систему управления базами данных (DB2, SQL Server и т.д.) Может быть труднее.

Шаг 1 - Выбор разделителя

Разделитель - это символ или строка символов, которые вы будете использовать, чтобы сообщить клиенту MySQL, что вы закончили вводить текст в инструкции SQL. На протяжении веков разделитель всегда был точкой с запятой. Это, однако, вызывает проблемы, потому что в хранимой процедуре может быть много операторов, и каждый должен заканчивать точкой с запятой. Я использую "//"

Шаг 2 - Как работать со хранимой процедурой

Создание хранимой процедуры

DELIMITER //

CREATE PROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A procedure'
BEGIN
    SELECT 'Hello World !';
END//

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

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

Четыре характеристики процедуры:

Язык:

В целях мобильности; значением по умолчанию является SQL.

Детерминированные:

Если процедура всегда возвращает те же результаты, учитывая тот же ввод. Это предназначено для репликации и ведения журнала. Значение по умолчанию НЕ ДЕТЕРМИНИСТИЧЕСКОЕ.

Безопасность SQL:

Во время разговора проверьте привилегии пользователя. INVOKER - пользователь, который вызывает процедуру. DEFINER является создателем процедуры. Значение по умолчанию - DEFINER.

Комментарий:

В целях документации; значением по умолчанию является ""

Вызов сохраненной процедуры

Чтобы вызвать процедуру, вам нужно только ввести слово CALL, затем имя процедуры, а затем круглые скобки, включая все параметры между ними (переменные или значения). Скобки обязательны.

CALL stored_procedure_name (param1, param2, ....)

CALL procedure1(10 , 'string parameter' , @parameter_var);

Изменить сохраненную процедуру

MySQL предоставляет инструкцию ALTER PROCEDURE, чтобы изменить процедуру, но только позволяет изменять некоторые характеристики. Если вам нужно изменить тело или параметры, вы должны сбросить и воссоздать процедуру.

Удалить сохраненную процедуру

DROP PROCEDURE IF EXISTS p2;

Это простая команда. Предложение IF EXISTS предотвращает ошибку в случае, если процедура не существует.

Шаг 3 - Параметры

Давайте рассмотрим, как вы можете определить параметры в хранимой процедуре.

- CREATE PROCEDURE proc1 (): список параметров пуст  - CREATE PROCEDURE proc1 (IN varname DATA-TYPE): Один входной параметр. Слово IN является необязательным, поскольку по умолчанию параметры IN (ввод).  - CREATE PROCEDURE proc1 (OUT varname DATA-TYPE) : Один выходной параметр.  - CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE) : Один параметр, который является как входным, так и выходным. Конечно, вы можете определить несколько параметров, определенных разными типами.

В примере

DELIMITER //

CREATE PROCEDURE `proc_IN` (IN var1 INT)
BEGIN
    SELECT var1 + 2 AS result;
END//

Пример INOUT

DELIMITER //

CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
BEGIN
    SET var1 = var1 * 2;
END //

Шаг 4 - Переменные

Следующий шаг научит вас определять переменные и хранить значения внутри процедуры. Вы должны объявить их явно в начале блока BEGIN/END вместе со своими типами данных. После того, как вы объявили переменную, вы можете использовать ее в любом месте, где вы могли бы использовать переменную сеанса или литерал или имя столбца.

Объявить переменную, используя следующий синтаксис:

DECLARE varname DATA-TYPE DEFAULT значение по умолчанию; Пусть объявить несколько переменных:

DECLARE a, b INT DEFAULT 5;

DECLARE str VARCHAR(50);

DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;

DECLARE v1, v2, v3 TINYINT;

Работа с переменными

Как только переменные были объявлены, вы можете назначить их значения с помощью команды SET или SELECT:

Шаг 5 - Структуры управления потоком

MySQL поддерживает конструкцию IF, CASE, ITERATE, LEAVE LOOP, WHILE and REPEAT для управления потоком в хранимых программах. Мы рассмотрим, как использовать IF, CASE и WHILE в частности, поскольку они являются наиболее часто используемыми операторами в подпрограммах.

оператор IF С помощью оператора IF мы можем обрабатывать задачи, которые включают в себя условия:

DELIMITER //

CREATE PROCEDURE `proc_IF` (IN param1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = param1 + 1;

    IF variable1 = 0 THEN
        SELECT variable1;
    END IF;

    IF param1 = 0 THEN
        SELECT 'Parameter value = 0';
    ELSE
        SELECT 'Parameter value <> 0';
    END IF;
END //

Оператор CASE Оператор CASE является еще одним способом проверки условий и выбора соответствующего пути. Это отличный способ заменить несколько операторов IF. Оператор может быть написан двумя разными способами, обеспечивая большую гибкость при работе с несколькими условиями.

DELIMITER //

CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = param1 + 1;

    CASE variable1
        WHEN 0 THEN
            INSERT INTO table1 VALUES (param1);
        WHEN 1 THEN
            INSERT INTO table1 VALUES (variable1); 
        ELSE
            INSERT INTO table1 VALUES (99);
    END CASE;

END //

ИЛИ

DELIMITER //

CREATE PROCEDURE `proc_CASE` (IN param1 INT)
BEGIN
    DECLARE variable1 INT;
    SET variable1 = param1 + 1;

    CASE
        WHEN variable1 = 0 THEN
            INSERT INTO table1 VALUES (param1);
        WHEN variable1 = 1 THEN
            INSERT INTO table1 VALUES (variable1); 
        ELSE
            INSERT INTO table1 VALUES (99);
    END CASE;

END //

WHILE statement

Существует технически три стандартных цикла: WHILE loops, LOOP loops, and REPEAT loops. У вас также есть возможность создать цикл с использованием метода Дарта Вейдера: GOTO statement. Проверьте этот пример цикла в действии:

DELIMITER //

CREATE PROCEDURE `proc_WHILE` (IN param1 INT)
BEGIN
    DECLARE variable1, variable2 INT;
    SET variable1 = 0;

    WHILE variable1 < param1 DO
        INSERT INTO table1 VALUES (param1);
        SELECT COUNT(*) INTO variable2 FROM table1;
        SET variable1 = variable1 + 1;
    END WHILE;
END //

Шаг 6 - Курсоры

Cursor используется для итерации по набору строк, возвращаемых запросом, и обработке каждой строки.

MySQL поддерживает курсор в хранимых процедурах. Вот краткое описание основного синтаксиса для создания и использования курсора.

- DECLARE cursor-name CURSOR FOR SELECT...;/* Объявление и заполнение курсора оператором SELECT */

• DECLARE CONTINUE HANDLER FOR NOT FOUND/Укажите, что делать, если больше записей не найдено /

OPEN cursor-name;/Открыть курсор для использования /

FETCH cursor-name INTO variable [, variable];/Назначить переменные с текущими значениями столбца /

CLOSE cursor-name;/Закрыть курсор после использования /

В этом примере я буду выполнять некоторые простые операции с помощью курсора:

DELIMITER //

CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)
BEGIN
    DECLARE a, b, c INT;
    DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
    OPEN cur1;

    SET b = 0;
    SET c = 0;

    WHILE b = 0 DO
        FETCH cur1 INTO a;
        IF b = 0 THEN
            SET c = c + a;
    END IF;  
    END WHILE;

    CLOSE cur1;
    SET param1 = c;

END //

Курсор имеет три важных свойства, которые вам нужно знать, чтобы избежать неожиданных результатов:

• Asensitive: После открытия курсор не будет отражать изменения в его исходных таблицах. Фактически, MySQL не гарантирует, что курсор будет обновлен, поэтому вы не можете полагаться на него.

• Только для чтения: Курсоры не могут обновляться.

• Не прокручивается: Курсоры могут перемещаться только в одном направлении, вперед, и вы не можете пропускать записи из выборки

Надеюсь, что это полезно