Ход работы

Операции по созданию генераторов, исключений и триггеров с помощью выполнения сценария были изучены в ходе выполнения предыдущих лабораторных работ.

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

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

2. Открыть окно с таблицей, для которой будет создаваться триггер.

3. Перейти на вкладку "Триггеры" (Рис. 1).

4. Нажать в этом окне правую кнопку мыши на одном из событий, для которого будет создаваться триггер.

5. Откроется контекстное меню, в котором надо выбрать команду "Новый триггер".

Рис. 1: Вкладка "Triggers" окно просмотра таблицы

6. В результате откроется окно создания триггера (рис. 2 или 3), в котором достаточно лишь ввести тело триггера, а затем нажать кнопку [Компилировать триггер] (Ctrl+F9).

Рис. 2: Окно создания триггера в "ленивом" режиме

Рис. 3: Окно создания триггера в обычном режиме

Переключение между двумя режимами просмотра и редактирования триггера, показанными на рис. 2 и 3 производится кнопкой [Включить/Выключить "ленивый" режим].

Создание с помощью программы "IBExpert" в диалоговом режиме генераторов и исключений происходит следующим образом:

7. Открывается окно "Генераторы" либо "Исключения". Для этого можно, на-пример, выбрать команду главного меню "База данных > Новый генератор" или "База данных > Новое исключение".

8. В окне "Генераторы" или "Исключения" вводятся для генератора – имя и начальное значение, а для исключения – имя и текст сообщения. Можно ввести данные сразу о нескольких генераторах и исключениях.

9. После ввода данных необходимо нажать кнопку [Компиляция] (F9), которая приведет к автоматической генерации необходимых операторов и их выполнению.

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

Отчет о выполнении лабораторной работы №5 оформляется так же, как и для предыдущих лабораторных работ.

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

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

2. Распечатка сценария создания базы данных с комментариями, которые должны содержать сведения об авторе, дате создания, всех выполняемых действиях и пояснения к генераторам, исключениям и триггерам.

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

Лабораторная работа №6. Внесение изменений в базу данных

Цель работы

Изучить используемые в реляционных СУБД операторы изменения данных. Получить навыки работы с SQL-операторами INSERT, DELETE и UPDATE. Заполнить разрабатываемую базу данных тестовыми данными для последующего использования.

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

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

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

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

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

В SQL имеется три оператора, относящиеся к группе операторов DML (Data Manipulation Language), которые предназначены для выполнения запросов на добавление, удаление и обновление данных.

Добавление новых данных

Новые данные добавляются оператором INSERT. Наименьшей единицей информации, которую можно добавить в реляционную базу данных, является одна строка таблицы.

Немного упрощенный синтаксис оператора INSERT имеет вид:

INSERT INTO <имя_таблицы> [(столбец [, столбец ...])]

{VALUES(<значение> [, <значение> ...]) | <оператор SELECT>};

<величина> = {:Переменная | <константа> | <выражение> | <функция> | udf ([<величина> [, <величина> ...]]) | NULL | USER}

<константа> = Число | 'Строка'

<функция> = CAST(<величина> AS <тип данных>)

| UPPER(<величина>)

| GEN_ID(<имя_генератора>, <величина>)

< выражение> = SQL выражение, возвращающее единичное значение

В этом описании можно выделить два варианта оператора:

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

2. Вставка в таблицу нескольких строк, выбранных с помощью оператора SELECT¹.

В этой лабораторной работе рассматривается только первый вариант оператора INSERT.

Пример, когда в качестве вставляемых величин применены константы:

INSERT INTO Person(Pr_ID, Pr_LastName, Pr_FirstName) VALUES(15 0, 'Иванов', 'Петр');

INSERT INTO REFREG (CODCTR, CODREG, CENTER, NAMEREG) VALUES ((SELECT CODCTR FROM REFCTR WHERE NAME = 'РОССИЯ'), '50', 'МОСКВА', 'Московская область');

Пример использования для вставки значений переменных:

--объявление локальных переменных

DECLARE Person_Number INTEGER;

DECLARE LastName VARCHAR(30);

DECLARE FirstName VARCHAR(30);

BEGIN

Person_Number = 150;

LastName = 'Иванов';

FirstName = 'Петр';

INSERT INTO Person (Pr_ID, Pr_LastName, Pr_FirstName)

VALUES(:Person_Number, :LastName, :FirstName);

END;

Удаление существующих данных

Для удаления строк из таблицы используется оператор DELETE. Вот его упрощенный синтаксис:

DELETE FROM <имя_таблицы>

[WHERE <условие поиска>];

< условие поиска> = как в операторе SELECT

Если не использовать предложение WHERE, то будут удалены все строки в таблице.

-- Удаление всех служащих:

DELETE FROM Employee;

-- Удаление всех людей с номерами 150 и больше:

DELETE FROM Person WHERE Pr_ID >= 150;

Отбирать строки для удаления не обязательно только на основании содержимого этих строк. Можно составить условие для удаляемых строк, опираясь на данные из других таблиц. Для составления таких условий необходимо сначала изучить оператор SELECT.

Обновление существующих данных

Оператор UPDATE обновляет значения одного или нескольких столбцов в выбранных строках одной таблицы. Строки для обновления указываются в предложении WHERE. Если пропустить предложение WHERE, то изменятся все строки таблицы.

UPDATE <имя_таблицы>

SET Колонка = <величина> [,

Колонка = <величина> ... ]

[WHERE <условие поиска>]

<величина> = {Колонка | :Переменная | <константа> | <выражение> | <функция>

| udf([<величина> [, <величина> ...]]) | NULL | USER}

< выражение> = SQL выражение, возвращающее единичное значение

< условие поиска> = как в операторе SELECT

Примеры:

-- Увеличить зарплату всем служащим на 10%:

UPDATE Employee

SET Salary = 1.1*Salary;

/* Увеличить зарплату всем служащим, которые имеют зарплату меньше 10000 на 15%: */

UPDATE Employee

SET Salary = 1.15*Salary;

WHERE Salary <= 10000;

Отбирать строки для изменения, как и для удаления, можно с использование подчиненного запроса SELECT, который позволит учитывать в условии поиска изменяемых строк данные из других таблиц.

Например, можно выполнить такой запрос: увеличить зарплату на 10% всем служащим, работающим в отделе продаж, которые обслужили за последний месяц клиентов больше чем в полтора раза, чем в среднем по их отделу.

Задание

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

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

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

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

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

5. Добавить в конец сценария для каждой имеющейся таблице по три оператора, выполняющих операции INSERT, UPDATE и DELETE.

6. Выполнить сценарий и сохранить его в папке "ЛР6".

7. Зарегистрировать созданную базу данных в программе "IBExpert" и подключиться к ней.

8. Заполните созданные таблицы согласованными данными в диалоговом режиме программы "IBExpert". В каждой таблице должно быть не менее 30 строк (кроме справочников с ограниченным количеством данных).

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

10. Создать и сохранить в папке "ЛР6" файл с отчетом о выполнении лабораторной работы №6, который должен называться "Отчет.doc".

Ход работы

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

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

2. Открыть окно с таблицей, в которую будет заноситься информация.

3. Перейти на вкладку "Данные" (рис. 1). На этой вкладке располагается сетка со строками и столбцами.

Рис. 1: Окно ввода данных в таблицу

При вводе полей есть возможность использования различных диалогов, таких как календарь, калькулятор или окно ввода поля внешнего ключа.

4. Ввести данные, используя для добавления строки клавишу [Insert] или кнопку , а для удаления строки - клавиши [Ctrl+Delete] или кнопку .

5. После ввода данных для сохранения их в базе данных необходимо зафиксировать изменения кнопкой [Подтвердить транзакцию] (Ctrl+Alt+C). Чтобы отменить сделанные изменения, можно воспользоваться кнопкой [Откатить транзакцию] (Ctrl+Alt+R).

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

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

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

1. Распечатка сценария создания базы данных с комментариями, которые должны содержать сведения об авторе, дате создания, всех выполняемых действиях и пояснения к введенным вручную операторам изменения данных.

2. Сведения о количестве введенных в каждую таблицу тестовых данных.

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

Лабораторная работа №7. Выборка данных

Цель работы

Изучить используемый в реляционных СУБД оператор извлечения данных из таблиц. Получить навыки работы с оператором SELECT в программе "IBExpert".

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

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

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

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

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

В SQL имеется единственный оператор, который предназначен для выборки данных из базы данных. Как и операторы INSERT, DELETE и UPDATE он относится к подмножеству DML.

Ниже приведен почти полный синтаксис оператора SELECT.

SELECT [DISTINCT | ALL]

{* | <величина> [,<величина> ...]}

[INTO :Переменная [, :Переменная ...]]

FROM <tableref> [, <tableref> ...]

[WHERE <условие поиска>]

[GROUP BY Колонка [, Колонка ...]]

[HAVING <условие поиска>]

[UNION [ALL] <select_expr>]

[ORDER BY <список сортировки>];

<величина> = {Колонка | :Переменная | <константа> | <выражение> | <функция>

| udf ([<величина> [, <величина> ...]])

| NULL | USER} [AS Псевдоним]

<константа> = Число | 'Строка'

< выражение > = SQL выражение, возвращающее единичное значение

< функция> =

COUNT (* | [ALL] <величина> | DISTINCT <величина>)

| SUM ([ALL] <величина> | DISTINCT <величина>)

| AVG ([ALL] <величина> | DISTINCT <величина>)

| MAX ([ALL] <величина> | DISTINCT <величина>)

| MIN ([ALL] <величина> | DISTINCT <величина>)

| CAST(<величина> AS <тип данных>)

| UPPER(<величина>)

| GEN_ID(Имя_Генератора, <величина>)

<tableref> = {<joined_table> table view

| procedure[(<величина> [, <величина> ...])]}

[ Псевдоним]

<joined_table> = <tableref> <join_type> JOIN <tableref> ON <условие поиска>

| (<joined_table>)

<join_type> = [INNER] | {LEFT | RIGHT | FULL} [OUTER]

< условие поиска> =

< величина> < оператор сравнения>

{<величина> | (<select_one>)}

| <величина> [NOT] BETWEEN <величина> AND <величина>

| <величина> [NOT] LIKE <величина>

| <величина> [NOT] IN (<величина> [, <величина> ... ] | <select_list>)

| <величина> IS [NOT] NULL

| <величина> { >= | <=} <величина>

| < величина> [NOT] { = | < | > } < величина>

| {ALL | SOME | ANY} (<select_list>)

| EXISTS (<select_expr>)

| SINGULAR (<select_expr>)

| <величина> [NOT] CONTAINING <величина>

| <величина> [NOT] STARTING [WITH] <величина>

| ( <условие поиска>)

| NOT < условие поиска>

| <условие поиска> OR <условие поиска>

| <условие поиска> AND <условие поиска>

<оператор сравнения> =

{ = | < | > | <= | >= | !< | !> | <> | != }

<select_one> = оператор SELECT, выбирающий одну колонку и возвращающий ровно одно значение

<select_list> = оператор SELECT, выбирающий одну колонку, возвращающий ноль или много значений

<select_expr> = оператор SELECT, выбирающий несколько величин и возвращающий ноль или много значений

< список сортировки> = {Колонка | Номер} [ASC | DESC]

[, < список сортировки > ... ]

Некоторые параметры, входящие в этот оператор, описаны в табл. 1.

Таблица 1 Описание параметров оператора SELECT

Параметр	Описание
DISTINCT | ALL	DISTINCT - предотвращает дублирование данных, которые будут извлечены. ALL (по умолчанию) - приведет к извле­чению всех данных
{* |< величина> [,< величина >...]}	Звездочка (*) означает, что надо извлекать все колонки из указанных таблиц. <величина> [,<величина> ...] -извлекает список указанных колонок, пе­ременных или выражений
INTO : Переменная [, : Переменная ... ]	Используется только в триггерах и храни­мых процедурах для операторов SELECT, возвращающих не более одной строки. Указывается список переменных, в кото­рые извлекаются величины
FROM <tableref> [,<tableref>... ]	Указывает список таблиц, просмотров и хранимых процедур, из которых извлека­ются данные. Список может включать со­единения и соединения могут быть вло­женными
table	Имя существующей в базе данных табли­цы
view	Имя существующего базе данных про­смотра
procedure	Имя существующей хранимой процедуры, предназначенной для использования в операторе SELECT
Псевдоним	Короткое альтернативное имя для табли­цы, просмотра или колонки. После описа­ния в <tableref>, псевдоним может ис­пользоваться для ссылок на таблицу или просмотр
join_type	Задает тип соединения, которое может быть внутренним или внешним
Параметр	Описание
WHERE <условие поиска>	Указывает условие, которое ограничивает количество извлекаемых строк
GROUP BY Колонка [, Колонка ...]	Разбивает результат запроса на группы, содержащие все строки с идентичными значениями указанными в списке колонок
HAVING <условие поиска>	Использует совместно с GROUP BY. Зада­ет условие, которое ограничивает количе­ство возвращаемых групп
UNION [ALL]	Объединяет результаты нескольких запро­сов. Все запросы должны извлекать оди­наковое количество столбцов, тип данных каждого столба первого запроса должен совпадать с типом данных других запро­сов, имена столбцов в разных запросах мо­гут отличаться. Необязательный параметр ALL указывает, что надо выводить одина­ковые строки
ORDER BY <список сортировки>	Указывает колонки, по которым будет производиться сортировка извлекаемых строк. Можно указывать либо имена коло­нок, либо их порядковые номера в списке извлекаемых колонок. Если указать ASC, то строки будут выдаваться в порядке воз­растания значений сортируемых полей, если DESC - в порядке убывания значений

Как видно из синтаксиса оператора SELECT, обязательными являются только предложение SELECT с перечнем выдаваемых колонок и предложение FROM.

Пример простейшего оператора SELECT:

-- Выдать перечень всех служащих:

SELECT * FROM Employee;

Пример. Подсчитаем количество регионов (штатов) в стране США:

SELECT COUNT(*) FROM REFREG WHERE CODCTR = 'USA';

--Выбрать список сотрудников только одной организации

SELECT CODPEOPLE AS "Код сотрудника",

DUTIES AS "Должность",

SALARY AS "Оклад"

FROM STAFF WHERE CODORG = 11;

--Находит среднюю зарплату сотрудников этой организации и месячный фонд заработной платы.

SELECT AVG(SALARY), SUM(SALARY)

FROM STAFF

-- Выберем из этого списка только тех сотрудников, чей оклад находится в диапазоне от 12 000 до 23 700:

SELECT CODPEOPLE AS "Код сотрудника",

DUTIES AS "Должность",

SALARY AS "Оклад"

FROM STAFF

WHERE CODORG =11 AND

SALARY BETWEEN 12000 AND 23700;

--Вывести сотрудников фамилия которых должна заканчиваться на "ОВ".

SELECT COD AS "Код",

NAME3 AS "Фамилия",

NAME1 AS "Имя",

NAME2 AS "Отчество",

BIRTHDAY AS "Дата рождения"

FROM PEOPLE

WHERE NAME3 LIKE '%OB'

ORDER BY NAME3;

Ниже приведено несколько упрощенных вариантов синтаксиса оператора SELECT, помогающих научиться составлять простые запросы.

Упрощенный синтаксис внутреннего соединения (стандарт SQL-92):

SELECT Колонка [, Колонка ...] | *

FROM <tableref_left> [INNER] JOIN <tableref_right>

[ON <условие поиска>]

[WHERE <условие поиска>];

Упрощенный синтаксис внешнего соединения:

SELECT Колонка [, Колонка ...] | *

FROM <tableref_left>

{LEFT | RIGHT | FULL} [OUTER] JOIN

<tableref_right>

[ON <условие поиска>]

[WHERE <условие поиска>];

Упрощенный синтаксис использования подзапроса:

SELECT [DISTINCT] Колонка [, Колонка ...]

FROM <tableref> [, <tableref> ...]

WHERE

{expression {[NOT] IN | <оператор сравнения>}

| [NOT] EXISTS

}

(SELECT [DISTINCT] Колонка [, Колонка ...]

FROM <tableref> [, <tableref> ...]

WHERE <условие поиска>

) ;

Задание

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

1. Изучить синтаксис оператора SELECT.

2. Зарегистрировать свою базу данных "***.fdb" и подключиться к ней в программе "IBExpert".

3. Выполнить в окне "SQL Редактор" двадцать запросов к своей базе данных "***.fdb" и сохранить их в файле "***_20.sql" в рабочей папке. Каждый запрос должен иметь комментарии с описанием, а файл в целом должен иметь комментарии со сведениями об авторе и дате создания.

4. Создать аналогично предыдущим лабораторным работам рабочую папку и назвать ее "ЛР7".

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

6. Открыть в приложении "IBExpert" этот сценарий, исправить комментарии и сделать, чтобы база данных теперь создавалась в папке "ЛР7".

7. Выполнить сценарий и сохранить его в папке "ЛР7".

8. Зарегистрировать созданную базу данных в программе "IBExpert" и подключиться к ней.

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

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

Ход работы

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

1. Подключиться к базе данных и выполнить команду "Инструменты> SQL Редактор" (F12). В результате откроется окно "SQL Редактор" (рис. 1).

Рис. 1: Окно выполнения запросов

2. Ввести в поле на вкладке "Edit" текст запроса.

3. Нажать на панели инструментов кнопку [Выполнить] (F9).

4. Если запрос правильный, то в результате произойдет его выполнение и ре­зультат будет отображен на вкладке "Результаты" (рис. 2).

Рис. 2: Окно с результатом выполнения запроса

2. Количество извлеченных в результате выполнения запроса строк отобра­жается над сеткой с данными справа. Чтобы узнать, сколько всего строк соответствуют выполненному операто­ру, надо перейти в конец отображаемого набора данных.

3. Чтобы выполнить другой запрос, надо вернуться на вкладку "Редактор", за­менить содержимое редактора на новый запрос и повторить те же действия. К тексту ранее выполнявшихся правильных запросов можно вернуться, если перейти на вкладку "История запросов", либо находясь на вкладке "Редактор" нажимать кнопки [Предыдущий запрос] и [Сдедующий запрос].

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

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

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

1. Перечень запросов к базе данных "***.fdb", выполненных при выполнении этой лабораторной работы с краткими пояснениями и данными о количестве строк, выданных при выполнении каждого запроса.

2. Словесное описание двадцати запросов к разработанной в предыдущих лабораторных работах базе данных.

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

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

Лабораторная работа №8. Представления

Цель работы

Изучить используемые в реляционных СУБД операторы создания и удаления представлений. Получить навыки работы с представлениями с помощью команд SQL и с помощью программы "IB Expert".

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

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

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

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

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

Представления (Views)

Представление представляет собой запрос на выборку, которому присвоили имя и сохранили в базе данных. Другими словами это виртуальная таблица, которая реально не содержит данных, а всяких раз, когда требуется его содержимое, происходит выполнение запроса.

Когда СУБД встречает в инструкции SQL ссылку на представление, она находит его определение, сохраненное в базе данных, преобразует пользовательский запрос, ссылающийся на представление, в эквивалентный запрос к исходным таблицам представления и выполняет этот запрос. Таким образом, СУБД создает иллюзию существования представления в виде отдельной таблицы.

Причины использования представлений

1. Они изолируют реализацию хранения данных от пользователя. Если клиенты будут работать с данными через представление, то у разработчика базы данных появляется возможность менять запрос, лежащий в основе представления, а клиент ничего не будет замечать.

2. С их помощью можно ограничить доступ к данным, разрешая определенным пользователям иметь права на чтение/изменение данных в представлении, но даже не иметь понятия о таблицах, лежащих в основе этого представления.

3. Они упрощают доступ к базе данных, показывая пользователям структуру хранимых данных в наиболее подходящем для них виде.

Представления могут основываться на данных из нескольких таблиц и других представлений. Представления можно использовать как обычные таблицы. Оператор SELECT, образующий представление, может делать то же, что и обычный оператор SELECT, кроме включения предложения ORDER BY и использования в качестве источника данных хранимых процедур.

Синтаксис оператора создания представления:

CREATE VIEW Имя_Представления [(Колонка [, Колонка ...])]

AS <select> [WITH CHECK OPTION];

<select> - оператор SELECT, который выбирает данные включаемые в представление

WITH CHECK OPTION - если указать этот необязательный параметр, то при вставке или изменении строки представления будет проверяться условие "попадания" этой строки в представление. Если новая или измененная запись не удовлетворяет условиям запроса, на котором основано представление, то операция будет отменена и возникнет ошибка.

Пример создания представления:

CREATE VIEW ViewStudent (St_ID, LastName, FirstName)

AS SELECT Pr_ID, Pr_LastName, Pr_FirstName

FROM Person, Student

WHERE Pr_ID = St_ID;

Чтобы изменить какое-либо представление, его надо удалить и создать заново. При удалении представления необходимо также удалить все зависимые от его объекты - триггеры, хранимые процедуры и другие представления. Программа "IB Expert" пересоздает зависимые объекты автоматически.

Пример удаления представления:

DROP VIEW ViewStudent;

Модифицируемые представления

Для некоторых представлений вставку, удаление или обновление строки можно преобразовать в эквивалентные операции по отношению к исходным таблицам этого представления. Могут существовать представления, для которых добавление, удаление или обновление строк не имеет смысла. Такие представления можно только читать.

Содержимое представления может изменяться командами модификации, которые будут автоматически перенаправлены к базовой таблице. Чтобы представление было модифицируемым, оно должно быть создано на основе одной таблицы (или другого модифицируемого представления), а запрос, на котором основано представление, не должен содержать подзапросов, агрегатных функций, UDF, хранимых процедур, предложений DISTINCT, GROUP BY и HAVING. Если выполняются все эти условия, то представление автоматически становится модифицируемым, т. е. для него можно выполнять запросы DELETE, INSERT и UPDATE, которые будут изменять данные в таблице-источнике.

Чтобы сделать модифицируемым представление, которое нарушает любое из вышеперечисленных условий, применяется механизм триггеров. Триггеры для модификации представления должны срабатывать при событи­ях BEFORE DELETE, BEFORE UPDATE и BEFORE INSERT. В теле тригге­ров необходимо описать, что должно происходить с данными при удалении, изменении и вставке.

Задание

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

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

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

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

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

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

6. Выполнить сценарий и сохранить его в папке "ЛР8".

7. Зарегистрировать созданную базу данных в программе "IB Expert" и под­ключиться к ней.

8. Создать в своей базе данных еще одно представление в диалоговом режи­ме, которое не должно быть модифицируемым. Оно должно называться "TestView" и основываться, например, на нескольких таблицах или других представлениях. Добавить в диалоговом режиме к этому просмотру триг­геры для разрешения выполнения обновления. Перенести в сценарий опе­раторы создания этого представления и триггеров, которые автоматически сгенерирует "IBExpert".

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

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

Ход работы

Для создания представлений в диалоговом режиме программы "IB Ex­pert" необходимо выполнить следующие действия:

1. Подключиться к базе данных и выполнить команду главного меню "База данных > Новый просмотр". В результате откроется окно "View", в котором будет содержаться заготовка оператора создания представления (рис. 1).

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

2. Изменить в поле на вкладке "SQL" текст оператора создания представления для создания нужного представления, а затем нажать кнопку [Compile View] (Ctrl+F9). В результате откроется диалоговое окно (рис. 2), в котором надо подтвердить (кнопка [Commit]) или отменить (кнопка [Rollback]) выполнение операции.

Рис. 2: Диалог создания представления

3. Представление, команда создания которого приведена на рис. 2 не является модифицируемым. Чтобы сделать его модифицируемым, создадим для него триггер позволяющий изменять поле "id" (изменять поле "text" не имеет смысла).

4. Чтобы создать триггер у имеющегося представления, необходимо в окне "View" перейти на вкладку "Триггеры", нажать правую кнопку мыши на типе события, при котором должен срабатывать триггер, и в контекстном меню выбрать команду "Новый триггер" (рис. 3). В результате откроется окно "Триггер", в котором будет находиться заготовка оператора создания триггера. Используя эту заготовку, следует ввести правильный оператор создания триггера и нажать кнопку [Компилировать триггер] (Ctrl+F9).

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

Пример триггера, которые для представления "TestView" позволяет модифицировать поле "id" приведен на рис. 4.

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

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

Отчет о выполнении лабораторной работы №8 необходимо оформить на листах формата A4. Отчет должен содержать описание и результаты работы, представляемые в следующей последовательности:

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

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

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

Лабораторная работа №9. Хранимые процедуры

Цель работы

Изучить виды используемых в Firebird хранимых процедур. Получить навыки работы с хранимыми процедурами с помощью команд SQL и с помощью программы "IB Expert".

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

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

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

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

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

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

Хранимая процедура - это откомпилированная во внутреннее представление сервера СУБД подпрограмма, хранящаяся в базе данных. Хранимые процедуры пишутся на специальном языке хранимых процедур и триггеров, в котором имеются операторы присваивания, ветвлений и циклов, и в которых можно использовать операторы SQL, такие как INSERT, DELETE, UPDATE и SELECT.

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

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

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

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

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

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

С помощью входных параметров внутрь хранимой процедуры можно передать значения, которые будут использоваться в ходе выполнения процедуры. С помощью выходных параметров хранимые процедуры возвращают значения, вычисленные в ходе выполнения процедуры.

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

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

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

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

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

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

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

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

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

<блок> = BEGIN

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

END

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

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

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

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

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

1. Производительность - перенос на сервер часто используемых действий приводит к существенному повышению производительности.

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

Виды хранимых процедур в 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. Создать аналогично предыдущим лабораторным работам рабочую папку и назвать ее "ЛР9".

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

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

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

5. Добавить в сценарий операторы создания хранимых процедур и других объектов, которые могут потребоваться. Создать не менее трех хранимых процедур, реализующих бизнес-правила в соответствии с выданным индивидуальным заданием. Для каждой хранимой процедуры должны присутствовать комментарии, поясняющие выполняемые операции.

6. Выполнить сценарий и сохранить его в папке "ЛР9".

7. Зарегистрировать созданную базу данных в программе "IBExpert" и подключиться к ней.

8. Создать в своей базе данных по одной хранимой процедуре каждого вида в диалоговом режиме. Они должны называться "TestProcedurel" и "TestProcedure2" и иметь входные и выходные параметры. Скопировать в отчет сценарий создания этих процедур, который сгенерирует "IBExpert".

9. Выполнить в окне "SQL Редактор" по одному запросу с каждой созданной хранимой процедурой. Поместить выполненные запросы в отчет, добавив комментарии и сведения о результате их выполнения.

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

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

Ход работы

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

1. Подключиться к базе данных и выполнить команду главного меню "База данных>Новая процедура". В результате откроется окно "Процедура" для создания процедуры (рис. 1).

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

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

3. При нажатой кнопке [Вставить параметр] добавить входные параметры. Для этого находясь в сетке, расположенной в средней части окна, нажать клавишу [Insert] или [Стрелка вниз]. После этого ввести в новой строке имя входного параметра (на рис. 1 имя входного параметра - "PARAM1") и с помощью показанного на рис. 1 выпадающего списка выбрать его тип.

4. При нажатой кнопке [Выходные параметры] таким же образом добавить выходные параметры.

5. При необходимости точно так же добавить локальные переменные (нажать для этого кнопку [Переменные]).

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

7. Нажать кнопку [Компилировать процедуру] (Ctrl+F9).

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

Переключение между двумя режимами просмотра и редактирования хранимой процедуры, показанными на рис. 2 и 3 производится кнопкой [Включить/Выключить «Ленивый режим»].

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

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

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

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

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

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

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

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

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

Лабораторная работа №10. Подключение бд в Delphi