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

 

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

Первая процедура будет выводить список книг, находящихся в библиотеке. Для этого зададим запрос на выборку списка книг, в поле «BOOKS.ID_ABONENT» которых стоит значение NULL.

Для создания новой процедуры выберем Procedures\New Procedure(Процедуры\Новая процедура). Выберем Output Parameters (Выходные Параметры) затем Insert parameter/variable (Добавить параметр/переменную). Добавим параметр «NAME_BOOKS VARCHAR(30)» под название книги, а также ещё два – под наименование и город издательства – соответственно «NAME_PUBLISHERS VARCHAR(20)» и «CITY_PUBLISHERS VARCHAR(20)».

Листинг первой процедуры «BOOKS_LIBRARY»:

CREATE PROCEDURE BOOKS_LIBRARY

returns (

city_publishers varchar(20) character set win1251,

name_publishers varchar(20) character set win1251,

name_books varchar(30) character set win1251)

as

begin

for

select distinct books.name, publishers.name, publishers.city

from books inner join publishers on books.id_publishers = publishers.id_publishers

where books.id_abonent iS NULL

into: name_books,: name_publishers,: city_publishers

do suspend;

end

В данной процедуре вместо обычного запроса на выборку SELECT использована конструкция «FOR SELECT... INTO... DO...», которая производит обработку возвращаемого набора записей в цикле. Иначе если SELECT возвратит более одной строки, то возникнет ошибка «multiple rows in singleton select». Здесь же после каждой возвращаемой строки производится принудительная выдача параметров, после чего они принимают новые значения при следующей итерации цикла и т. д. пока не будут выданы все строки, удовлетворяющие условию запроса.

Вторая процедура будет выводить список книг, выданных за указанный период (входные параметры – начальная и конечная дата). Зададим входные параметры «DATE_1 DATE» и «DATE_2 DATE». Зададим выходные параметры: «NAME_BOOKS VARCHAR (30)», «NAME_PUBLISHERS VARCHAR(20)» и «CITY_PUBLISHERS VARCHAR(20)».

Листинг процедуры «BOOKS_LIST_PERIOD»:

CREATE PROCEDURE BOOKS_LIST_PERIOD (

date_1 date,

date_2 date)

returns (

city_publishers varchar(20) character set win1251,

name_publishers varchar(20) character set win1251,

name_books varchar(30) character set win1251)

as

declare variable var_date date;

begin

for

select distinct books.name, publishers.name, publishers.city, books.date_issue

from books inner join publishers on books.id_publishers = publishers.id_publishers

into: name_books,: name_publishers,: city_publishers,:var_date

do if (var_date between date_1 and date_2) then suspend;

end

В этой процедуре в операторе циклической выборки для каждой записи, полученной в результате выполнения оператора выборки «SELECT», проверяется, лежит ли значение одной из возвращаемых SELECT-ом переменной var_date между двумя введёнными, заданными входными параметрами date_1 и date_2. Если да, то производится выдача процедурой значений выходных параметров при помощи оператора suspend.

Создадим третью процедуру, которая будет выводить количество книг и средний тираж по издательствам. Здесь выходные данные будут представлять собой значения функций агрегирования, вычисляемые для каждой группы по значению поля «PUBLISHERS. NAME».

Листинг процедуры «KOL_BOOKS_TIRAZ»:

CREATE PROCEDURE KOL_BOOKS_TIRAZ

returns (

publishers_name varchar(20) character set win1251,

avg_tiraz integer,

count_ integer)

as

begin

for

select distinct publishers.name, count(publishers.id_publishers), AVG(books.tiraz)

from books inner join publishers on books.id_publishers = publishers.id_publishers

group by publishers.name

into: publishers_name,: count_,: avg_tiraz

do suspend;

end

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

· publishers_name varchar(20) character set win1251 – строковой параметр для значений имени издательства;

· avg_tiraz integer – параметр для среднего значения тиража книги каждой группы.

· count_ integer – параметр для выдачи значения количества строк каждой группы.

В данной процедуре осуществляется выборка значений поля «PUBLISHERS.NAME», количества возвращаемых строк и среднего значения по полю «BOOKS.TIRAZ» для каждого значения поля «PUBLISHERS.NAME».

Теперь структура базы данных готова. Заполним таблицы некоторыми записями:

 

Таблица «Издательства» (Publishers)

ID_Publishers	Name	City
1	Питер	С.- Петербург
2	ДиаСофт	Киев
3	КОРОНА принт	С.- Петербург
4	Финансы и статистика	Москва

 

Таблица «Читатели» (Readers)

ID_ abonent	FIO	Telephone	Address
1	Иванов Вадим	12345678	Москва ул. 1 д.1 кв. 1
2	Петров Борис	11111111	Орел ул. 1 д.1 кв. 1
3	Сидоров Иван	22222222	Курск ул. 1 д.1 кв. 1
4	Кузнецов Артем	12121212	Воронеж ул.1 д.1 кв.1

 

Таблица «Книги» (Books)

ID_ Books	Name	ID_ Publishers	K_ pages	Cover	Tiraz	ID_ abonent	Date_issue
1	Эффективная работа с СУБД	1	704	твёрдый	6000	1	01.01.2009
2	Delphi. Разработка баз данных	1	477	твёрдый	5000	1	01.01.2009
3	Базы данных и приложения	2	592	твёрдый	7000	2	11.01.2009
4	Базы данных	2	416	твёрдый	5000	NULL	NULL

 

Создание клиентского приложения баз данных

 

Создадим клиентское приложение в ИСР Delphi, используя технологию доступа к данным InterBase eXpress (IBX).

Выберем File/New/Application (Файл/Новое/Приложение), затем добавим модуль данных для компонентов доступа к данным – File/New/Data Module (Файл/Новый/Модуль данных). Компоненты доступа к данным расположены на страницеData AccessПалитры компонентов. Компоненты отображения данных расположены на страницеData Controls Палитры компонентов. Компоненты, используемые в технологии InterBase eXpress распологаются на странице InterBase, а компоненты для создания отчётов – QReport.

Поместим на модуль данных компонент TIBDatabase. Укажем в свойстве DatabaseName полный путь (включая имя сервера) к выбранному файлу БД – «C:\01\LIBRARY.FDB».

Поместим следующие компоненты на форму модуля данных:

· компонент IBTransaction

· три IBDataSet,

· три DataSource

Подключимся к базе данных. Выделим компонент TIBDatabase и выберем из контекстного меню Database Editor.… В этом окне укажем User Name = SYSDBA, Character Set = WIN1251. Затем установим свойство Connected компонента IBDatabase1 равным True и свойство DefaultTransaction компонента IBDatabase1 равным IBTransaction1.

Зададим управление транзакциями. Сделаем активным компонент IBTransaction1, для чего его свойству Active придадим значение True. Вызовем редактор Transaction Editor..., и в появившемся диалоговом окне выберем уровень изоляции транзакций – Read Committed.

Установим значения свойств:

· DefaultAction – TACommitRetaining

· DefaultDatabase – IBDatabase1

· Params – read_committed

rec_version

nowait

· Active – True

Перейдём к компоненту IBDataSet1. Переименуем его на BOOKS_DataSet (свойство Name). Укажем базу данных – DataBase = IBDataBase1 и компонент обработки транзакций – Transaction = IBTransaction1. Укажем в свойстве SelectSQL текст основного запроса: «select* from BOOKS». При помощи свойства GeneratorField выбираем поле, значение которого присваивается генератором и сам генератор. Активируем компонент: Active – True. Вызовем редактор компонента Dataset Editor.... Выберем из списка Table Name таблицу и нажмём кнопку Get Table Fields (Получить поля таблицы). В списке Key Fields (Ключевые поля) выделим поле «ID_BOOKS», которое будут формировать условие WHERE в запросах. После нажатия на кнопку Generate SQL автоматически сгенерируются значения свойств DeleteSQL, InsertSQL, ModifySQL, RefreshSQL. Эти значения станут равны:

DeleteSQL:

delete from BOOKS

where

ID_BOOKS =:OLD_ID_BOOKS

InsertSQL:

insert into BOOKS

(ID_BOOKS, NAME, ID_PUBLISHERS, K_PAGES, COVER, TIRAZ, ID_ABONENT, DATE_ISSUE)

values

(:ID_BOOKS,:NAME,:ID_PUBLISHERS,:K_PAGES,:COVER,:TIRAZ,:ID_ABONENT,

:DATE_ISSUE)

ModifySQL:

update BOOKS

set

ID_BOOKS =:ID_BOOKS,

NAME =:NAME,

ID_PUBLISHERS =:ID_PUBLISHERS,

K_PAGES =:K_PAGES,

COVER =:COVER,

TIRAZ =:TIRAZ,

ID_ABONENT =:ID_ABONENT,

DATE_ISSUE =:DATE_ISSUE

where

ID_BOOKS =:OLD_ID_BOOKS

RefreshSQL:

Select

ID_BOOKS,

NAME,

ID_PUBLISHERS,

K_PAGES,

COVER,

TIRAZ,

ID_ABONENT,

DATE_ISSUE

from BOOKS

where

ID_BOOKS =:ID_BOOKS

Аналогично зададим значения свойств двум остальным компонентам IBDataSet.

У каждого компонента DataSource в свойстве Dataset укажем название соответствующего ему компонента IBDataSet.

Создадим три формы для отображения таблиц. На каждую форму поместим компоненты DBGrid и DBNavigator. У компонентов DBGrid и DBNavigator в свойстве DataSource укажем соответствующий компонент DataSource.

Поиск данных

 

Для поиска данных используется функция

function LocateNext(const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions): Boolean;

где KeyFields – список полей, по которым производится поиск (имена полей разделяются точкой с запятой), KeyValues – список значений, сравниваемых со значениями этих полей (значения разделяются запятой), TLocateOptions – параметры поиска, где loCaselnsensitive означает поиск без учета регистра (заглавные и малые символы), а loPartialKey. – значения полей для поиска даны не полностью. Функция LocateNext позволяет находить несколько записей, удовлетворяющих условиям поиска, для отображения очередной записи следует вызвать функцию ещё раз. Функция возвращает значение логического типа, равного TRUE (ИСТИНА), если найдена подходящая запись, и FALSE (ЛОЖЬ) в противном случае.

На форме отображения и редактирования данных из таблицы BOOKS для поиска разместим следующие компоненты:

· Edit1 – для ввода пользователем значений KeyValues.

· Button1 – для активации поиска.

· Несколько компонентов CheckBox для указания списка полей для поиска, т. е. значения KeyFields.

В процедуре – обработчике события нажатия на кнопку Button1 сначала производится сбор строки списка полей KeyFields в соответствии с указаниями флажков CheckBox. Затем это значение вместе со значением поля Edit1 отправляется в функцию LocateNext через пользовательскую функцию loc(Fields,Values), описанную в модуле Unit2 (в модуле Unit2 описан класс TDataModule2 (форма модуля данных) в котором определён и компонент «BOOKS_DataSet: TIBDataSet;»).

Аналогично в программе организован поиск в таблицах PUBLISHERS и READERS.

Фильтрация данных

 

Зададим фильтрацию значений в таблице BOOKS.

На форме таблицы BOOKS поместим компоненты CheckBox для того, чтобы пользователь имел возможность включать и выключать фильтр по отдельным полям. Фильтр производится только по тем полям, для которых выбраны соответствующие им переключатели CheckBox, для этого в программе определены логические переменные (в модуле Unit2) для указания, следует ли фильтровать записи по введённому пользователем значению соответствующего поля. Для включения фильтра служит кнопка «Включить фильтр» где производится присвоение переменным (модуля Unit2) введённых значений указанных полей, после чего включается фильтр заданием свойству BOOKS_DataSet.Filtered значения True. После того, как свойству Filtered компонента TIBDataSet будет присвоено значение True то производится переоткрытие набора данных, но при этом выполняется метод OnFilterRecord этого компонента. Процедура – обработчик этого метода определена следующим образом:

procedure TDataModule2.BOOKS_DataSetFilterRecord(DataSet: TDataSet; var Accept: Boolean);

где DataSet соответствующий набор данных. Эта процедура выполняется для каждой записи и если возвращаемый этой процедурой параметр Accept равен True, то текущая запись отображается, в противном случае – нет. Пример фильтра:

if (DataSet['NAME'] <> Name_) then Accept:= False;

где NAME – название поля, Name_ – переменная, значение которой сравнивается со значением этого поля текущей записи.

В программе определён следующий фильтр:

procedure TDataModule2.BOOKS_DataSetFilterRecord(DataSet: TDataSet;

var Accept: Boolean);

begin

Accept:= True;

if P1 then if (DataSet['NAME'] <> Name_) then Accept:= False;

if P2 then if (DataSet['K_PAGES'] < K_Pages_1) then Accept:= False;

if P3 then if (DataSet['K_PAGES'] > K_Pages_2) then Accept:= False;

if P4 then if (DataSet['DATE_ISSUE'] < Date_1) then Accept:= False;

if P5 then if (DataSet['DATE_ISSUE'] > Date_2) then Accept:= False;

if P6 then if (DataSet['ID_PUBLISHERS'] <> Id_pub) then Accept:= False;

if P7 then if (DataSet['COVER'] <> Cov) then Accept:= False;

if P8 then if (DataSet['TIRAZ'] < Tiraz_1) then Accept:= False;

if P9 then if (DataSet['TIRAZ'] > Tiraz_2) then Accept:= False;

if 10 then if (DataSet['ID_ABONENT'] <> Id_ab) then Accept:= False;

end;

Логические переменные P1 – P10 хранят значения, соответствующие выбранным флажкам CheckBox, и если флажок выбран, то соответствующая ему переменная будет равна True, а значит, будет производиться фильтр по значению этого поля. Изначально переменная Accept принимает значение True, т. е. считается, что строка проходит, если не будет показано невыполнение хотя бы одного условия фильтрации. Поэтому здесь проверяется невыполнение каждого из заданных условий (например вместо «DataSet['NAME'] = Name_» проверяется «DataSet['NAME'] <> Name_» и ставится не «Accept:= True» в случае выполнения этого условия, а «Accept:= False»). Достаточно невыполнения одного условия, чтобы строка не прошла, т. е. переменная Accept принимает значение False.

Аналогично зададим фильтр и для других таблиц.

Вывод отчёта

 

Организуем вывод отчётов в программе. Для начала создадим запрос на выборку записей таблицы BOOKS, где вместо кодов издательства и читателя будет выводиться вся о них информация, полученная из таблиц PUBLISHERS и READERS. Для этого поместим компонент IBQuery, назовём его BOOKS_Query и в свойстве SQL запишем:

select all BOOKS.NAME, BOOKS.COVER, BOOKS.TIRAZ, BOOKS.K_PAGES, PUBLISHERS.NAME, PUBLISHERS.CITY, READERS.FIO, READERS.ADDRESS, READERS.TELEPHONE, BOOKS.DATE_ISSUE

from (BOOKS inner join PUBLISHERS on BOOKS.ID_PUBLISHERS= PUBLISHERS.ID_PUBLISHERS) left join READERS on BOOKS.ID_ABONENT= READERS.ID_ABONENT

order by BOOKS.NAME;

Запрос выводит поля из всех трёх таблиц. Таблицы BOOKS и PUBLISHERS связаны по равенству поля ID_PUBLISHERS, а полученная в результате такой связи выборка дополнительно связана с таблицей READERS по равенству поля ID_ABONENT. Здесь благодаря связи left join в запрос выводятся записи, содержащие в поле BOOKS.ID_ABONENT значение NULL. Дополнительно к записям применяется сортировка по значению поля BOOKS.NAME.

Установим свойство Active компонента BOOKS_Query в True. Если при этом не появится сообщение об ошибке, то запрос SQL введён без ошибок.

Также поместим на модуль данных компонент DataSourse, назовём его BOOKS_Query_Source и в свойстве DataSet укажем BOOKS_Query.

Создадим новую форму и поместим на неё компонент DBGrid1 в свойстве DataSourse которого укажем DataModule2.BOOKS_Query_Source. На форме расположим кнопку «Вывести отчёт», по нажатию на которую будет выводиться отчёт запроса BOOKS_Query.

Для создания непосредственно отчёта создадим для него новую форму и поместим на неё компонент QuickRep. И укажем в свойстве DataSet DataModule2.BOOKS_Query. Укажем в свойстве Page.Orientation = poLandscape (Пейзажная ориентация страницы). У свойства Bands установим в True следующие подсвойства: HasDetail, hasTitle, PageHander. Поместим на компонент QuickRep в область Detail компоненты QRDBText для каждого поля, в свойстве DataSet которых укажем DataModule2.BOOKS_Query, а в свойстве DataField соответствующее поле. В область Title поместим такое же количество компонентов QRLabel для названий атрибутов, и укажем эти названия в свойстве Caption этих компонентов. Расположим на форме компоненты QRTextFilter, QRHTMLFilter для сохранения отчетов в форматах HTML, TXT. В области отчёта PageHander поместим компонент QRSysData для вывода текущей даты, для чего в свойстве Data установим значение qrsDate. Те же компоненты используем и для вывода текущего времени и текущего номера страницы.