42331

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

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

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

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

Русский

2013-10-29

113.5 KB

58 чел.

?     

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


 

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

46649. Методы и средства обеспечения безопасности установок и систем повышенной опасности 24.43 KB
  Методы и средства обеспечения безопасности установок и систем повышенной опасности. Основные методы обеспечения безопасности установок. Госгортехнадзор РФ утверждает обязательные для предприятий организаций и АО далее предприятий всех форм собственности межотраслевые правила по устройству и безопасной эксплуатации этих установок [19. В них содержатся требования безопасности которые должны быть выполнены как при проектировании и изготовлении так и при монтаже эксплуатации и ремонте данных установок.
46650. Дополнительное профессиональное образование 24.45 KB
  Представляет собой систему средств обеспечивающих прогностическую оценку взаимосоответствия человека и профессии в тех видах деятельности которые осуществляются в нормативно заданных опасных условиях гигиенических микроклиматических технических социальнопсихологических требующих от человека повышенной ответственности здоровья высокой работоспособности и точности исполнения задания устойчивой эмоциональноволевой регуляции. Вот почему на каждом уроке как правило учителя дают учащимся домашние задания. Обычно эти задания включают в...
46651. Статус української мови як державної 24.5 KB
  Українська мова в Україні має статус державної. Це закріплено у десятій статті Конституції України. Державна мова - мова, яка користується у конкретній державі законодавчим статусом обов'язкової для вживання в офіційних сферах життя. Державна мова обслуговує державні і суспільні органи, організації, заклади культури і освіти. Вона повинна побутувати у всіх сферах життя
46653. Declarative sentences 24.5 KB
  A declarative sentence contains a statement which gives the reader or the listener some information about various events, activities or attitudes, thoughts and feelings. Statements form the bulk of monological speech, and the greater part of conversation. A statement may be positive (affirmative) or negative, as in...
46654. Рак легкого. Классификация. Стадии процесса. Пути метастазирования. Современные принципы лечения 24.66 KB
  Различают две формы: центральный рак легкого исходящий из крупного или мелкого бронха и периферический рак развивающийся из самой ткани легкого. Различают центральный рак легкого растущий преимущественно внутри или перибронхиапьно 80 случаев; периферический рак; редко диагностируется медиастинальная форма милиарный узелковый карциноз и др. По гистологической структуре раки легкого чаще всего бывают плоскоклеточными хотя наблюдаются также железистые формы аденокарциномы резко анаплазированные мелкоклеточный рак...
46655. Становление и развитие экологического права. Проблемы дифференциации и интеграции в развитии экологического права 24.72 KB
  Лишь в 70е годы применительно к водам и в 80е применительно к атмосферному воздуху проблемы охраны окружающей среды от загрязнения стали оцениваться и регулироваться как экологические. В то время не законы а именно правительственные постановления определяли некоторые комплексные подходы к регулированию природопользования и охраны окружающей среды как единого объекта. В конце 80х годов ЦК КПСС и Правительство СССР осознавали что основными причинами резкого ухудшения состояния окружающей среды в стране являлись: слабое правовое...
46656. Договор международного факторинга 24.74 KB
  Договор международного факторинга это специфический по своему характеру вид факторинга. Преимущества субъектов международного факторинга Поставщик отгрузивший продукцию получает оплату за товар не дожидаясь оплаты покупателем что увеличивает объемы продаж и конкурентоспособность. Содержание договора международного факторинга ПРЕДМЕТ ДОГОВОРА Согласно ст.
46657. Международный кодекс рекламной практики 24.91 KB
  Реклама должна расцениваться прежде всего с точки зрения ее воздействия на покупателя причем следует обращать внимание каким видом СМИ она будет распространяться. Определения Для целей настоящего Кодекса: термин реклама должен употребляться в самом широком смысле включающем любую форму рекламы относительно изделий услуг и благ независимо от вида СМИ которое используется в том числе рекламные надписи и изображения на упаковках этикетках. А также любые надписи и изображения на самом товаре; термин товар включает изделия...