ЗАДАНИЕ НА КУРСОВОЕ ПРОЕКТИРОВАНИЕ
Создать две таблицы, каждая из которых должна иметь первичный ключ и, по крайней мере, один столбец с ограничением NOT NULL. Таблицы должны быть связаны внешним ключом; тип связи - "один-ко-многим". Создать пакет, содержащий процедуру начального заполнения таблиц данными (по 7-10 записей в таблице) и процедуру
очистки таблиц (удаления записей).
Для одной из таблиц разработать триггер для обеспечения дополнительных ограничений на изменение данных таблицы (см. свой вариант задания).
Создать представление, которое позволяет запрашивать данные из обеих (связанных) таблиц. Представление должно ограничивать доступ к данным по столбцам и строкам.
Написать второй пакет, в состав которого включить вызовы процедур из первого пакета. В пакет также поместить процедуру изменения данных в таблицах (см. свой вариант задания). Значения изменяемых данных должны передаваться в процедуру как
параметры. В процедурах предусмотреть обработку исключений. Предоставить привилегии всем пользователям базы данных Oracle на использование представления для просмотра данных. Предоставить привилегию конкретному пользователю на выполнение процедуры изменения данных.
Отчет должен отвечать всем требованиям к оформлению курсовых работ и содержать текст задания, тексты сценариев, пакетов, содержимое таблиц и результаты запросов и выполнения процедур.
Чудотворов А.А.
Требуется обрабатывать данные об Издательствах и Спонсорах. Одно издательство может иметь несколько спонсоров. Процедура должна вводить нового спонсора. Триггер должен регистрировать ввод, указывая идентификатор спонсора, пользователя и время ввода. Включить в пакет еще одну процедуру, которая выбирает из БД издательства с максимальным и минимальным числом спонсоров. Выборку данных производить в коллекцию, которую передать как параметр в другую процедуру для вывода данных на экран.
Создание таблиц
Скрипт createtables.sql
Таблица Издательство
-- создание таблицы издательств Publisher
create table Publisher
(pnum number (4),
pname VARCHAR2 (20) NOT NULL);
-- определение первичного ключа таблицы publisher
ALTER TABLE Publisher
ADD (CONSTRAINT pub_pk_pnum PRIMARY KEY (pnum));
Таблица Спонсоры
-- создание таблицы спонсоров Sponsor
create table Sponsor
(snum NUMBER (4),
pnum NUMBER (4),
sname VARCHAR2 (20) NOT NULL);
-- определение первичного и внешнего ключей таблицы Sponsor
ALTER TABLE Sponsor
ADD (CONSTRAINT sp_pk_snum PRIMARY KEY (snum),
CONSTRAINT sp_fk_pnum FOREIGN KEY (pnum)
REFERENCES Publisher(pnum));
Связь между таблицами - «один ко многим», так как каждое издательство может иметь несколько спосноров.
Пакет №1
Пакет №1 содержит процедуру начального заполнения таблиц данными и процедуру очистки таблиц.
Скрипт pack1.sql
CREATE OR REPLACE PACKAGE Pack1 IS
PROCEDURE fill_tables;--заполнениетаблиц
PROCEDURE clear_tables;--очисткатаблиц
END Pack1;
CREATE OR REPLACE PACKAGE BODY Pack1 is
PROCEDURE fill_tables
as
Begin
EXECUTE IMMEDIATE 'CREATE SEQUENCE PubSeq INCREMENT BY 1 START WITH 1000';
EXECUTE IMMEDIATE 'CREATE SEQUENCE spSeq INCREMENT BY 1 START WITH 2000';
INSERT INTO Publisher (pname)
VALUES ('Piter');
INSERT INTO Publisher (pname)
VALUES ('ACT');
INSERT INTO Publisher (pname)
VALUES ('EKSMO');
INSERT INTO Publisher(pname)
VALUES ('Moscow');
INSERT INTO Publisher(pname)
VALUES ('MangaPub');
INSERT INTO Publisher(pname)
VALUES ('Sci-Fi');
INSERT INTO Publisher(pname)
VALUES ('FantasyWorld');
INSERT INTO Publisher(pname)
VALUES ('Aeternum');
INSERT INTO Sponsor (pnum,sname)
VALUES (1000,'GAZPROM');
INSERT INTO Sponsor (pnum,sname)
VALUES (1000,'VTB24');
INSERT INTO Sponsor (pnum,sname)
VALUES (1001,'LUKOIL');
INSERT INTO Sponsor (pnum,sname)
VALUES (1002,'KFC');
INSERT INTO Sponsor (pnum,sname)
VALUES (1002,'ROSTELECOM');
INSERT INTO Sponsor (pnum,sname)
VALUES (1003,'ALFABANK');
INSERT INTO Sponsor (pnum,sname)
VALUES (1003,'ANIDUB');
INSERT INTO Sponsor (pnum,sname)
VALUES (1003,'Cuba77');
INSERT INTO Sponsor (pnum,sname)
VALUES (1004,'Persona99');
INSERT INTO Sponsor (pnum,sname)
VALUES (1004,'ANCORD');
INSERT INTO Sponsor (pnum,sname)
VALUES (1005,'Velzepuz');
INSERT INTO Sponsor (pnum,sname)
VALUES (1006,'Panda`s Soft Inc.');
INSERT INTO Sponsor (pnum,sname)
VALUES (1006,'Microsoft');
INSERT INTO Sponsor (pnum,sname)
VALUES (1007,'Tux');
INSERT INTO Sponsor (pnum,sname)
VALUES (1007,'TrollSposnor');
commit;
DBMS_OUTPUT.put_line('Таблицы успешно заполнены');
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(SQLERRM);
end fill_tables;
PROCEDURE clear_tables
as
Begin
EXECUTE IMMEDIATE 'DROP SEQUENCE PubSeq';
EXECUTE IMMEDIATE 'DROP SEQUENCE spSeq';
DELETE FROM Sponsor;
DELETE FROM publisher;
commit;
DBMS_OUTPUT.put_line('Таблицы успешно очищены');
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(SQLERRM);
end clear_tables;
end Pack1;
Дополнительно в процедуру заполнения таблиц добавлено создание последовательностей, которые используется в триггере для заполнения первичного ключа таблиц Издательство и Спонсор. При удалении записей из таблиц созданные последовательности удаляются.
Данные в таблице Издательства:
Данные в таблице Спонсоры:
Создание триггера
Триггер должен регистрировать ввод, указывая идентификатор спонсора, пользователя и время ввода.CREATE OR REPLACE TRIGGER sponsor_inserting
BEFORE INSERT ON sponsor
FOR EACH ROW
BEGIN
INSERT INTO PubReg VALUES
('INSERT',: new. snum, user, to_char (sysdate,'dd.mm.yyyy hh24:mi:ss'));
dbms_output.put_line('В таблицу sponsor добавлена строка.');
END;
Таблица учёта вставки нового пользователя:
CREATE TABLE PubReg
(
operation VARCHAR2 (10),
SponsorNum NUMBER (4),
user_name VARCHAR2 (20),
user_date VARCHAR2 (20)
);
--формирует автоматически первичный ключ при вставке строк в таблицу Спонсор
CREATE OR REPLACE TRIGGER Sponsor_inc
BEFORE INSERT ON sponsor
FOR EACH ROW
BEGIN
SELECT spSeq. NEXTVAL INTO: new. snum FROM dual;
END;
--формирует автоматически первичный ключ при вставке строк в таблицу издательство
CREATE OR REPLACE TRIGGER Publisher_inc
BEFORE INSERT ON publisher
FOR EACH ROW
BEGIN
SELECT PubSeq. NEXTVAL INTO: new. pnum FROM dual;
END;
Результат выполнения триггера:
Создание представления
Представление PublView позволяет запрашивать данные из обеих (связанных) таблиц. Для ограничения доступа к данным по столбцам выберем только следующие столбцы: Имя спонсора, название издательства. Для ограничения доступа к данным по строкам выберем только строки, где спонсором не является ГазПром.
Скрипт createview.sql
CREATE OR REPLACE VIEW PublView AS
SELECT sname AS Спонсор,pname AS Издательство FROM Publisher,sponsor
WHERE Publisher.pnum = Sponsor.pnum AND sname!= 'GAZPROM';
/
SELECT * FROM PublView;
Результат выполнения скрипта:
Пакет №2
В состав пакета включен вызов процедур из первого пакета, также размещена процедура для добавления нового спонсора. Имя спонсора вводится, как параметр. Также в пакет включена процедура для вывода издательств минимальным и максимальным числом спонсоров. Выборка данных производится в коллекцию, которая передаётся как параметр в процедуру печати результата.
Скрипт pack2.sql
create or replace package pack2 is
TYPE EmpTab IS TABLE OF publisher.pname% type;
PROCEDURE FillTab;
PROCEDURE ClearTab;
PROCEDURE MinAndMaxPub;
PROCEDURE Run(emps IN EmpTab);
PROCEDURE addSponsor(pnum IN NUMBER, sname IN VARCHAR2);
end pack2;
CREATE OR REPLACE PACKAGE BODY pack2 is
PROCEDURE FillTab
AS
BEGIN
pack1.fill_tables;
END FillTab;
PROCEDURE ClearTab
AS
BEGIN
pack1.clear_tables;
END ClearTab;
PROCEDURE addSponsor
(pnum IN NUMBER, sname IN VARCHAR2)
AS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO Sponsor VALUES('||
TO_CHAR (spSeq. NEXTVAL)||','|| TO_CHAR (pnum)||','''||
TO_CHAR (sname)||''')';
END addSponsor;
PROCEDURE MinAndMaxPub
AS
tmp EmpTab;
emps EmpTab;
minPubNum Number (4);
maxPubNum Number (4);
BEGIN
select pnum bulk collect INTO tmp from
(select count (snum) as cnt,pnum from sponsor group by pnum order by cnt);
maxPubNum:= tmp(tmp.Last);
minPubNum:= tmp(tmp.First);
SELECT pname bulk collect INTO emps FROM publisher
WHERE pnum=maxPubNum or pnum=minPubNum;
RUN(emps);
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(SQLERRM);
END MinAndMaxPub;
procedure run(emps IN EmpTab)
as
Begin
if (emps. count = 0) then
DBMS_OUTPUT.PUT_LINE('Коллекция пуста');
Else
DBMS_OUTPUT.PUT_LINE('Результаты:');
for i in emps.FIRST..emps.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(i||') '||emps(i));
END LOOP;
END IF;
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.put_line(SQLERRM);
end run;
end pack2;
Привилегии
Предоставить привилегии всем пользователям базы данных Oracle на использование представления для просмотра данных.
GRANT SELECT ON publview TO public;
GRANT EXECUTE ON pack2 TO up1;
Результаты запросов и выполнения процедур
1. Попытка добавить нового спонсора
pack2.addSponsor(1005, 'Asus');
Результат:
2. Вызов представления PublView
SELECT * FROM PublView;
Результат:
Вызов процедуры minandmaxpub, которая выбирает из БД
1. издательства с минимальным и максимальным числом спонсоров.
Begin
pack2.minandmaxpub;
end;
Результат:
Процедура Run(EmpTab) вызывается внутри функции minmaxpub, передавая сформированную коллекцию в качестве параметра