Просмотры

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

Разработка бизнес-логики на стороне SQL-сервера

Цель работы

1. Научиться созданию бизнес-логики на стороне СУБД Firebird.

2. Изучить PSQL.

3. Научиться созданию представлений (просмотров).

4. Научиться созданию хранимых процедур.

5. Научиться созданию исключений.

6. Научиться созданию генераторов и триггеров для реализации бизнес-логики.

Порядок выполнения работы

1. Используя базу данных (полученную при выполнении лабораторной работы №2) создать модифицируемые и не модифицируемые представления (не менее 5), при этом необходимо:

· Выполнить запросы к таблицам БД.

· Выполнить объединения данных.

· Создать псевдонимы.

· Выполнить группировку и упорядочение данных.

· Научиться использовать в SQL-запросах агрегатные функции.

2. С использованием утилиты IBExpert выполнить разработанные представления.

3. Создать хранимые процедуры:

Хранимые процедуры выполнения (не менее 3), для ввода, модификации и удаления информации в таблицах БД.

Хранимые процедуры выборки (не менее 5).

4. С использованием утилиты IBExpert выполнить разработанные хранимые процедуры.

5. Создать необходимые исключения (не менее 2 исключений).

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

7. Создать генераторы и триггеров для реализации бизнес-логики (не менее 3 триггеров). При разработке триггеров обязательно использовать индикаторы фазы: BEFORE и AFTER, индикаторы события DML: DELETE, INSERT, UPDATE и логические контекстные переменные INSERTING и DELETING

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

9. Составить электронный отчет о проделанной работе.

Содержание отчета

1. Титульный лист.

2. Теоретическая часть (написать самостоятельно, что изучено, понято и сделано от 1 страницы).

3. Логическая и физическая модели данных полученные с помощью ERwin (из лабораторной работы №1).

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

5. Разработанные SQL-скрипты.

6. Копии экрана с исходными данными (в таблице ~ 10 -15 записей), иллюстрирующие работу.

7. Пояснения к каждой копии экрана.

Теоретическая часть

Разработка бизнес-логики на стороне SQL-сервера

Просмотры

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

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

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

Команда создания представления имеет следующий синтаксис:

CREATE VIEW name [(view_col [, view_col])]

AS < select>

[WITH CHECK OPTION];

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

Простое представление, которое вернет пользователю всю таблицу, создается несложным SQL-запросом:

CREATE VIEW MYJIEW

AS SELECT * FROM EMPLOYEE;

Также представление может возвращать поля из нескольких таблиц сразу, как показано ниже:

CREATE VIEW ENTRY_LEVEL_WORKERS AS

SELECT JOB CODE, JOB TITLE, FIRST NAME, LAST NAME

FROM JOB, EMPLOYEE

WHERE JOB.JOB_CODE = EMPLOYEE.JOB_CODE AND SALARY < 15000:

Рассмотрим несколько примеров создания просмотров с использованием утилиты IBExpert при разработке информационной системы «Аренда недвижимости»(лабораторная работа № 2).

Пример 1. Создать просмотр, который позволяет просмотреть данные таблицы LEASE (Арендный договор) базы данных Realt.fdb. Ниже, приведен SQL-скрипт просмотра LEASE_INFO.

CREATE VIEW LEASE_INFO(

ID,

O_FIO,

T_FIO,

REALTY_ADDR,

V_PRICE,

V_DATE)

AS

select lease.id, owner.fio, tenant.fio, realty.adres, lease.price, lease." DATE"

from lease, owner, tenant, realty

where owner.id=lease.id_owner and tenant.id=lease.id_tenant

and realty.id=lease.id_realty

;

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

Рисунок 1. Данные, полученные в результате выполнения просмотра LEASE_INFO с использование утилиты IBExpert

Рисунок 2. Форма, полученная в результате выполнения просмотра LEASE_INFO

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

1.1. Типы просмотров

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

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

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

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

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

Команда WITH CHECK OPTION определяет правила, на основе которых модифицируются данные при помощи представления. Представления, созданные с использованием этой команды, указывают серверу на необходимость проверки вставленных или обновленных записей на соответствие условию проверки, определенному выражением WHERE. Значения могут быть добавлены через представления только в те поля, которые определены в представлении. В остальные поля будет помещено значение NULL. В качестве примера можно привести запрос, создающий обновляемое представление на базе таблицы СОМРАСТ_ DISC по всем ее полям:

CREATE VIEW СОМРАCT_VIEW

AS

SELECT * FROM COMPACT_DISC

WHERE PROIZVODITEL = 'BASF'

WITH CHECK OPTION

После этого к представлению можно добавить новую запись:

INSERT INTO COMPACT_VIEW

VALUES ('Музыка света'.'5, 'SOFT', 'BASF'):

Пример 2. Создать просмотр, который позволяет вывести список цен по арендным договорам. Ниже приведен SQL-скрипт просмотра RENT_COST.

CREATE VIEW RENT_COST(

ID,

PRICE)

AS

select lease.id, lease.price from lease

;

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

Рисунок 4. Данные, полученные в результате выполнения просмотра RENT_COST

Просмотр RENT_COST можно модифицировать, как показано на рисунке 5.

Результат выполнения модифицированного просмотра RENT_COST приведен на рисунке 6.

Рисунок 5. SQL-скрипт модификации просмотра RENT_COST

Рисунок 6. Данные полученные в результате выполнения модифицированного просмотра RENT_COST