Vidokq: /* Хранимые процедуры */

2014-02-24T12:46:58Z

‎Хранимые процедуры

Vidokq: Новая страница: «== Работа в '''mySQL Workbench''' == === Хранимые процедуры === Хранимые процедуры – это объекты базы …»

2014-02-24T12:45:59Z

Новая страница: «== Работа в '''mySQL Workbench''' == === Хранимые процедуры === Хранимые процедуры – это объекты базы …»

Новая страница

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

<source lang="sql">DELIMITER //
CREATE PROCEDURE имя_процедуры [(параметры)]
#Код процедуры
//</source>

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

<source lang="sql">DECLARE имя_переменной тип_переменной [(длина)];</source>
Блок операторов заключается в команды '''BEGIN … END'''

Оператор присвоения выглядит так:
<source lang="sql">SET переменная=значение;</source>
Если нужно присвоить переменной результат команды SELECT, то используется следующий формат (многоточие означает стандартное продолжение команды):
<source lang="sql">SELECT имя_столбца INTO переменная FROM ...;</source>
Условный оператор имеет вид:
<source lang="sql">IF условие THEN
Оператор1 или Группа операторов1
[ELSE
Оператор2 или Группа операторов2]
END IF;</source>

Есть несколько операторов цикла, самый распространенный из них:
<source lang="sql">WHILE условие DO
Оператор или Группа операторов
END WHILE;</source>

Выражение CASE применяется для выбора на основании нескольких опций:
<source lang="sql">CASE выражение
WHEN вариант1 THEN выражение1
WHEN вариант2 THEN выражение2
…
ELSE выражениеN
END CASE;</source>
Для удаления процедур используется команда:

<source lang="sql">DROP PROCEDURE IF EXISTS Имя_процедуры;</source>

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

<source lang="sql">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//</source>

Для запуска этой процедуры нужно выполнить, например, команду
<source lang="sql">CALL show_contracts('Иванов');</source>
или
<source lang="sql">CALL show_contracts('Петров');</source>

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

<source lang="sql">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//</source>

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

<source lang="sql">CALL find_contracts_by_month_and_year(11,2011);</source>

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

<source lang="sql">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//</source>

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

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

<source lang="sql">CALL clearance(10);</source>

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

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

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

*{{V|24px}}Курсор (current set of record) – это временный набор строк, которые можно перебирать последовательно, с первой до последней.
Для работы с курсорами существуют следующие команды.

Объявление курсора:
<source lang="sql">DECLARE имя_курсора CURSOR FOR SELECT текст_запроса;</source>
Таким образом, любой курсор создается на основе некоторого оператора SELECT.
Открытие курсора:
<source lang="sql">OPEN имя_курсора;</source>
Только после открытия курсора он становится активным, и из него можно читать строки.

Чтение значений из следующей строки курсора в набор переменных:
<source lang="sql">FETCH имя_курсора INTO список_переменных;</source>
Переменные в списке должны иметь то же количество и тип, что и столбцы курсора.

Закрытие курсора:
<source lang="sql">CLOSE имя_курсора;</source>
При переборе строк курсора возникает необходимость проверки, добрались ли мы до конца курсора или еще нет. В разных СУБД для этого могут быть предусмотрены разные средства. В СУБД MySQL назначается обработчик состояния “NOT FOUND”. Определять его нужно сразу же после описания структуры курсора:
<source lang="sql">DECLARE CONTINUE HANDLER FOR NOT FOUND оператор;</source>
Например, этот обработчик может выглядеть так:
<source lang="sql">DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;</source>
Теперь попробуем модифицировать процедуру «Распродажа» с учетом того, что у нас может быть несколько товаров с минимальным количеством продаж.

<source lang="sql">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//
</source>

@@ Строка 109: / Строка 109: @@
 А что произойдет, если в нашей базе данных есть несколько товаров, количество продаж которых минимально? К сожалению, в нашем случае процедура выдаст ошибку: Error Code: 1172, Result consisted of more than one row. Когда в команде SELECT выбирается сразу несколько значений поля k_price из таблицы k_protokol, невозможно присвоить эти несколько значений одной переменной p. Данную ситуацию можно обработать с помощью так называемых курсоров.
+=== Курсоры ===
 *{{V|24px}}Курсор (current set of record) – это временный набор строк, которые можно перебирать последовательно, с первой до последней.
 Для работы с курсорами существуют следующие команды.

СУБД/Практические занятия №1(2 семестр)help - История изменений

Vidokq: /* Хранимые процедуры */

Vidokq: Новая страница: «== Работа в '''mySQL Workbench''' == === Хранимые процедуры === Хранимые процедуры – это объекты базы …»