Работа с Oracle 1: Начало



Привет, я решил поделиться своим опытом создания в Delphi программ, работающих с СУБД Oracle. Этот материал предназначен для новичков осваивающих Oracle, может быть профессионал хорошо знакомый с СУБД Oracle и раскритикует меня, но, я думаю, для начинающих эта статья будет полезна. По крайней мере, после прочтения вы будете знать, как создать таблицы, связи между ними и т.д. – реализовать ограничения предметной области (это по-умному :) ), и как связать приложение в Delphi с созданной базой.

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

Итак, для начала необходимо иметь установленный сервер Oracle (желательно версии 9.х, т.к. именно в данной версии мы будем создавать нашу базу), ну и конечно Delphi (не младше 7 версии, т.к. с младшими не работал) версии 10 (BDS 2006). В данной статье я не буду касаться настроек сервера Oracle, в принципе, устроят настройки «по умолчанию», т.к. здесь не рассматриваются вопросы промышленной эксплуатации программы - это всего лишь учебный пример, что бы помочь вам понять с чего начать знакомство с Oracle.

Прежде чем писать приложение, в соответствии с теорией создания информационных систем, а разрабатываемое нами приложение и является мини-системой, необходимо выполнить так называемое физическое проектирование базы данных, т.е. создать физическую модель данных – схему, где будут отражены создаваемые таблицы и связи между ними. Вообще профессионалы (в моём понимании это люди которые зарабатывают этим на жизнь), используют для проектирования баз данных так называемые CASE-средства, программы для создания схем баз данных (логических/физических), которые позволяют потом сгенерировать разработанную схему в выбранную СУБД, т.е. упростить (ускорить) процесс разработки информационной системы. Мы же, как новички, для понимания происходящего будем рисовать данную схему вручную (с помощью Paint’а).

Для реализации нашей базы необходимо создать четыре таблицы:
Заказ, Позиции заказа, Клиент, Изделие.

Теперь определимся со структурой таблиц.

Таблица "Заказ" будет выглядеть следующим образом:

Таблица "Позиции" заказа будет выглядеть следующим образом:

Таблица "Клиент" будет выглядеть следующим образом:

Таблица "Изделие" будет выглядеть следующим образом:

Таким образом, схема нашей базы данных будет выглядеть так:

Эта схема читается так: Клиент выполняет Заказ, который содержит Позиции, в которые входят Изделия. «Куриная лапка» на конце стрелки обозначает, что связь Один-Ко-Многим, т.е. одно и то же Изделие может входить в Позиции разных Заказов, в то время как одна Позиция заказа может включать только одно наименование Изделия. То же самое относительно связи Заказ/Позиции заказа: одному Заказу соответствует много Позиций, в то время как одна и та же Позиция может входить только в один Заказ; и связи Заказ/Клиент: один Клиент может делать много Заказов, в то время как одному Заказу соответствует только один Клиент.

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

Итак, схема нашей будущей базы готова, это так сказать скелет будущей «информационной системы».
Далее я покажу как реализовать данную схему в СУБД Oracle.

Вкратце расскажу про Oracle. В этой СУБД (иногда звучит название «сервер»), пользователь работает с объектами, основными из которых являются (точнее которые необходимы нам):
- Таблицы – основные таблицы, которые составляют базу данных;
- Индексы – индекс создаётся по столбцу или набору столбцов;
- Представления – виртуальные таблицы, основанные на SQL-запросах;
- Последовательности – генератор последовательностей Oracle используется для автоматической выработки уникальной последовательности чисел в КЭШе (счётчик);
- Функции – представляют собой набор операторов языка SQl или PL/SQL;
- Процедуры – отличаются от функций тем, что не возвращают результата;
- Триггеры – это код (программа), который хранится в базе данных и вызывается событиями (например, вставка данных в таблицу).

Создавать таблицы будем в схеме SCOTT, это учебная схема, которая создаётся по умолчанию при установке сервера, пароль для данной схемы TIGER. Осуществлять всё это будем средствами Sql Plus, я предпочитаю графические средства командной строке, хотя возможен и вариант из командной строки. Хотя существует множество визуальных средств для работы с СУБД Oracle от сторонних разработчиков, я, например, люблю пользоваться продуктом Quest Software TOAD. В этой программе есть средства для создания объектов БД, администрирования, изучения структур БД, конструирования запросов и т.д. и т.п. В общем, советую посмотреть.

Итак, при запуске Sql Plus требуется ввести имя пользователя, пароль и базу, к которой будем подсоединяться:

У меня база называется MANUF, вы введите имя вашей базы. После удачного соединения нам понадобится выполнить так называемые скрипты - это файлы в которых содержится текст SQL конструкций объектов БД: таблиц, последовательностей и т.д. Но прежде нам необходимо создать эти скрипты, здесь необходимо знание языка SQL. В общем, не буду затягивать, а сразу представлю текст скриптов создания таблиц и связей (ограничений ссылочной целостности) с комментариями.

Для таблицы КЛИЕНТ:

CREATE TABLE KLIENT
(
ID_KL NUMBER NOT NULL,
NAIMEN VARCHAR2(20) NOT NULL,
ADRES VARCHAR2(30),
FAX VARCHAR2(10),
TEL VARCHAR2(10)
);
 
CREATE UNIQUE INDEX PK_KLIENT ON KLIENT (ID_KL);
 
ALTER TABLE KLIENT ADD ( CONSTRAINT PK_KLIENT PRIMARY KEY (ID_KL) );

Для таблицы ИЗДЕЛИЕ:

CREATE TABLE IZDELIE
(
ID_IZD NUMBER NOT NULL,
NAIMEN VARCHAR2(20) NOT NULL,
CHARACT VARCHAR2(30)
);
 
CREATE UNIQUE INDEX PK_IZDELIE ON IZDELIE (ID_IZD);
 
ALTER TABLE IZDELIE ADD ( CONSTRAINT PK_IZDELIE PRIMARY KEY (ID_IZD) );

Для таблицы ЗАКАЗ:

CREATE TABLE ZAKAZ
(
ID_ZAK NUMBER NOT NULL,
NOMER VARCHAR2(10) NOT NULL,
DATA DATE DEFAULT SYSDATE NOT NULL,
ID_KL NUMBER
);
 
CREATE UNIQUE INDEX PK_ZAKAZ ON ZAKAZ (ID_ZAK);
 
ALTER TABLE ZAKAZ ADD ( CONSTRAINT PK_ZAKAZ PRIMARY KEY (ID_ZAK) );
 
ALTER TABLE ZAKAZ ADD ( CONSTRAINT FK_KLIENT FOREIGN KEY (ID_KL) REFERENCES KLIENT (ID_KL) );

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

CREATE TABLE POS_ZAKAZA
(
ID_STR NUMBER NOT NULL,
KOLVO INTEGER DEFAULT 0 NOT NULL,
ID_ZAK NUMBER NOT NULL,
ID_IZD NUMBER NOT NULL
);
 
CREATE UNIQUE INDEX PK_POS_ZAKAZA ON POS_ZAKAZA (ID_STR);
 
ALTER TABLE POS_ZAKAZA ADD ( CONSTRAINT PK_POS_ZAKAZA PRIMARY KEY (ID_STR) );
 
ALTER TABLE POS_ZAKAZA ADD ( CONSTRAINT FK_ZAKAZ FOREIGN KEY (ID_ZAK) REFERENCES ZAKAZ (ID_ZAK) );
 
ALTER TABLE POS_ZAKAZA ADD ( CONSTRAINT FK_IZDELIE FOREIGN KEY (ID_IZD) REFERENCES IZDELIE (ID_IZD) );

Попробую объяснить что есть что. Конструкция CREATE TABLE создаёт таблицу (я думаю вы и без меня это поняли ), в скобках перечисляются столбцы создаваемой таблицы, соответственно указывается наименование столбца, тип данных (при необходимости размер), значение по умолчанию (default), ограничения на значение (not null – значение д.б. определено), которое в данном случае означает что в столбец обязательно должно быть занесено какое-либо значение, иначе СУБД будет материться при попытке добавления строки без данных в этом столбце. Таким образом, накладывая определённые ограничения мы предусматриваем вероятность того что пользователь забудет ввести данные, которые обязательно должны быть введены, за этим будет следить СУБД. Согласитесь, заказ без номера это не заказ, по крайней мере там где я работаю это именно так, без номера заказа никто и пальцем не пошевелит.

Конструкция CREATE UNIQUE INDEX создаёт индекс в таблице, он нужен для ускоренного поиска, сортировки данных в столбце. Индексировать обязательно ключевые столбцы, и столбцы по которым чаще всего происходит поиск, сортировка данных (например, в запросах).

Конструкция ALTER TABLE имя_таблицы ADD (CONSTRAINT имя_ограничения PRIMARY KEY (имя_столбца) ) указывает базе какой столбец является ключевым.

Конструкция ALTER TABLE имя_таблицы ADD (CONSTRAINT имя_ограничения FOREIGN KEY (имя_столбца) REFERENCES родительская_таблица (имя_столбца) ) создаёт внешний ключ в таблице, это столбец через который будет происходить связь с родительской таблицей. Например, в таблице Заказ связь с таблицей Клиент происходит через столбец ID_KL. После создания данного ключа удаление клиента при привязке его хотя бы к одному заказу будет невозможно. Таким образом реализуется обеспечение непротиворечивости данных в базе. Например, вы создали заказ, привязали к нему позиции, а после этого (случайно или осознанно) удалили заказ, таким образом, эти позиции потеряют связь с определённым заказом, и не будут связаны ни с одним из них, получается, база находится в противоречивом состоянии – позиции есть, а заказа нет. А теперь представьте: в приложении в форме Журнала заказов для выбранного заказа отображаются только привязанные к нему позиции, т.е. пользователь не будет видеть остальных позиций в базе, таким образом, у нас получится несколько строк в зависшем состоянии, что не есть хорошо. Для того чтобы удалить нужного клиента необходимо будет удалить все связанные с ним заказы, т.е. выполнить обратную последовательность ввода информации.

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

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

Для таблицы КЛИЕНТ:

create sequence KLIENT_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
 
CREATE OR REPLACE TRIGGER KLIENT_INS_ID
BEFORE INSERT ON KLIENT
FOR EACH ROW
begin
select KLIENT_SEQ.nextval into :new.ID_KL from dual;
end KLIENT_INS_ID;

Для таблицы ИЗДЕЛИЕ:

create sequence IZDELIE_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
 
CREATE OR REPLACE TRIGGER IZDELIE_INS_ID
BEFORE INSERT ON IZDELIE
FOR EACH ROW
begin
select IZDELIE_SEQ.nextval into :new.ID_IZD from dual;
end IZDELIE_INS_ID;

Для таблицы ЗАКАЗ:

create sequence ZAKAZ_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
 
CREATE OR REPLACE TRIGGER ZAKAZ_INS_ID
BEFORE INSERT ON ZAKAZ
FOR EACH ROW
begin
select ZAKAZ_SEQ.nextval into :new.ID_ZAK from dual;
end ZAKAZ_INS_ID;

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

create sequence POS_ZAKAZA_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
 
CREATE OR REPLACE TRIGGER POS_ZAKAZA_INS_ID
BEFORE INSERT ON POS_ZAKAZA
FOR EACH ROW
begin
select POS_ZAKAZA_SEQ.nextval into :new.ID_STR from dual;
end POS_ZAKAZA_INS_ID;

Здесь мы создаём так называемые последовательности, в них будет храниться значение для ключевого столбца, и с каждой вставкой строки будет увеличиваться на единицу (столбец-счётчик). Для вставки значения последовательности применяется триггер, который срабатывает до вставки строки (BEFORE INSERT). Таким образом, мы обеспечиваем уникальность вводимых значений в ключевое поле.

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

Загружаем Delphi и создаём новое приложение File->New->VCL Forms Application (учтите что это 10 версия, в 7, например, будет так: File->New->Application). Для реализации нашей системы я буду использовать компоненты доступа к данным разработанные фирмой Borland BDE. Как бы ни ругали эти компоненты, я считаю что они обеспечивают максимальные возможности предоставляемы данной средой разработки. Остальные механизмы связи с данными накладывают определённые ограничения на их использование, хотя в принципе всё зависит от решаемых задач, возможно в другом случае будет эффективнее использовать dbExpress (например, Borland рекомендует использовать эти компоненты при создании распределённых приложений).

Назовём нашу главную форму «Журнал регистрации заказов» (свойство Caption).Итак, на главную форму необходимо добавить компонент DataBase с закладки BDE. Затем двойным щелчком мыши на данном компоненте открываем окно свойств:

Здесь в поле name пропишем имя нашей базы (я написал имя моей БД manuf), можно написать любое, и в поле Driver name выбрать драйвер ORACLE, затем нажимаем кнопку Defaults для добавления параметров «по умолчанию». Соответственно в окне редактирования появится список параметров, там удаляем всё кроме SERVER NAME, USER NAME и PASSWORD. Для параметра SERVER NAME пишем имя нашей БД в системе, USER NAME и PASSWORD соответственно SCOTT/TIGER. Также снимем галочки со свойств Login prompt (запрос пользователя и пароля), и Keep inactive connection чтобы соединение не было открыто постоянно и нажимаем ОК. Всё, свойства соединения с БД настроены, можно установить свойство нашего DataBase Connected в true для проверки связи, в случае если связь настроена неправильно, вылетит соответствующее сообщение.

Далее будем добавлять компоненты для соединения с таблицами. Добавим компонент Table с закладки BDE, зададим ему сразу осмысленное имя, чтобы потом проще было разбираться какой компонент с какой таблицей связан (советую так делать и впредь). Назовём компонент Klient, он будет связан с таблицей KLIENT. Для соединения в свойстве Databasename необходимо из выпадающего списка выбрать имя созданной нами БД (manuf), а в свойстве Tablename выбрать таблицу KLIENT. Соединение с таблицей настроено, теперь делаем двойной щелчок мышкой на компоненте для вызова редактора полей, нажимаем правой кнопкой мыши в редакторе и выбираем действие Add all fields. Таким образом, в редактор загрузятся все поля данной таблицы. Теперь добавим с закладки DataAccess компонент DataSource для связи с нашим Table’ом и выберем в его свойстве DataSet таблицу Klient (точнее это наш Table).

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

Для полноценной работы наших таблиц (будем так называть наши Table-компоненты), необходимо настроить свойства полей, чтобы приложение понимало какие поля счётчики, иначе будет вылетать ошибка при попытке вставки строки в таблицу. Для этого заходим в редактор полей (двойной щелчок), и во всех таблицах в ключевых полях (все начинающиеся с ID), в свойстве (надо встать на это поле) Fieldkind выбрать fkInternalCalc, программа будет знать, что это счётчик.

Теперь выполним окончательные действия по настройке компонентов доступа к данным (ух как загнул): нам необходимо связать таблицу Pos_zakaza с родительской Zakaz, чтобы в ней содержались данные именно для выбранного заказа. В Delphi это делается очень просто. У меня Table называется Pos_zakaza, выбираем в свойстве MasterSource DataSource связанный с таблицей Zakaz, у меня это DataSource2. Затем в свойстве MasterFields щёлкаем на кнопке с тремя точками и открываем Field Link Designer, где укажем, через какое поле будет организована связь с родительской таблицей, здесь в правом и левом окошках выберем ID_ZAK и нажмём Add, соответственно в нижнем окошке (Joined Fields), появится связь, нажимаем ОК. Всё, теперь в таблице Pos_zakaza будут содержаться данные соответствующие выбранному заказу. И никакой ручной работы.

Наконец, следуя правилам хорошего тона, пропишем вручную соединение при открытии и отключение при закрытии приложения компонентов доступа к данным. Для этого щёлкаем в свободном месте нашей формы, и в инспекторе объектов на закладке Events ищем событие OnCreate, делаем двойной щелчок на данном свойстве и попадаем в редактор кода. Там прописываем следующий код:

procedure TForm3.FormCreate(Sender: TObject);
begin
 
try
DataBase1.Connected:=true;
Klient.Open;
Zakaz.Open;
Izdelie.Open;
Pos_zakaza.Open;
except
ShowMessage('Ошибка соединения с базой данных!');
Application.Terminate;
end;
 
end;

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

Затем идём всё в тот же инспектор объектов, ищем событие формы OnDestroy и прописываем:

procedure TForm3.FormDestroy(Sender: TObject);
begin
 
DataBase1.Close;
 
end;

Здесь мы программно закрываем соединение.

Теперь приступим к приведению внешнего вида нашего приложения к подобающему виду. Я не буду в подробностях описывать добавление на форму таких компонентов как панели и гриды (думаю, вы и сами догадаетесь как их расположить ), покажу лишь что получилось:

Стоит уделить внимание только полям Клиент в таблице заказов и Изделие в таблице позиций заказа. Данные поля реализованы следующим образом: заходим в редактор полей таблицы, нажимаем правую кнопку мыши и выбираем New field. Появляется окно свойств создаваемого поля,

здесь в поле Name пишем имя создаваемого столбца, в поле Type указываем тип столбца (в нашем случае String), в поле Size – размер, Field type устанавливаем в Lookup (поле просмотра), далее определяем параметры просмотра. Key Fields – поле в текущей таблице, которое редактируется, Dataset – таблица, из которой будем тянуть значения, Lookup Keys – поле, по которому будем связываться, это ключевое поле в таблице, из которой выбираем данные для просмотра, Result Field – столбец, данные которого будут появляться в выпадающем списке.

Вроде всё, таким образом в таблице добавляется так называемое поле просмотра. Ну и напоследок надо установить свойство данных полей просмотра LookupCache в true и в событии таблиц в которых они добавлены прописать следующее: имя_тэйбла.Refresh, у меня это выглядит так: для таблицы Заказ:

procedure TForm3.ZakazAfterPost(DataSet: TDataSet);
begin
 
Zakaz.Refresh;
 
end;

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

Ещё хочу добавить по внешнему виду нашей формы: я добавил компонент MainMenu и добавил в него два пункта для открытия справочников Клиентов и Изделий, а также добавил сплиттер для возможности изменения размеров таблиц Заказов и Позиций заказов, но вы можете придумать что-то ещё, в принципе это дело вкуса.

Ну и конечно расположим на форме DBNavigator’ы для навигации по таблицам, связав их с необходимыми DataSource’ами. Прежде чем работать с Журналом заказов, необходимо добавить возможность работы со справочниками. Для этого добавим в приложение две формы: для справочника Изделий и Клиентов.

Заходим в меню File->New->Form (или пиктограмма на панели инструментов), меняем название (Caption) формы на Справочник клиентов. Сохраняем всё это дело. Также поступаем со Справочником изделий.

Затем щёлкаем на главную форму, идём в меню File, выбираем Use unit и в появившемся списке выбираем оба добавленных модуля и нажимаем ОК. Это для того, чтобы главная форма видела добавленные в приложение справочники, иначе будет ругаться. Затем щёлкаем на пункт меню нашей главной формы Справочники->Клиенты, соответственно попадаем в редактор кода, и для данного события прописываем:

procedure TForm3.N2Click(Sender: TObject);
begin
 
Form1.Show;
 
end;

это открытие Справочника клиентов

procedure TForm3.N3Click(Sender: TObject);
begin
 
Form2.Show;
 
end;

это открытие Справочника изделий.

У вас нумерация форм может быть по-другому. Ну и ещё я постоянно ставлю свойство форм Position в poScreenCenter чтобы при открытии форма появлялась в центре экрана.

Остаётся только оформить новые формы следующим образом:

Справочник клиентов:

Справочник изделий:

Да, и не забудьте для каждой формы выполнить File->Use unit сославшись на модуль главной формы, чтобы получилось связать сетку (Grid) и навигатор с соответствующими таблицами. И ещё, во избежание глюков необходимо прописать для форм справочников на событие Form.Close обновление данной таблицы (Klient, Izdelie), и обновление списка значений для полей просмотра в таблицах Заказ и Позиции заказа. У меня это выглядит так:

Для Справочника клиентов:

 

Для Справочника изделий:

 

Теперь можете попробовать добавлять клиентов и изделия и формировать заказы.

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

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

Written by: Ronin (master_t@inbox.ru)