Главная >> Методички

Pages: |

| 2 |

«РАЗРАБОТКА КЛИЕНТ-СЕРВЕРНЫХ ПРИЛОЖЕНИЙ С ИСПОЛЬЗОВАНИЕМ ТЕХНОЛОГИЙ ORACLE Учебно-методическое пособие Омск • 2008 Федеральное агентство по образованию Сибирская государственная автомобильно-дорожная академия (СибАДИ) ...»

-- [ Страница 1 ] --

И.И. Семенова

РАЗРАБОТКА КЛИЕНТ-СЕРВЕРНЫХ

ПРИЛОЖЕНИЙ

С ИСПОЛЬЗОВАНИЕМ ТЕХНОЛОГИЙ

ORACLE

Учебно-методическое пособие

Омск • 2008

Федеральное агентство по образованию

Сибирская государственная автомобильно-дорожная академия

(СибАДИ)

И.И. Семенова

РАЗРАБОТКА КЛИЕНТ-СЕРВЕРНЫХ

ПРИЛОЖЕНИЙ

С ИСПОЛЬЗОВАНИЕМ ТЕХНОЛОГИЙ

ORACLE

Учебно-методическое пособие Омск Издательство СибАДИ УДК 004. ББК 32.973.2- С Рецензенты:

канд. техн. наук, доцент кафедры АСОИУ, В.Н. Цыганенко, ОмГТУ канд. техн. наук, доцент кафедры СС иИБ, В.Г. Осипов, ОмГТУ Работа одобрена редакционно-издательским советом академии в качестве учебно-методического пособия для специальностей 230102 «Автоматизированные системы обработки информации и управления», 080801 «Прикладная информатика в экономике», 090105 «Комплексное обеспечение информационной безопасности автоматизированных систем».

Семенова И.И.

С 30 Разработка клиент-серверных приложений с использованием технологий Oracle: Учебно-методическое пособие.– Омск: Изд-во СибАДИ,2008.–121 с.

ISBN 978–5–93204–420– Основной целью создания данного учебно-методического пособия стала необходимость закрепления навыков работы в одной из современных СУБД с целью создания приложений для различных предметных областей у студентов высших учебных заведений, изучающих дисциплину “Системы управления базами данных”.

Учебно-методическое пособие по курсу «Системы управления базами данных» предназначено для студентов, обучающихся по специальностям 230102, 080801, 090105.

Табл. 17 Ил. 24 Библиогр.: 7 назв.

ОБЩИЕ ПОЛОЖЕНИЯ

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

Изучение этих вопросов предусмотрено действующим государственным образовательным стандартом и в данном учебно-методическом пособии базируется на решении задач, актуальных для студентов специальности «Автоматизированные системы обработки информации и управления»

(АСОИУ), «Прикладная информатика в экономике» (ПИЭ), «Комплексное обеспечение информационной безопасности автоматизированных систем»

(КОИБАС).

В серии лабораторных работ используются Oracle 9i, Borland Delphi 7.

Важной составной частью работ является освоение SQL стандарта. Чтобы приступить к выполнению этих работ, студенту уже нужно иметь минимальные навыки обращения с используемыми в них программными средствами. Эти необходимые навыки даются студентам на установочных занятиях в начале семестра вместе с выдачей заданий и электронных материалов.

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

Лабораторная работа №

СОЗДАНИЕ ОБЪЕКТОВ БАЗЫ ДАННЫХ (БД) В ORACLE 9i

Цель работы – с помощью операторов языка PL/SQL научиться создавать пользователя, который будет управлять всеми объектами создаваемой базы данных, схему, табличное пространство для хранения объектов базы данных и совокупность связанных таблиц, принадлежащих указанной базе данных в конкретной схеме.

Содержание работы:

1. Познакомиться с набором утилит, входящих в состав Oracle 9i.

2. Познакомиться с работой утилит Enterprise MANAGER Console и SQL *Plus Worksheet.

3. Создать с помощью приведенных операторов пример базы данных «Книжное дело».

4. По выданным вариантам создать персональную базу данных с набором связанных таблиц.

В качестве примера базы данных, которая будет создана программно с помощью операторов языка PL/SQL, выберем БД «Книжное дело» (рис.

1.1). Структура таблиц данной БД представлена в табл. 1.1-1.5.

Amount Code_purchase Type_purchase Char (1) Code_purchase Number (5)

PRIMARY

Title_book VarChar Code_author Number (3) Code_publish Number (4) Справочник авторов (название таблицы Authors) Code_author Number (3)

PRIMARY

Name_author Char (30) Справочник поставщиков (название таблицы Deliveries) Code_delivery Number(4)

PRIMARY

Name_delivery Char (35) Name_company Char (30) Справочник издательств (название таблицы Publishing_house) Code_publish Number(4)

PRIMARY

Прежде чем создавать таблицы БД, необходимо создать схему (она будет создана автоматически при создании пользователя) и соответствующее табличное пространство, в котором будут храниться объекты БД.

Это можно сделать программно и интерактивно.

Интерактивный способ создания реализуется через утилиту Enterprise Manager Console. Для соединения с экемпляром БД Oracle при первом соединении выберите режим Launch standalone, далее в появившемся окне дважды щелкните по экземпляру БД и воспользуйтесь учетной записью System с паролем по умолчанию manager и уровнем доступа SYSDBA (рис. 1.2).

Перед созданием табличного пространства необходимо создать пользователя, который будет играть роль администратора создаваемой БД.

Для этого выполните действие, как показано на рис. 1.3.

Процесс создания пользователя в интерактивном режиме выглядит следующим образом (рис. 1.4):

Рис. 1.4. Настройка привилегий пользователя Теперь необходимо переподключиться к экземпляру БД под созданной учетной записью (рис. 1.5).

Затем приступаем к созданию табличного пространства (рис. 1.6).

Рис. 1.6. Пример создания табличного пространства Введем параметры, как показано на рис. 1.7.

Рис. 1.7. Настройка свойств табличного пространства Теперь переопределяем ранее созданного пользователя ADMIN_BOOKS на работу только в этом табличном пространстве (рис.

1.8).

Рис. 1.8. Изменение свойств существующего пользователя Затем можно приступать к созданию таблиц БД в табличном пространстве, как показано на рис. 1.9.

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

1.1 – 1.5, по примеру, показанному ниже на рис. 1.10.

Кроме того, при создании новой таблицы на вкладке Constraints Storage в качестве табличного пространства для хранения индекса выбираем DB_BOOKS (рис. 1.11).

Те же самые операции можно выполнить программно, используя операторы SQL стандарта, реализованные в Oracle как операторы встроенного языка PL/SQL.

Для выполнения операторов можно запустить утилуту SQL *Plus Worksheet через меню Windows или через утилиту Enterprise Manager Console, выбрав на левой панели соответствующую пиктограмму, как показано на рис. 1.12.

Рис. 1.12. Пример запуска утилиты SQL * Plus Worksheet При запуске SQL *Plus Worksheet подключиться к серверу с помощью пользователя System с паролем по умолчанию manager и уровнем доступа SYSDBA, в качестве SERVICE ввести имя экземпляра БД, в данном примере это ORCL.

Вводим набор операторов для создания администратора создаваемой БД.

CREATE USER "ADMIN_BOOKS" PROFILE "DEFAULT" IDENTIFIED BY "worlds"

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP"

ACCOUNT UNLOCK;

GRANT "CONNECT" TO "ADMIN_BOOKS" WITH ADMIN OPTION;

GRANT "DBA" TO "ADMIN_BOOKS" WITH ADMIN OPTION;

GRANT "EXP_FULL_DATABASE" TO "ADMIN_BOOKS" WITH

ADMIN OPTION;

Пример ввода программного кода показан на рис. 1.13.

Для запуска операторов на исполнение выделяем группу операторов мышкой и нажимаем F5. Если нет ошибок, то на каждый оператор система в нижней половине экрана выдаст сообщения: «Пользователь создан», «Привилегии предоставлены».

Теперь необходимо переподключиться к экземпляру БД под созданной учетной записью, для этого выберите значок «Электрическая вилка» и введите в появившемся окне данные для подключения под новым пользователем.

Рис. 1.13. Пример работы утилиты SQL * Plus Worksheet Приступаем к созданию табличного пространства программно (знак ; в конце оператора обязателен).

CREATE TABLESPACE "DB_BOOKS"

LOGGING

DATAFILE 'C:\ORACLE\ORADATA\ORCL\DB_BOOKS.dbf' SIZE 5M

EXTENT

MANAGEMENT LOCAL;

Для запуска оператора на исполнение выделяем его мышкой и нажимаем F5. Если нет ошибок, то на оператор система в нижней половине экрана выдаст сообщение: «Раздел создан».

Теперь переопределяем ранее созданного пользователя ADMIN_BOOKS на работу только в табличном пространстве DB_BOOKS.

ALTER USER "ADMIN_BOOKS" DEFAULT TABLESPACE

"DB_BOOKS";

Для запуска оператора на исполнение выделяем его мышкой и нажимаем F5. Если нет ошибок, то на оператор система в нижней половине экрана выдаст сообщение: «Пользователь изменен».

Создаем таблицы, структура которых представлена в табл. 1.1 – 1.5, по примеру представленного кода в утилите SQL *Plus Worksheet:

CREATE TABLE "ADMIN_BOOKS"."AUTHORS" ("CODE_AUTHOR" NUMBER(3) NOT NULL, "NAME_AUTHOR" CHAR(30), "BIRTHDAY" DATE, CONSTRAINT "ID_AUTHOR" PRIMARY KEY("CODE_AUTHOR") USING INDEX TABLESPACE "DB_BOOKS") TABLESPACE "DB_BOOKS";

CREATE TABLE "ADMIN_BOOKS"."DELIVERIES" ("CODE_DELIVERY" NUMBER(4) NOT NULL, "NAME_DELIVERY" CHAR(35), "NAME_COMPANY" CHAR(30) NOT NULL, "ADDRESS" VARCHAR2(100), "PHONE" CHAR(11) NOT NULL, "INN" CHAR(13), CONSTRAINT "ID_DELIVERY" PRIMARY KEY("CODE_DELIVERY") USING INDEX TABLESPACE "DB_BOOKS") TABLESPACE "DB_BOOKS";

CREATE TABLE "ADMIN_BOOKS"."PUBLISHING_HOUSE" ("CODE_PUBLISH" NUMBER(4) NOT NULL, "PUBLISH" CHAR(30) NOT NULL, "CITY" CHAR(15), CONSTRAINT "ID_PUBLISH" PRIMARY KEY("CODE_PUBLISH") USING INDEX TABLESPACE "DB_BOOKS") TABLESPACE "DB_BOOKS";

CREATE TABLE "ADMIN_BOOKS"."BOOKS" ("CODE_BOOK" NUMBER(5) NOT NULL, "TITLE_BOOK" VARCHAR2(100) NOT NULL, "CODE_AUTHOR" NUMBER(3) NOT NULL, "PAGES" NUMBER(4), "CODE_PUBLISH" NUMBER(4) NOT NULL, CONSTRAINT "ID_BOOK_FK" PRIMARY KEY("CODE_BOOK") USING INDEX TABLESPACE "DB_BOOKS", CONSTRAINT "ID_AUTHOR_FK" FOREIGN KEY("CODE_AUTHOR") REFERENCES "ADMIN_BOOKS"."AUTHORS"("CODE_AUTHOR") ON

DELETE CASCADE, CONSTRAINT "ID_PUBLISH_FK" FOREIGN

KEY("CODE_PUBLISH") REFERENCES "ADMIN_BOOKS"."PUBLISHING_HOUSE"("CODE_PUBLISH") ON DELETE CASCADE) TABLESPACE "DB_BOOKS";

CREATE TABLE "ADMIN_BOOKS"."PURCHASES" ("CODE_BOOK" NUMBER(5) NOT NULL, "DATE_ORDER" DATE NOT NULL, "CODE_DELIVERY" NUMBER(4) NOT NULL, "TYPE_PURCHASE" CHAR(1) NOT NULL, "COST" NUMBER(10,2) NOT NULL, "AMOUNT" NUMBER(4) NOT NULL, "CODE_PURCHASE" NUMBER(5) NOT NULL, CONSTRAINT "ID_PURCHASE" PRIMARY KEY("CODE_PURCHASE") USING INDEX TABLESPACE "DB_BOOKS", CONSTRAINT "ID_BOOK_FK2" FOREIGN KEY("CODE_BOOK") REFERENCES "ADMIN_BOOKS"."BOOKS"("CODE_BOOK"), CONSTRAINT "ID_DELIVERY_FK" FOREIGN KEY("CODE_DELIVERY") REFERENCES "ADMIN_BOOKS"."DELIVERIES"("CODE_DELIVERY")) TABLESPACE "DB_BOOKS";

Не забывайте: сначала создаются справочники, в которых есть только ключевые поля типа PRIMARY, а затем другие таблицы, в которых создаются внешние ключи типа FOREIGN KEY.

Использованные операторы:

PRIMARY KEY – признак создания ключевого поля.

FOREIGN KEY...REFERENCES… – признак создания поля связи с другой таблицей.

CREATE TABLE – команда создания таблицы в текущей БД.

В табл. 1.6 описаны типы данных при создании таблиц БД, особенность которых заключается в том, что поведение некоторых типов данных и одноименных типов переменных в PL/SQL отличаются, на что необходимо обратить внимание. Типы переменных PL/SQL и их особенности описаны в лабораторной работе №4.

Точность Число с заданной точно- NUMBER(точность, масNUMBER LONG RAW 2Гб RAW Char и Varchar2 в автоматическом преобразовании данных при передаче DATE 01.01. до нэ своения даты использу- PEREM1:=TO_DATE('21-декется функция нэ TO_DATE(‘символьная PEREM2:=TO_DATE('21-12дата’, ‘формат даты’) 1980', 'dd-mm-yyyy')

UROWID

ссылки на строки таблицы. Тип UROWID (универсальная ссылка) может хранить физические, BFILE Варианты заданий к лабораторной работе № Общие положения В утилите Enterprise Manager Console создайте пример базы данных, как показано по ходу работы.

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

В утилите Enterprise Manager Console создать вашего пользователя, табличное пространство и таблицы БД. Перед созданием очередного объекта (т.е. до нажатия клавиши Create) скопируйте SQL код создаваемого объекта, который можно увидеть, нажав клавишу Show SQL.

Сохранить последовательно операторы в файле программы с названием ФамилияСтудента_ЛАб_1_№варианта. Данные операторы потребуются для переноса БД с одного сервера БД на другой.

Вариант 1. БД «Учет выданных подарков несовершеннолетним детям сотрудников предприятия».

Должность Подразделение Дата приема на работу Вариант 2. БД «Учет выполненных ремонтных работ».

Дата производства Дата приема в ремонт Отчество мастера Вариант 3. БД «Продажа цветов».

Вариант 4. БД «Поступление лекарственных средств».

Название лекарства Код поставщика Сокращенное название Показания к применению Дата поставки Полное название Единица измерения Цена за единицу Юридический адрес Название производителя Код поступления ФИО руководителя Вариант 5. БД «Списание оборудования».

Название оборудования Причина списания Фамилия Вариант 6. БД «Поваренная книга».

Порядок приготовления Количество калорий Количество углеводов Вариант 7. БД «Регистрация входящей документации».

Код регистратора Код документа Код организацииотправителя Дата приема на работу Код организации- ФИО руководителя Вариант 8. БД «Увольнение сотрудника».

Подразделение Код сотрудника Дата приема на работу Денежная компенсация Вариант 9. БД «Приказ на отпуск».

Подразделение Код сотрудника Дата приема на работу Код отпуска Вариант 10. БД «Регистрация выходящей документации».

Код отправителя Код документа Код организацииполучателя Дата приема на работу Код организации- ФИО руководителя Вариант 11. БД «Назначение на должность».

Дата приема на работу Код сотрудника Вариант 12. БД «Выдача оборудования в прокат».

Серия и номер паспорта Стоимость Вариант 13. БД «Списание оборудования из проката».

Код оборудования Код оборудования Код сотрудника Название оборудования Причина списания Фамилия Дата поступления в прокат Код сотрудника Отчество Вариант 14. БД «Прием цветов в магазин».

Дополнительные сведения Вариант 15. БД «Регистрация клиентов гостиницы».

Вариант 16. БД «Возврат оборудования в службу проката».

Серия и номер паспорта Штраф Вариант 17. БД «Учет материальных ценностей на предприятии».

Закупочная стоимость Дата постановки на учет Отчество Вариант 18. БД «Состав ремонтных работ».

Код ремонтной работы Код ремонтной работы Код мастера Название этапа работы Стоимость ремонта Имя мастера Стоимость этапа Количество дней ремонта Отчество мастера Вариант 19. БД «Продажа лекарственных средств».

Количество в упаковке Код записи в чеках Название производителя Вариант 20. БД «Учет исполнения по входящей документации».

Подразделение Факт исполнения Организация-отправитель

ЗАПОЛНЕНИЕ ТАБЛИЦ ORACLE 9i ДАННЫМИ

Цель работы – изучить и освоить все способы заполнения данными таблиц Oracle.

Содержание работы:

1. Создать с помощью приведенных операторов пример базы данных «Книжное дело», описанный в предыдущей лабораторной работе (если БД отсутствует на сервере).

2. С помощью утилиты Enterprise Manager Console заполнить таблицы данными (по 3-5 записей).

3. С помощью операторов Insert создать программу в SQL *Plus Worksheet для заполнения таблиц данными (по 3-5 записей).

4. Научиться создавать последовательности и соединять их с полями таблиц.

5. Выполнить задания по вариантам.

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

Для заполнения таблиц данными можно воспользоваться несколькими способами:

1) Заполнение таблиц непосредственно через утилиту Enterprise 2) Заполнение с помощью программного кода через утилиту SQL 3) Заполнение данными через интерфейс внешнего приложения или клиентского приложения, например, создав приложение в MS Access, Borland Delphi, PHP и т.д.

Рассмотрим каждый пример заполнения.

В первом случае запускаем утилиту Enterprise Manager Console. Для соединения с экземпляром БД Oracle выберите режим Launch standalone, далее в появившемся окне дважды щелкните по экземпляру БД и воспользуйтесь учетной записью ADMIN_BOOKS с паролем worlds и уровнем доступа SYSDBA.

В разделе Schema выбрать объекты типа Table, далее выбрать схему пользователя ADMIN_BOOKS, далее в правой части экрана на соответствующей таблице в контекстном меню выбрать пункт Table data editor.

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

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

Пример ввода представлен на рис. 2.1.

В втором случае программного заполнения таблиц запускаем утилиту SQL *Plus Worksheet. Для соединения с экемпляром БД Oracle выберите режим Launch standalone, далее в появившемся окне дважды щелкните по экземпляру БД и воспользуйтесь учетной записью ADMIN_BOOKS с паролем worlds и уровнем доступа SYSDBA.

INSERT INTO "ADMIN_BOOKS"."AUTHORS" ("CODE_AUTHOR", "NAME_AUTHOR", "BIRTHDAY") VALUES (3, 'Лермонтов', TO_DATE('12-05-1810') );

Или в случае совпадения заполняемых значений с порядком столбцов в таблице можно названия столбцов пропустить:

'Лермонтов', TO_DATE('12-05-1810') );

Последовательно наберите в SQL *Plus Worksheet операторы INSERT, разделяя их точкой с запятой, а также используйте в конце оператор Commit для фиксации изменений в таблице.

Пример:

INSERT INTO authors VALUES(6, 'Гончаров', TO_DATE('12-03dd-mm-yyyy'));

--другие операторы INSERT;

COMMIT;

Для выполнения нажмите F5. Если нет ошибок, то на каждый оператор система в нижней половине экрана выдаст сообщение: «1 строка создана». На оператор Commit система выдаст сообщение: «Фиксация обновлений завершена».

Для просмотра результата заполнения можно воспользоваться оператором SELECT, например SELECT * FROM "ADMIN_BOOKS"."AUTHORS";

SELECT * FROM ADMIN_BOOKS.AUTHORS;

В третьем случае заполнения таблиц через клиентское приложение создадим соединение с объектами Oracle через ODBC драйвер. Для этого в операционной среде через ПУСК зайти в Панель управления, далее в разделе Производительность и обслуживание выбрать подраздел Администрирование, затем выбрать ссылку на утилиту Источники данных (ODBC). Далее выбрать тип «Системный источник данных» (System Name или System DSN), добавить новый источник данных, выбрав в появившемся списке драйверов Oracle. В следующем окне в качестве имени источника данных (Data Source Name) написать DB_BooksDSN, в поле TSN Service Name указать название вашего экземпляра БД, в данном примере это ORCL, в поле User ID указать ADMIN_BOOKS. Проверить соединение, нажав Test Connection. В случае успешной проверки сохранить результат.

Теперь можно подключаться к БД через любой интерфейс, рассмотрим пример работы через MS Access.

Создадим новый проект БД в MS Access. В разделе Таблицы в контекстном меню необходимо выбрать Связь с таблицами. В появившемся окне в качестве типа файлов выбрать ODBC Databases. Далее на второй вкладке Источники данных компьютера (Machine Source Name) выбрать DB_BooksDSN. Далее в окне Oracle ODBC Driver Connect ввести пароль доступа. При успешном прохождении аутентификации появится окно Связь с таблицами, в котором необходимо выбрать пять таблиц из схемы ADMIN_BOOKS. Если все выполено правильно, то результат будет таким, как показано на рис. 2.2.

Рис. 2.2. Пример связи с таблицами Oracle в Access Теперь можно создать формы ввода данных и приступить к заполнению таблиц. Например, для таблицы Книги можно создать следующую форму (рис. 2.3).

Рис. 2.3. Пример созданных форм в Access для ввода данных в таблицы Oracle Для контроля ввода значений в отдельные столбцы таблиц можно использовать специальные объекты Domain и Sequence.

Создание и использование последовательностей Последовательность (SEQUENCE) – это объект, обеспечивающий генерацию уникальных последовательностей в условиях многопользовательского асинхиронного режима доступа.

Пример создания последовательности:

CREATE SEQUENCE "ADMIN_BOOKS"."ID_AUTHOR_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 99999 MINVALUE NOCYCLE CACHE 20 NOORDER;

Для обращения к значениям последовательности используют следующие операторы:

Пример использования последовательности:

INSERT INTO "ADMIN_BOOKS"."AUTHORS" ("CODE_AUTHOR", (ADMIN_BOOKS.ID_AUTHOR_SEQ.NextVal, 'Лермонтов', TO_DATE('12Варианты заданий к лабораторной работе № В утилите SQL *Plus Worksheet по вашим вариантам баз данных, которые были реализованы в лабораторной работе №1, создать для полей типа Primary Key последовательности c помощью оператора CREATE SEQUENCE.

В утилите SQL *Plus Worksheet создать файл, в котором написать последовательность операторов Insert для заполнения таблиц данными (по 10 операторов на каждую из таблиц).

Сохранить все операторы CREATE SEQUENCE и INSERT в файле программы с названием ФамилияСтудента_ЛАб_2_№варианта.

УПРАВЛЕНИЕ ПРАВАМИ ДОСТУПА И РАЗРЕШЕНИЯМИ

К СОЗДАВАЕМЫМ ОБЪЕКТАМ БД В ORACLE 9i

Цель работы – научиться создавать профили пользователей и ролей для разграничения прав доступа к объектам создаваемой БД, а также извлекать системную информацию о пользоватях и объектах для последующего использования в удаленном управлении правми доступа к объектам экземпляра БД.

Содержание работы:

1. Познакомиться с назначением системных таблиц и представлений, выполнить примеры.

2. С помощью операторов и действий, описанных в лабораторной работе, создать роли и пользователей.

3. Выполнить самостоятельное задание к лабораторной работе по вариантам.

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

Для анализа ролей, системных привилегий и привилегий по доступу к объектам экземпляра БД, которыми наделены пользователи, необходимо зайти в утилите Enterprise Manager Console в раздел Security – Users и дважды щелкнуть по имени пользователя.

Для обеспечения безопасности работы экземпляра БД необходимо после инсталляции программного продукта проверить состав пользователей и их статус (в утилите Enterprise Manager Console в раздел Security – Users в правой части экрана колонка Account Status). Если необходимо заблокировать какого-либо пользователя, то выберите его двойным щелчком, в появившемся окне в разделе Status выбрать режим Locked.

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

3.1 дан краткий список объектов, которые используются в данной работе.

System_privilege_map Table Table_privilege_map Table Dba_role_privs View Для просмотра информации о пользователе можно воспользоваться оператором:

SELECT username, user_id, password, account_status, default_tablespace, temporary_tablespace, profile FROM dba_users WHERE username = 'SYSTEM';

Следующий пример позволяет определить список привилегий на работу с объектами:

SELECT sysauth$.*, table_privilege_map.name, user$.name FROM sysauth$,table_privilege_map, user$ WHERE sysauth$.grantee#=user$.user# and table_privilege_map.privilege = sysauth$.privilege# and user$.name='ADMIN_BOOKS';

или, используя представление SYS.dba_sys_privs:

SELECT * FROM sys.dba_sys_privs WHERE grantee='ADMIN_BOOKS';

Следующий пример позволяет определить список системных привилегий введенного пользователя:

SELECT sysauth$.*, system_privilege_map.name, user$.name FROM sysauth$, system_privilege_map, user$ WHERE sysauth$.grantee#=user$.user# and system_privilege_map.privilege = sysauth$.privilege# and user$.name='ADMIN_BOOKS';

Следующий пример позволяет определить список присвоенных ролей введенного пользователя:

SELECT * FROM dba_role_privs WHERE grantee = 'ADMIN_BOOKS';

Результат:

GRANTEE GRANTED_ROLE ADM DEF

ADMIN_BOOKS DBA YES YES

ADMIN_BOOKS EXP_FULL_DATABASE YES YES

Для определения списка таблиц, собственником которых является конкретный пользователь, необходимо выполнить следующий SQL запрос:

SELECT table_name FROM all_all_tables WHERE owner = 'ADMIN_BOOKS';

Результат:

TABLE_NAME -----------------------------AUTHORS BOOKS

DELIVERIES

PUBLISHING_HOUSE

PURCHASES

Для прямого получения информации о пользователе можно обратиться к системной таблице SYS.USER$, например:

SELECT * FROM sys.user$ WHERE name = 'ADMIN_BOOKS';

Например, необходимо определить список всех объектов пользователя, собственником которых он является, для этого:

Определим код пользователя (колонка USER# в таблице SYS.USER$):

SELECT user#, name FROM sys.user$ WHERE name = 'ADMIN_BOOKS';

Результат:

---------- -----------------------------ADMIN_BOOKS Далее определим перечень объектов, собственником которых является пользователь:

SELECT OBJ#, NAME FROM SYS.OBJ$ WHERE OWNER# = 67;

где 67 – идентификатор пользователя ADMIN_BOOKS, определенный по предыдущему запросу.

Результат:

---------- -----------------------------AUTHORS 32177 BOOKS_ALL 32162 CHECKNAME 32157 COUNT_BOOKS 32161 COUNT_BOOKS_ITOGO 32159 COUNT_BOOKS_PAGES 32160 COUNT_BOOKS_TITLE 32166 COUNT_PURCHASES 32129 DELIVERIES 32128 ID_AUTHOR 32147 ID_BOOK_SEQ 32130 ID_DELIVERY 32132 ID_PUBLISH 32138 ID_PURCHASE 32131 PUBLISHING_HOUSE 32137 PURCHASES 32165 UPDATE_PROC 32167 UPDATE_PROC 20 строк выбрано.

Можно просмотреть состав каждого объекта-таблицы, например таблицы AUTHORS, используя ее идентификационный номер, который определили в предыдущем запросе:

SELECT name, type# FROM sys.col$ WHERE obj# = '32127';

Результат:

SYS_C00004_07073100:07:39$ или, используя имя объекта:

SELECT c.name, c.type# FROM sys.col$ c INNER JOIN sys.obj$ o ON c.obj#= o.obj# WHERE o.name = 'AUTHORS';

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

Создадим для нашего примера роли библиотекаря (LIBRAR) и читателя (READER).

Пример создания роли библиотекаря:

CREATE ROLE "LIBRAR" IDENTIFIED BY "LIBRAR";

Пример создания роли читателя:

CREATE ROLE "READER" IDENTIFIED BY "READER";

Библиотекарь должен обладать правами на чтение, удаление, изменение, добавление во все таблицы схемы ADMIN_BOOKS, а также должен иметь возможность инициировать сессию и запускать на исполнение процедуры и функции схемы ADMIN_BOOKS. Поэтому роли библиотекаря из системных привилегий назначаем CREATE SESSION, а из привилегий доступа к объектам назначаем DELETE, INSERT, UPDATE, SELECT, позже в лабораторной работе №5 для созданных процедур и функций добавить EXECUTE.

Читатель должен обладать правами на чтение из таблиц ADMIN_BOOKS.PUBLISHING_HOUSE, а также должен иметь возможность инициировать сессию и запускать на исполнение процедуры и функции схемы ADMIN_BOOKS. Поэтому роли читателя из системных привилегий назначаем CREATE SESSION, а из привилегий доступа к объектам назначаем SELECT, позже в лабораторной работе №5 для созданных процедур и функций добавить EXECUTE.

Синтаксис:

GRANT,...

ON < объект >, … [WITH grant option];

Атрибут with grant option дает право пользователю самому раздавать права, которые он получил.

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

Пример: grant select, update (Sales, num) ON Sales_data TO user with grant option;

Пользователь, предоставивший привилегию другому, называется грантор (grantor — предоставитель). Привилегия является предоставляемой, если право на нее можно предоставить другим пользователям.

PUBLIC — имя пользователя, который распространяет привилегии на все множество зарегистрированных в системе пользователей.

Роль библиотекаря названа LIBRAR с аналогичным паролем LIBRAR.

Операторы назначения прав доступа ниже:

GRANT DELETE, INSERT, UPDATE, SELECT

ON "ADMIN_BOOKS"."BOOKS" TO "LIBRAR";

GRANT DELETE, INSERT, UPDATE, SELECT

ON "ADMIN_BOOKS"."AUTHORS" TO "LIBRAR";

GRANT DELETE, INSERT, UPDATE, SELECT

ON "ADMIN_BOOKS"."DELIVERIES" TO "LIBRAR";

GRANT DELETE, INSERT, UPDATE, SELECT

ON "ADMIN_BOOKS"."PUBLISHING_HOUSE" TO "LIBRAR";

GRANT DELETE, INSERT, UPDATE, SELECT

ON "ADMIN_BOOKS"."PURCHASES" TO "LIBRAR";

GRANT CREATE SESSION TO "LIBRAR";

Commit;

Роль читателя названа READER с аналогичным паролем READER. Операторы назначения прав доступа ниже:

GRANT SELECT ON "ADMIN_BOOKS"."BOOKS" TO "READER";

GRANT SELECT ON "ADMIN_BOOKS"."AUTHORS" TO "READER";

GRANT SELECT ON "ADMIN_BOOKS"."PUBLISHING_HOUSE" TO "READER";

GRANT CREATE SESSION TO "READER";

Commit;

Создание пользователей с определенной ролью Пример создания библиотекаря Ivanov_Lib и присвоения роли:

CREATE USER "Ivanov_Lib" PROFILE "DEFAULT" IDENTIFIED BY "Ivanov_Lib" DEFAULT TABLESPACE "DB_BOOKS"

TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;

GRANT "LIBRAR" TO "Ivanov_Lib";

Commit;

Пример создания читателя Petrov_Read и присвоения роли:

CREATE USER "Petrov_Read" PROFILE "DEFAULT" IDENTIFIED BY "Petrov_Read"

DEFAULT TABLESPACE "DB_BOOKS"

TEMPORARY TABLESPACE "TEMP"

ACCOUNT UNLOCK;

GRANT "READER" TO "Petrov_Read";

Commit;

Если система управления паролями доступна, то пользователи не могут изменить пароли командой ALTER USER. Пользователь должен изменять пароли, используя SQL*Plus или OCIPasswordChange, в которых принимаются в качестве параметров старый и новый пароли.

Синтаксис отмены привилегий:

REVOKE [with grant option] < привилегии >,… ON < объект >,… FROM ;

Предложение with grant option сохраняет за пользователем перечисленные привилегии, но отменяет его право передавать их кому-либо другому.

Пример:

REVOKE SELECT ON "ADMIN_BOOKS"."AUTHORS" FROM "READER";

Оператор изымания роли у пользователя:

Revoke from.

Пример:

REVOKE READER FROM "Petrov_Read";

Варианты заданий к лабораторной работе № Общие положения В утилите SQL *Plus Worksheet выполнить примеры, которые даны по ходу работы.

По индивидуальному варианту базы данных, которая выполнена в лабораторной работе №1, определить 2-3 должностных лица, которые могут работать с таблицами БД. Для каждого должностного лица определить набор привилегий, которыми он может пользоваться.

В утилите SQL *Plus Worksheet создать под каждое должностное лицо соответствующую роль, наделить эту роль определенными привилегиями.

Далее создать по одному пользователю на каждую должность и присвоить им соответствующие роли.

Сохранить последовательно операторы с указанием заданий в файле с названием ФамилияСтудента_ЛАб_3_№варианта_общее, результаты выполнения задания по варианту сохранить в файле ФамилияСтудента_ЛАб_3_результаты. Операторы создания ролей, привилегий и пользователей сохранить в файле с названием ФамилияСтудента_ЛАб_3_№варианта_роли.

ОСВОЕНИЕ ПРОГРАММИРОВАНИЯ С ПОМОЩЬЮ

ВСТРОЕННОГО ЯЗЫКА PL/SQL В ORACLE

Цель работы – знакомство с основными принципами программирования в ORACLE средствами встроенного языка PL/SQL.

Содержание работы:

1. Знакомство с правилами обозначения синтаксиса команд в справочной системе ORACLE (утилита SQL *Plus WorkSheet).

2. Изучение правил написания программ на PL/SQL.

3. Изучение правил построения идентификаторов, правил объявления переменных и их типов.

4. Изучение работы с циклами и ветвлениями.

5. Изучение работы с переменными типа Table, Record и Cursor.

6. Проработка всех примеров, анализ результатов их выполнения в утилите SQL *Plus WorkSheet.

7. Выполнение индивидуальных заданий по вариантам.

Для освоения программирования используем пример базы данных в табличном пространстве DB_Books, которая была создана в лабораторной работе №1. При выполнении примеров и заданий обращайте внимание на соответствие названий БД, таблиц и других объектов проекта.

Программа на PL/SQL состоит из трех блоков: блок описаний переменных, констант и пользовательские типы данных; исполнительный блок;

блок обработки исключительных ситуаций. Специальные знаки и простейшие операторы языка PL/SQL приведены в табл. 4.1.

SET -- настройки окружения сервера;

DECLARE --объявление переменных;

BEGIN --Операторы или NULL;

EXCEPTION

--Операторы обработки исключительных ситуаций;

END;

Специальные знаки и простейшие операторы в PL/SQL Знак сложения или конкатена- Однострочный комментарий, наREM или комментарий с текущей позиции и до конца строки Идентификаторы – это имена объектов, на которые можно ссылаться в программе, написанной на языке PL/SQL. Первый символ может состоять из букв английского алфавита, внутри может содержать цифры или “_”, “#”, «$». Имя идентификатора не должно совпадать с зарезервированным словом. Регистр для идентификаторов не важен, длина его не должна превышать 30 символов.

Примеры правильных идентификаторов:

phone# credit_limit LastName oracle$number Примеры ошибочных идентификаторов:

mine&yours debit-amount on/off user id Если необходимо в идентификаторах использовать специальные символы или пробел, то нужно заключать их в кавычки.

Примеры идентификаторов, которые пишутся в кавычках:

"X+Y" "last name" "on/off switch" "employee(s)" "*** header info ***" DECLARE – используется для определения начала блока объявления переменных, констант и пользовательских типов данных, пишется один раз для всего блока. Типы переменных представлены в табл. 4.2.

CONSTANT – переменная с заданным значением, которое нельзя изменить в теле программы.

Синтаксис для объявления константы:

имя_переменной1 CONSTANT тип_переменной:= значение_переменной1;

Синтаксис для объявления переменных:

имя_переменной1 тип_переменной; …;

имя_переменнойN тип_переменной;

Если тип переменной предполагает указание длины, то используется следующий синтаксис для объявления переменных:

имя_переменной1 тип_переменной (длина);

имя_переменнойN тип_переменной(длина);

Пример простейшей программы:

declare perem1 numeric(10,0);

perem2 varchar(15);

const1 char(10):= 'Привет!';

begin perem1:=12*2;

end;

Использование значений по умолчанию (DEFAULT) DEFAULT используется для переменных, у которых есть типичное значение. Этот оператор можно использовать для объявления параметров подпрограмм, параметров курсора, а также для значения полей пользовательского типа записи.

Пример:

SET SERVEROUTPUT ON

DECLARE

hours_worked INTEGER DEFAULT 40;

employee_count INTEGER := 0;

BEGIN DBMS_OUTPUT.PUT_LINE(hours_worked);

DBMS_OUTPUT.PUT_LINE(employee_count);

END;

Присвоение значений переменным и вывод значений на экран Присвоение значений переменным и константам осуществляется посредством знака :=.

Вывод значения на экран реализуется с помощью системной процедуры DBMS_OUTPUT.PUT_LINE(имя переменной) при включенной настройке SET SERVEROUTPUT ON.

Все системные процедуры можно увидеть в справочной системе Oracle в разделе «PL/SQL Packages and Types Reference» по адресу:

http://www.oracle.com/pls/db102/to_toc?pathname=appdev.102%2Fb 8%2Ftoc.htm&remark=portal+%28Books% или в формате PDF:

http://www.oracle.com/pls/db102/to_pdf?pathname=appdev.102%2Fb 8.pdf&remark=portal+%28Books% Пример:

SET SERVEROUTPUT ON

declare perem1 numeric(10,0);

perem2 varchar(15);

const1 char(10):= 'Привет!';

begin perem1:=12**2;

DBMS_OUTPUT.PUT_LINE(perem1);

end;

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

Пример:

SET SERVEROUTPUT ON

BEGIN DBMS_OUTPUT.PUT_LINE('DELETE FROM Authors ' || ' WHERE Name_author = ''' || 'Пушкин А.С.' || ''' ');

END;

BINARY_INT - EGER, PLS_INTEGE EGER:

NATURALN

POSITIVE

POSITIVEN

SIGNTYPE

BOOLEAN

VARCHAR2 Абсолютное Число, поддерживающее режимы фиксированной

NUMBER

значение в точки и плавающей точки. Масштаб, указанный с диапазоне минусом, указывает на округление целой части.

Подтипы DEC, DECIMAL и NUMERIC – типы чисел с фиксированной точкой при максимальной точности до NUMBER:

DEC

DOUBLE

PRECISION

FLOAT

NUMERIC

REAL

SMALLINT

CHAR

CHARACTER

32760 байт LONG может хранить текст, массивы символов или LONG вызываемых параметров функций SQL, а также в таких конструктах SQL, как WHERE, GROUP BY,

LONG RAW

32767 байта Статичная символьная строка в формате Unicode, NCHAR NVARCHAR2 32767 байта Тип NVARCHAR2 может быть использован для хранения символьных строк переменной длины в формате Unicode. Максимальные размеры строки данного BFILE байт CLOB и NCLOB позволяет хранить блоки неструктурированных данных, таких как текст, изображения, BLOB CLOB NCLOB от 01.01.4712 Дата и время. Для присвоения даты используется Date до н.э. функция TO_DATE(‘символьная дата’, ‘формат дадо 01.01.4712 ты’) Пример использования подтипов длинного целого:

SET SERVEROUTPUT ON

declare i NATURAL; q NATURALN:=9; w POSITIVE:=2;

e POSITIVEN:=8; r SIGNTYPE:=0;

begin DBMS_OUTPUT.PUT_LINE('i='||i||',q=' || q || ',w=' || w || ',e=' || e || ',r=' || r);

end;

Пример присвоения значений переменной с типом NUMBER:

DECLARE

n NUMBER;

BEGIN n := -9.999999E-130; -- правильно n := 9.999E125;

-- n := 10.0E125;

END;

В табл. 4.3 представлены основные функции по преобразованию типов переменных, кроме описанных существуют еще такие функции, как TO_SINGLE_BYTE, TO_MULTI_BYTE, TO_BLOB, TO_CLOB, TO_NCLOB, с которыми можно ознакомиться дополнительно.

Краткий обзор функций преобразования типов переменных Функция конвертирует символьную строку с данными, соотCHARTOROWID

HEXTORAW SET SERVEROUTPUT ON

RAWTOHEX DECLARE

RAWTONHEX n raw(10):='123da12';

ROWIDTOCHAR SET SERVEROUTPUT ON

DECLARE

TO_CHAR SET SERVEROUTPUT ON

TO_NCHAR DECLARE

DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'SSSSS'));

TO_DATE SET SERVEROUTPUT ON

DECLARE

DBMS_OUTPUT.PUT_LINE(TO_DATE(ch, 'dd-mon-yyyy'));

DBMS_OUTPUT.PUT_LINE(TO_DATE(ch1, 'mm-ddyyyy'));

TO_NUMBER SET SERVEROUTPUT ON

DECLARE

n int:=1029; num number(4,0); num1 number(4,-1);

num:=TO_NUMBER(n); num1:=TO_NUMBER(n);

BEGIN /* Операторские скобки должны содержать хотя бы один оператор.

Требуются для конструкций поливариантных ветвлений, условных и циклических конструкций.

END;

Синтаксис:

IF условие_1 THEN Операторы;

ELSIF условие_2 THEN Операторы;

ELSIF условие_3 THEN ELSE Операторы;

END IF Пример ветвления:

SET SERVEROUTPUT ON

DECLARE

n NUMBER;

BEGIN n := DBMS_RANDOM.RANDOM;

IF n40) AND (a 20;

Пример объявления курсора с выбором автора и одновременным помещением результата в переменную:

DECLARE

my_authors ADMIN_BOOKS.Authors%ROWTYPE;

CURSOR c2 RETURN ADMIN_BOOKS.Authors%ROWTYPE IS SELECT * FROM ADMIN_BOOKS.Authors WHERE Code_author = 20;

Пример объявления курсора с использованием полей со значениями по умолчанию:

DECLARE

CURSOR c3 (low NUMBER(3) DEFAULT 0, high NUMBER(3) DEFAULT 105) IS SELECT * FROM ADMIN_BOOKS.Authors WHERE Code_author> low AND Code_author< high;

Пример работы курсора с циклом LOOP:

SET SERVEROUTPUT ON

DECLARE

CURSOR c1 IS SELECT Publish, Code_publish FROM ADMIN_BOOKS.Publishing_house WHERE Code_publish=Count_pages;

end Count_Books_Pages;

Задание 2. Создайте данную процедуру в разделе Schema/ Procedure в схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. Запустите ее в утилите SQL *Plus Worksheet с помощью программы:

begin ADMIN_BOOKS.Count_Books_Pages(12);

end;

Проверьте результат.

Пример создания процедуры c входными параметрами:

CREATE OR REPLACE PROCEDURE ADMIN_BOOKS.Count_Books_Title (Count_pages IN Int, Title IN Char) perem3 integer;

begin Select count(Code_book) INTO perem3 from Books WHERE Pages>=Count_pages AND Title_book LIKE trim(Title)||'%';

end Count_Books_Title;

Задание 3. Создайте данную процедуру в разделе Schema/ Procedure в схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. Запустите ее в утилите SQL *Plus Worksheet с помощью программы:

begin ADMIN_BOOKS.Count_Books_Title (100, 'П');

end;

Проверьте результат.

Пример создания процедуры c входными параметрами и выходным параметром:

CREATE OR REPLACE PROCEDURE ADMIN_BOOKS.Count_Books_Itogo (Count_pages IN Int, Title IN Char, Itogo OUT Int) begin Select count(Code_book) INTO Itogo from Books WHERE Pages>=Count_pages AND Title_book LIKE '%'||trim(Title)||'%';

end Count_Books_Itogo;

Задание 4. Создайте данную процедуру в разделе Schema/ Procedure в схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. Запустите ее в утилите SQL *Plus Worksheet с помощью программы:

SET SERVEROUTPUT ON

DECLARE

I INTEGER;

begin ADMIN_BOOKS.Count_Books_Itogo (56, 'Руслан и Людмила', I);

DBMS_OUTPUT.PUT_LINE(I);

end;

Проверьте результат.

Пример создания процедуры без параметров для увеличения значения ключевого поля в таблице Purchases в 2 раза:

CREATE OR REPLACE PROCEDURE ADMIN_BOOKS.update_proc BEGIN UPDATE Purchases SET Code_purchase = Code_purchase*2;

END update_proc;

Задание 5. Создайте данную процедуру в разделе Schema/ Procedure в схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. Запустите ее в утилите SQL *Plus Worksheet с помощью программы:

begin ADMIN_BOOKS.update_proc;

end;

Процедура не возвращает никаких данных.

Пример создания процедуры с входным параметром и значением по умолчанию для увеличения значения ключевого поля в таблице Purchases в заданное количество раз (по умолчанию в 2 раза):

CREATE OR REPLACE PROCEDURE ADMIN_BOOKS.update_proc (p IN INT:= 2) BEGIN UPDATE Purchases SET Code_purchase = Code_purchase*p;

END update_proc2;

Задание 6. Создайте данную процедуру в разделе Schema/ Procedure в схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. Запустите ее в утилите SQL *Plus Worksheet с помощью программы:

begin ADMIN_BOOKS.update_proc2;

end;

или begin ADMIN_BOOKS.update_proc2(4);

end;

Процедура не возвращает никаких данных.

Пример создания функции c входными параметрами и RETURN:

CREATE OR REPLACE function ADMIN_BOOKS.checkname (param IN int, ch IN char) RETURN integer type_end integer;

begin SELECT count(Name_author) INTO type_end FROM authors WHERE Code_author = param AND Name_author=ch;

RETURN type_end;

end checkname;

Задание 7. Создайте данную функцию в разделе Schema/ Function в схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. Запустите ее в утилите SQL *Plus Worksheet с помощью программы:

SET SERVEROUTPUT ON

DECLARE

I INTEGER;

begin I:=ADMIN_BOOKS.checkname(12,'Пушкин А.С.');

DBMS_OUTPUT.PUT_LINE(I);

end;

Пример создания функции с входными и выходным параметрами.

Создать функцию для определения количества заказов, совершенных за указанный период:

CREATE OR REPLACE function ADMIN_BOOKS.count_purchases (d1 DATE, d2 DATE) RETURN integer count_ integer;

begin SELECT count(Code_purchase) INTO count_ from Purchases WHERE Date_order BETWEEN d1 AND d2;

RETURN count_;

end count_purchases;

Задание 8. Создайте данную функцию в разделе Schema/ Function в схеме ADMIN_BOOKS через утилиту Enterprise MANAGER Console. Запустите ее в утилите SQL *Plus Worksheet с помощью программы:

SET SERVEROUTPUT ON

DECLARE

I INTEGER;

begin I:=ADMIN_BOOKS.count_purchases(TO_DATE('12.06.2007', 'dd-mmyyyy'),TO_DATE('30.06.2007', 'dd-mm-yyyy') );

DBMS_OUTPUT.PUT_LINE(I);

end;

Проверьте результат.

Создание хранимых процедур с выходным параметром – набором Для вывода в качестве результата выполнения процедуры набора строк используют курсор типа SYS_RefCursor.

Например:

create or replace procedure ADMIN_BOOKS.List_Authors (cursor1 out SYS_REFCURSOR) is begin open cursor1 for select Name_Author from ADMIN_BOOKS.Authors;

end List_Authors;

Пример запуска процедуры и просмотра набора строк, полученных из нее:

SET SERVEROUTPUT ON

Declare type cur_select1 is ref cursor;

cur_select2 cur_select1;

name1 ADMIN_BOOKS.Authors.Name_Author %type;

begin ADMIN_BOOKS.List_Authors(cur_select2);

loop FETCH cur_select2 INTO name1;

exit when cur_select2%NOTFOUND OR cur_select2%NOTFOUND IS NULL;

DBMS_OUTPUT.PUT_LINE(cur_select2%ROWCOUNT || '. ' || name1);

end loop;

close cur_select2;

end;

Варианты заданий к лабораторной работе № Общие положения В лабораторной работе в примерах тела процедур и функций написаны так, что их можно полностью копировать, вставлять в утилиту SQL *Plus Worksheet и запускать (F5) для их создания на сервере. Если создавать процедуру или функцию в Enterprise MANAGER Console через мастер создания, то первая строка с конструкцией CREATE OR REPLACE PROCEDURE имя_процедуры или CREATE OR REPLACE FUNCTION имя_функции не копируется, а само имя_процедуры или имя_функции заносится в поле Name.

Создать примеры хранимых процедур и функций в вашем табличном пространстве. Проверьте их работу.

По вариантам на базе таблиц, созданных в лабораторной работе №1, в утилите SQL *Plus Worksheet создайте процедуры и функции, причем каждую программу сохранять на диске в отдельном файле с названием ФамилияСтудента_ЛАб_5_№_варианта_№_задания.

Вариант 1. Вывести список сотрудников, у которых есть хотя бы один ребенок.

2. Вывести список детей, которым выдали подарки в указанный период.

3. Вывести список родителей, у которых есть несовершеннолетние дети.

4. Вывести информацию о подарках со стоимостью больше указанного числа, отсортированных по дате.

Вариант 1. Вывести список приборов с указанным типом.

2. Вывести количество отремонтированных приборов и общую стоимость ремонтов у указанного мастера.

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

4. Вывести информацию о мастерах с разрядом больше указанного числа или с датой приема на работу меньше указанной даты.

Вариант 1. Вывести список цветков с указанным типом листа.

2. Вывести список кодов продаж, по которым продано цветов на сумму больше указанного числа.

3. Вывести дату продажи, сумму, продавца и цветок по указанному коду продажи.

4. Вывести список цветов и сорт для цветов с высотой больше указанного числа или цветущий.

Вариант 1. Вывести список лекарств с указанным показанием к применению.

2. Вывести список дат поставок, по которым продано больше указанного числа одноименного лекарства.

3. Вывести дату поставки, сумму, ФИО руководителя от поставщика и название лекарства по коду поступления больше указанного числа.

4. Вывести список лекарств и единицы измерения для лекарств с количеством в упаковке больше указанного числа или кодом лекарства меньше определенного значения.

Вариант 1. Вывести список сотрудников с указанной должностью.

2. Вывести список списанного оборудования по указанной причине.

3. Вывести дату поступления, название оборудования, ФИО ответственного и дату списания для оборудования, списанного в указанный 4. Вывести список оборудования с указанным типом или с датой поступления больше определенного значения.

Вариант 1. Вывести список блюд с весом больше указанного числа.

2. Вывести список продуктов, в названии которых встречается указанный фрагмент слова.

3. Вывести объем продукта, название блюда, название продукта с кодом блюда от указанного начального значения по определенному конечному значению.

4. Вывести порядок приготовления блюда и название блюда с количеством углеводов больше определенного значения или количеством калорий больше указанного значения.

Вариант 1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

3. Вывести дату регистрации, тип документа, ФИО регистратора и название организации для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с определенным типом документа или с датой регистрации больше указанного значения.

Вариант 1. Вывести список сотрудников с указанной причиной увольнения.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, причину увольнения, ФИО сотрудника для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 1. Вывести список сотрудников, бравших отпуск указанного типа.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, тип отпуска, ФИО сотрудника для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

3. Вывести дату регистрации, тип документа, ФИО отправителя и название организации для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа меньше определенного значения.

Вариант 1. Вывести список сотрудников, назначенных на указанную должность.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, должность, ФИО сотрудника для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое брал в прокат определенный 3. Вывести список лиц, бравших оборудование в прокат, отсортированный по количеству их обращений, а также количество этих обращений.

4. Вывести информацию о клиентах, отсортированных по адресам.

Вариант 1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое списал определенный сотрудник.

3. Вывести количество списанного оборудования, сгруппированного по типам оборудования.

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

Вариант 1. Вывести список цветков с указанным типом листа.

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

3. Вывести дату поступления, сумму, названия поставщика и цветов по определенному коду поставщика.

4. Вывести список цветов и сорт для цветов с высотой больше определенного числа или цветущий.

Вариант 1. Вывести список клиентов, заехавших в номера в указанный период.

2. Вывести общую сумму оплат за номера для каждого клиента.

3. Вывести дату заезда, тип номера, ФИО клиентов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных клиентов в номерах определенного типа.

Вариант 1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое брал в прокат определенный 3. Вывести список лиц, бравших оборудование в прокат, и количество их обращений, отсортированных по количеству обращений по убыванию.

4. Вывести информацию о клиентах, отсортированных по адресам.

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

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

3. Вывести сумму стоимости ценностей с кодом в указанном диапазоне.

4. Вывести список материально-ответственных лиц с датой приема на работу в указанном диапазоне.

Вариант 1. Вывести список ремонтных работ, выполненных определенным мастером.

2. Вывести список этапов работ, входящих в работы, в названии которых встречается указанное слово.

3. Вывести сумму стоимости этапов ремонтных работ для работ с кодом в указанном диапазоне.

4. Вывести список мастеров с датой приема на работу в указанном диапазоне.

Вариант 1. Вывести список лекарств с определенным показанием.

2. Вывести список номеров чеков, по которым продано больше определенного числа лекарств.

3. Вывести дату продажи, сумму, ФИО кассира и лекарство по чеку с указанным номером.

Вариант 1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

3. Вывести дату регистрации, тип документа, ФИО исполнителя и факт исполнения для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа в определенном диапазоне.

СОЗДАНИЕ ТРИГГЕРОВ В ORACLE

Цель работы – научиться создавать и использовать триггеры в Oracle.

Содержание работы:

1. Проработка всех примеров, анализ результатов их выполнения в утилите SQL *Plus Worksheet. Проверка наличия созданных триггеров в текущей схеме.

2. Проверка всех выполненных примеров в утилите Enterprise MANAGER Console.

3. Выполнение всех примеров и заданий по ходу лабораторной работы.

4. Выполнение индивидуальных заданий по вариантам.

Для освоения программирования триггеров используем пример базы данных в табличном пространстве DB_Books, которая была создана в лабораторной работе №1. При выполнении примеров и заданий обращайте внимание на соответствие названий таблиц и других объектов проекта.

Триггер базы данных — это процедура PL/SQL, которая автоматически запускается при возникновении определенных событий, связанных с выполнением операций вставки, удаления или модификации данных таблицы. Событие, управляющее запуском триггера, описывается в виде логических условий. Когда возникает событие, соответствующее условиям триггера, сервер Oracle автоматически запускает триггер, то есть интерпретирует код программы триггера, записанный на языке PL/SQL.

В основном триггеры используются для реализации сложных ограничений целостности данных, которые невозможно осуществить через описательные ограничения, устанавливаемые при создании таблиц, организации всевозможных видов аудита, автоматического оповещения других модулей о том, что делать в случае изменения информации, содержащейся в таблице БД, для реализации "бизнес-правил", для организации каскадных воздействий на таблицы БД.

Синтаксис:

CREATE [OR REPLACE] TRIGGER имя_триггера BEFORE | AFTER активизирующее_событие ON ссылка_на_таблицу FOR EACH ROW [WHEN условие_срабатывания] тело_триггера где активизирующее_событие указывает момент активации триггера BEFORE до срабатывания оператора DML, а AFTER – после срабатывания оператора DML.

FOR EACH ROW указывает на активацию триггера от воздействия на каждую строку в наборе строк, без этого указателя триггер запускается после или до оператора DML в целом.

Порядок активации триггеров в большинстве случаев таков:

1) Выполняется операторный триггер BEFORE (при его наличии).

2) Для каждой строки, на которую воздействует оператор:

- выполняется строковый триггер BEFORE (при его наличии);

- выполняется собственно оператор;

- выполняется строковый триггер AFTER (при его наличии).

3) Выполняется операторный триггер AFTER (при его наличии).

При создании строковых триггеров можно использовать псевдозаписи :old и :new. Строковый триггер срабатывает один раз для каждой строки.

При этом внутри триггера можно обращаться к строке, обрабатываемой в данный момент времени, применяя псевдозаписи.

Например:

CREATE OR REPLACE TRIGGER ADMIN_BOOKS.Trigger BEFORE DELETE ON ADMIN_BOOKS.Authors FOR EACH ROW

DECLARE

a ADMIN_BOOKS.Authors.Code_author%TYPE;

b ADMIN_BOOKS.Authors.Name_Author%TYPE;

c ADMIN_BOOKS.Authors.Birthday%TYPE;

BEGIN a:= :old.Code_author;

b:= :new.Name_Author;

c:= :old.Birthday;

End;

Обращение к псевдозаписям :old и :new должно производиться через имена полей и применимо только к строковым триггерам.

С его помощью можно заставить триггер работать по условию! Само условие WHERE в триггере применимо к типу строчных триггеров.

Пример:

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

CREATE TABLE ADMIN_BOOKS.Audit_Trigger2(date_oper date, user_name char(20), code_book number(5));

Commit;

Теперь создадим триггер:

CREATE OR REPLACE TRIGGER ADMIN_BOOKS.Trigger BEFORE INSERT OR UPDATE OF Title_Book ON ADMIN_BOOKS.Books FOR EACH ROW WHEN (TRIM(new.Title_Book) = '')

DECLARE

BEGIN INSERT INTO ADMIN_BOOKS.Audit_Trigger (date_oper,user_name,code_book) VALUES(sysdate, user, :new.code_book);

END Trigger2;

Обратите внимание на наличие строки OF Title_Book ON ADMIN_BOOKS.Books – это определяется поле, на которое устанавливаем условие триггера.

В триггерах БД Oracle возможно применение логических операторов предикатов. Они имеют следующие определения: INSERTING, UPDATING, DELETING. Это внутренние переменные среды Oracle, которые в зависимости от воздействующего на таблицу оператора DML принимают одно из значений: TRUE или FALSE.

Пример:

Предварительно создадим таблицу, которая будет фиксировать виды выполненных операций.

CREATE TABLE ADMIN_BOOKS.Audit_Trigger3(date_oper date, user_name char(20), oper varchar2(10));

Теперь создадим триггер:

CREATE OR REPLACE TRIGGER ADMIN_BOOKS.Trigger3 AFTER INSERT OR UPDATE OR DELETE ON ADMIN_BOOKS.Publishing_house

FOR EACH ROW

DECLARE

TIP VARCHAR2(10);

BEGIN

IF INSERTING THEN

ELSIF UPDATING THEN

ELSIF DELETING THEN

INSERT INTO ADMIN_BOOKS.Audit_Trigger3(date_oper, user_name, oper) VALUES (SYSDATE, USER, TIP);

END Trigger3;

В БД существует два основных вида событий, на которые активизируются системные триггеры, а именно на события DDL или самой БД. К событиям DDL относятся операторы CREATE, DROP, ALTER, к событиям базы данных – запуск останов сервера, регистрация отключения пользователя БД, ошибка сервера. Основные примеры событий и их обработки даны в табл. 6.1. Описание атрибутивных функций, которые могут быть использованы в триггерах дано в табл. 6.2.

Синтаксис создания системного триггера:

CREATE OR REPLACE TRIGGER [схема.]имя_триггера {BEFORE | AFTER} {список_событий_DDL | список_событий_базы_данных} ON {DATABASE | [схема.]SCHEMA} конструкция_REFERENCING [условие_WHEN] тело триггера;

Системные триггеры могут создаваться на уровне схемы (SCHEMA) или уровне БД (DATABASE).

CREATE OR REPLACE TRIGGER Trigger4 AFTER LOGON ON

DATABASE

BEGIN

INSERT INTO SYSTEM.AUDTBASE(NZAP, POLZ, TMIN, OPER)

VALUES(1, USER, SYSDATE, 'UserIsLog(off)');

END Trigger4;

CREATE OR REPLACE TRIGGER Trigger5 AFTER LOGON ON SCHEMA

BEGIN

INSERT INTO SYSTEM.AUDTBASE(NZAP, POLZ, TMIN, OPER)

VALUES(1, USER, SYSDATE, 'UserIsLog(off)');

END Trigger5;

STARTUP AFTER

SHUTDOWN BEFORE

SERVERERROR AFTER

LOGON AFTER

CREATE BEFORE, AFTER

DROP BEFORE, AFTER

ALTER BEFORE, AFTER

Триггеры STARTUP и SHUTDOWN имеют смысл только на уровне БД, хотя их можно создать и в конкретной схеме, но активизироваться они не будут. Кроме того, для системных триггеров существует ряд атрибутных функций.

INSTANCE_NUM NUMBER

DATABASE_NAME VARCHAR

БАЗЫ ДАННЫХ

SERVER_ERROR NUMBER

IS_SERVERERROR BOOLEAN

LOGIN_USER VARCHAR

DICTIONARY_OBJ_ VARCHAR DICTIONARY_OBJ_ VARCHAR DICTIONARY_OBJ_ VARCHAR DES_ENCRYPTED_P VARCHAR Варианты заданий к лабораторной работе № Общие положения В лабораторной работе в примерах тела триггеров написаны так, что их можно полностью копировать, вставлять в утилиту SQL *Plus Worksheet и запускать (F5) для их создания на сервере. Если создавать триггер в Enterprise MANAGER Console через мастер создания, то первая строка с конструкцией CREATE OR REPLACE TRIGGER имя_триггера не копируется, а само имя_триггера заносится в поле Name.

Создайте примеры триггеров в вашем табличном пространстве. Проверьте их работу.

По вариантам в утилите SQL *Plus Worksheet создайте триггеры, причем каждую программу сохранять на диске в отдельном файле с названием ФамилияСтудента_ЛАб_6_№_варианта_№_задания.

Вариант 1. Создать строковый триггер, который будет фиксировать ФИО и код каждого сотрудника в отдельно организованной таблице, у которого изменяли данные его детей.

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

3. Создать операторный триггер, который будет фиксировать операции добавления в любую из трех таблиц с указанием даты и времени операции.

2. Создать строковый триггер, который будет фиксировать количество отремонтированных приборов и общую стоимость ремонтов с указанием имени мастера в отдельно организованной таблице у мастера, информация о котором удаляется.

3. Создать операторный триггер, который будет фиксировать операции добавления владельца прибора в таблицу владельцев и наименование прибора, с которым он пришел в отдельно организованной таблице, а также с указанием имени пользователя и даты операции.

4. Создать системный триггер на уровне схемы, который будет фиксировать в отдельно организованной таблице информацию о пользователях и объектах, структуру которых изменяют.

Вариант Создать строковый триггер, который будет фиксировать старое и новое наименование цветка с типом листа в отдельно организованной таблице, если изменяется наименование цветка в таблице или удаляется цветок из таблицы.

Создать строковый триггер, который будет фиксировать коды продаж и даты продаж в отдельно организованной таблице при добавлении записи в таблицу продаж.

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

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

Вариант Создать строковый триггер, который будет фиксировать в отдельно организованной таблице наименование лекарства с указанным показанием к применению в отдельно организованной таблице в случае его удаления.

Создать строковый триггер, который будет фиксировать в отдельно организованной таблице дату поставки и наименование лекарства, которого продано больше указанного числа.

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

Создать системный триггер на уровне схемы, который будет фиксировать в отдельно организованной таблице информацию о пользователях и объектах, структуру которых создают.

Вариант 1. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице сотрудника с должностью при добавлении нового сотрудника в таблицу сотрудников.

2. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице наименование списанного оборудования, тип операции, на которую запущен триггер, и дату списания по указанной причине при добавлении или изменении записи в таблице списания.

3. Создать операторный триггер, который будет фиксировать в отдельно организованной таблице информацию о дате поступления, названии оборудования, ФИО ответственного для оборудования, списанного указанным пользователем.

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

Вариант Создать строковый триггер, который будет фиксировать в отдельно организованной таблице наименование блюда с его весом при изменении данных в таблице блюд.

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

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

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

Вариант Создать строковый триггер, который будет фиксировать в отдельно организованной таблице сотрудника с должностью при добавлении нового сотрудника в таблицу сотрудников.

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

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

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

Вариант Создать строковый триггер, который будет фиксировать в отдельно организованной таблице данные сотрудника, на которого формируют документ об увольнении.

Создать строковый триггер, который будет фиксировать в отдельно организованной таблице информацию о документах, которые регистрируют датой, меньшей текущей системной даты.

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

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

Вариант 1. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице данные сотрудника, которого оформляют в отпуск указанного типа.

2. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице информацию о документах, которые удаляют при сроке давности документов больше года.

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

4. Создать системный триггер на уровне схемы, который будет фиксировать в отдельно организованной таблице информацию о пользователях и объектах, структуру которых создают, причем таким объектом является таблица или представление.

Вариант 1. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице ФИО отправителя, которого удалили из таблицы отправителей.

2. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице дату операции и информацию о документах, которые изменяются.

3. Создать операторный триггер, который будет фиксировать в отдельно организованной таблице тип и дату операции при изменении, удалении или удалении данных из справочника организацийполучателей.

4. Создать системный триггер на уровне схемы, который будет фиксировать в отдельно организованной таблице информацию о пользователях и объектах, структуру которых меняют, причем такими объектами являются процедоры или пакеты.

Вариант Создать строковый триггер, который будет фиксировать в отдельно организованной таблице льготы по должности, которую добавили в справочник должностей.

Создать строковый триггер, который будет фиксировать в отдельно организованной таблице код документа, пользователя, если документ регистрируется датой, меньшей системной.

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

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

Вариант 1. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице старое и новое название оборудования, которое изменяют.

2. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице информацию о клиенте, которого удалили.

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

4. Создать системный триггер на уровне схемы, который будет фиксировать в отдельно организованной таблице информацию о пользователях и объектах, структуру которых создают, причем такими объектами являются функции.

Вариант 1. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице дату операции, пользователя и информацию об оборудовании, которое пытаются повторно списать.

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

4. Создать системный триггер на уровне схемы, который будет фиксировать в отдельно организованной таблице информацию о пользователях и объектах, структуру которых создают.

Вариант 1. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице старое и новое названия цветка при изменении информации о цветке при условии, что новое значение средней высоты больше 15.

2. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице коды поступлений и наименования цветов при добавлении записи в таблицу поступлений.

4. Создать системный триггер на уровне БД, который будет фиксировать в отдельно организованной таблице подключения пользователей Вариант 1. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице информацию о клиенте, которого добавили в справочник клиентов при условии, что фамилия начинается на 2. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице старое и новое значения перечня удобств при изменении данных в справочнике номеров.

Вариант 1. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице информацию о добавляемом оборудовании при типе оборудования ‘комбайн’.

2. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице старое и новое имена клиента и дату операции при изменении фамилии клиента.

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

Вариант 1. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице информацию об удаляемой ценности с закупочной стоимостью больше 15000.

2. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице старое и новое названия подразделения при изменении данных в справочнике материально-ответственных.

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

Вариант 1. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице данные о лекарстве, которые удаляются.

2. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице номера чеков и наименования лекарства при добавлении записи в таблицу СОСТАВ ЧЕКА с условием, что количество проданного лекарства больше 10.

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

Вариант 1. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице информацию об исполнителе, при изменении даты приема на работу.

2. Создать строковый триггер, который будет фиксировать в отдельно организованной таблице старое и новое содержания документа, если в таблице регистрации документов удаляется или изменяется запись.

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

СОЗДАНИЕ КЛИЕНТСКОЙ ЧАСТИ ПРИЛОЖЕНИЯ

ДЛЯ ПРОСМОТРА, РЕДАКТИРОВАНИЯ ДАННЫХ БД.

ВЫЗОВ ХРАНИМЫХ ПРОЦЕДУР ИЗ КЛИЕНТСКОЙ ЧАСТИ

Цель работы – научиться создавать клиентское приложение для работы с базой данных с применением встроенных инструментов в среде Delphi 7.

Содержание работы:

1. Выполнение всех заданий по ходу лабораторной работы.

2. Выполнение индивидуальных заданий.

Для создания клиентского приложения в Delphi 7 используем пример базы данных в табличном пространстве DB_Books, которая была создана в лабораторной работе №1.

При выполнении примеров и заданий обращайте внимание на соответствие названий таблиц и других объектов проекта.

В данной работе при создании клиентского приложения будем использовать встроенные инструменты для работы с данными (в Delphi 7 – вкладка Data Controls).

Если данный проект был выполнен в лабораторной работе с СУБД MS SQL Server, то в нем необходимо лишь перенастроить свойство Connection String на соединение с Oracle согласно пункту 4 и перекомпилировать проект.

В Delphi 7:

1. В новом проекте создадим модуль данных (меню File – New – DataModule). Переименуем форму в DM (свойство Name – DM).

2. В основной форме (например, Form1, переименовать ее в MainForm) в коде добавить описание модуля DM, для этого после ключевого слова USES в конце списка поставить запятую и дописать название программного модуля, например UNIT2, который соответствует созданной форме DM.

3. На форму DM с вкладки ADO добавить компонент ADOConnection (название, например ADOConnection1), 5 компонентов типа ADOTable (переименовать компоненты в ADOPurchases, ADOBooks, ADOAuthors, ADODeliveries, ADOPublish), с вкладки Data Access 5 компонентов типа DataSource (переименовать компоненты в DataPurchases, DataBooks, DataAuthors, DataDeliveries, DataPublish).

4. У компонента ADOConnection1 настроить свойства:

Connected String = нажать кнопку Build\ выбрать Поставщик данных – Microsoft OLE DB Provider for Oracle Получится в результате – Provider=MSDAORA.1;User ID=ADMIN_BOOKS;Data Source=Orcl;Persist Security 5. У ADOAuthors изменить следующие свойства:

6. У DataAuthors изменить следующие свойства (это будет ссылка на таблицу):

7. У ADOPurchases изменить следующие свойства:

8. У DataPurchases изменить следующие свойства (это будет ссылка на таблицу):

9. У ADOBooks изменить следующие свойства:

10. У DataBooks изменить следующие свойства (это будет ссылка на таблицу):

11. У ADODeliveries изменить следующие свойства:

12. У DataDeliveries изменить следующие свойства (это будет ссылка на таблицу):

13. У ADOPublish изменить следующие свойства:

14. У DataPublish изменить следующие свойства (это будет ссылка на таблицу):

15. На основной форме (MainForm) добавить компонент Меню с вкладки Standart. В редакторе меню сделать первый пункт «Работа с таблицами» и в подменю пункты «Авторы», «Книги», «Издательства», «Поставщики», «Поставки».

16. Создать пять форм, каждую из которых назвать (изменить свойство Name): FormAuthors, FormPurchases, FormBooks, FormDeliveries, FormPublish. В главной форме, в коде добавить описание этих форм, для этого после ключевого слова USES в конце списка через запятую дописать названия программных модулей, которые соответствуют описанным формам. В каждой из созданных форм в коде добавить описание модуля DM, для этого после ключевого слова USES в конце списка поставить запятую и дописать название программного модуля, например UNIT2, который соответствует созданной форме DM.

17. На основной форме в подпунктах меню в соответствующих методах Click вызвать соответствующие формы с помощью кода:

для FormAuthors:

FormAuthors:=TFormAuthors.Create(Application);

для FormPurchases:

FormPurchases:=TFormPurchases.Create(Application);

для FormBooks:

FormBooks:=TFormBooks.Create(Application);

для FormDeliveries:

FormDeliveries:=TFormDeliveries.Create(Application);

для FormPublish:

FormPublish:=TFormPublish.Create(Application);

18. На формы FormAuthors, FormPurchases, FormBooks, FormDeliveries, FormPublish добавить с вкладки Data Controls по паре компонент типа DBGrid и DBNavigator. Настроить у DBGrid и DBNavigator свойство DataSource для связи с соответствующим источником данных.

19. Проверить работу приложения.

20. На форму FormBooks с вкладки Data Controls добавить 3 компонента типа DBEdit, 2 компонента типа DBLookupComboBox.

У 1-го компонента DBEdit изменить свойства:

У 2-го компонента DBEdit изменить свойства:

У 3-го компонента DBEdit изменить свойства:

У 1-го компонента DBLookupComboBox изменить свойства:

У 2-го компонента DBLookupComboBox изменить свойства:

Рядом с каждым настроенным компонентом поставить Label (надпись), у которой в свойстве Caption написать, например, "Введите название книги" и т.д.

У компонента DBGrid настроить свойство ReadOnly в режим True.

Так как на форме будут использоваться компоненты DBLookupComboBox, которые ссылаются на таблицы Authors и Publishing_house, нужно на основной форме в подпункте меню "Книги" в методе Click добавить в начало две команды:

В противном случае при открытии формы может произойти ошибка, если по какой-то причине источники данных для компонент DBLookupComboBox будут неактивны.

21. Аналогично для остальных форм добавить элементы типа DBEdit (для полей данные которых будут просто набираться или редактироваться пользователем) и DBLookupComboBox (для полей связи, к которым подходит связь типа «много», они позволят выбирать данные из соответствующего справочника и пользователю не придется помнить значения кодов).

22. Проверить работу приложения.

23. Теперь необходимо создать фильтры по текущим значениям полей таблицы. Значения для фильтра будут браться из колонок DBGrid. Например, строка formbooks.DBGrid1.Columns.Items[0].Field.Text; будет брать текущее значение из первой колонки DBGrid. Поэтому при выполнении этого задания обращайте внимание на соответствие индексов колонки порядку полей и исправьте при необходимости индексы в соответствии с вашим порядком полей в таблице.

На форму FormBooks с вкладки Standart добавить 5 компонентов типа Button.

У 1-го компонента Button изменить свойства и метод:

Caption на Фильтр по текущему издательству;