42331

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

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

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

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

Русский

2013-10-29

113.5 KB

54 чел.

?     

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


 

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

37720. Побудова кінематичної схеми плоского механізму та його структурний аналіз 952.57 KB
  Мета роботи - набути навичок складання структурних і кінематичних схем механізмів та проведення їх структурного аналізу. Зміст роботи: на прикладі моделі плоского механізму скласти кінематичну і структурну схеми, визначити кількість ланок, у тому числі вхідних і вихідних, кількість кінематичних пар, записати структурну формулу механізму та встановити його клас і порядок.
37721. Специфікування предметної галузі проекту засобами мови uml. Кількісна оцінка діаграм 108 KB
  кількісна оцінка діаграм Мета: дослідження класів та отримання навиків у побудові діаграми класів UML для специфікування предметної галузі використання стереотипів UML та структурування моделі UML за допомогою пакетів. Опис класів. Побудова діаграми класів Діаграма класів Clss digrm призначена для відображення статичної структури ПЗ проекту що проектується. Діаграма містить класи і взаємозв’язки між ними та дозволяє описати їх структуру та типи відношень.
37722. ІМПІЧМЕНТ (АМЕРИКАНСЬКА ЗА ПОХОДЖЕННЯМ МОДЕЛЬ) 99.5 KB
  Тема даної роботи досить актуальна, адже складність процедури імпічменту зумовлює те, що в історії відбувалися лише окремі успішні випадки відсторонення посадових осіб з посад, а імпічмент главі держави вважається резонансною подією.
37723. Подготовка изображений для WEB 3.35 MB
  Изображения в сети также важны как и в любом печатном издании. Изображения должны быть правильно отмасштабированы иметь хорошую четкость и сохранены в цветовом пространстве sRGB. Поэтому для получения хороших результатов при сайтостроительстве нужно корректно отмасштабировать изображения перед помещением их в сеть. В Интернет используются изображения с цветовым пространством sRGB.
37724. Создание Форм В INKSCAPE 874 KB
  Для этого щелкните по верхней линейке и перетащите вниз чтобы создать горизонтальную направляющую и щелкните по левой линейке и перетащите вправо чтобы создать вертикальную направляющую см. Выберите инструмент Рисовать круги эллипсы и дуги F5 и щелкните на значке Заливка в правом верхнем углу. Щелкните правой кнопкой мышки на круг и нажмите Продублировать CtrlD. Затем в окне трансформации установите 80 в поле Ширина и щелкните по кнопке pply.
37725. Создание трехмерного Текста в Inkscape 787 KB
  Выберите инструмент Создавать и править текстовые объекты F8 и введите на лист Вашу фамилию. Теперь добавим эффект перспективы к тексту и придадим трехмерность. Инструментом Выделять и трансформировать объекты F1 можно нажать пробел выделите текст и выполните команду Контуры Оконтурить объект ShiftCtrlС.
37726. ВИМІРЮВАННЯ ПАРАМЕТРІВ ІМПУЛЬСНИХ СИГНАЛІВ МЕТОДОМ ДИСКРЕТНОГО РАХУНКУ 132 KB
  ОСНОВНІ ТЕОРЕТИЧНІ ВІДОМОСТІ Методи вимірювання частоти і інтервалів часу різноманітні. Застосовуються методи безпосереднього вимірювання методи засновані на порівнянні частоти зі зразковою частотою за допомогою осцилографа гетеродинний нульове биття і резонансний методи. Метод вимірювання Вхідний сигнал В Відносна нестабільність частоти кварцового ген. Похибка вимірювання частоти Електродинамічний логометричний частотомір 36 127 220 __  1.
37727. Программирование на ассемблере MASM32. Изучение среды разработки RADasm и отладчика OllyDbg 584.5 KB
  Они необходимы программе для обработки и хранения в памяти команд и данных а также получения информации о собственном текущем состоянии и состоянии процессора.1: пространство адресуемой памяти до 2х в 32й степени байт 4 Гбайт для Pentium II и выше до 2х в 36 степени байт 64 Гбайт; регистры для хранения данных общего назначения; сегментные регистры; регистры состояния и управления; регистры устройства вычислений с плавающей запятой сопроцессора; набор регистров целочисленного MMXрасширения отображенных на регистры...
37728. Исследование линейных электрических цепей постоянного тока 309.11 KB
  1 ток в цепи и падения напряжения на участках цепи определяются по закону Ома: Разветвленная цепь с одним источником э. Сущность метода наложения основывается на принципе суперпозиции заключающегося в том что ток в отдельной ветви линейной разветвленной цепи равен алгебраической сумме...