2020-01-15
1235
5.1 Инструментальные средства.
Инструментальные средства проектирования.
Определение и назначение.
Инсталляция, настройка параметров.
Основные свойства, конструкции.
Метаданные БД SQL – сервера.
Проектирование средств поддержки ссылочной целостности данных.
Реляционные связи баз данных.
Проектирование генераторов.
Реализация бизнес – правил на стороне сервера.
5.2 Определение.
IB Expert (Эксперт) – профессиональная графическая среда управления и администрирования SQL – серверов InterBase и FireBird.
Основное преимущество - использование для выполнения функций управления графического интерфейса со встроенной справочной системой, позволяющего программисту абстрагироваться от синтаксиса SQL – языка.
Встроенный графический дизайнер реляционной диаграммы БД.
Генератор тестовых наборов данных.
5.3 Инсталляция IB Expert.
1. Дистрибутив:
ibet_2.5.0.14_full.exe
2. Каталог.
C:\Program Files\HK-Software\IB Expert 2.0
3. Запуск Эксперта.
|
|
|
«Пуск\Программы\IB Expert 2.0\IBExpert».
Демонстрация инсталляции и настройки.
5.4 Структура среды IB Expert.
Главное окно приложения:
5.5 Определение языка.
Русификация:
Options | Environment Options | Interface Language – выбрать русский:
Остальные функции – настройка интерфейса пользователя Эксперта оставить по умолчанию.
5.6 Создание БД.
Создание файла базы данных «База данных\Создать БД»
5.7 Регистрация БД.
Выбор пункта «Зарегистрировать базу» в меню «База данных» или из всплывающего меню, вызываемого правым кликом на поле окна «Редактора БД». Для регистрации задается следующая информация:
5.8 Основные функции.
Вызов всех основных функций работы с БД правый клик на отмеченной базе.
5.9 Просмотр объектов.
Для просмотра объекта метаданных достаточно выполнить двойной клик на выделенном поле. После отображения окна свойств, переключение между объектами возможно при помощи выпадающего меню:
5.10 Резервное копирование.
Меню «Службы\Резервирование БД»
5.11 SQL - редактор.
Выполнение SQL – операторов производится в окне редактора
«Инструменты\SQL редактор»
5.12 Пользователи.
Определение пользователей SQL - сервера «Инструменты \ Менеджер пользователей»
5.13 Создание объектов.
Создание доменов.
Создание таблиц.
Создание ограничений.
Определение первичного (уникального) ключа.
Определение внешнего ключа.
Все объекты метаданных создаются выбором функции «Новый объект» контекстного меню, вызываемого правым кликом, на объекте дерева БД Expert, или выбором соответствующей функции меню, или выбором соответствующей пиктограммы в панели управления.
|
|
|
5.14 Управление свойствами.
Управление свойствами объектов, например, полей, выполняется выбором требуемой функции контекстного меню.
5.15 Определение внешнего ключа.
Внешний ключ это столбец, чьи значения должны соответствовать значениям столбца в другой таблице.
Определение внешнего ключа производится из меню «Ограничения» выбором пункта «2 Внешние ключи»:
5.16 Формат оператора.
Добавление внешнего ключа производится оператором
ALTER TABLE Имя_Таблицы
ADD
FOREIGN KEY (<список столбцов внешнего ключа>)
REFERENCES <имя родительской таблицы>
[<список столбцов родительской таблицы>]
[ON DELETE (NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
[ON UPDATE {NO ACTION | CASCADE I SET DEFAULT | SET NULL}]
Параметры:
ON DELETE:
ON UPDATE
определяют способы изменения подчиненных записей дочерней таблицы при удалении или изменении поля связи в записи родительской таблицы.
Вопрос.
Сколько внешних ключей необходимо для организации связи один к одному между двумя таблицами?
1. Один в дочерней таблице.
2. По одному в дочерней и в родительской.
3. Два ключа в дочерней таблице.
4. Такая связь не допустима.
5. Для организации такой связи внешние ключи не используются.
5.18 Реляционная диаграмма.
Отображение связей на реляционных диаграммах
Надписи на связях определяются именами оператора
[CONSTRAINT <имя ссылочной целостности>]
5.19 Проверка зависимости.
Проверка реляционной зависимости производится при помощи выполнения операторов управления данными:
INSERT, UPDATE, DELETE
над родительской или дочерней таблицей.
Подготавливать набор данных для операторов можно с использованием окна свойств таблиц со встроенным навигатором:
5.20 Генератор.
Механизм генераторов предназначен для формирования автоинкрементных значений целочисленных полей.
CREATE GENERATOR G_EMP;
SET GENERATOR G_EMP TO 100;
Такой генератор будет выдавать значения, начиная со 101 до максимально возможного целого числа, определяемого типом данных, заполняемого поля таблицы.
Функция обращения к значению генератора
GEN_ID(Имя_Генератора, Шаг_приращения);
Может использоваться с операторами присвоения значений
INSERT, SET, UPDATE.
5.21 Вопрос.
Поле первичного ключа таблицы SQL – сервера, определенное типом данных INTEGER и заполняемое генератором БД, стартующим с 0, может обеспечить запись следующего количества уникальных значений:
A) 2 15.
B) 2 16.
C) 2 32.
D) 1024.
E) 32000.
5.22 Вопросы по 3 лабораторной.
Объяснить необходимость обязательного определения первичного ключа в таблице.
В чем заключается смысл ссылочной целостности данных в реляционной модели?
Операторы определения ссылочной целостности данных, каскадного обновления и удаления.
SQL - оператор создания триггеров, алгоритмический язык проектирования триггеров.
Формат заголовка триггера, определение очередности срабатывания.
Формат определения старых и новых значений полей ассоциированной таблицы.
Определение тела триггера для заполнения автоинкрементного поля значением генератора.
Организация каскадных воздействий в таблицах при помощи триггеров.
Использование механизма триггеров для ведения архивов изменений в таблицах базы данных.
5.23 Задания СРСП.
1. Защита выбора механизма каскадного ведения данных.
2. Выбор и защита сложного бизнес – правила, реализуемого триггером.
3. Ответить на контрольные вопросы третьего модуля [1];
4. Провести отладку SQL – кода спроектированных триггеров;
|
|
|
5. Защитить отчет по третьей лабораторной работе;
6. Защитить отчет по разделу 3.3 курсовой работы [2];
7. Тест первого рубежного контроля.
8. Разработать пример вопроса тестового задания по теме раздела.
5.24 Задания СРС.
1. Изучить методические указания к третьей лабораторной работе [1];
2. Ответить на примеры тестовых заданий к третьему модулю [1];
3. Изучить SQL - код триггеров, используемых в учебной БД (FONEBOOK.GDB);
4. Проектирование и использование генераторов;
5. Изучение системы помощи (HELP), утилиты InteractivSQL, операторы: CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER, CREATE GENERATOR, SET GENERATOR, INSERT, UPDATA, DELETE;
6. Изучить конспект 5,6 лекций [3];
7. Среда управления SQL – сервера - IBExpress.
5.25 Демонстрация.
Инсталляция среды IB Expert.
Задание параметров.
Создание и регистрация БД.
Создание объектов метаданных.
Просмотр свойств объектов.
Редактирование свойств объектов БД.
Создание и использование генераторов.
ТРИГГЕРЫ
6.1 Механизм триггеров.
Определение и назначение.
Формат оператора создания триггера.
Основные свойства конструкции.
Создание триггера в среде IB Expert.
Особенности создания триггеров в IB Console.
Примеры использования.
6.2 Определение.
Триггер - это процедура базы данных, написанная на специальном алгоритмическом языке и автоматически вызываемая SQL-сервером при обновлении, удалении или добавлении новой записи в таблицу БД. Триггеры всегда ассоциируется с действием по отношению к таблице. Запуск триггера связан с выполнением одного из операторов управления данными (INSERT, UPDATE, DELETE) к определенной записи таблицы. Любой пользователь, имеющий определенные привилегии на таблицу, автоматически имеет права выполнять связанные с ней триггеры. Непосредственно из программы к триггерам обратиться нельзя. Нельзя и передавать им входные параметры и получать от них значения выходных параметров.
6.3 Назначение.
Обычно триггера используются для:
1. каскадного изменения в связанных таблицах;
2. формирования автоинкрементных полей;
|
|
|
3. ведение архивов записей;
4. автоматического документирования изменений, вносимых пользователями в таблицы;
5. автоматического контроля бизнес – правил на сервере.
6.4 Формат.
Формат оператора создания триггеров:
CREATE TRIGGER name FOR table
[ACTIVE | INACTIVE]
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE}
[POSITION number]
AS
DECLARE VARIABLE variable <datatype>;
[DECLARE VARIABLE variable <datatype>;...]
BEGIN
<тело_триггера>
END
6.5 Параметры.
Оператор [ACTIVE | INACTIVE ] определяет активность триггера.
По отношению к событию {BEFORE | AFTER}, влекущему их вызов, триггеры различаются на: выполняемые до наступления события, и выполняемые после наступления события.
По событию изменения таблицы БД {DELETE | INSERT | UPDATE} триггеры различаются на вызываемые при: удалении записи, добавлении новой записи, изменении существующей записи.
Очередность выполнения [POSITION номер],в случае нескольких триггеров не одно действие и отношение к нему.
В предложении AS записывается тело в операторном блоке
BEGIN <тело_триггера> END
6.6 Вопрос.
Сколько триггеров можно определить к одной таблице?
1. Один в дочерней таблице.
2. По одному в дочерней и в родительской.
3. По количеству действий над таблицей.
4. Определяется спецификацией языка.
5. Определено в конкретной реализации СУБД.
6.7 Тело триггера.
Программный код триггера (тело триггера) состоит из операторов процедурного языка, заключенного в блок
BEGIN
<compound_statement> [<compound_statement>...]
END
Программный код состоит из команд специального процедурного языка, реализуемого в той или иной интерпретации конкретного SQL – сервера.
Часть операторов будут рассмотрены в следующих лекциях, посвященных хранимым процедурам.
6.8 Операторы языка.
Алгоритмический язык триггеров и хранимых процедур
Объявление локальных переменных
DECLARE VARIABLE <имя переменной> <тип данных>;
Операторные скобки BEGIN... END
Оператор присваивания
Имя переменной = выражение;
Оператор условного перехода IF... THEN... ELSE
IF (<условие>) THEN
< оператор 1>
[ELSE < оператор 2>]
6.9 Операторы языка.
Оператор выбора SELECT, отличие в предложении INTO
SELECT AVG(KOLVO), SUM(KOLVO) FROM RASHOD
WHERE TOVAR =:INTOVAR
INTO:AVG_KOLVO,:SUM_KOLVO;
Оператор FOR SELECT... DO
FOR
<оператор SELECT>
DO
< оператор>;
ОператорSUSPEND, возврат значений в вызываемую процедуру.
6.10 Операторы языка.
Оператор WHILE... DO
WHILE (<условие>) DO <оператор>
ОператорEXIT
ОператорPOST _EVENT
POST_EVENT "Имя события";
Оператор EXECUTE PROCEDURE
EXECUTE PROCEDURE имя
[параметр [, параметр ]]
[RETURNING_VALUES параметр [, параметр...]];
6.11 Операторы OLD и NEW.
Оператор версии значения OLD и NEW изменяемого поля (контекстные переменные), позволяют обрушатся к старому (хранящемуся в поле) значению и к новому, пришедшему в операторе. Например, при каскадном изменении связанных таблиц используется следующая конструкция:
IF (OLD.ПoлeCвязиPoдитeля <> NEW.ПолеСвязиРодителя)
THEN UPDATE ДочерняяТаблица
SET ПолеСвязиДочернейТаблицы = NEW.ПолеСвязиРодителя
WHERE ПолеСвязиДочернейТаблицы = OLD.ПoлeCвязиPoдитeля;
при изменении столбца связи в родительской таблице будет изменено значение столбца связи у записей соответствующих дочерних таблиц.
6.12 Терминатор.
Для работы в IB Console необходимо переопределять терминатор (символ, завершающий оператор программного кода).
Выполняется оператором:
SET TERM!!;
CREATE TRIGGER...
...
END!!
SET TERM;!!
После кода триггера, значение терминатора возвращается в предыдущее. Последний оператор END тела триггера должен заканчиваться знаком терминатора!!
6.13 Создание в Expert.
Функция «Новый триггер», контекстного меню.
6.14 Параметры.
Имя триггера.
Ассоциированная таблица.
Порядок, по умолчанию 0.
Тип действия.
Операторы тела.
6.15 Изменение триггера.
Изменить существующий триггер можно при помощи оператора
ALTER TRIGGER ИмяТриггера FOR ИмяТаблицы
[ACTIVE | INACTIVE]
{BEFORE | AFTER)
{DELETE | INSERT | UPDATE}
[POSITION номер] AS <тело триггера>
Имя триггера должно совпадать с именем существующего триггера базы данных.
6.16 Удаление триггера.
Для удаления триггера следует воспользоваться оператором
DROP TRIGGER ИмяТриггера
Триггеры, используемые для активных транзакций, не могут быть удалены, пока транзакция не завершена.
6.17 Примеры.
Для таблицы CREATE TABLE "NE1"("a1" INTEGER NOT NULL primary key,
"a2" CHAR(10), "a3" VARCHAR(10))
Создадим генератор: CREATE GENERATOR G1
Создаем триггер:
CREATE TRIGGER TR1 FOR "NE1"
ACTIVE BEFORE INSERT POSITION 0
AS begin
NEW."a1"= GEN_ID(g1,1);
End
Проверяем работу генератора.
insert into ne1("a2", "a3") values ('aa','фф') или по всем полям
insert into ne1 values (1,'aa','фф')
6.18 Предустановленные значения.
Ведение архива изменений БД, предусматривает задание пользователя, сделавшего изменения и времени, когда совершалось изменение.
Для определения этих параметров на сервере определены специальные переменные:
"NOW" и USER
Для фиксирования значений таблицы архива должны содержать соответствующие поля:
DAT_IZM TIMESTAMP,
USER_NAME CHAR(15),
Значение текущей даты имеет универсальный формат для любого типа поля, например DATE или TIME.
6.19 Вопросы по 3 лабораторной.
Объяснить необходимость обязательного определения первичного ключа в таблице.
В чем заключается смысл ссылочной целостности данных в реляционной модели.
Операторы определения ссылочной целостности данных, каскадного обновления и удаления.
SQL - оператор создания триггеров, алгоритмический язык проектирования триггеров.
Формат заголовка триггера, определение очередности срабатывания.
Формат определения старых и новых значений полей ассоциированной таблицы.
Определение тела триггера для заполнения автоинкрементного поля значением генератора.
Организация каскадных воздействий в таблицах при помощи триггеров.
Использование механизма триггеров для ведения архивов изменений в таблицах базы данных.
6.20 Задания СРСП.
1. Защита выбора механизма каскадного ведения данных.
2. Выбор и защита сложного бизнес – правила, реализуемого триггером.
3. Ответить на контрольные вопросы третьего модуля [1];
4. Провести отладку SQL – кода спроектированных триггеров;
5. Защитить отчет по третьей лабораторной работе;
6. Защитить отчет по разделу 3.3 курсовой работы [2];
7. Тест первого рубежного контроля.
8. Разработать пример вопроса тестового задания по теме раздела.
6.21 Задания СРС.
1. Изучить методические указания к третьей лабораторной работе [1];
2. Ответить на примеры тестовых заданий к третьему модулю [1];
3. Изучить SQL - код триггеров, используемых в учебной БД (FONEBOOK.GDB);
4. Проектирование и использование генераторов;
5. Изучение системы помощи (HELP), утилиты InteractivSQL, операторы: CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER, CREATE GENERATOR, SET GENERATOR, INSERT, UPDATA, DELETE;
6. Изучить конспект 5,6 лекций [3];
7. Среда управления SQL – сервера - IBExpress.
6.22 Демонстрация.
Создание триггера в среде IB Console.
Создание триггера заполнения автоинкрементного поля.
Оператор INSERT в таблицу с автоинкрементным полем.
Демонстрация изменения триггера.
Примеры реализации сложных бизнес правил.
Пробное тестирование.
6.23 Правила электронного тестирования.
1. Можно пропустить (не заполнять ячейку) несколько (2-3) вопроса для обсуждения на апелляции, нельзя пропускать 40-ой вопрос.
2. Если Вы не успели ответить на вопрос, но у Вас осталось время, можно в присутствии преподавателя вернуться к пропущенному вопросу, для проставления ответа.
3. При тестировании нельзя прокручивать список вопросов в обратную сторону.
4. Для перехода вперед, не дожидаясь срабатывания таймера достаточно кликнуть мышкой или нажать «стрелку вниз».
5. Ответы проставляются сразу в лист ответов без промежуточной записи.
6.24 Тренировочный тест, 10 вопросов.
Ответы
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
ХРАНИМЫЕ ПРОЦЕДУРЫ
7.1 Механизм хранимых процедур (Stored Procedures).
Определение и назначение.
Формат оператора создания объекта.
Преимущества использования.
Алгоритмический язык.
Проектирование в среде IBExpert.
Примеры реализации.
Вызов хранимых процедур в приложении.
Компоненты Delphi для работы с хранимыми процедурами.
IBStoredProc – вызов ХП действия.
IBQuery – вызов ХП выбора.
Совместимость переменных SQL – сервера и среды разработки Delphi.
Тренировочный тест к рубежному контролю.
7.2 Определение.
Хранимая процедура(Stored procedures) - это модуль, написанный на процедурном языке и хранящийся в базе данных как метаданные.
Хранимые процедуры, в отличие от триггеров, вызываются из приложений клиента, могут получать значения входных параметров и передавать данные в приложение.
7.3 Типы процедур.
Процедуры выбора могут возвращать более одного значения. В приложении имя хранимой процедуры выбора подставляется в оператор SELECTвместо имени таблицы или просмотра.
Процедуры действия возвращают одно значение или вообще могут не возвращать данных и используются для реализации каких-либо действий.
Хранимым процедурам можно передавать данные и получать обратно значения параметров, измененные в соответствии с алгоритмами работы хранимых процедур.
7.4 Преимущества.
Преимущества использования хранимых процедур:
1 одну процедуру можно использоваться многими приложениями;
2 разгрузка приложений клиента путем переноса части кода на сервер и вследствие этого - упрощение клиентских приложений;
3 при изменении хранимой процедуры все изменения немедленно становятся доступны для всех клиентских приложений; при внесении же изменений в приложение клиента требуется повторное распространение новой версии клиентского приложения между пользователями;
4 улучшенные характеристики выполнения, связанные с тем, что хранимые процедуры выполняются сервером, в частности - уменьшенный сетевой трафик.
7.5 Оператор создания.
Оператор создания хранимой процедуры:
CREATE PROCEDURE name
[(param <datatype> [, param <datatype>...])]
[RETURNS param <datatype> [, param <datatype>...])]
AS
DECLARE VARIABLE var <datatype>;
[DECLARE VARIABLE var <datatype>;...]
BEGIN
<compound_statement>
[<compound_statement>...]
END
7.6 Алгоритмический язык.
Объявление локальных переменных
DECLARE VARIABLE <имя переменной > <тип данных>;
Операторные скобки
BEGIN... END
Оператор присваивания
Имя переменной = выражение;
7.7 Операторы циклов.
Оператор условного перехода IF... THEN... ELSE
IF (<условие>) THEN
< оператор 1> [ELSE
< оператор 2>]
Оператор цикла FOR SELECT... DO
FOR
<оператор SELECT>
DO
< оператор>;
Оператор условного цикла WHILE... DO
WHILE (<условие>)
DO < оператор>
7.8 Оператор выбора SELECT.
SELECT AVG(KOLVO), SUM(KOLVO) FROM RASHOD
WHERE TOVAR =:INJTOVAR
INTO:AVG_KOLVO,:SUM_KOLVO;
Отличие в предложении INTO
ОператорSUSPENDвозвращает полученные значения в приложение.
7.9 Операторы.
Оператор завершения алгоритма
EXIT
Оператор вызоваEXECUTE PROCEDURE
EXECUTE PROCEDURE имя [параметр [, параметр …]]
[RETURNING_VALUES параметр [, параметр...]];
Оператор генерации события POST _EVENT
POST_EVENT "Имя события";
7.10 Подразделы SQL.
Data Definition Language (DDL) – язык определения данных. Те операторы, которые затрагивают структуру данных. Создают, изменяют и удаляют объекты метаданных. Операторы – CREATE, ALTER, DROP.
Data Manipulation Language (DML) – язык управления данными. Операторы INSERT, UPDATE, DELETE.
Data Control Language (DCL) – управление доступом к данным. Привилегии пользователей. Операторы GRANT и REVOKE.
Transaction Control Language (TCL) – язык управления изменениями, сделанными группами пользователей. Управление транзакциями. Операторы START TRANSACTION, COMMIT, ROLLBACK.
Cursor Control Language (CCL) – язык определения и управления курсором для подготовки выполнения SQL
7.11 Алгоритмический язык.
Язык проектирования триггеров и хранимых процедур включает:
1. DML и оператор SELECT.
2. Операторы и выражения, включая UDF, связанные с БД и генераторы.
3. Инструкции присвоения,
control – flow инструкции,
контекстные переменные только для триггеров,
инструкции обработки событий,
инструкции обработки ошибок.
7.12 Проектирование ХП.
7.13 Порядок действий.
Для проектирования ХП необходимо выполнить следующие действия:
1. Выделить пункт «Процедуры».
2. Вызвать контекстное меню.
3. Выбрать пункт «Новая процедура».
4. В открывшемся окне редактора задать:
- имя новой процедуры;
- входные и выходные параметры;
- код тела процедуры;
5. Выполнить команду компиляции кода (Ctrl+F9).
7.14 Код ХП для IBConsole.
SET TERM ^;
CREATE PROCEDURE COL_EMP
RETURNS (NUM_D INTEGER, CNT_EMP INTEGER)
AS BEGIN
FOR SELECT EMPLOYEE.NUM_D, count(EMPLOYEE.TAB_NUM)
FROM EMPLOYEE
GROUP BY EMPLOYEE.NUM_D
INTO:NUM_D,:CNT_EMP
DO SUSPEND; END ^
SET TERM; ^
7.15 Выполнение ХП.
Выполнение ХП действия FIND_COL учебного примера.
По заданному наименованию подразделения, подсчитывается количество телефонов в нем.
7.16 Вызов из SQL - редактора.
Для защиты лабораторной обязательно знание вызова процедуры в окне редактора SQL – запросов.
Для вызова процедур действия:
EXECUTE PROCEDURE FIND_COL
(‘Кафедра информационных систем’)
Для вызова процедур выбора:
SELECT * FROM D_ARX(2006)
7.17 Изменение ХП.
Изменение хранимой процедуры производится оператором:
ALTER PROCEDURE ИмяПроцедуры
[(входной_параметр тип_данных
[,входной_параметр тип_данных...])]
[RETURNS (выходной_параметр тип_данных
[,выходной_параметр тип_данных...])]
AS
<тело процедуры>;
7.18 Удаление ХП.
Для удаления хранимой процедуры из базы данных используется оператор:
DROP PROCEDURE ИмяПроцедуры;
7.19 Функции агрегирования.
Для построения сложных алгоритмов проверки используются агрегатные функции:
AVG(), SUM(), COUNT(),
MAX(), MIN(),
CAST(),
UPPER(),
EXTRACT()
и оператор SELECT с использованием ключевых слов
DISTINCT, GROUP BY, HAVING, LIKE и др.
7.20 EXTRACT().
Эта функция позволяет извлекать из полей типа даты значения месяца, дня, года, часов, минут и секунд для сравнения в предложении where.
extract (year from arx_emp.dat_izm) =:d_emp
month
day
hour
minute
second
Функция позволяет вычислять интервалы времени в годах, месяцах, часах и т.д.
7.21 Вопрос.
Какой тип данных имеет переменная d_emp?
1. DATE.
2. INTEGER.
3. TIMESTAMP.
4. NUMERIC().
5. Любой из перечисленных.
7.22 Пример.
Пример использования в операторе, извлечения года из поля даты:
for select
S_NUM, TAB_NUM, FIRST_NAME, LAST_NAME, PHONE, NUM_T, NUM_D, DEISTV
from arx_emp where
extract(year from arx_emp.dat_izm) =:d_emp
into:s_num,:tab_num,:first_name,:last_name,:phone,:num_t,:num_d,:deistv
do
7.23 Текущие параметры.
Для задания значения текущего времени используются следующие служебные операторы:
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
Например:
UPDATE TablName SET Name Field = CURRENT_TIME;
7.24 Вопрос.
Функция EXTRACT() позволяет извлекать компоненты из полей типа?
1. DATE.
2. TIME.
3. TIMESTAMP.
4. Только DATE и TIMESTAMP.
5. Нет правильного ответа.
7.25 Привилегии пользователя.
Для вызова хранимой процедуры из приложения, пользователю или объекту требуется привилегия EXECUTE на нее, задаваемая оператором:
GRANT
EXECUTE ON PROCEDURE ИмяПроцедуры
TO {<Пользователь>};
На момент выполнения операторов все объекты метаданных и пользователи должны быть определены.
7.26 Приложение Delphi.
Демонстрация работы хранимых процедур при помощи вызова их в среде разработки Delphi.
Для обращения к хранимой процедуре выбора в приложении клиента используется компонент TQuery. Вызов хранимой процедуры производится в предложении FROM оператора SELECT с указанием входных параметров процедуры. Выходные параметры процедуры (все или часть) указываются в качестве возвращаемых значений оператора SELECT.
Компонент IBStoredProc предназначен для вызова хранимых процедур действия SQL - сервера. Для вызова каждой хранимой процедуры в приложении лучше использовать собственный компонент.
7.27 Технология IBExpress.
Вкладка Delphi – «InterBase». Компоненты:
7.28 Вызов ХП выбора.
Новый проект с одной формой для вызова ХП «FIND_DIV»
7.29 Соединение с БД.
Для настройки параметров соединения вызываем окно редактора (Database Editor) компонента базы данных и устанавливаем имя пользователя, пароль, кодировку символов и убираем флаг в поле Login Prompt, после чего можно активизировать соединение.
7.30 Связи компонентов.
1. IBTransaction и IBDatabase
2. IBQuery и IBDatabase
3. DataSource и IBQuery
4. DBGrid и DataSource
Демонстрация проектирования приложения.
7.31 Параметры Query.
Для задания свойства SQL используется окно редактора запросов, вызов которого осуществляется нажатием поля этого свойства или выбором функции Edit SQL всплывающего меню компонента.
SELECT * FROM FIND_DIV(:in_divis),
Свойство SQL может определяться на стадии выполнения программы, через вызов соответствующего метода. В этом случае один компонент Query может использоваться для вызова различных процедур.
Свойство Params позволяет задать в Инспекторе Объектов свойство нашего параметра in_divis:
ParamType – ptInput,
DataType – ftString.
7.32 Код вызова.
Для запуска процедуры будем использовать событие от нажатия кнопки, в обработчик которого, записываем следующий код:
procedure TForm1.Button1Click(Sender: TObject);
begin
IBQuery1.Close;
IBQuery1.ParamByName('in_divis').Value:= StrToInt(Edit1.Text);
IBQuery1.Open;
end;
После выполнения всех действий проект компилируется и в запущенном приложении проверяется работа процедуры.
7.33 Ошибки.
Нет соединения с БД, не запущен сервер.
Не преобразован тип данных параметров.
Превышение лимита подключений клиентов.
Используется не тот метод, или не в той последовательности.
Выбрана не та процедура.
Работа не с тем файлом БД, копия файла в другом каталоге.
7.34 Вызов ХП действия.
Для вызова хранимой процедуры действия из приложения клиента используется специальный класс компонентов TStoredProc.
Рассмотрим последовательность действий для вызова процедуры INS_DIV, выполняющей добавление записи в таблицу DIVISION, содержащий код:
BEGIN
INSERT INTO DIVISION (NUM_DIV, NAME_DIV, "LEVEL", MAIL_DIV)
VALUES (:NUM_D,:NAME_D,:LEVELS,:e_mail);
END
В процедуре определяется четыре входных параметра
NUM_D,:NAME_D,:LEVELS,:e_mail
в соответствии с полями таблицы.
7.35 Проверка в Expert.
В среде проверка работы процедуры производится двумя способами, выполнением SQL оператора и запуском с консоли:
7.36 Приложение.
Добавляем в проект с вкладки InterBase поочередно следующие компоненты: IBTable; IBStoredProc. Для ввода значений параметров добавляем с вкладки Standard три компонента Edit. В результате форма примет вид:
7.37 Связи компонентов.
1. IBTransaction и IBDatabase
2. IBTable и IBDatabase, выбрать таблицу
3. IBStoredProc и IBDatabase, выбрать процедуру
4. DataSource и IBTable
5. DBGrid и DataSource
Демонстрация.
7.38 Параметры StoredProc.
Настройка свойств компонента хранимой процедуры заключается в выборе имени требуемой процедуры, свойство StoredProcName. После чего можно просмотреть созданные параметры:
0 – NUM_DIV;
1 – NAME_DIV;
2 – LEVELS;
3 – E_MAIL.
Определение типа данных происходит автоматически, в соответствии с типами данных Delphi, как и индексация параметров.
7.39 Код вызова.
В обработчике TForm1.Button1Click (Sender: TObject), сделаем следующие изменения:
{IBQuery1.Close;
IBQuery1.ParamByName('D_EMP').Value:= StrToInt(Edit1.Text);
IBQuery1.Open;}
IBStoredProc1.ParamByName('NUM_DIV').Value:=StrToInt(Edit1.Text);
IBStoredProc1.ParamByName('NAME_DIV').Value:=(Edit2.Text);
IBStoredProc1.ParamByName('LEVELS').Value:=StrToInt(Edit3.Text);
IBStoredProc1.ParamByName('E_MAIL').Value:=(Edit4.Text);
IBStoredProc1.ExecProc;
IBTable1.Refresh;
Обратить внимание на отсутствие ограничений полей.
7.40 Вопрос.
Сколько раз в разработанном приложении можно нажать на клавишу «Button1» с введенным набором данных, без появления ошибки?
1. Один.
2. Любое количество раз.
3. По одному разу для каждого нового набора данных.
4. По одному разу для каждого наименования подразделения.
5. Нет правильного ответа.
7.41 Заключение.
Механизм хранимых процедур, как самый универсальный и экономичный используется:
Для ведения данных серверных БД;
Для формирования наборов данных для построения отчетов;
При реализации сложных алгоритмов бизнес-правил, обеспечивающих целостность БД;
При формировании результатов сложных поисковых запросов.
7.42 Ограничения.
Механизм ХМ не может управлять структурой данных.
В отличии от компонента запроса (Query), в коде хранимой процедуры нельзя выполнять операторы управления структурой данных, таких как, CREATE …, ALTER…, DROP… и других.
Реализация функции управления зависит от программной реализации SQL – сервера. Например, MS SQL – сервер позволяет в хранимой процедуре выполнять оператор CREATE TABLE…
Вызов одной процедуры из нескольких приложений может привести к исключительной ситуации, вызванной нарушением ссылочной целостности данных, такие вопросы решаются на уровне управления транзакциями.
7.43 Вопрос.
Какой SQL – оператор создания хранимой процедуры не будет выполнен:
A) CREATE PROCEDURE Name_SP (EMP_NO SMALLINT, PROJ_ID CHAR (5))
AS BEGIN BEGIN
INSERT INTO "N_table" (emp_no, proj_id) VALUES (:emp_no,:proj_id);
WHEN SQLCODE -530 DO EXCEPTION unknown_emp_id;
END SUSPEND; END ^
B) CREATE PROCEDURE Name_SP (Var_I INTEGER)
AS BEGIN
INSERT (Col_Value) INTO "N_table" VALUES (:Var_I);
END^
C) CREATE PROCEDURE Name_SP
RETURNS (CODE VARCHAR (5), COUNTRY VARCHAR (15), LANG VARCHAR (15))
AS BEGIN
FOR SELECT job_code, job_country FROM job INTO:code,:country DO
BEGIN FOR SELECT languages FROM show_langs (:code,:country) INTO:lang DO
SUSPEND; code = '====='; country = '==============='; lang = '==============';
SUSPEND; END END ^
D) Все SQL – операторы будут выполнены.
7.44 Вопросы по 4 лабораторной.
Типы хранимых процедур, преимущества использования, особенности реализации.
Параметры хранимых процедур, определение параметров в теле процедуры.
Совместимость типов данных SQL – сервера и среды разработки приложений Delphi.
Операторы алгоритмического языка триггеров и хранимых процедур.
Особенности использования оператора SELECT в теле хранимой процедуры.
Операторы организации циклов в процедурах.
Вызов процедур в среде управления SQL – сервера, параметры анализа выполнения процедур.
Вызов процедур из приложения клиента. Использование процедур одновременно несколькими клиентскими приложениями.
Вызов процедур действия из приложения клиента, компонент TSTOREDPROC.
Вызов процедуры выбора, отображение набора данных, навигация по набору данных, изменение полученных данных.
7.45 Задания СРСП.
1. Выбор и защита не менее четырех процессов, реализуемых ХП;
2. Ответить на контрольные вопросы четвертого модуля [1];
3. Провести отладку SQL – кода спроектированных хранимых процедур;
4. Защитить отчет по четвертой лабораторной работе;
5. Защитить отчет по разделу 3.3 курсовой работы [2];
6. Разработать пример вопроса тестового задания по теме раздела.
7.46 Задания СРС.
1. Изучить методические указания к четвертой лабораторной работе [1];
2. Ответить на примеры тестовых заданий к четвертому модулю [1];
3. Изучить код ХП, используемых в учебной БД (FONEBOOK.GDB);
4. Изучение системы помощи (HELP), утилиты InteractivSQL, операторы: CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, EXECUTE PROCEDURE, SELECT;
5. Изучить конспект 7,8 лекций, агрегатные функции SQL – сервера [3];
6. Проектирование индивидуальной функции пользователя (UDF).
7.47 Демонстрация.
Создание хранимой процедуры действия, выводящей агрегированные значения.
Вызов ХП в редакторе SQL – запросов.
Порядок создания ХП выбора, метод от простого к сложному.
Вызов ХП выбора при помощи оператора SELECT.
Отладка процедуры средствами IBExpert.
Проектирование приложения для вызова ХП выбора, связи компонентов.
Проектирование приложения для вызова ХП действия.
Изменение и удаление хранимых процедур.
7.48 Тренировочный тест, 10 вопросов.
Ответы
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
UDF
8.1 Функции (UDF).
Функции, определяемые пользователем – User Defined Functions (UDF).
Определение и назначение.
Набор функций SQL – сервера InterBase.
Объявление функции, определяемой пользователем.
Разработка функций в Delphi.
Совместимость переменных SQL – сервера и среды разработки Delphi.
8.2 Определение.
Функции (UDF) предназначены для расширения возможностей серверной части проектируемой информационной системы, поскольку набор встроенных функций ограничен: AVG(), SUM(), COUNT(), MAX(), MIN(), CAST(), UPPER(), EXTRACT(), ||, математические операции.
Используется механизм подключения внешних библиотек.
Проектирование функций может осуществляться на любом алгоритмическом языке, позволяющем создавать динамически загружаемые библиотеки.
Реализация в виде dll – библиотеки. Одна библиотека должна содержать минимум одну функцию.
8.3 Примеры функций.
Встроенная библиотека функция сервера IB расположена в каталоге:
C:\Program Files\Borland\InterBase\UDF
Если каталог не существует, необходимо создать.
Каталог содержит файл библиотеки - ib_udf.dll, с примерами некоторых функций:
abs(), acos(), asin(), cos(), log(), rand() …
Пользователь имеет возможность подключать собственные библиотеки с наборами функций. В примере рассматривается проектирование функции, генерирующей случайный символ из заданного диапазона кодов.
8.4 Объявление функций.
Определение функции на сервере производится SQL – оператором:
DECLARE EXTERNAL FUNCTION name [datatype | CSTRING (int)
[, datatype | CSTRING (int) …]]
RETURNS {datatype [BY VALUE] | CSTRING (int)} [FREE_IT]
ENTRY_POINT 'entryname' // Имя функции
MODULE_NAME 'modulename'; // Имя файла (путь к библиотеке)
По умолчанию сервер ищет функцию в корневом каталоге, определяемом при инсталляции.
8.5 Пример объявления.
Для всех функций библиотеки примера ib_udf.dll создан файл описания ib_udf.sql, содержащий полный перечень операторов объявления на сервере, хранящийся в каталоге
C:\Program Files\Borland\InterBase\examples\Udf\ ib_udf.sql
Файл содержит краткое описание и операторы определения функций, готовые к выполнению в редакторе запросов среды управления.
Там же расположены модули проектов в языке программирования С.
8.6 Порядок действий.
Например, выполним объявление функции rand():
1. Открыть в блокноте файл «ib_udf.sql».
2. Скопировать оператор
DECLARE EXTERNAL FUNCTION rand
RETURNS DOUBLE PRECISION BY VALUE
ENTRY_POINT 'IB_UDF_rand' MODULE_NAME 'ib_udf';
3. Выполнить в редакторе запросов.
4. Проверить вызов функции.
8.7 Пример.
Просмотр объявленной функции в среде Expert:
8.8 Вопрос.
Функцию вызова значения генератора gen_id()можно использовать в операторе:
1. Создания триггера.
2. Создания хранимой процедуры.
3. INSERT.
4. UPDATE.
5. Нет правильного ответа.
8.9 Проверка вызова.
Проверка объявленной функции осуществляется посредством добавления записи в таблицу:
CREATE TABLE TUDF (T1 INTEGER,
T2 DOUBLE PRECISION, T3 FLOAT);
Выполнением нескольких операторов:
insert into tudf (T2) values (rand()); или
insert into tudf (T3) values (rand());
Обратите внимание на отсутствие случайности в данных.
8.10 Проектирование библиотеки.
Проектирование библиотеки функций в Delphi осуществляется в шаблоне DLL Wizard выбираемом из репозитория проектов:
8.11 Код функции.
В открывшемся окне создаем набор функций:
8.12 Листинг кода.
library MyIB_UDF;
Uses SysUtils, Classes;
{$R *.res}
function randomx(var InInt:Integer):Integer; cdecl; export;
begin
Result:=(Trunc(Int(Random(InInt))));
end;
function randvar():PChar; cdecl; export;
begin
Result:=PChar(Trim(AnsiString(Chr(192+Random(31)))));
end;
exports randomx, randvar;
begin
end.
8.13 Описание параметров.
cdecl – директива, указывающая компилятору на использование соглашений для передачи параметров, принятых в С++.
export – объявление функции экспортируемой.
Совместимость переменных.
Для совместимости строковых переменных InterBase и Delphi используются соответствующие форматы:
CSTRING – PChar;
DOUBLE PRECISION – Double;
8.14 Объявление.
Операторы объявления вновь созданных функций оформляются аналогично операторам встроенной библиотеки:
DECLARE EXTERNAL FUNCTION RANDVAR
RETURNS CSTRING(1) FREE_IT
ENTRY_POINT 'randvar' MODULE_NAME 'MyIB_UDF'
DECLARE EXTERNAL FUNCTION RANDOMX INTEGER
RETURNS INTEGER BY VALUE
ENTRY_POINT 'randomx' MODULE_NAME 'MyIB_UDF'
Библиотеку необходимо разместить в каталоге
C:\Program Files\Borland\InterBase\UDF
8.15 Удаление библиотеки.
Удаление объявленных функций библиотеки производится оператором:
DROP EXTERNAL FUNCTION name;
При внесении изменении в оператор объявления функции IBExpert генерирует два оператора, на удаление старого объявления, и создание нового.
8.16 Вопрос.
Выбрать наиболее точный ответ для среды разработки функций, определяемых пользователем в SQL – сервере InterBase:
1. Функции процедурного языка и SQL – операторы сервера InterBase.
2. Элементы языка управления данными (DML) и типы данных, используемых в SQL – сервере InterBase.
3. Элементы любого алгоритмического языка, позволяющего создавать динамически загружаемые библиотеки (DLL).
4. Функции и типы данных, определенные в Delphi, для динамически загружаемых библиотек.
5. Любые функции и типы данных, используемых в языке Object Pascal.
8.17 Предупреждения.
Объектом метаданных является оператор объявления функции, но не сама библиотека.
Для транспортировки всех компонентов системы, требуется перенос всех файлов библиотек в определенном каталоге.
Для каждой задачи может создаваться собственный каталог для библиотек расширения.
8.18 Версия IB 7.
Из описания к 7 версии IB (DevGuide.pdf).
Встроенная библиотека - ib_util.dll, примеры:
Function
