42331

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

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

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

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

Русский

2013-10-29

113.5 KB

55 чел.

?     

  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.  Как происходит процесс обработки исключений в хранимых процедурах?


 

А также другие работы, которые могут Вас заинтересовать

27535. Принципы права: понятие, классификация и значение 28 KB
  Принципы права исходные нормативноруководящие начала императивные требования определяющие общую направленность правового регулирования общественных отношений. Принципы права обусловлены рядом факторов: уровень экономического развития; особенности политической системы; социальноклассовые природы государства. Принципы российского права: 1 принцип справедливости; 2 принцип равноправия равенство перед законом и судом; 3 единство прав и обязанностей; 4 гуманизм основан на уважении человека его достоинства и прав; 5...
27536. Проиллюстрируйте соотношение норм права и статьи закона на примере ст.8.13 КоАП РФ 30 KB
  Соотношение между статьей и нормой во многом зависит оттого что перед нами логическая норма или норма предписание. Если это норма предписание то она полностью содержится в статье. В одной статье может содержаться две или более нормы предписания. Логическая норма может находиться в составе трех ее элементов и одной целой статье но тогда диспозицию или санкцию нужно выводить в статье логическим путем.
27537. Публичная (политическая власть) признак государства 23.5 KB
  Имеется в виду то что: 1 государство является властью которая не сливается с обществом а выделяется из него будучи относительно самостоятельной по отношению к обществу в определенном смысле над ним; 2 государственная власть внешне и официально представляет все общество выступает в качестве особого органа управления обществом; 3 эта власть призвана обеспечить поддержание правопорядка нормальной жизнедеятельности людей; 4 публичная власть как разновидность политической власти означает наличие у нее особого аппарата включающего...
27538. Разделение власти как принцип организации деятельности правового государства 27 KB
  Правовое государство можно определить как организацию политической власти в обществе основанную на верховенстве правового закона. Государство возлагает на граждан юридические обязанности запрещая совершать противоправные и обязывая совершать правомерные действия и в случае их неисполнения привлекает их к юридической ответственности. В свою очередь государство приобретая ряд монопольных прав на правотворчество на применение государственного принуждения на сбор налогов на эмиссию денежных знаков и др. Государство должно не просто...
27539. Рассмотрите отличия государственной власти от власти общественной 28 KB
  Рассмотрите отличия государственной власти от власти общественной. Таким образом можно выделить две основные особенности общественной власти характерные для первобытнообщинного строя: 1. Главными органами власти являлись: род собрание старейшина при необходимости военачальник. Эта власть по существу сливалась с населением так как не существовало разграничения между субъектом и объектом власти общество одновременно было и объектом и субъектом власти.
27540. Рассмотрите соотношение права и религиозных норм 29.5 KB
  Рассмотрите соотношение права и религиозных норм. Сходство норм права и религиозных норм имеет практически те же черты что и сходство норм права и норм морали и выражается в нормативности совокупность определенных норм являющихся образцом масштабом поведения людей универсальности эти нормы распространяются на общественные отношения общности права и религии в нормативноценностных критериях правомерного и неправомерного. В настоящее время принято выделять семьи религиозного права где в качестве основных источников права выступают...
27541. Система права: понятие и основные элементы 31 KB
  Система права: понятие и основные элементы. Система права – явление объективное хотя оно и находит свое проявление вовне через деятельность законодателя. Структура права предопределена в конечном счете характером общественных отношений на том или ином этапе общественного развития. Таким образом система права – это внутреннее строение права деление его на отрасли подотрасли и правовые институты в соответствии с предметом и методом правового регулирования.
27542. Соотношение инкорпорации, кодификации и консолидации 29.5 KB
  Систематизация нормативно-правовых актов – это их упорядочение приведение в единую более или менее сложную систему. Особенности систематизационной деятельности: сопоставляются и анализируются изданные в разное время действующие акты; 2 выявляются имеющиеся противоречия в содержании правовых норм пробелы в праве множественность актов посвященных одним и тем же вопросам обнаруживаются устаревшие нормы действие которых перекрыто актами изданными позднее; 3 подготавливаются проекты новых актов предложения об изменении и дополнении...
27543. Соотношение нормативного и ненормативного в правовом регулировании общественных отношений 37 KB
  Соотношение нормативного и ненормативного в правовом регулировании общественных отношений. Втретьих в качестве предпосылки нормативности может выступать ценность для общества тех или иных вариантов поведения следовательно и образуемых общественных отношений. Но при этом следует учитывать что для эффективного нормативнорегулятивного выражения тех или иных социальных отношений недостаточно простого познания уяснения характерных и сущностных данных отношений. Далее механизм экономического регулирования как справедливо отмечает автор...