42331

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

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

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

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

Русский

2013-10-29

113.5 KB

48 чел.

?     

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


 

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

80283. Удосконалення управління процесами детінізації економічної діяльності 140.5 KB
  Роль і функції держави у зниженні тіньової економічної діяльності в Україні. Механізми запобігання розвитку тіньової економічної діяльності у сфері фінансово грошових відносин. Основні принципи розроблення комплексу заходів запобіжного характеру спрямованих на зниження рівня тіньової економічної діяльності. Методологічні підходи до визначення послідовності реалізації заходів спрямованих на запобігання розвитку тіньової економіки.
80284. Особливості розвитку тіньової економіки в України 65.5 KB
  Тіньовий ринок тіньова економіка істотно впливають на всі сторони економічної діяльності на політичне і суспільне життя кожної країни. Вже на такому рівні вплив державно нерегульованих факторів стає настільки відчутним що суперечності між легальним і тіньовим секторами спостерігаються практично в усіх сферах життєдіяльності суспільства. Труднощі які виникають в результаті внутрішньої неузгодженості регуляторноправового механізму та безсистемної беззмістовної зміни концептуальних орієнтирів розвитку економіки створюють необґрунтовані...
80285. Соціально-економічна сутність тіньової економічної діяльності 86.5 KB
  Соціальноекономічні причини виникнення та джерела походження тіньової економіки. Основні складові тіньової економіки. Класифікація форм і видів прояву тіньової економіки. Тому кожна держава розробляє стратегію форми і методи протистояння тіньовій економічній діяльності упроваджує механізми зниження її негативного впливу на розвиток національної економіки.
80286. Міжнародні організації з боротьби з легалізацією тіньових доходів 128.5 KB
  Створення структура і основні принципи діяльності Групи з розробки фінансових заходів з боротьби з відмиванням грошей FTF. Група з розробки фінансових заходів з боротьби з відмиванням грошей FTF застосувала до України контрзаходи і в кінці року ми були занесені в чорний список країн де відмиваються злочинні кошти. Основні міжнародні вимоги які необхідно враховувати при побудові національно системи боротьби з відмиванням грошей є такі: Адаптація Сорока рекомендацій FTF; Адаптація рекомендацій Базельського комітету Знай...
80287. Збитки від надзвичайних ситуацій та методи їх визначення 263.5 KB
  Класифікація збитків від наслідків надзвичайних ситуацій природного і техногенного характеру. Розрахунок збитків від різних видів наслідків надзвичайних ситуацій природного і техногенного характеру.
80288. Позабюджетна діяльність органів та підрозділів цивільного захисту 127.5 KB
  При укладанні договорів із субєктом обслуговування за узгодженням сторін обумовлюються особливості взаємодії обєкта з державною аварійно-рятувальною службою з питань надання необхідної інформації, доступу особового складу державної аварійно-рятувальної служби
80289. Соціальне забезпечення особового складу органів і підрозділів цивільного захисту 106.5 KB
  Соціальне забезпечення особового складу органів і підрозділів цивільного захисту. Загальні засади соціального захисту особового складу органів і підрозділів цивільного захисту ІІ. Державне обов’язкове соціальне страхування особового складу органів і підрозділів цивільного захисту...
80290. Страхування, як вид цивільно-правових відносин 122.5 KB
  Страхування — це вид цивільно-правових відносин щодо захисту інтересів громадян та юридичних осіб у разі настання певних подій (страхових випадків), визначених договором страхування або чинним законодавством, за рахунок грошових фондів
80291. Основні та оборотні фонди, як поняття бухгалтерського обліку. Амортизація та знос основних фондів 78.5 KB
  Амортизація та знос основних фондів. Оцінка основних фондів. Знос і амортизація основних фондів. Особливістю виробничих основних фондів є функціонування в якості засобів праці в ряді виробничих процесів багатократне їх використання у виробництві в незмінній натуральній формі.