Тема. Архитектура клиент – сервер. Сервер и база данныхinterbase

Практическая работа № 1

Тема:

Язык SQL. Программирование основных компонентов СУБД INTERBASE.

Цель практической работы:

Изучить язык SQL. Научить студентов формировать структуру базы данных, запросы, процедуры.

Ожидаемый результат:

Создание базы данных Interbase заданной структуры. Демонстрация работы запросов, процедур, триггеров.

Задача 1.

Формирование структуры БД Interbase.

Установка и запуск сервера InterBase

Сервер InterBase поставляется вместе с выпуском Delphi 7 Enterprise и уста­навливается отдельно. Процесс установки этого сервера не сложен — достаточ­но просто подтверждать параметры, выбранные мастером установки. Единственный выбор мастера, который можно изменить,— это тип установки. Можно выбрать в окне мастера Select Components (Выбор компонентов) сокращенную установку (Compact Installation).

После завершения установки сервера InterBase 6.5 способ его запуска опреде­ляется при помощи специальной программы Interbase Manager. Сервер может запускаться либо автоматически при загрузке операционной системы (переклю­чатель Automatic), либо вручную (переключатель Manual).

Приложение IBConsole

Все средства администрирования и работы с базами данных InterBase 6.5 (включая редактор SQL-запросов) находятся в одном приложении — IBConsole. Прежде, чем приступить к работе с этим приложением, убедитесь, что сервер InterBase уже запущен. Запустите приложение IBConsole при помощи меню Пуск или любым другим доступным способом. Внешний вид этого приложения при его первом запуске представлен на рисунке.

1. Выполните команду Server > Register или просто дважды щелкните мышью на корневом элементе древовидной структуры InterBase Servers, чтобы зарегистрировать сервер. В результате на экране появится диалоговое окно регистрации сервера и его подключения.

Здесь следует указать, к какому серверу будет выполняться подключение: к локальному - переключатель Local Server, или к удаленному - переключатель Remote Server. Локальным

является сервер, установленный на компьюте­ре пользователя, а удаленным — сервер, дос­туп к которому выполняется через сеть. В слу­чае удаленного подключения в поле Server Name указывается сетевое имя сервера или сетевой адрес, соответствующий протоколу, указанному в поле Network Protocol.

2. Щелкните мышью на переключателе Local Server.

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

Для создания баз данных в SQL используется команда CREATE DATABASE.

Создадим базу данных staff. gdb.

1. Для этого введите и выполните в окне Interactive SQL следующий SQL-оператор:

CREATE DATABASE "e:\programs\staff\base\staff.gdb" USER "SYSDBA" PASSWORD "masterkey" PAGE_SIZE = 4096 LENGTH = 10000 PAGES

В результате в каталоге e:\programs\staff\base будет создан файл базы данных staff.gdb. Размер этой базы данных не может превышать 4 Кбайт * 10000 = 40 Мбайт.

Для создания таблиц используется оператор SQL CREATE TABLE.

CREATE TABLE REGIONS

( Zip integer NOT NULL PRIMARY KEY, Area varchar(30),

Region varchar(30),

City varchar(20) NOT NULL )

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

Оператор удаления таблицы:

DROP TABLE имя таблицы Если таблица имеет связи с другими таблицами по внешним ключам, тогда команда DROP TABLE выполнена не будет.

Добавление данных

Для добавления строки данных в таблицу используется оператор INSERT. На­пример, добавим пару строк в таблицу REGIONS базы данных staff.gdb.

INSERT INTO REGIONS

VALUES (8320,'Киевская’,'','Борисполь');

INSERT INTO REGIONS

VALUES (8324,'Киевская','Бориспольский',‘Гора')

Просмотр данных

Для просмотра данных в таблицах используется команда SELECT. В простей­шей форме эта команда имеет следующий синтаксис:

SELECT * FROM REGIONS или

SELECT DISTINCT Area From REGIONS

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

Ключевое слово WHERE

Ключевое слово WHERE применяется в SQL для наложения условий на значения, выбираемые при помощи оператора SELECT. Выполните следующий оператор:

SELECT * FROM REGIONS

WHERE Zip = 8324

В результате будет выбрана одна строка, у которой в столбце Zip указано значение 8324.

В представленном ниже операторе осуществляется подсчет количества строк таблицы REGIONS, у которых значения столбца REGION начинается с "Борисп":

SELECT COUNT(*) FROM REGIONS

WHERE Region LIKE 'Борисп%'

В результате будет возвращено число 1.

Изменение данных

Для изменения информации, хранящейся в таблицах базы данных, использу­ется команда SQL UPDATE. Рассмотрим примеры использования этой команды.

UPDATE STAFF

SET Salary = Salary + 100

WHERE PosID IN (SELECT PosID FROM POSS

WHERE PosLevel < 3)

В этом операторе значение поля Salary увеличивается на 100 в тех строках, в которых должности сотрудника соответствует уровень не выше 3.

Удаление строк

Для удаления строк таблицы используется команда DELETE. Например, для удаления всех строк таблицы FAMILY необходимо выполнить оператор:

DELETE FROM FAMILY или удаление по условию:

DELETE FROM FAMILY WHERE ID = 0

Задача 2.

Связи таблиц. Хранимые процедуры.

Напоминаем, что для администрирования базы данных InterBase использует­ся программа IBConsole (Или конструктор Interbase Expert). В этой программе открывается окно Interactive SQL. При помощи этого окна можно выполнять SQL- команды, рассматриваемые в данной работе. Кроме того, по ходу изучения представленного материала реко­мендуется исследовать изменения, вносимые в базу данных *.gdb, при по­мощи древовидной структуры главного окна программы IBConsole. Такая мето­дика позволяет лучше разобраться в том, как представленные примеры реализу­ются на практике, а также лучше освоить основы администрирования баз данных InterBase.

Генераторы

Генератор (generator) — это последовательно возрастающее число, которое может автоматически вставляться в столбец при помощи функции GEN_ID(). Генераторы часто используются для формирования уникальных значений столбцов, входящих в первичный ключ. База данных InterBase может содержать любое количество генераторов, и они могут использоваться или обновляться в любой транзакции.

Создадим в базе данных staff.gdb генератор, который будут использо­ваться для занесения значений в первый столбец (столбец идентификатора) таб­лицы STAFF.

Выполните следующие команды SQL:

CREATE GENERATOR STAFF_ ID­_GEN;

В результате в базе данных будет создано три генератора. Для того чтобы убе­диться в этом, перейдите в основное окно программы IBConsole и выделите в древовидной структуре элемент Interbase Servers > Local Server > Databases > STAFF.GDB > Generators

Триггеры

Триггер (trigger) - это программа на языке SQL, которая выполняется при определенной операции над конкретной таблицей. Триггеры включаются при выполнении таких операций, как вставка (INSERT), обновление (UPDATE) и удаление (DELETE) данных. Создадим несколько триггеров в базе данных staff.gdb.

создадим триггер для таблицы STAFF, вызывающий функцию GENID для увеличения текущего значения соответствующего гене­ратора.

SET TERM ^ ;

CREATE TRIGGER STAFFInsert FOR STAFF

BEFORE INSERT POSITION 0

AS BEGIN

NEW.ID = GEN_ID(STAFF_ID_GEN, 1);

END

Ключевое слово POSITION используется для определения порядка выполне­ния нескольких триггеров, связанных с одной и той же операцией. В данном случае POSITION 0 означает, что триггер выполняется первым среди всех триг­геров, связанных с операцией INSERT.

В триггере в поле идентификатора добавляемой строки (NEW.ID) вносится значение соответствующего генератора, увеличенное на единицу.

Первичный ключ

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

CREATE TABLE REGIONS

( Zip integer NOT NULL RIMARY KEY),

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

Кроме того, для изменения структуры, включая наложение ограничений, можно после создания таблицы использовать команду ALTER TABLE:

ALTER TABLE REGIONS

ADD PRIMARY KEY (Zip)

Внешние ключи

Внешний ключ (foreign key) определяет столбец одной таблицы, значения ко­торого должны существовать во второй таблице, которая называется внешней. В отличие от первичного ключа, внешний ключ не является уникальным индек­сом строки. Уникальными являются ключевые столбцы во внешней таблице. Добавление в таблицу внешнего ключа приводит к автоматическому созданию вторичного индекса по ключевым столбцам.

Также как и первичный, внешний ключ можно определять в команде CREATE TABLE или после создания таблицы при помощи команды ALTER TABLE ADD

FOREIGN KEY.

1. Создадим внешний ключ для таблицы STAFF:

ALTER TABLE STAFF

ADD FOREIGN KEY (PosID) REFERENCES POSS

Это ограничение назначает столбец PosID внешним ключом, который ссыла­ется на аналогичный столбец таблицы POSS. Это означает, что введенные в таб­лице STAFF идентификаторы должностей должны существовать в таблице POSS, а также, что идентификаторы, использующиеся в таблице STAFF, нельзя удалить из таблицы POSS. Такая возможность закрепления связи между двумя таблицами называется ссылочной целостностью (referential integrity). Обеспечение целостно­сти ссылок таблиц друг на друга выполняется при их создания или объявлении на уровне сервера базы данных, а не на уровне приложения.

Хранимые процедуры

Хранимая процедура (stored procedure) — это скомпилированная программа произвольной длины на языке SQL, которая хранится в базе данных вместе с другими объектами. Хранимые процедуры InterBase подразделяются на две ос­новные разновидности: процедуры выбора (select procedure) и выполняемые про­цедуры (executable procedure). Процедуры выбора используются в операторе SELECT вместо таблицы, и, следовательно, должны возвращать наборы данных, тогда как для выполняемых процедур это не обязательно.

Хранимые процедуры создаются командой CREATE PROCEDURE, а удаляются командой DROP PROCEDURE.

Процедуры выбора

Создадим в базе данных staff.gdb процедуру формирования списка подчи­ненных подразделений. В данном случае подразумевается, что предприятие име­ет иерархическую структуру подразделений. В таблице DEPS в столбце ParentDeptID указывается идентификатор "родительского" подразделения. Ес­ли родительского подразделения не существует (элементы на вершине иерархии), тогда в поле ParentDeptID содержится значение 0.

SET TERM ^ ;

CREATE PROCEDURE ChildDeptsList (pDeptID integer)

RETURNS (rDeptFullName varchar(100))

AS BEGIN

FOR SELECT DeptFullName FROM DEPS WHERE ParentDeptID = :pDeptID

INTO :rDeptFullName

DO SUSPEND;

END

В эту процедуру передается параметр pDeptlD, соответствующий идентифи­катору "родительского" подразделения, а в качестве результата возвращается список подчиненных подразделений.

В процедурах выбора возвращаемые данные определяются при помощи ключе­вого слова RETURNS. Параметры, перечисленные после имени процедуры, назы­ваются входными (input), а перечисленные после ключевого слова RETURNS — выходными (output). При обращении как к входным, так и к выходным пара­метрам в теле процедуры (между ключевыми словами BEGIN и END) перед их именами ставится символ ":".

Для возвращения строк результата в вызывающую программу используется конструкция FOR SELECT ... DO. Команда SUSPEND приостанавливает выпол­нение процедуры до следующего запроса. Перед приостановкой выполнения возвращаются значения, присвоенные выходным параметрам.

Выполняемые процедуры

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

SET TERM ^

CREATE PROCEDURE DeleteEmp (pEmpID integer)

AS BEGIN

DELETE FROM STAFF

WHERE ID =:pEmpID;

END

Объявление переменных

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

CREATE PROCEDURE имя_процедуры

AS

VARIABLE имя_переменной тип_данных [, ...] BEGIN

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

Выполнение хранимых процедур

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

EXECUTE PROCEDURE DELETEEMP 5

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

Напишем еще одну процедуру, которая вычисляет сумму заработной платы сотрудников по столбцу SALARY каждый раз, когда в таблицу STAFF вносится новая строка.

SET TERM ^ ;

CREATE PROCEDURE "PROC_CALC_SUM"

(

"ID_STAFF" INTEGER

)

AS

BEGIN

UPDATE STAFF SET ST_SUM = (SELECT SUM (SALARY) FROM STAFF) WHERE (ID = :ID_STAFF);

SUSPEND;

END

Здесь введена переменная ID_STAFF, чтобы обновлялось значение только в одной добавляемой строке. Проверка индекса производится оператором WHERE. В таблице STAFF при этом добавляется еще один столбец ST_SUM, в который и вносится сумма заработных плат.

Теперь нужно создать триггер, который будет запускать эту процедуру при каждом вводе новой записи в таблицу STAFF:

SET TERM ^ ;

CREATE TRIGGER "TRIG_STAFF" FOR "STAFF"

ACTIVE AFTER INSERT POSITION 0

AS

BEGIN

EXECUTE PROCEDURE PROC_CALC_SUM (NEW.ID);

END

Триггер запускает процедуру PROC_CALC_SUM при внесении новой записи и передает в нее в качестве параметра идентификатор новой записи NEW.ID.

Внешние функции

В InterBase пользователи могут расширять диалект SQL путем введения соб­ственных функций. Функции определяются в совместно используемых внешних библиотеках (в системе Windows это динамические библиотеки типа DLL). Эти функции объявляются для использования в базе данных InterBase с помощью команды DECLARE EXTERNAL FUNCTION. С этого момента функция помещается в базу данных и ведет себя точно также как любая другая внутренняя функция. Приведем пример исходного кода библиотеки DLL с определением функции конкатенации (слияния) двух строк.

library StrFLib;

uses SysUtils, Classes, System;

function ConcatStr(var Strl, Str2: string): string;

far cdecl export;

begin

Result := Strl + Str2;

end;

exports ConcatStr;

begin

end.

Рассмотрим пример команды SQL, которая делает описанную функцию дос­тупной для использования в InterBase.

DECLARE EXTERNAL FUNCTION ConcatStr varchar(100), varchar(l00)

RETURNS varchar(200) BY VALUE

ENTRY_ POINT 'ConcatStr' MODULE_ NAME 'StrFLib.dll'

Теперь функцию ConcatStr можно использовать, например, так:

SELECT * FROM TABLE1

WHERE Columnl = ConcatStr(Column2,Column3)

Для удаления объявления внешней функции из базы данных используется команда DROP EXTERNAL FUNCTION.

Контрольные вопросы

1. Какие операторы языка SQLсоздают базу данных, таблицу, определяют типы переменных.

2. Какие операторы языка SQLпозволяют внести данные в поля таблицInterbase.

3. Как создать первичный ключ таблицы данных.

4. Как создать внешний ключ таблицы данных.

5. Что такое хранимая процедура, триггер.

6. Как создать и выполнить хранимую процедуру.