Работа с СУБД. Определение набора процедур.

Проектировщики БД и администратор предоставляют готовый набор скриптов и процедур на языке SQL, которые затем разработчик клиента внедряет как пользовательские функции. Работой по написанию и отлаживанию процедур занимается проектировщик БД.

Однако, в качестве учебной модели, была создана собственная структура БД, определен ряд процедур.

Работа велась с СУБД Firebird. Вспомогательное ПО для создания и модификации БД: утилита Firebird – isql, а так же IBExpert.

Разрабатываемая БД имеет 4 таблицы:

- основания (CAUSES). Содержит описание оснований к аресту, применению санкций и так далее;

- сотрудники (EMPLOYEES). Содержит перечень приставов с необходимой информацией. При регистрации в СУБД пользователя, данные из таблицы поступают во все необходимые отчеты и документы в качестве информации о лице, которое вело производство;

- производства (FABRICATIONS). Информация о производствах, которые ведет каждый пристав. Имеет поля субъект производства(SUBJ_FIO), основание к производству, дату начала производства;

- отчеты (REPORT). По завершении производства сюда регистрируется вся необходимая информация.

Таблица FABRICATIONS имеет внешний ключ, ссылающийся на первичный ключ таблицы CAUSES по полю “Идентификатор Основания”. Так же таблица REPORT имеет два внешних ключа, ссылающихся на первичный ключ FAB_ID таблицы FABRICATIONS и первичный ключ EMP_ID таблицы EMPLOYEES.

В качестве схемы для данных вывода была составлено отношение REPLY (Резюме, Оюзор), выполняющее сложное соединение по ключам 4 таблиц.

Отношение было организовано в виде просмотра (View), специального объекта СУБД Firebird (поддерживается и во многих других СУБД).

Код просмотра REPLY на языке SQL представлен ниже.

CREATE VIEW REPLY(

REPLY_REPORT_ID,

REPLY_CAUSE,

REPLY_SFIO,

REPLY_EMPFIO,

REPLY_FABDATE,

REPLY_REPDATE,

REPLY_SUMMARY)

AS

select rep_reply.report_id,fab_reply.fab_reply_cause, fab_reply.fab_reply_subject, rep_reply.fab_empfio, fab_reply.fab_reply_dat,

rep_reply.rep__reply_date, rep_reply.reply_summary from fab_reply JOIN rep_reply ON fab_reply.fab_reply_fab_id=rep_reply.fab_id;

Здесь исползуется соединение двух просмотров по общему полю fab_id. Два задействуемых просмотра выполняют попарные соединения (JOIN) таблиц FABRICATIONS, CAUSES и REPORT, FABRICATIONS соответственно.

В терминологии SQL-89 и SQL-92 просмотр явлется стандартным типом таблицы, он также называется “просматриваемой” или “виртуальной таблицей”. Он характеризуется как “виртуальный”, потому что вместо того, чтобы хранить табличный объект и выделять страницы для хранения данных, сервер Firebird сохраняет только описание метаданных объекта. Оно содержит уникальный идентификатор, список спецификаций столбцов и компилированный оператор SELECT для поиска описанных в этих столбцах данных во время выполнения [2].

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

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

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

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

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

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

Далее, на основе просмотров строятся хранимые процедуры (PROCEDURE) для выборки данным по полям (ФИО пристава, основание, дата начала, дата завершения и так далее).

Процедура является самостоятельной программой, написанной на языке PSQL Firebird, скомпилированной интерпретатором во внутренний двоичный язык Firebird и сохраненной как исполняемый код в метаданных базы данных. Однажды скомпилированная, хранимая процедура может быть вызвана непосредственно из приложения или другого модуля PSQL с использованием оператора EXECUTE PROCEDURE или SELECT в соответствии с заданным стилем процедуры[2].

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

Ниже приведен код процедуры выбора резюме по задаваемому диапазону даты начала производства (reply_fab_date):

begin

FOR

SELECT reply.reply_report_id

from reply WHERE (reply.reply_fabdate>=:low_date AND reply.reply_fabdate<=:high_date) INTO:IDLIST_REP_ID

DO

BEGIN

suspend;

end

end

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

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

Дата добавления: 2015-09-05