Размещено на http://www.Allbest.ru/

Федеральное агентство связи

Государственное образовательное учреждение высшего профессионального образования

Сибирский государственный университет телекоммуникаций и информатики

Кафедра ПМиК

Курсовая работа

По дисциплине

Сетевые базы данных

Создание хранимых процедур и представлений. Триггеры и привилегии

Выполнил: Семенов В.П.

Студент группы П-93

Проверил: Грязнов Н.Г.

Новосибирск, 2013

Содержание

• Задание
• 1. Структура таблиц
• 2. SQL-сценарии
• Начальное заполнение
• Начальные данные
• Триггер
• Представление
• Второй пакет
• 3. Привилегии

Выводы

Литература

Задание

Создать две таблицы, каждая из которых должна иметь первичный ключ и, по крайней мере, один столбец с ограничением NOT NULL. Таблицы должны быть связаны внешним ключом; тип связи - "один-ко-многим". Создать пакет, содержащий процедуру начального заполнения таблиц данными (по 7-10 записей в таблице) и процедуру очистки таблиц (удаления записей).

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

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

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

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

Варианты заданий находятся в файлах varNN.txt, где NN - номер группы.

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

1. Структура таблиц

Авиарейсы: race

Название поля	Описание поля	Тип
race_id	Первичный ключ	number(8), not null
name	Название рейса	varchar(128)

Сервисные категории: categories

Название поля	Описание поля	Тип
id	Первичный ключ	number(8), not null
name	Название категории	varchar(128)
race	Внешний ключ - идентификатор рейса	number(8), not null

2. SQL-сценарии

Начальное заполнение

drop table categories;

drop sequence seq_id_categories;

commit;

drop table race;

drop sequence seq_id_race;

commit;

/* рейсы */

create sequence seq_id_race;

create table race(

race_id number(8) not null,

name varchar(128)

);

alter table race add (constraint race_pk primary key (race_id));

commit;

/* сервисные категории */

create sequence seq_id_categories;

create table categories(

categories_id number(4) not null,

name varchar(128),

race number(8) not null

);

alter table categories add (constraint categories_pk primary key

(categories_id));

alter table categories add (constraint categories_fk foreign key (race)

references race(race_id));

commit;

create or replace package fill_everything is

procedure fill_races;

procedure clear_races;

procedure clear_categories;

procedure fill_categories;

end fill_everything;

create or replace package body fill_everything is

procedure clear_races is

begin

delete from race;

if sql % notfound then

raise NO_DATA_FOUND;

end if;

exception

when NO_DATA_FOUND then

dbms_output.put_line('No races found!');

end clear_races;

procedure fill_races is

begin

insert into race values (seq_id_race.nextval, 'ТИ 916');

insert into race values (seq_id_race.nextval, 'ЛА 161');

insert into race values (seq_id_race.nextval, 'ПЛ 541');

insert into race values (seq_id_race.nextval, 'С7 173');

insert into race values (seq_id_race.nextval, 'У6 100');

insert into race values (seq_id_race.nextval, 'ЮТ 569');

insert into race values (seq_id_race.nextval, 'УН 107');

insert into race values (seq_id_race.nextval, 'ГЛ 3338');

end fill_races;

таблица авиарейс ключ триггер представление

procedure clear_categories is

begin

delete from categories;

if sql%notfound then

raise NO_DATA_FOUND;

end if;

exception

when NO_DATA_FOUND then

dbms_output.put_line('No companies found!');

end clear_categories;

procedure fill_categories is

begin

insert into categories values (seq_id_categories.nextval, 'первый класс

улучшенный', 1);

insert into categories values (seq_id_categories.nextval, 'первый класс',

1);

insert into categories values (seq_id_categories.nextval, 'первый класс со

скидкой', 2);

insert into categories values (seq_id_categories.nextval, 'бизнес-класс

улучшенный', 2);

insert into categories values (seq_id_categories.nextval, 'бизнес-класс', 2);

insert into categories values (seq_id_categories.nextval, 'бизнес-класс со

скидкой', 3);

insert into categories values (seq_id_categories.nextval, 'экономический

класс улучшенный', 4);

insert into categories values (seq_id_categories.nextval, 'экономический

класс с фиксированными тарифами', 5);

insert into categories values (seq_id_categories.nextval, 'экономический

класс со скидкой', 6);

end fill_categories;

end fill_everything;

begin

fill_everything.fill_races;

fill_everything.fill_categories;

end;

Начальные данные

Рейсы:

select * from race;

Категории сервиса:

select * from categories;

select * from categories, race where categories.race = race.race_id;

Триггер

Триггер должен разрешать добавление только после 20-го числа.

drop trigger on_insert_race;

create or replace trigger on_insert_race

before insert on race

begin

if to_char(sysdate,'dd')<'20' then

raise_application_error(-20999, 'You cannot insert entries to table "race".');

end if ;

end;

При выполнении такого скрипта до 20-го числа появится исключение:

begin

fill_everything.fill_races;

fill_everything.fill_categories;

end;

Представление

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

create or replace view categories_race as

select categories.name as categories_name, race.name as race_name

from categories, race

where (categories.race = race.race_id)

and (race.race_id = 2);

Второй пакет

Содержит вызовы процедур из первого пакета.

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

create or replace package pack_2 is

procedure categories_for_races(except_race_name in VARCHAR);

procedure fill_all;

procedure clear_all;

end pack_2;

create or replace package body pack_2 is

procedure categories_for_races(except_race_name in VARCHAR) is

var_race_id number(8);

var_race_name varchar(128);

custnum number;

cursor c1 is

select race.race_id as race_id, race.name as race_name

from race

where not race.name = except_race_name;

begin

open c1;

FETCH c1 INTO var_race_id, var_race_name;

LOOP

select count(*) into custnum from categories where categories.race =

var_race_id;

dbms_output.put_line(var_race_name || ': ' || custnum);

FETCH c1 INTO var_race_id, var_race_name;

EXIT WHEN c1%NOTFOUND;

END LOOP;

CLOSE c1;

end categories_for_races;

procedure fill_all is

begin

fill_everything.fill_categories;

fill_everything.fill_races;

end fill_all;

procedure clear_all is

begin

fill_everything.clear_categories;

fill_everything.clear_races;

end clear_all;

end pack_2;

Результаты запуска процедуры «categories_for_races»:

3. Привилегии

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

grant select on categories to public;

grant select on race to public;

grant update on categories to up1;

grant update on race to up1;

Выводы о проделанной работе

В процессе разработки скриптов, необходимых для выполнения задания курсовой работы, были разработаны две таблицы: airplanes и flights, необходимые для хранения информации предметной области. При этом были учтены связи между таблицами. Также были разработаны пакеты, содержащие процедуры обработки данных; триггер, контролирующий время выхода фильма; и представление view, позволяющее пользователям БД просматривать информацию из связанных таблиц в формализованном и удобочитаемом виде.

В ходе выполнения данной курсовой работы я закрепил и улучшил навыки работы с СУДБ Oracle, полученные при выполнении лабораторных работ.

Литература

1. Лекции по курсу «Сетевые базы данных»

2. Oracle PL/SQL для профессионалов 3-е изд. / С. Фейерштейн, Б. Прибыл, - СПб.: Питер, 2004. - 941 с.

3. Краткий справочник по Oracle7. http://www.codenet.ru/db/oracle/ora1/

4. Oracle Database SQL Reference 10g Release 2 (10.2).

Размещено на Allbest.ru

...