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

Ссылки сюда (1) →

Работа в 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%.

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

Курсоры

• Курсор (current set of record) – это временный набор строк, которые можно перебирать последовательно, с первой до последней.

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

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

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//