Практическое занятие 4. Разработка хранимых процедур и функций на сервере MySql Введение
Хранимые процедуры и функции являются важнейшим элементом современных промышленных СУБД. В MySQL хранимые процедуры поддерживаются начиная с версии 5. Хранимые процедуры и функции (а также триггеры как разновидность процедур) реализуются в виде подпрограмм. При этом процедуры и функции называются хранимыми т.к. они хранятся в базе данных наряду с другими объектами (таблицами, ограничениями и т.д.). Хранимые подпрограммы представляют собой набор команд SQL. Кроме операторовSQLв хранимой подпрограмме могут быть использованы основные элементы свойственные любому языку программирования – переменные, операторы ветвления, циклы и др.
Хранимые процедуры и функции позволяют реализовать часть логики работы прикладной системы на стороне сервера, что оптимизирует производительность, упрощает разработку клиентской части системы, существенно повышает безопасность. Преимущество, связанное с безопасностью, заключается в защите программного кода бизнес - логики от несанкционированного просмотра и изменения. Защитить подпрограмму, которая находится на сервере намного проще, чем гарантировать безопасность клиентского приложения.
Создание, запуск и удаление простой процедуры
Для создания простой процедуры в клиенте mysql.exeможно выполнить следующие операторы:
DELIMITER //
CREATE PROCEDURE Hello_World()
BEGIN
SELECT(‘Hello, world!’);
END
//
В данном примере первая строка задает последовательность символов, которая будет использоваться в качестве признака завершения ввода. Обычно для этого используется точка с запятой, однако, в приведенном выше примере точка с запятой уже используется в качестве разделителя операторов в теле функции.
Во второй строке следует оператор создания процедуры CREATEPROCEDURE, после которого указывается имя процедуры. Создаваемая процедура не имеет параметров, поэтому скобки после имени процедуры – пустые.
Далее следует конструкция BEGIN..END, в которую должны быть заключены все операторы процедуры. Операторы разделяются символом точка с запятой.
Для запуска процедуры на выполнение можно использовать команду CALL:
CALL Hello_World;
Для удаления процедуры можно использовать команду DROPPROCEDURE:
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 можно использовать опциюLIMIT1. Следующая процедура выводит наименование самой тяжелой детали:
CREATEPROCEDUREHeavy()
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
//
