Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
Работа в 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].
|
Последнее изменение этой страницы: 2019-06-19; Просмотров: 777; Нарушение авторского права страницы