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

Авиарейсы: race.

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

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 (-20 999, '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₂ is.

procedure categories_for_races (except_race_name in VARCHAR);

procedure fill_all;

procedure clear_all;

end pack₂;

create or replace package body pack₂ 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₂;

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