Работа в mySQL Workbench - Хранимые процедуры

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

Структура хранимой процедуры следующая:

 

DELIMITER //

CREATE PROCEDURE имя_процедуры [(параметры)]

#Код процедуры

//

Объявление переменных имеет вид

DECLARE имя_переменной тип_переменной [(длина)];

Блок операторов заключается в команды BEGIN … END

 

Оператор присвоения выглядит так:

SET переменная=значение;

Если нужно присвоить переменной результат команды SELECT, то используется следующий формат (многоточие означает стандартное продолжение команды):

SELECT имя_столбца INTO переменная FROM...;

Условный оператор имеет вид:

IF условие THEN

Оператор1 или Группа операторов1

[ELSE

Оператор2 или Группа операторов2]

END IF;

 

Есть несколько операторов цикла, самый распространенный из них:

WHILE условие DO

Оператор или Группа операторов

END WHILE;

 

Выражение CASE применяется для выбора на основании нескольких опций:

CASE выражение

WHEN вариант1 THEN выражение1

WHEN вариант2 THEN выражение2

…

ELSE выражениеN

END CASE;

Для удаления процедур используется команда:

 

DROP PROCEDURE IF EXISTS Имя_процедуры;

 

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

 

DELIMITER //

 

CREATE PROCEDURE show_contracts

(v_staff_name CHAR(50))

BEGIN

 

SELECT contract_num, contract_date, contract_type

FROM k_contract c JOIN k_staff s ON         

    c.k_staff_staff_num=s.staff_num

WHERE s.staff_name=v_staff_name;

 

END//

 

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

CALL show_contracts('Иванов');

 

 

или

CALL show_contracts('Петров');

 

 

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

 

DELIMITER //

CREATE PROCEDURE find_contracts_by_month_and_year

(v_month INT, v_year INT)

BEGIN

 

SELECT contract_num, contract_date, contract_type

FROM k_contract

WHERE MONTH(contract_date)=v_month AND    

     YEAR(contract_date)=v_year;

END//

 

Выполним процедуру:

 

CALL find_contracts_by_month_and_year(11, 2011);

 

 

Создадим процедуру «Распродажа», которая находит самый непродаваемый (по количеству) товар и уценивает его на заданный процент.

 

DELIMITER //

CREATE PROCEDURE clearance (percent INT)

BEGIN

DECLARE p INT;

IF percent > 0 AND percent < 100 THEN

    SELECT k_price_price_num INTO p FROM k_protokol

    GROUP BY k_price_price_num

        HAVING SUM(kolvo)< =ALL

              (SELECT SUM(kolvo) FROM k_protokol

                   GROUP BY k_price_price_num);                    

    UPDATE k_price

      SET price_sum=price_sum*(100-percent)/100

        WHERE price_num=p;

END IF;

END//

 

Содержимое таблицы " Прайс-лист" до выполнения процедуры:

 

 

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

 

CALL clearance(10);

 

Содержимое таблицы " Прайс-лист" после выполнения процедуры:

 

Как видим, товар с номером 4 в прайс-листе уценен на 10%.

 

А что произойдет, если в нашей базе данных есть несколько товаров, количество продаж которых минимально? К сожалению, в нашем случае при выполнении команды SELECT процедура выдаст ошибку: Error Code: 1172, Result consisted of more than one row. Когда в команде SELECT выбирается сразу несколько значений поля k_price из таблицы k_protokol, невозможно присвоить эти несколько значений одной переменной p. Данную ситуацию можно обработать с помощью так называемых курсоров.

 

Курсор ( cur rent s et of r ecord) – это временный набор строк, которые можно перебирать последовательно, с первой до последней.

Для работы с курсорами существуют следующие команды.

Объявление курсора:

DECLARE имя_курсора CURSOR FOR SELECT текст_запроса;

Таким образом, любой курсор создается на основе некоторого оператора SELECT.

Открытие курсора:

OPEN имя_курсора;

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

Чтение значений из текущей строки курсора в набор переменных и перемещение указателя на следующую строку:

FETCH имя_курсора INTO список_переменных;

Переменные в списке должны иметь то же количество и тип, что и столбцы курсора.

Закрытие курсора:

CLOSE имя_курсора;

При переборе строк курсора возникает необходимость проверки, добрались ли мы до конца курсора или еще нет. В разных СУБД для этого могут быть предусмотрены разные средства. В СУБД MySQL назначается обработчик состояния “NOT FOUND”. Определять его нужно сразу же после описания структуры курсора:

DECLARE CONTINUE HANDLER FOR NOT FOUND оператор;

Например, этот обработчик может выглядеть так:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Теперь попробуем модифицировать процедуру «Распродажа» с учетом того, что у нас может быть несколько товаров с минимальным количеством продаж.

 

DELIMITER //

 

CREATE PROCEDURE clearance2 (percent INT)

BEGIN

DECLARE p INT;

DECLARE finished NUMERIC(1);

–- объявляем курсор на основе некоторого оператора SELECT

DECLARE my_cursor CURSOR

FOR SELECT k_price_price_num FROM k_protokol

GROUP BY k_price_price_num

    HAVING SUM(kolvo)< =ALL

    (SELECT SUM(kolvo) FROM k_protokol

         GROUP BY k_price_price_num);       

-- объявляем обработчик состояния NOT FOUND

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

 

IF percent > 0 AND percent < 100 THEN

SET finished = 0;

OPEN my_cursor;          -- открываем курсор

FETCH my_cursor INTO p; -- читаем первую строку 

WHILE( finished! = 1) DO

   UPDATE k_price

      SET price_sum=price_sum*(100-percent)/100

      WHERE price_num=p;

   FETCH my_cursor INTO p; -- читаем очередную строку

END WHILE;

CLOSE my_cursor; -- закрываем курсор

END IF;

END//

Задание. Создайте несколько (не менее 2) хранимых процедур для вашей базы данных. Желательно использовать параметры. Запустите процедуры на выполнение. Более подробно о хранимых процедурах в MySQL можно прочитать в [2, Глава 33].

 

⇐ Предыдущая45678910111213Следующая ⇒

Поделиться: