WITH CHECK OPTION;
Обновляемость представления проверим, используя инструкцию INSERTINTO.
INSERT INTO ned_in
VALUES (null, 'Nevezhda Nadezhda', 3,'18.11.2016', 1);
СУБД добавит новую строку в исходную таблицу zh_orders; она будет видна также в представлении ned_in. Однако попытка добавить строку с датой раньше sysdate -7 будет отклонена
3. (Вертикальное или смешанное необновляемое представление) Создадим похожее представление, но без некоторых столбцов(код заказа и имя покупателя, вовсе не потому что сломан триггер).
CREATE OR REPLACE VIEW ned_in AS
SELECT kp, data, kolichestvo from zh_orders
WHERE data BETWEEN sysdate -7 and sysdate;
В данном смешанном представлении скрыты от пользователя столбцы, обязательные для заполнения, что не дает использовать через него DML команды.
Лабораторная работа №4
1. Создать процедуру.
2. Создать функцию.
Варианты заданий для написания процедур и функций приведены в Приложении 4.
При созданииследует выполнить следующие требования:
- использовать явный курсор или курсорную переменную, а также атрибуты курсора;
- использовать пакет DBMS_OUTPUT для вывода результатов работы в SQLPlus;
- предусмотреть секцию обработки исключительных ситуаций, причем обязательно использовать как предустановленные исключительные ситуации, так и собственные (например, стоит контролировать наличие в БД значений, передаваемых в процедуры и функции, как параметры);
3. Объединить процедуру и функцию в пакет.
4. Написать анонимный PL/SQL блок, в котором будут вызовы реализованных функций и процедур пакета с различными характерными значениями параметров для проверки правильности работы основных задач и обработки исключительных ситуаций.
5. Написать локальную программу.
6. Написать перегруженную программу.
Итоговый пакет.
. Написать процедуру, которая формирует заказ на товары, которых осталось меньше необходимого запаса и выводит его на экран, подсчитывая общий итог. Величину минимального запаса передавать во входном параметре.
Создать функцию, возвращающую пустую строку или строку «закончился срок реализации», если дата поставки плюс срок реализации партии товара больше текущей даты и времени. Поля даты поставки и срока реализации должны присутствовать в таблицах БД.
Create or replace package Meshok as
procedure tepre(A in integer);
function is_my_item_spoiled(A zh_catalog.kp%TYPE) return varchar2;
procedure testin;
END Meshok;
/
create or replace package body Meshok AS
procedure locale(addme integer, id integer) as
begin
update zh_catolog set amount = amount + addme where kp = id;
dbms_output.put_line('dobavleno '||addme|| ' k predmetam '||id);
end;
procedure locale(newn varchar2, id integer) as
begin
update zh_catolog set name = newn where kp = id;
dbms_output.put_line ('renaming comlited');
end;
procedure testin
is
Kop integer default 1;
Kip varchar2(50) default 'meloch za 300';
kup integer default 4;
begin
locale(Kup, Kop); /*количество, которое добавляется и номер, к которому добавляется*/
locale(Kip, kop); /*новое название и код предмета, котороый меняем*/
end;
procedure tepre(A in integer)
is
Bi integer;
Ci integer;
Di integer;
err_empty exception;
cursor tempo(A in integer)
is
select kp, (A - amount) from zh_catolog where amount < A;
begin
open tempo(A);
fetch tempo into Bi, Ci;
if tempo%NOTFOUND then
raise err_empty;
end if;
LOOP
DBMS_OUTPUT.enable;
exit when tempo%NOTFOUND;
DBMS_OUTPUT.put_line('Master, we need '||Ci||' more kp '||Bi);
fetch tempo into Bi, Ci;
end LOOP;
close tempo;
select sum(A-amount) into Di from zh_catolog where amount < A;
DBMS_OUTPUT.put_line('In total: '||Di||' items');
exception
when err_empty then dbms_output.put_line('we found nothing');
when INVALID_CURSOR then DBMS_OUTPUT.PUT_LINE('Some problems with cursor');
END tepre;
function is_my_item_spoiled(A zh_catalog.kp%TYPE)
return varchar2
is
Ki number(1);
No varchar2(28);
begin
DBMS_OUTPUT.enable;
select case
when not exists (select * from zh_catolog where kp=A)
then 1
else 0
end into Ki
from dual;
if Ki = 1
then dbms_output.put_line('item not found');
end if;
select case
when exists(
select zh_catolog.kp
from zh_postavki, zh_catolog
where zh_catolog.kp=zh_postavki.kp and zh_postavki.kp=A and (zh_postavki.datap + zh_catolog.srok_realizacy)< sysdate
)
then 1
else 0
end into Ki
from dual;
if Ki = 1
then
No:= 'implementation period ended';
else
No:= '';
end if;
return No;
end;
end Meshok;
/
Использование
declare
begin
Meshok.tepre(2);
commit;
end;
/
(локальные и перегруженные)
declare
begin
Meshok.testin;
commit;
end;
/
declare
tt varchar2(28);
begin
DBMS_OUTPUT.enable;
tt:= Meshok.is_my_item_spoiled(1);
dbms_output.put_line(tt);
end;
/ Лабораторная работа №5
1. Написать DML триггер, регистрирующий изменение данных (вставку, обновление, удаление) в одной из таблиц БД. Во вспомогательную таблицуLOG1 записывать кто, когда (дата и время) и какое именно изменение произвел, для одного из столбцов сохранять старые и новые значения.
2. Написать DDL триггер, протоколирующий действия пользователей по созданию, изменению и удалению таблиц в схеме во вспомогательную таблицуLOG2 в определенное время и запрещающий эти действияв другое время.
3. Написать системный триггер добавляющий запись во вспомогательную таблицу LOG3, когда пользователь подключается или отключается. В таблицу логов записывается имя пользователя (USER), тип активности (LOGONилиLOGOFF), дата (SYSDATE), количество записей в основной таблице БД.
4. Написать триггеры, реализующие бизнес-логику (ограничения) в заданной предметной области. Варианты заданий приведены в Приложении5. Тип триггера: строковый или операторный, выполнятся AFTER или BEFORE определить самостоятельно, исходя из сути задания, третий пункт задания предполагает использование триггера с предложением WHEN.
Написать DML триггер, регистрирующий изменение данных
(вставку, обновление, удаление) в одной из таблиц БД.
Во вспомогательную таблицу LOG1 записывать кто, когда (дата и время) и какое
именно изменение произвел, для одного из столбцов сохранять старые и новые значения.
create table zh_log1 (
actor varchar2(10),
ddata VARCHAR2(20),
action varchar2(10));
create or replace trigger change_table_Materials
before delete or insert or update on zh_materials
for each row
declare
x varchar2(10);
begin
if deleting then x:='delete';
elsif inserting then x:='insert';
elsif updating then x:='update';
end if;
insert into zh_log1 values (user,TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS'), x,);
end change_table_materials;
/
INSERT INTO zh_materials
VALUES (4,'kozhzam','white');
delete from zh_materials where km=4;
2/
2. Написать DDL триггер, протоколирующий действия пользователей по созданию,
изменению и удалению таблиц в схеме во вспомогательную таблицу LOG2
в определенное время и запрещающий эти действияв другое время
create table log2 (operation varchar2(14) not null,
table_name varchar2(20),
tdate varchar2(20),
useer varchar2(15));
create or replace trigger trg2
after create or alter or drop on database
declare
op1 varchar2(10):='kos';
forbiden exception;
begin
if (to_char(sysdate,'hh24') between 8 and 17)
then
case
when sys.sysevent='CREATE'
then
op1:='Creat';
when sys.sysevent='ALTER'
then
op1:='Alter';
when sys.sysevent='DROP'
then
op1:='Delit';
end case;
else
dbms_output.enable;
raise forbiden;
end if;
insert into log2 values (op1,sys.dictionary_obj_name,TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS',user)
proc2(op1,sys.dictionary_obj_name);
exception
when forbiden then raise_application_error(-20000,'You cant do it right now. Time = '||to_char(sysdate,'hh24'));
end trg2;
3. Написать системный триггер добавляющий запись во вспомогательную таблицу LOG3, когда пользователь подключается или
отключается. В таблицу логов записывается имя пользователя (USER), тип активности (LOGON или LOGOFF), дата (SYSDATE),
количество записей в основной таблице БД.
CREATE TABLE SYSTEM.log3(
BAZA VARCHAR2(20),
DATE_TIME VARCHAR2(20),
OPERAT VARCHAR2(50),
ZAPISI integer);
CREATE OR REPLACE TRIGGER system_trigger
AFTER LOGON ON DATABASE
BEGIN
if user!= 'SYS'
then
INSERT INTO SYSTEM.log3
VALUES(USER, TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS'), 'UserIsLoged', (select count(*) from zh_orders));
end if;
END system_trigger;
/
CREATE OR REPLACE TRIGGER system_trigger1
before LOGOFF ON DATABASE
BEGIN
if user!= 'SYS'
then
INSERT INTO SYSTEM.log3
VALUES(USER, TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS'), 'UserGoneAway', (select count(*) from zh_orders));
end if;
END system_trigger1;
/
select * from system.log3;
1 подготовка
create table zh_reward(no_pro integer,name varchar2(22), bonuses integer);
связал по no_pro c заказами.
1) Реализовать политику премирования продавцов, в зависимости от количества или суммы продаж в месяц.
теперь продавцу возвращается часть проданого
create or replace trigger backs
before insert or update on zh_orders
for each row
declare
x integer;
begin
x:=100;
update zh_reward set bonuses =(bonuses + 0.05*(select c.cost from zh_catolog c where c.kp=:new.kp)*:new.kolichestvo) where no_pro=:new.no_pro;
end;
/
2 Управлять количеством товара в наличии при осуществлении продажи (вычитать количество купленного, запрещать продажи с недостающим количеством или предлагать купить остаток и т.д.), рассчитывать общую стоимость каждой покупки с учетом наценки магазина.
create or replace trigger logic
before insert or update on zh_orders
for each row
declare
x integer;
cursor oned is
select amount from zh_catolog where kp=:new.kp;
denied exception;
begin
open oned;
dbms_output.enable;
fetch oned into x;
if (x <:new.kolichestvo)
then
raise denied;
else
update zh_catolog set amount = (amount -:new.kolichestvo) where kp=:new.kp;
end if;
close oned;
exception when denied then raise_application_error(-20998,'failure, not enough items in stock');
end logic;
/
3 В определенное время сообщать о наличии продавцов, которые не продали за день (неделю, месяц) ни одного товара
CREATE OR REPLACE TRIGGER lentyi
AFTER LOGON ON DATABASE
when (to_char(sysdate,'hh24') between 8 and 17)
declare
x integer;
cursor komet is
select no_pro from zh_reward where no_pro not in(select no_pro from zh_orders
where data between sysdate -7 and sysdate);
BEGIN
open komet;
fetch komet into x;
LOOP
DBMS_OUTPUT.enable;-
exit when komet%NOTFOUND;
DBMS_OUTPUT.put_line('One of your staff dont work till week #'||x);
fetch komet into x;
end LOOP;
close komet;
END lentyi;
/