42331

Хранимые процедуры (Procedures)

Лабораторная работа

Информатика, кибернетика и программирование

Хранимые процедуры Цель работы Изучить виды используемых в Firebird хранимых процедур. Теоретические сведения Хранимые процедуры Procedures Хранимая процедура это откомпилированная во внутреннее представление сервера СУБД подпрограмма хранящаяся в базе данных. Хранимые процедуры пишутся на специальном языке хранимых процедур и триггеров в котором имеются операторы присваивания ветвлений и циклов и в которых можно использовать операторы SQL такие как INSERT DELETE UPDTE и SELECT. Хранимые процедуры позволяют переносить часть...

Русский

2013-10-29

113.5 KB

60 чел.

?     

  1.  Лабораторная работа №8. Хранимые процедуры
    1.  Цель работы

Изучить виды используемых в Firebird хранимых процедур. Получить навыки работы с хранимыми процедурами с помощью команд SQL и с по-мощью программы "IBExpert".

Время выполнения: 4 часа.

  1.  Исходные данные

Исходными данными является индивидуальное задание и результат предыдущих лабораторных работ.

  1.  Используемые программы

Программы "IBExpert" и "Microsoft Word".

  1.  Теоретические сведения
    1.  Хранимые процедуры (Procedures) 

Хранимая процедура – это откомпилированная во внутреннее представление сервера СУБД подпрограмма, хранящаяся в базе данных. Хранимые процедуры пишутся на специальном языке хранимых процедур и триггеров, в котором имеются операторы присваивания, ветвлений и циклов, и в которых можно использовать операторы SQL, такие как INSERT, DELETE, UPDATE и SELECT.

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

Хранимые процедуры создаются оператором CREATE PROCEDURE, в котором указываются следующие элементы:

  1.  имя хранимой процедуры;
  2.  входные и выходные параметры и их типы;
  3.  имена и типы данных локальных переменных, используемых процедурой;
  4.  последовательность инструкций, которые выполняются при вызове процедуры.

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


Многие СУБД поддерживают хранимые функции, которые отличаются от хранимых процедур тем, что возвращают значение. Firebird не поддерживает хранимых функций. В Firebird для возврата значений из хранимых процедур применяются выходные параметры.

Некоторые СУБД поддерживают параметры, которые одновременно являются и входными, и выходными. Firebird не поддерживает такой возможности.

Для параметров и локальных переменных хранимых процедур используются те же типы данных, которые поддерживаются СУБД для столбцов таблиц.

Синтаксис оператора создания хранимой процедуры в Firebird:

CREATE PROCEDURE Имя_Процедуры [(Параметр <тип данных> [, Пара      метр <тип данных> ...])]

[RETURNS (Параметр <тип данных> [, Параметр <тип данных> …])]

   AS [<список переменных>] <блок>;

 <список переменных> =

DECLARE [VARIABLE] Переменная <тип данных>; [DECLARE [VARIABLE] Переменная <тип данных>; …]

<блок> =

BEGIN

    <составной оператор> [<составной оператор>...]

END

<составной оператор> = <блок> | Оператор;

<тип данных> = один из типов данных Firebird.

Оператор – любой одиночный оператор языка хранимых процедур и триггеров Firebird.

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

Основные преимущества хранимых процедур:

  1.  Производительность – перенос на сервер часто используемых действий приводит к существенному повышению производительности.
  2.  Многократное использование кода – части приложения, перенесенные на сервер, могут использоваться любыми другими приложениями, имеющи-ми доступ к серверу.


  1.  Виды хранимых процедур в Firebird

В Firebird существует два типа хранимых процедур:

  1.  Процедуры выбора данных – могут использоваться вместо таблиц в операторе SELECT.
  2.  Выполняемые процедуры – производят какие-либо действия с базой дан-ных и не обязаны возвращать данные. Вызываются с помощью команды EXECUTE PROCEDURE.

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

Оба типа процедур имеют одинаковый синтаксис создания и формально ничем не отличаются. Любая выполнимая процедура, если она имеет выходные параметры, может быть вызвана в запросе SELECT, любая процедура выбора данных – с помощью EXECUTE PROCEDURE. Разница заключается в цели разработки процедур каждого вида. Процедура выбора данных специально создается для вызова из оператора SELECT, а выполняемая процедура – для вызова оператором EXECUTE PROCEDURE.

Пример создания процедуры выбора данных:

-- возвращает перечень служащих, работающих в отделе,

-- который передается в качестве входного параметра:

CREATE PROCEDURE Test_Procedure (DeptNo CHAR(3))

RETURNS (

   Number INT, -- порядковый номер

   EmpNo SMALLINT, -- идентификатор служащего

   FirstName VARCHAR(15), -- имя

   LastName VARCHAR(20) -- фамилия )

AS

BEGIN

   Number = 0;

   FOR SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM Employee

   WHERE DEPT_NO = :DeptNo

   INTO :EmpNo, :FirstName, :LastName

   DO

   BEGIN Number = Number + 1;

      SUSPEND;

   END

END;

 


Каждая процедура выбора данных должна обязательно содержать оператор SUSPEND, который передает набор выходных параметров в то место, откуда вызвали процедуру.

Пример вызова процедуры выбора данных:

SELECT * FROM Test_Procedure('000');

Пример создания выполняемой процедуры:

CREATE PROCEDURE MakeGenerator (

  GenName VARCHAR(31), GenValue INTEGER )

AS

DECLARE VARIABLE

   Sql VARCHAR(256);

BEGIN

   Sql = 'CREATE GENERATOR ' || GenName || ';';

   EXECUTE STATEMENT Sql;

   Sql = 'SET GENERATOR ' || GenName || ' TO ' || CAST(GenValue

        AS      VARCHAR(10)) || ';';

   EXECUTE STATEMENT Sql;

END; 

Синтаксис оператора вызова процедуры:

EXECUTE PROCEDURE ИмяПроцедуры [Параметр [, Параметр ...]] [RETURNING_VALUES Параметр [, Параметр ...]];

Пример вызова выполняемой процедуры:

EXECUTE PROCEDURE MakeGenerator 'Test_Gen', 1;

  1.  Порядок выполнения работы

Лабораторную работу следует выполнять в следующем порядке:

  1.  Создать на компьютере sqledu02 (или на локальном компьютере) рабочую папку для хранения файлов, получаемых при выполнении лабораторной работы №8. Эта папка должна располагаться в той же папке, что и папка для лабораторной работы №7, и называться "ЛР8" . Пример правильного названия рабочей папки при выполнении лабораторной работы №8: "sqledu02:\D:\Data\ЛР8".
  2.  Открыть окно "Редактор скриптов" в приложении "IBExpert".
  3.  Используя кнопку   [Загрузить из файла] на панели инструментов, открыть диалоговое окно "Open SQL File" и с его помощью открыть файл сценария,  созданный при выполнении лабораторной работы №7.
  4.  Используя кнопку [Save as] на панели инструментов, сохранить загруженный сценарий в папке "sqledu02:\D:\Data\ЛР8".  


  1.  Изменить в сценарии путь до файла с базой данных, чтобы база данных создавалась в папке "ЛР8".   
  2.  Открыть в приложении "IBExpert" этот сценарий, исправить комментарии и сделать, чтобы база данных теперь создавалась в папке "ЛР8".  
  3.  Добавить в сценарий операторы создания хранимых процедур и других объектов, которые могут потребоваться. Создать не менее трех хранимых процедур, реализующих бизнес-правила в соответствии с выданным индивидуальным заданием. Для каждой хранимой процедуры должны присутствовать комментарии, поясняющие выполняемые операции.
  4.  Выполнить сценарий и сохранить его в папке "ЛР8".
  5.  Зарегистрировать созданную базу данных в программе "IBExpert" и подключиться к ней.
  6.  Создать в своей базе данных по одной хранимой процедуре каждого вида в диалоговом режиме. Они должны называться "TestProcedure1" и "TestProcedure2" и иметь входные и выходные параметры. Скопировать в отчет сценарий создания этих процедур, который сгенерирует "IBExpert".
  7.  Выполнить в окне "SQL Editor" по одному запросу с каждой созданной хранимой процедурой. Поместить выполненные запросы в отчет, добавив комментарии и сведения о результате их выполнения.
  8.  Создать в папке "ЛР8" резервную копию базы данных.
  9.  Создать и сохранить в папке "ЛР8" файл с отчетом о выполнении лабораторной работы, который должен называться "Отчет8-xx.odt", где xx — это номер варианта задания.
    1.  Ход работы 

Операции по созданию хранимых процедур с помощью выполнения сценария были изучены в ходе выполнения предыдущих лабораторных работ. Для создания хранимой процедуры с помощью программы "IBExpert" в диалоговом режиме необходимо выполнить следующие действия:

  1.  Подключиться к базе данных и выполнить команду главного меню База данных > Новая процедура. В результате откроется окно "Процедура" для создания процедуры (рис. 1).
  2.  В этом окне в правом верхнем углу следует ввести имя процедуры (заменив имя "NEW_PROCEDURE").
  3.  При нажатой кнопке [Входные параметры] добавить входные параметры. Для этого находясь в сетке, расположенной в средней части окна, нажать клавишу [Insert]. После этого ввести в новой строке имя входного параметра, например – "PARAM1" и с помощью выпадающего списка выбрать его тип.
  4.  

При нажатой кнопке [Выходные параметры] таким же образом добавить выходные параметры.


 

  1.  При необходимости точно так же добавить локальные переменные (нажать для этого кнопку [Переменные]).
  2.  В поле ввода, расположенном в нижней части окна, ввести тело процедуры.
  3.  Нажать кнопку [Компилировать процедуру] (Ctrl+F9).

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

Переключение между двумя режимами просмотра и редактирования хранимой процедуры, показанными на рис. 2 и 3 производится кнопкой [Включить/Выключить "ленивый" режим].


  1.  


  1.  Отчет о выполнении работы

Отчет о выполнении лабораторной работы №8 необходимо оформить на листах формата A4. Для создания отчета следует использовать шаблон отчета для соответствующей лабораторной работы, который необходимо открыть с помощью приложения OpenOffice.

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

  1.  Цель работы.
  2.  Краткое описание предметной области в соответствии с вариантом.
  3.  Данные о версии СУБД, использованной в лабораторной работе.
  4.  Перечисление всех использованных при выполнении лабораторной работы команд главного меню приложения "IBExpert" с краткими пояснениями выполняемых действий.
  5.  Перечень файлов, полученных при выполнении лабораторной работы с указанием их имен, места расположения, даты изменения и размеров (сценарий, база данных, резервная копия базы данных, файл с отчетом).
  6.  Письменные ответы на контрольные вопросы.
  7.  Выводы.


  1.  Распечатку сценария создания базы данных с комментариями, которые должны содержать сведения об авторе, дате создания, всех выполняемых действиях и пояснения к выполняемым действиям.
    1.  Контрольные вопросы
  2.  Что такое бизнес-правила?
  3.  Что такое хранимая процедура?
  4.  Какие в Firebird существуют виды хранимых процедур?
  5.  Как происходит вызов процедур каждого вида?
  6.  В чем главное отличие в работе процедур каждого вида?
  7.  Какие преимущества имеет использование хранимых процедур?
  8.  Как записываются параметры и локальные переменные в операторах SQL внутри хранимых процедур и триггеров?
  9.  Какие операторы SQL допускается использовать в хранимых процедурах и триггерах?
  10.  Какие операторы можно использовать в хранимых процедурах?
  11.  Для чего используется оператор SUSPEND?
  12.  Какие хранимые процедуры называются рекурсивными?
  13.  Привести синтаксис оператора IF … THEN … ELSE.
  14.  Привести синтаксис оператора FOR SELECT … DO.
  15.  Привести синтаксис оператора WHILE … DO.
  16.  Чем отличается обычный и "ленивый" режим создания и редактирования хранимых процедур?
  17.  Как происходит процесс обработки исключений в хранимых процедурах?