Агапов И.Е. Лабораторный практикум. Базы данных

Подождите немного. Документ загружается.

Рис. 27. Окно создания триггера для представления

Пример триггера, которые для представления "TestView" позволяет мо-

дифицировать поле "Name" приведен на рис. 28.

Рис. 28. Триггер, позволяющий модифицировать представление

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

Отчет о выполнении лабораторной работы №8 необходимо оформить

на листах формата A4. Отчет должен содержать описание и результаты рабо-

ты, представляемые в следующей последовательности:

1. Словесное описание и сценарий создания представлений для своей базы

данных.

2. Краткое описание запросов с использованием всех созданных представле-

ний. Распечатка этих запросов с указанием количества возвращенных

ими

строк.

3. Перечень файлов, полученных при выполнении лабораторной работы с

указанием их имен, места расположения, даты изменения и размеров

(сценарий, база данных, резервная копия базы данных, файл с запросами и

файл с отчетом).

8.8. Контрольные вопросы

1. Что такое представление?

2. Какие запросы допускается использовать для создания представлений?

3. Как изменить представление?

4. Для чего можно использовать представления?

5. Какие представления являются модифицируемыми?

6. Как сделать любое представление модифицируемым?

7. Зачем используется параметр WITH CHECK OPTION?

8. Какие имеются неудобства работы с представлениями?

9. Для чего предназначено представление, созданное

на рис. 26?

10. Почему представление, показанное на рис. 26 не является модифицируе-

мым?

11. Можно ли сделать так, чтобы у представления, показанного на рис. 26,

можно было удалять строки? Что для этого надо сделать?

9. Лабораторная работа №9.

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

9.1. Цель работы

Изучить виды используемых в Firebird хранимых процедур. Получить

навыки работы с хранимыми процедурами с помощью команд SQL и с по-

мощью программы "IB Expert".

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

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

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

предыдущих лабораторных работ.

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

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

9.4. Теоретические сведения

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

Хранимая процедура – это откомпилированная во внутреннее пред-

ставление сервера СУБД подпрограмма, хранящаяся в базе данных. Храни-

мые процедуры пишутся на специальном языке хранимых процедур и триг-

геров, в котором имеются операторы присваивания, ветвлений и циклов, и в

которых можно использовать операторы SQL, такие как INSERT, DELETE,

UPDATE и SELECT.

Хранимые процедуры позволяют переносить часть прикладных

функ-

ций, связанных с обработкой данных, в саму данных. Например, хранимая

процедура может управлять приемом заказа или переводом денег с одного

банковского счета на другой. Для автоматического выполнения хранимых

процедур при возникновении в базе данных определенных условий исполь-

зуются триггеры.

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

котором указываются следующие элементы

1. имя хранимой процедуры;

2. входные и выходные параметры и их типы;

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

4. последовательность инструкций, которые выполняются при вызове про-

цедуры.

С помощью входных параметров внутрь хранимой процедуры можно

передать значения, которые будут использоваться в ходе выполнения проце-

дуры. С помощью выходных параметров хранимые процедуры возвращают

значения, вычисленные в ходе выполнения процедуры.

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

от хранимых процедур тем, что возвращают значение. InterBase не поддер-

живает хранимых функций. В InterBase для возврата значений из хранимых

процедур применяются выходные параметры.

Некоторые СУБД поддерживают параметры, которые одновременно

являются и

входными, и выходными. InterBase не поддерживает такой воз-

можности.

Для параметров и локальных переменных хранимых процедур исполь-

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

таблиц.

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

CREATE PROCEDURE Имя_Процедуры

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

Параметр <тип данных> ...])]

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

Параметр <тип данных> ...])]

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

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

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

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

<блок> =

BEGIN

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

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

END

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

<тип данных> = однин из типов данных InterBase (табл. 1 на стр. 15)

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

геров InterBase.

Внутри хранимых процедур может быть предусмотрена обработка ис-

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

т.е. вызывающими сами себя.

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

1. Производительность – перенос на сервер

часто используемых действий

приводит к существенному повышению производительности.

2. Многократное использование кода – части приложения, перенесенные на

сервер, могут использоваться любыми другими приложениями, имеющи-

ми доступ к серверу.

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

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

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

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;

9.5. Задание

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

1. Создать аналогично предыдущим лабораторным работам рабочую папку и

назвать ее "ЛР9".

2. Скопировать в эту папку файл сценария, созданный при выполнении пре-

дыдущей лабораторной работы.

3. Открыть в приложении "IB Expert" этот сценарий.

4. Исправить текст комментариев и сделать, чтобы база данных теперь соз-

давалась

в папке "ЛР9".

5. Добавить в сценарий операторы создания хранимых процедур и других

объектов, которые могут потребоваться. Создать не менее трех хранимых

процедур, реализующих бизнес-правила в соответствии с выданным ин-

дивидуальным заданием. Для каждой хранимой процедуры должны при-

сутствовать комментарии, поясняющие выполняемые операции.

6. Выполнить сценарий и сохранить его

в папке "ЛР9".

7. Зарегистрировать созданную базу данных в программе "IB Expert" и под-

ключиться к ней.

8. Создать в своей базе данных по одной хранимой процедуре каждого вида

в диалоговом режиме. Они должны называться "TestProcedure1" и

"TestProcedure2" и иметь входные и выходные параметры. Скопировать в

отчет сценарий создания этих процедур, который сгенерирует "IB Expert".

9. Выполнить в окне "SQL Editor" по одному запросу с каждой созданной

хранимой процедурой. Поместить выполненные запросы в

отчет, добавив

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

10. Создать в папке "ЛР9" резервную копию базы данных.

11. Создать и сохранить в папке "ЛР9" файл с отчетом о выполнении лабора-

торной работы, который должен называться "Отчет.doc".

9.6. Ход работы

Операции по созданию хранимых процедур с помощью выполнения

сценария были изучены в ходе выполнения предыдущих лабораторных ра-

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

диалоговом режиме необходимо выполнить следующие действия:

1. Подключиться к базе данных и выполнить команду главного меню

"DatabaseÖNew Procedure". В результате откроется окно "Procedure" для

создания процедуры (

рис. 29).

Рис. 29. Окно создания хранимой процедуры в режиме диалога

2. В этом окне в правом верхнем углу следует ввести имя процедуры (заме-

нив имя "NEW_PROCEDURE").

3. При нажатой кнопке [Input Parameters] добавить входные параметры. Для

этого находясь в сетке, расположенной в средней части окна, нажать кла-

вишу [Insert] или [Стрелка вниз]. После этого ввести в новой строке имя

входного параметра (на рис. 29 имя входного параметра – "PARAM1") и с

помощью показанного на рис. 29 выпадающего списка выбрать его тип.

4. При нажатой

кнопке [Output Parameters] таким же образом добавить вы-

ходные параметры.

5. При необходимости точно так же добавить локальные переменные (на-

жать для этого кнопку [Variables]).

6. В поле ввода, расположенном в нижней части окна, ввести тело процеду-

ры.

7. Нажать кнопку

[Compile Procedure] (Ctrl+F9).

После тог, как хранимая процедура будет создана, ее можно легко из-

менить, используя для этого, как и для триггеров, либо обычный режим, ко-

гда можно изменить весь оператор создания процедуры, либо "ленивый" –

когда вручную меняется только тело процедуры, а входные и выходные па-

раметры и локальные переменные создаются и изменяются

в режиме диало-

га.

Переключение между двумя режимами просмотра и редактирования

хранимой процедуры, показанными на рис. 30 и 31 производится кнопкой

[Lazy mode on/off].

Рис. 30. Окно редактирования хранимой процедуры в режиме диалога

Рис. 31. Окно редактирования хранимой процедуры в обычном режиме

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

Отчет о выполнении лабораторной работы №9 необходимо оформить

на листах формата A4.

Отчет должен содержать описание и результаты работы, представляе-

мые в следующей последовательности:

1. Описание бизнес-правил, реализованных с помощью хранимых процедур.

2. Распечатка сценария создания базы данных с комментариями к созданным

хранимым процедурам.

3. Результаты выполнения запросов

4. Перечень файлов

, полученных при выполнении лабораторной работы с

указанием их имен, места расположения, даты изменения и размеров

(сценарий, база данных, резервная копия базы данных, файл с отчетом).

9.8. Контрольные вопросы

1. Что такое бизнес-правила?

2. Что такое хранимая процедура?

3. Какие в InterBase существуют виды хранимых процедур?

4. Как происходит вызов процедур каждого вида?

5. В чем главное отличие в работе процедур каждого вида?

6. Какие преимущества имеет использование хранимых процедур?

7. Как записываются параметры и локальные переменные в операторах SQL

внутри

хранимых процедур и триггеров?

8. Какие операторы SQL допускается использовать в хранимых процедурах

и триггерах?

9. Какие операторы можно использовать в хранимых процедурах?

10. Для чего используется оператор SUSPEND?

11. Какие хранимые процедуры называются рекурсивными?

12. Привести синтаксис оператора IF … THEN … ELSE.

13. Привести синтаксис оператора FOR SELECT … DO.

14. Привести синтаксис оператора WHILE … DO.

15. Чем отличается обычный и "ленивый

" режим создания и редактирования

хранимых процедур?

16. Как происходит процесс обработки исключений в хранимых процедурах?