Синтаксис создания обзора

create view <name>[(<list view_col>)]
as <select> [with check option];

<name> - имя об­зо­ра, имя долж­но быть уни­каль­ным в пе­реч­не имен об­зо­ров, таб­лиц и хра­ни­мых процедур

<list_view_col> ::= <view_col>[,<list_view_col>]

<view_col>  - имя столб­ца об­зо­ра, имя долж­но быть уни­каль­ным в пе­реч­не имен столб­цов об­зо­ра. Спи­сок имен обя­за­те­лен, ес­ли об­зор вклю­ча­ет столб­цы-вы­ра­же­ния. Ес­ли не ука­за­но, ис­поль­зу­ет­ся име­на со­от­вет­ст­вую­щих столб­цов из select.

<select> - за­да­ет ус­ло­вия вы­бор­ки дан­ных по­сред­ст­вом ко­ман­ды select. Мо­жет ис­поль­зо­вать­ся пол­ный син­так­сис select за ис­клю­че­ни­ем кон­ст­рук­ции order by.

with check option - Пре­дот­вра­ща­ет опе­ра­ции insert или update в об­зо­ре, ес­ли они на­ру­ша­ют ус­ло­вие по­ис­ка в кон­ст­рук­ции where ко­ман­ды select.

Ти­пы об­зо­ров

Об­зо­ры мо­гут быть об­нов­ляе­мы­ми (updatable) или толь­ко для чте­ния (read-only).

Что­бы об­зор был об­нов­ляе­мым, не­об­хо­ди­мо:

• что­бы он пред­став­лял со­бой под­мно­же­ст­во од­ной таб­ли­цы или об­нов­ляе­мо­го об­зо­ра;

• что­бы все столб­цы таб­ли­цы, не во­шед­шие в об­зор, до­пус­ка­ли зна­че­ние null;

• что­бы ко­ман­да select, на ко­то­рой ос­но­ван об­зор, не со­дер­жа­ла под­за­про­сов, кон­ст­рук­ций distinct, having, аг­ре­гат­ных функ­ций, при­сое­ди­нен­ных таб­лиц, поль­зо­ва­тель­ских функ­ций или хра­ни­мых про­це­дур.

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

Для обес­пе­че­ния воз­мож­но­сти об­нов­ле­ния дан­ных (ко­ман­ды update и insert) поль­зо­ва­тель дол­жен по­лу­чить со­от­вет­ст­вую­щие пра­ва, кро­ме то­го, для соз­да­ния об­нов­ляе­мо­го об­зо­ра не­об­хо­ди­мо, что­бы его соз­да­тель имел все пра­ва на таб­ли­цы, ис­поль­зуе­мые об­зо­ром. Для об­зо­ра толь­ко для чте­ния дос­та­точ­но иметь пра­ва на select.

ПРИМЕР

create view rubrics as
select unikey, booknm from tbook where (matherkey=0);

Об­нов­ле­ние дан­ных в об­зо­ре с кон­ст­рук­ци­ей WITH CHECK OPTION

Ес­ли об­зор соз­дан с оп­ци­ей про­вер­ки (with check option), то при по­пыт­ке об­нов­ле­ния или встав­ки дан­ных про­ве­ря­ет­ся, удов­ле­тво­ря­ют ли но­вые дан­ные ус­ло­ви­ям вы­бор­ки, за­дан­ным кон­ст­рук­ци­ей where. Дан­ные бу­дут за­пи­са­ны толь­ко при вы­пол­не­нии этих ус­ло­вий. Оп­ция with check option при­ме­ни­ма толь­ко для об­нов­ляе­мых об­зо­ров.

Зна­че­ния мо­гут быть встав­ле­ны че­рез об­зор толь­ко для тех столб­цов, ко­то­рые вхо­дят в об­зор. Для остальных столб­цов таб­ли­цы ус­та­нав­ли­ва­ется зна­че­ния по умолчанию или null.

Из­ме­не­ние об­зо­ра

Нель­зя не­по­сред­ст­вен­но из­ме­нить об­зор. Не­об­хо­ди­мо сна­ча­ла вы­пол­нить его уда­ле­ние (drop view), а за­тем соз­дать его вновь ко­ман­дой create view с тре­буе­мы­ми ха­рак­те­ри­сти­ка­ми.

Уда­ле­ние об­зо­ров

Уда­ле­ние об­зо­ра оз­на­ча­ет уда­ле­ние из ба­зы его опи­са­ния, но не са­мих дан­ных. Оно раз­ре­ше­но поль­зо­ва­те­лю, соз­дав­ше­му об­зор, при вы­пол­не­нии ря­да ус­ло­вий, а имен­но: об­зор не ис­поль­зу­ет­ся дру­гим об­зо­ром, хра­ни­мой про­це­ду­рой или ог­ра­ни­че­ни­ем це­ло­ст­но­сти (check -кон­ст­рук­ци­ей).

drop view <view _name>;

7.2. Триггеры для об­зо­ров (представлений)

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

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

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

Пусть таблица создана командой

create table ProdPrice(

 codeprod integer not null,

 date_beg date not null,

 date_end date not null,

 price numeric (12,2)

);

Здесь

 codeprod – код продукции;

 date_beg – дата начала действия цены (входит в интервал действия цены);

 date_end – дата конца действия цены (не входит в интервал действия цены);

 price – цена продукции.

Первичный ключ составляют поля codeprod и date_beg.

alter table prodprice add constraint pk_prodprice primary key (codeprod,date_beg)

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

Рассмотрим решение этой проблемы с использованием триггера обзора.

Обзор может иметь, например, следующий вид.

create view vProdPrice(codeprod, date_beg, date_end, price)
as

select codeprod, date_beg, date_end, price from ProdPrice;

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

 

create trigger vprodprice_bi for vprodprice

active before

insert or

update

position 0

as

declare variable vcodeprod integer;

declare variable vdate_beg date;

declare variable vdate_end date;

declare variable vprice numeric(12, 2);

begin

if(new.date_beg >= new.date_end) then exit;

delete from ProdPrice where codeprod= new.codeprod

and date_beg>= new.date_beg and date_end<=new.date_end;

-- удаляем все данные внутри нового интервала

for

select * from ProdPrice

where codeprod= new.codeprod

   and date_beg<=new.date_beg and date_end> new.date_beg

into :vcodeprod, :vdate_beg, :vdate_end, :vprice

-- находим запись, внутрь интервала дат, в которой попадает

-- начало добавляемой

-- Из-за удаления внутренних интервалов, таких записей не

-- может быть более одной

do

begin

if(vprice=new.price) then

-- цены не менялись

   if(vdate_end>= new.date_end) then exit;

-- попали внутрь существующего интервала, цены не менялись,

-- ничего не надо делать

   else

    begin

      new.date_beg= vdate_beg;

      delete from ProdPrice

      where codeprod= :vcodeprod and date_beg= :vdate_beg;

-- переместили начало вставляемой записи, старую удалили

    end

else

-- цены менялись

    update ProdPrice set

      date_end= new.date_beg

    where codeprod= :vcodeprod and date_beg= :vdate_beg;

-- закрыли период действия цены началом нового интервала

end

for

select * from ProdPrice

where codeprod= new.codeprod

   and date_beg<new.date_end and date_end>= new.date_end

into :vcodeprod, :vdate_beg, :vdate_end, :vprice

-- находим запись, внутрь интервала дат, в которую попадает начало

-- добавляемой.

-- Из-за удаления внутренних интервалов, таких записей не

-- может быть более одной

do

begin

if(vprice=new.price) then

-- цены не менялись

  begin

      new.date_end= vdate_end;

      delete from ProdPrice

      where codeprod= :vcodeprod and date_beg= :vdate_beg;

  end

else

-- цены менялись

  update ProdPrice set

   date_beg= new.date_end

  where codeprod= :vcodeprod and date_beg= :vdate_beg;

end

insert into ProdPrice(codeprod, date_beg, date_end, price)

values(new.codeprod, new.date_beg, new.date_end, new.price);

end

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

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