Разработка хранимых процедур и функций на сервере MySQL

Введение

Хранимые процедуры и функции являются важнейшим элементом современных промышленных СУБД. В MySQL хранимые процедуры поддерживаются начиная с версии 5. Хранимые процедуры и функции (а также триггеры как разновидность процедур) реализуются в виде подпрограмм. При этом процедуры и функции называются хранимыми т.к. они хранятся в базе данных наряду с другими объектами (таблицами, ограничениями и т.д.). Хранимые подпрограммы представляют собой набор команд SQL. Кроме операторов SQL в хранимой подпрограмме могут быть использованы основные элементы свойственные любому языку программирования – переменные, операторы ветвления, циклы и др.

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

Запуск утилиты mysql на примере OpenServer

-Определяем конфигурацию OpenServer и выбираем сервер баз данных MysqL-5.1

-Нажимаем сохранить и запускаем OpenServer.

-Далее запуска консоль:

-В консоли вводим команду

mysql -h localhost -u root -p

-Пользователь root по умолчанию, либо созданный пользователь. Далее вводим пароль пользователя root, пароль root.

После успешной авторизации появиться сообщение:

Теперь переходим к созданию тестовой базы данных create database test; и указываем ее активной use test;

-Переходим к созданию процедур сервера баз данных

Создание, запуск и удаление простой процедуры

Для создания простой процедуры в клиенте mysql.exe можно выполнить следующие операторы:

DELIMITER //
CREATE PROCEDURE Hello_World()
BEGIN
SELECT('Hello, world!');
END
//

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

Консольный оператор DELIMITER переопределяет строку, по которой MySQL определяет завершение команды. Задав DELIMITER равным //, мы получаем возможность использовать точку с запятой внутри кода например создание процедуры.
Делимитер позволяет передать на сервер разделитель ; , указанный в теле процедуры, тем самым освобождая mysql от самостоятельной интерпретации данного разделителя.
пример:
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT paraml INT)
-> BEGIN
-> SELECT COUNT(*) INTO paraml FROM t;
-> END
-> // Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

то есть Указав Delimiter // mysql не будет воспринимать ; ( точку с запятой) как окончание sql запроса и не будет пытаться выполнить его.
P/S/ если используешь делимитер нужно не забывать переопределять его назад к стандартному ; как указано в примере.

Во второй строке следует оператор создания процедуры CREATE PROCEDURE, после которого указывается имя процедуры. Создаваемая процедура не имеет параметров, поэтому скобки после имени процедуры – пустые.

Далее следует конструкция BEGIN .. END, в которую должны быть заключены все операторы процедуры. Операторы разделяются символом точка с запятой.

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

CALL Hello_World;

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

DROP PROCEDURE Hello_World;

Создание, запуск и удаление простой функции

Для создания простой процедуры в клиенте mysql.exe можно выполнить следующие операторы:

DELIMITER //

CREATE FUNCTION Hello_World()

RETURNS VARCHAR(20)

BEGIN

RETURN ‘Hello, world!’;

END

//

Для запуска функции необходимо ввести

SELECT Hello_World()//

Простые и системные переменные

Объявить переменную в хранимой подпрограмме можно в любом месте тела подпрограммы (внутри блока BEGIN .. END). Синтаксис оператора объявления переменной:

DECLARE имя [, имя] … тип_данных [DEFAULT значение]

Объявить переменную в хранимой подпрограмме можно в любом месте тела подпрограммы. Если параметр DEFAULT отсутствует, то переменная инициализируется со значением NULL.

Для присвоения значения переменной может быть использован оператор SET. В следующем примере переменной S присваивается текстовое значение, которое затем выводится на экран:

CREATE PROCEDURE Hello_World()

BEGIN

DECLARE S VARCHAR(20);

SET S=‘Hello, world!’;

SELECT(S);

END

//

Результат вызова процедуры на выполнение:

Иногда бывает необходимо присвоить переменной значение, возвращаемое в результате запроса. Это можно сделать при помощи оператора SELECT..INTO. При этом запрос должен возвращать только одну строку. Если запрос возвращает пустой результат, это приведет к ошибке 1329 (No data). Если запрос содержит более одной строки, это приведет к ошибке 1172 (Result consisted of more than one row). Количество строк, возвращаемых запросом, можно ограничить опцией LIMIT оператора SELECT. Данная опция имеет два параметра. Первый параметр указывает смещение возвращаемого набора строк относительно начала, второй – количество возвращаемых строк. При использовании опции только с одним параметром он интерпретируется как количество возвращаемых строк от начала результата. Таким образом, совместно с оператором SELECT..INTO можно использовать опцию LIMIT 1. Следующая процедура выводит наименование самой тяжелой детали:

CREATE PROCEDURE Heavy()

BEGIN

DECLARE S VARCHAR(20);

SELECT weight INTO S FROM Parts ORDER BY Weight DESC LIMIT 1;

SELECT(S);

END

//

В процессе выполнения оператора SELECT..INTO выполняется неявное приведение типа возвращаемого запросом значения типу переменной:

CREATE PROCEDURE Parts_count()

BEGIN

DECLARE S VARCHAR(20);

SELECT count(*) INTO S FROM Parts;

SELECT(S);

END

//

Разница между простыми и системными переменными в том, что системные переменные доступны извне хранимой процедуры. Системную переменную не нужно инициализировать. Разница в простой и системной переменной пользовании префикса @ в имени системной переменной.

SET @S=‘Hello, world!’;

Значение системной переменной можно узнать после выполнения хранимой процедуры:

CREATE PROCEDURE Parts_count()

BEGIN

SELECT count(*) INTO @S FROM Parts;

END

//

Параметры процедур и функций

Параметры процедуры или функции указываются в операторе CREATE после имени в скобках. Если параметров нет, то необходимо указывать пустые скобки. Параметры бывают типов IN, OUT INOUT (см. таблицу).

Режим

Предназначение

Использование параметра

IN

Только для чтения

Значение параметра может применяться, но не может быть изменено в модуле

OUT

Только для записи

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

IN OUT

Для чтения и записи

В модуле можно использовать и изменять значение параметра

Пример процедуры, в которой используется один параметр IN и один параметр OUT:

CREATE PROCEDURE Parts_count(IN Mat VARCHAR(20),OUT PNum INT)

BEGIN

SELECT count(*) INTO PNum FROM Parts WHERE Material=Mat ;

END

//

CALL Parts_count(‘Rubber’,@S)//

Пример функции с параметром:

CREATE FUNCTION Hello_World(S VARCHAR(20))

RETURNS VARCHAR(20)

BEGIN

RETURN CONCAT(‘Hello ’,S,’!!!’);

END

//

Для запуска функции необходимо ввести

SELECT Hello_World(‘Root’)//

Операторы управления ходом выполнения программы

Условный оператор IF..THEN имеет следующий синтаксис:

IF <условие> THEN <оператор 1>

[ELSEIF <условие> THEN <оператор 2>] …

[ELSE <оператор 3>]

END IF

Оператор CASE имеет следующий синтаксис:

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list] …

[ELSE statement_list]

END CASE

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

[Метка_начала:] LOOP

<оператор>

END LOOP [Метка_конца]

Операторы цикла выполняются до тех пор, пока внутри цикла не будет выполнен оператор LEAVE < Метка_начала >, который прерывает цикл с указанной меткой.

Оператор цикла с постусловием имеет следующий синтаксис:

REPEAT

<оператор>

UNTIL <условие_выхода>

END REPEAT

Цикл будет выполняться до тех пор, пока условие выхода не станет истинным.

Оператор цикла с предусловием имеет следующий синтаксис:

WHILE <условие_выполнения> DO

<оператор>

END WHILE

Цикл будет выполняться до тех пор, пока условие выполнения будет истинным.

Курсоры

Курсор представляет собой временную таблицу, получаемую в результате запроса, которая служит для построчной обработки данных. Курсор позволяет в цикле «перебирать» строки, выполняя над ними необходимые действия. Таки образом, курсор является таблицей, возможности которой выходят за рамки классической реляционной модели (в классическом отношении отсутствует понятие порядка следования строк – строки представляют собой множество).

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

DECLARE <имя_курсора> CURSOR FOR <SQL-выражение>;

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

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

После открытия указатель курсора устанавливается на первую строку. Для доступа к текущей строке открытого курсора используется оператор:

FETCH <имя_курсора> INTO <имя_переменной> [,<имя_переменной>] …

Этот оператор помещает значения строки курсора в переменные, количество и типы данных которых соответствуют схеме (столбцам) курсора. После выполнения оператора FETCH происходит автоматическое продвижение на следующую строку курсора. Если более нет доступных строк (достигнута последняя строка) происходит изменение значения переменной SQLSTATE в 02000. Для обработки этого события необходимо установить обработчик: HANDLER FOR SQLSTATE ‘02000’.

Следующий оператор закрывает курсор:

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

Если оператор закрытия курсора не указан явно, то курсор закрывается автоматически при закрытии соответствующего блока подпрограммы.

Для примера создадим процедуру, которая изменяет имя всех деталей с определенным именем на имя, формируемое как «Имя-N», где N – порядковый номер в списке всех деталей «Gasket» в порядке возрастания веса детали. Имя детали передается в качестве параметра.

CREATE PROCEDURE Parts_rename(PName VARCHAR(20))

BEGIN

DECLARE Done INT DEFAULT 0;

DECLARE S VARCHAR(20);

DECLARE N,I INTEGER;

DECLARE Cur1 CURSOR FOR SELECT Part_ID, Part_name FROM Parts WHERE Part_name=PName ORDER BY WEIGHT;

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = 1;

OPEN Cur1;

SET I=1;

REPEAT

FETCH Cur1 INTO N,S;

IF Done=0 THEN UPDATE Parts SET Part_name=CONCAT(S,’-’,I) WHERE Part_ID=N;

END IF;

SET I=I+1;

UNTIL Done END REPEAT;

CLOSE Cur1;

END

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

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

Задание на лабораторную работу

  1. Для БД «Предприятие» реализовать и проверить работу следующих процедур и функций:
  2. Реализовать функцию, которая возвращает количество различных городов, в которых находятся поставщики определенного проекта (входной параметр).
  3. Реализовать функцию, которая возвращает количество различных городов, в которых находятся проекты, обеспечиваемые определенным поставщиком (входной параметр).
  4. Реализовать функцию, которая возвращает количество различных городов, в которых находятся поставщики определенной детали (входной параметр).
  5. Реализовать процедуру, которая выводит информацию обо всех поставщиках и количестве различных городов, в которых находятся обслуживаемые ими проекты. Примечание: использовать функцию, реализованную в п. 1.b.
  6. Реализовать процедуру, которая выводит информацию обо всех проектах и количестве различных городов, в которых находятся обслуживающие их поставщики. Примечание: использовать функцию, реализованную в п. 1.a.
  7. Написать процедуру, которая изменяет имя каждой детали на имя, формируемое как «Имя_детали-N», где N – порядковый номер в списке всех одноименных деталей в порядке возрастания веса детали. Примечание: использовать вызов вышеописанной процедуры Parts_rename.

Содержание отчета

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

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

CREATE FUNCTION Qty_of_Prj_Towns(S VARCHAR(20))

RETURNS INTEGER

BEGIN

DECLARE N INTEGER;

SELECT COUNT(*) INTO N FROM Towns WHERE Town_ID IN (SELECT Projects.Town_ID FROM Parts, Projects, Supply WHERE Parts.Part_ID= Supply.Part_ID AND Projects.Project_ID=Supply.Project_ID AND Parts.Part_name=S);

RETURN N;

END

Исключения и триггеры

Исключения

Исключения, или исключительные ситуации – это ошибки, которые возникают в процессе выполнения инструкций SQL. MySQL перехватывает ошибки и реагирует на них при помощи т.н. обработчиков (handlers). Механизм функционирования обработчиков ис­ключений позволяет четко отделить код обработки ошибок от исполняемых опера­торов.

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

Существует два вида исключений:

Системное исключение. Определяется в MySQL и обычно инициируется ядром СУБД, обнаружившим ошибку.

Пользовательское исключение. Определяется программистом, следовательно оно специфично для данного приложения.

Системные исключения возникают при попытке выполнения недопустимых SQL-инструкций. О возникновении исключения можно узнать из сообщения клиента mysql.exe. Например, при попытке выбрать данные из несуществующей таблицы произойдет следующее:

Информация о произошедшем исключении стоит из трех элементов:

1. Число – код ошибки (1146). Этот код определен в MySQL и несовместим с другими СУБД.

2. Пятисимвольный код системной переменной SQLSTATE (42S02). Эти значения определяются в стандартах ANSI SQL и ODBC и стандартизированы в большей степени.

3. Текстовое описание ошибки (Table ‘database1.no_such_table’ doesn’t exist).

При создании обработчика исключения можно использовать различные способы идентификации исключения. Синтаксис оператора создания исключения:

DECLARE <тип_обработчика> HANDLER

FOR <идентификатор_исключения> [,<идентификатор_исключения>] …

<операторы>;

Здесь параметр <тип_обработчика> может принимать значения CONTINUE, EXIT или UNDO. Для обработчиков типа CONTINUE выполнение программы, вызвавшей исключение, продолжается после выполнения кода обработчика. Для обработчиков типа EXIT выполнение текущего блока BEGIN…END программы, вызвавшей исключение, прерывается. Обработчики типа UNDO не поддерживаются в версии MySQL 5.5.

Параметр <идентификатор_исключения> может представлять собой значение SQLSTATE, имя исключения, которое было задано при его создании оператором DECLARE … CONDITION, а также одно из ключевых слов:

SQLWARNING – синоним для значений SQLSTATE, начинающихся с 01

NOT FOUND — синоним для значений SQLSTATE, начинающихся с 02

SQLEXCEPTION — синоним для значений SQLSTATE, НЕ начинающихся с 00, 01 или 02

Например, создадим процедуру, в которой определим обработчик для исключения с кодом 42S02:

CREATE PROCEDURE h()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLSTATE ’42S02′ SELECT (‘Таблица не существует!’);

SELECT * FROM No_such_table;

END;

Вариант процедуры с предварительно заданным именем исключения при помощи DECLARE … CONDITION:

CREATE PROCEDURE h()

BEGIN

DECLARE No_my_table CONDITION FOR SQLSTATE ’42S02′;

DECLARE CONTINUE HANDLER FOR No_my_table SELECT (‘Таблица не существует!’);

SELECT * FROM No_such_table;

END;

Вариант процедуры с использованием синонима SQLEXCEPTION для идентификации исключения:

CREATE PROCEDURE h()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT (‘Таблица не существует!’);

SELECT * FROM No_such_table;

END;

Следующий пример демонстрирует продолжение выполнения программы после обработчика типа CONTINUE:

CREATE PROCEDURE h()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT (‘Таблица не существует!’);

SELECT * FROM No_such_table;

SET @A=1;

END;

Если использовать обработчик типа EXIT, то выполнение программы прерывается:

CREATE PROCEDURE h()

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT (‘Таблица не существует!’);

SELECT * FROM No_such_table;

SET @A=1;

END;

Следующая процедура содержит обработчик исключения 23000 (ER_DUP_KEY)

CREATE PROCEDURE h()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ SELECT ‘Первичный ключ уже существует’;

INSERT INTO Parts VALUES (9,’Spoon’,’Wood’,30);

INSERT INTO Parts VALUES (9,’Spoon’,’Wood’,30);

END;

Оператор SIGNAL

Оператор SIGNAL позволяет вызвать пользовательское исключение и сообщить обработчику или внешней программе информацию о произошедшей ошибке. Синтаксист оператора SIGNAL:

SIGNAL <идентификатор_исключения>

[SET <параметр> = <значение>, [<параметр> = <значение>]…]

Здесь параметр <идентификатор_исключения> может представлять собой значение SQLSTATE или имя исключения, которое было задано при его создании оператором DECLARE … CONDITION. Значение SQLSTATE не должно начинаться с цифр ‘00’. Для пользовательского исключения следует использовать значение SQLSTATE 45000.

Оператор SIGNAL может включать опцию SET, после которой следуют пары <параметр>=<значение>, разделенные запятыми.

Пример:

CREATE PROCEDURE p (val INT)

BEGIN

DECLARE my_error CONDITION FOR SQLSTATE ‘45000’;

DECLARE EXIT HANDLER FOR my_error SELECT(‘Произошла ошибка!’);

IF val = 0 THEN

SIGNAL my_error;

END IF;

END;

Пример с использованием параметра MESSAGE_TEXT:

CREATE PROCEDURE p (val INT)

BEGIN

DECLARE my_error CONDITION FOR SQLSTATE ‘45000’;

IF val = 0 THEN

SIGNAL my_error SET MESSAGE_TEXT = ‘Произошла ошибка!’;

END IF;

END;

Триггеры

Триггеры — это особые хранимые процедуры, выполняемые в ответ на происхо­дящие в базе данных события. Они относятся к числу наиболее важных элемен­тов промышленных приложений базы данных. Основным назначением триггеров является поддержка ограничений целостности, которые не реализуются при помощи внешних ключей и ограничений, накладываемых на значение столбца (NOT NULL, CHEK и т.д.).

Триггеры уровня инструкций языка манипулирования данными (триггеры DML) запускаются после вставки, обновления или удаления строки конкретной табли­цы. Это наиболее распространенный тип триггеров, особенно часто применяемый разработчиками.

Триггер BEFORE. Вызывается до внесения каких-либо изменений, в том чис­ле до вставки записи (BEFORE INSERT).

Триггер AFTER. Выполняется после того, как производятся все изменения, в частности после операции вставки записи (AFTER INSERT).

Существуют, также, следующие виды триггеров:

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

Триггер уровня записи. Вызывается для отдельной записи, обрабатываемой SQL-инструкцией. Если, предположим, таблица books содержит 1000 строк, то следующая инструкция UPDATE модифицирует все эти строки: UPDATE books SET title = UPPER (title); И если для данной таблицы определен триггер уровня записи, он будет запу­щен 1000 раз.

Псевдозапись NEW. Структура данных с именем NEW, которая так же выгля­дит и обладает такими же свойствами, как и запись таблицы. Эта псевдозапись доступна только внутри триггеров обновления и вставки; она содержит значе­ния модифицированной записи после внесения изменений.

Псевдозапись OLD. Структура данных с именем OLD, которая так же выгля­дит и обладает такими же свойствами, как и запись таблицы. Эта псевдозапись Доступна только внутри триггеров обновления и удаления; она содержит зна­чения модифицируемой записи до внесения изменений.

Синтаксис оператора создания триггера:

CREATE TRIGGER <имя_триггера> {BEFORE | AFTER} <событие_БД>

ON <имя_таблицы> FOR EACH ROW

BEGIN

<операторы>

END

<событие_БД> — определение типа DML-инструкции, с которой связывается триггер: INSERT, UPDATE или DELETE. У каждой таблицы для каждого события может существовать только один триггер.

Триггер, осуществляющий проверку веса детали при добавлении ее в таблицу Parts (вес не должен превышать заданного значения)

CREATE TRIGGER Check_Weight BEFORE INSERT ON Parts FOR EACH ROW

BEGIN

DECLARE Wrong_weight CONDITION FOR SQLSTATE ‘45000’;

IF NEW.Weight > 1000 THEN

SIGNAL Wrong_weight SET MESSAGE_TEXT = ‘Вес детали превышает 1000!’;

END IF;

END

Триггер, осуществляющий проверку на совпадение наименований деталей:

CREATE TRIGGER Check_Part_Name BEFORE INSERT ON Parts FOR EACH ROW

BEGIN

DECLARE Duplicate_part_name CONDITION FOR SQLSTATE ‘45000’;

DECLARE N INTEGER;

SELECT COUNT(*) INTO N FROM Parts WHERE Part_name=NEW. Part_name;

IF N > 0 THEN

SIGNAL Duplicate_part_name SET MESSAGE_TEXT = ‘Такая деталь уже есть в базе!’;

END IF;

END

Триггер, который удаляет все детали, наименование которых совпадает с удаляемой деталью (не будет работать):

CREATE TRIGGER Delete_the_same_parts AFTER DELETE ON Parts FOR EACH ROW

BEGIN

DELETE FROM Parts WHERE Part_name=OLD.Part_name;

END

http://doc.prototypes.ru/database/mysql/triggers/info/

Задание на лабораторную работу

  1. Реализовать триггер, автоматически добавляющий к имени детали символ ‘-‘ и порядковый номер детали (с учетом уже имеющихся одноименных деталей в базе) при добавлении каждой новой детали в таблицу. Т.е. детали должны получать уникальные имена «Деталь-1», «Деталь-2», «Деталь-3» и т.д.
  2. Реализовать триггер, ограничивающий максимальную суммарную стоимость всех деталей, поставляемых для определенного проекта.
  3. Реализовать триггер, ограничивающий максимальный суммарный вес всех деталей, поставляемых для определенного проекта.
  4. Реализовать триггер, который разрешает только поставки, в которых поставщик и проект находятся в одном городе.
  5. Реализовать триггер, который удаляет проект при удалении последней поставки для данного проекта

Поделитесь своим мнением или задайте вопрос