Работа с Oracle 2: Представления



Привет, это снова я! Ну что, раз меня не закидали тухлыми помидорами, я продолжу знакомить вас с возможностями такой замечательной системы как Oracle.

Кстати, о птичках, по поводу дистрибутивов Oracle предлагаю посмотреть здесь: ]]>http://ru.7uk.org/software/id_27130/]]> и здесь: ]]>http://www.scr.ru/content.php?go=0&art=2&chapter=2057&do=0]]> Там написано о бесплатном варианте поставки системы, правда с ограничениями, но например у меня на работе Oracle крутится на серваке с 1 Гб памяти, база не превышает 4 Гб, да и в принципе ты сам можешь следить за размером БД, не давать ей вырасти больше установленного ограничения. Я это всё к тому, что в принципе в организации реально установить бесплатную версию Oracle и пользоваться ей в полной мере. Я думаю, что такая продвинутая корпорация не остановится на достигнутом и будет расширять нишу бесплатных продуктов, такие правила диктует рынок.

В этой статье я покажу как использовать представления в своих программах. Представления, как я уже писал в прошлой статье, являются виртуальными таблицами, по-русски говоря, они существуют только на момент выполнения, т.е. при просмотре представления, а так это в принципе SQL запрос. Правда ещё есть вариант создания так называемого Материализованного представления, в базе оно представлено в виде таблицы, данные которой, при необходимости обновляются (в зависимости от установленных параметров), но это тема отдельной статьи, мы же упростим наш пример. В данной статье мы продолжим модернизацию нашей мини-информационной системы (правда думаю скоро она станет совсем не мини), созданной в предыдущей статье.

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

Попробую объяснить о чём идёт речь на пальцах. На примере нашего приложения есть форма Журнала заказов, она связана (на уровне базы данных) через ключевые поля с таблицами Изделий и Клиентов. В окошке Позиций заказа отображаются строки входящие в заказ, но в таблице Pos_zakaza для привязки Изделия используется столбец ID_IZD, согласитесь, если отображать просто это поле в форме в приложении это не совсем удобно, будет не понятно что за изделие включено в заказ. Для этого мы и добавили поле просмотра, которое берёт в свой кэш (кусок оперативной памяти) значения поля Наименование из таблицы Изделия и в соответствии со значением поля ID_IZD в таблице Pos_zakaza отображает соответствующее Наименование изделия. Так вот представьте что структура нашей базы данных сложнее, т.е. есть ещё какие-то таблицы, которые связанны с таблицей Изделия (так же через ключевые поля). И нам необходимо отобразить какие-то поля из этих таблиц в строке Позиций заказа. Таким образом, нам надо будет предусмотреть добавление полей просмотра из этих таблиц, соответственно реализация усложняется, слишком много ручной работы.

Всё что нам необходимо, это создать представление в базе данных в нужном нам виде, т.е. как мы хотим видеть форму редактирования Позиций заказа, с какими столбцами, добавить Table для связи с данным представлением, а Delphi понимает представления как таблицы, хотя это и виртуальный объект. Ну и правильно настроить этот Table (я покажу как).

Но прежде мы модернизируем нашу базу данных, усложним немного наш пример. Для этого нам надо вспомнить схему базы данных:

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

Получается что Накладная имеет Позиции Накладной, а в Позиции накладной включены Позиции заказа. Связь Накладная/Позиции накладной – Один-Ко-Многим, т.е. одна Накладная может иметь много Позиций, но одна Позиция может входить только в одну Накладную. Ну и связь Позиции заказа/Позиции накладной – тоже Один-Ко-Многим, т.е одна Позиция заказа может входить в разные Позиции накладной (например 10 велосипедов могут доставить не за один раз по одной накладной, а за два раза, соответственно по разным накладным), а одна Позиция накладной может включать только одну Позицию заказа (две то строчки мы в одну не втолкаем). Опять же повторюсь, всё зависит от предметной области – в другом случае может быть совершенно другая история.

Структура добавляемых таблиц следующая:

Таблица Накладная:

Таблица Позиции накладной:

Соответственно скрипты для данных таблиц следующие:

Для таблицы НАКЛАДНАЯ:

CREATE TABLE NAKLADNAYA
(
ID_NAKL NUMBER NOT NULL,
NOMER VARCHAR2(10) NOT NULL,
DATA DATE DEFAULT SYSDATE NOT NULL
);
 
CREATE UNIQUE INDEX PK_NAKLADNAYA ON NAKLADNAYA
(ID_NAKL);
 
ALTER TABLE NAKLADNAYA ADD (
CONSTRAINT PK_NAKLADNAYA PRIMARY KEY (ID_NAKL) );
 
create sequence NAKL_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
 
CREATE OR REPLACE TRIGGER NAKL_INS_ID
BEFORE INSERT ON NAKLADNAYA
FOR EACH ROW
begin
select NAKL_SEQ.nextval into :new.ID_NAKL from dual;
end NAKL_INS_ID;
/

Для таблицы ПОЗИЦИИ НАКЛАДНОЙ:

CREATE TABLE POS_NAKL
(
ID_POS NUMBER NOT NULL,
KOLVO INTEGER DEFAULT 0 NOT NULL,
ID_NAKL NUMBER NOT NULL,
ID_STR NUMBER NOT NULL
);
 
CREATE UNIQUE INDEX PK_POS_NAKL ON POS_ NAKL
(ID_ POS);
 
ALTER TABLE POS_NAKL ADD (
CONSTRAINT PK_POS_NAKL PRIMARY KEY (ID_POS) );
 
ALTER TABLE POS_NAKL ADD (
CONSTRAINT FK_NAKL FOREIGN KEY (ID_NAKL) 
REFERENCES NAKLADNAYA (ID_NAKL) );
 
ALTER TABLE POS_NAKL ADD (
CONSTRAINT FK_POS_ZAK FOREIGN KEY (ID_STR) 
REFERENCES POS_ZAKAZA (ID_STR) );
 
create sequence POS_NAKL_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
 
CREATE OR REPLACE TRIGGER POS_NAKL_INS_ID
BEFORE INSERT ON POS_NAKL
FOR EACH ROW
begin
select POS_NAKL_SEQ.nextval into :new.ID_POS from dual;
end POS_NAKL_INS_ID;
/

Здесь я думаю всё понятно, создаём таблицы, индексы, внешние ключи для связи, последовательности для полей-счётчиков и триггеры для вставки значений в поля-счётчики.

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

CREATE OR REPLACE VIEW POS_NAKL_VIEW
AS 
SELECT pos_nakl.id_pos, pos_nakl.kolvo, pos_zakaza.kolvo zak_kolvo, izdelie.naimen izdelie,
 
zakaz.nomer, klient.naimen klient, pos_nakl.id_nakl, pos_nakl.id_str
FROM pos_nakl, pos_zakaza, zakaz, klient, izdelie
WHERE ( (pos_zakaza.id_str = pos_nakl.id_str)
 
AND (zakaz.id_zak = pos_zakaza.id_zak)
 
AND (klient.id_kl = zakaz.id_kl)
 
AND (izdelie.id_izd = pos_zakaza.id_izd)
);
ORDER BY pos_nakl.id_pos

Структура запроса данного представления в SQL Modeler программы TOAD (я упоминал в прошлой статье) выглядит следующим образом:

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

Наша база подготовлена, теперь переходим к проектированию приложения. Запускаем Delphi, открываем созданное ранее приложение.Добавим два новых Table’а и свяжем первый с таблицей Накладная (NAKLADNAYA), второй с таблицей Позиции накладной (POS_NAKL). Свойство DatabaseName у обоих ставим имя нашей базы (у меня manuf), выбираем соответствующие TableName. Затем необходимо в событии главной формы OnCreate добавить программное открытие наших Table’ов, у меня это выглядит так:

Nakl.Open;
Pos_nakl.Open;

Открываем редактор полей наших Table’ов (двойной щелчок мышью), щёлкаем правой кнопкой мыши и выбираем Add all fields. Затем свойство ключевого поля (ID…) каждого Table’а FieldKind ставим в fkInternalCalc (поля-счётчики).

Далее добавляем ещё один Table для связи с созданным нами представлением Позиции накладной (POS_NAKL_VIEW). DatabaseName и TableName не забываем, плюс программное открытие. Также через редактор полей добавляем все столбцы, в свойстве IndexFieldName нашего Table’а прописываем ID_POS, иначе будет вылетать ошибка при попытке обновления компонента «… таблица не индексирована», правда этот глюк можно обойти выполняя открытие и закрытие компонента, сначала Close затем Open, но мы то всё делаем по-уму. Да, и ещё, не забудьте добавить DataSource’ы и связать их с новыми Table’ами.

Затем добавляем в приложение новую форму для нашего Журнала накладных. Сохраняем новый модуль (имена модулей тоже старайтесь делать осмысленными), и в главном модуле программы добавьте ссылку на новый модуль File->Use unit, также и в самом новом модуле добавляем ссылку на главную форму, чтобы потом соединить компоненты (гриды) с таблицами. В компоненте MainMenu добавьте пункт «Журнал накладных», примерно так:

И на событии нажатия данного пункта пропишите открывание формы Журнала накладных (Form.Show). А саму форму Журнала накладных оформите примерно как Журнал регистрации заказов, ну или как пожелаете. У меня получилось так:

Здесь стоит отметить, что верхний DBGrid связан с таблицей Nakladnaya (Nakl), нижний с представление Pos_nakl_view через соответствующие DataSource’ы. Верхний навигатор связан с таблицей Nakladnaya (Nakl), а нижний с таблицей Pos_nakl. Да, и свойство нижнего грида ReadOnly поставьте в true, так как редактирование представления с которым работает наше приложение невозможно (теоретически это возможно, но только надо учесть кучу ограничений), поэтому мы добавим программно редактирование Позиций накладной.

А вот теперь мы пойдём на маленькую хитрость. Просмотр данных мы будем вести через наше представление, а редактировать данные будем через Table связанный с таблицей POS_NAKL, для этого надо в свойстве MasterSource данного Table’а установить DataSource связанный с представлением, а в свойстве MasterFields связь ID_POS->ID_POS. Таким образом у нас в компоненте Pos_nakl будет загружена только одна строка – текущая строка набора данных (Table’а) связанного с представлением Pos_nakl_view, и редактировать мы будем именно строку Table’а связанного с таблицей Pos_nakl. Также для Table’а связанного с представлением Pos_nakl_view установим в свойстве MasterSource - DataSource связанный с таблицей Nakladnaya (Nakl), чтобы в форме отображались данные привязанные к текущей накладной, а в свойстве MasterFields – связь ID_NAKL_>ID_NAKL.

Для реализации нашего замысла нам остаётся немного изменить реакцию нижнего навигатора на нажатие кнопок. По нажатию кнопки Вставить добавим следующий код:

procedure TForm4.DBNavigator2Click(Sender: TObject; Button: TNavigateBtn);
begin
 
if Button=nbInsert then begin
Hide;
Form3.Show; //главная форма
end;
 
end;

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

procedure TForm3.DBGrid2KeyDown(Sender: TObject; var Key: Word;
Shift: TShiftState);
Begin
 
if Key=VK_SPACE then
begin
Form3.Pos_naklID_STR.Value:=
Form3.Pos_zakazaID_STR.Value;
Form3.Pos_naklKOLVO.AsString:=
InputBox('Введите количество изделий','Кол-во',Form3.Pos_zakazaKOLVO.AsString);
Form3.Pos_naklID_NAKL.Value:=
Form3.NaklID_NAKL.Value;
Pos_nakl_view.Refresh;
Form4.Show;// журнал накладных
end;
 
end;

Здесь происходит проверка: если нажат пробел, то полю ID_STR таблицы POS_NAKL присваивается текущее значение поля ID_STR таблицы POS_ZAKAZA (на которой находится курсор), т.е. пользователь находит строку заказа, которую надо добавить и нажимает пробел. Далее появляется окошко, в котором запрашивается указать вставляемое в позицию накладной количество выбранного изделия, после чего полю KOLVO таблицы POS_NAKL присваивается соответствующее значение. И наконец, так как у нас таблица POS_NAKL не связана с таблицей NAKLADNAYA (MasterSource=Pos_nakl_view а не Nakl), то необходимо вручную вставить значение ключевого столбца таблицы NAKLADNAYA. Затем обновляется представление, для того чтобы в окне Позиций накладной стала видна добавленная строка, и фокус переводится в форму Журнала накладных.

На удаление строки из Позиций накладной необходимо в событии Table’а Pos_nakl – AfterDelete прописать следующее:

procedure TForm3.Pos_naklAfterDelete(DataSet: TDataSet);
begin
 
Pos_nakl_view.Refresh;//обновление представления
 
end

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

if Button=nbRefresh then begin
Form3.Pos_nakl_view.Refresh;
end;

При редактировании строки будем открывать специальную форму для редактирования. Для этого добавим новую форму, обзовём её Редактор, сохраним. Затем добавим ссылку в главной форме на неё, и в ней на главную (Use unit). Вот что получилось:

а этой форме добавлены поля DBEdit связанные с соответствующими полями представления Pos_nakl_view. Всем полям кроме Кол-во отпр. свойству ReadOnly присвоено значение true, чтобы пользователь не мог изменить значение этих полей. Редактируется только поле Кол-во отпр.

На нажатие кнопки навигатора Редактировать необходимо прописать следующее:

if Button=nbEdit then begin
Form5.Show;//редактор
end;

Т.е. если нажата кнопка Редактировать открывается окно Редактора. На нажатие кнопки Редактора ОК пропишем следующее:

procedure TForm5.Button1Click(Sender: TObject);
begin
 
if DBEdit5.Text<>'' then begin
Form3.Pos_nakl_view.Refresh;
Close;
end;
 
end;

Здесь идёт проверка, чтобы пользователь не забыл вставить количество, затем идёт обновление представления и закрытие редактора.

Ну вот вроде и всё. Наш Журнал накладных готов. С первого взгляда может показаться что пример слишком сложный, конечно такие затраты не оправданы при небольшой структуре базы данных, возможно проще применить поля просмотра. Однако при сложной структуре базы и многочисленных связях, поля просмотра начинали глючить и путать значения, т.е. корректно показывались значения только из «соседних таблиц».

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

Written by : Ronin (master_t@inbox.ru)