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

create trigger <name> [active | inactive] on <ddl_event>
[position <number>]
as <trigger_body>

 

<db_event> ::= {any ddl statement | <ddl_list>}

<ddl_list> ::= <d_event> [,<ddl_list>]

<d_event> ::= {create | alter | drop} <ddl_object>

<ddl_object>={table | procedure | function | trigger | exception | view | domain | role | sequence | user | collation | package | package body | mapping}

Для объектов collation, package опция alter неприменима.

Данная группа триггеров предназначена для контроля изменений структуры базы данных. В больших системах возможны ситуации, когда отдельные давно функционирующие программы «вдруг» начинают работать неправильно. Причиной этой ситуации часто бывают, казалось бы, незначительные изменения в структуре таблиц обзоров, функций или процедур. В действующей системе с сотнями таких объектов отследить подобные изменения достаточно сложно, особенно, если это проявляется при работе программ, которые используются достаточно редко, например полугодовые и годовые отчеты и тому подобное. Использование триггеров этой группы позволяет сохранить историю подобных изменений с одной стороны, и контролировать сам процесс модификации структур базы данных с другой.

Ко­ман­да ALTER TRIGGER

Ко­ман­да alter trigger пред­на­зна­че­на для из­ме­не­ния ра­нее соз­дан­ных триг­ге­ров. Ис­поль­зуя ко­ман­ду alter trigger, мож­но из­ме­нить за­го­ло­вок триг­ге­ра, те­ло триг­ге­ра и то и дру­гое од­но­вре­мен­но. В за­го­лов­ке триг­ге­ра мож­но из­ме­нить: при­знак ак­тив­но­сти триг­ге­ра, ус­ло­вие вклю­че­ния триг­ге­ра (after-before, insert - update - delete), по­ря­док вклю­че­ния триг­ге­ров. Из­ме­не­ние те­ла триг­ге­ра со­сто­ит в его за­ме­не на вновь ука­зан­ное.

Для из­ме­не­ния ав­то­ма­ти­че­ски соз­да­вае­мо­го сис­те­мой (по ко­ман­де check) триг­ге­ра сле­ду­ет ис­поль­зо­вать alter table.

Ко­ман­да alter trigger мо­жет быть так­же ис­поль­зо­ва­на для от­клю­че­ния триг­ге­ра без его уда­ле­ния. Ес­ли ус­та­но­вить оп­цию inactive, то триг­гер не бу­дет вклю­чать­ся. В то же вре­мя он со­хра­ня­ет­ся в ба­зе и для его по­втор­но­го вклю­че­ния дос­та­точ­но вы­дать alter trigger с оп­ци­ей active.

alter trigger <name>[{active | inactive}][{before | after}
{delete | insert | update}][ position <number>] as <trigger_body>;

Синтаксис alter trigger аналогичен син­так­си­су create trigger, за ис­клю­че­ни­ем:

• В ко­ман­де от­сут­ст­ву­ет оп­ция ука­за­ния таб­ли­цы, к ко­то­рой от­но­сит­ся триг­гер. При соз­да­нии триг­ге­ра он свя­зы­ва­ет­ся с оп­ре­де­лен­ной таб­ли­цей и эта связь не мо­жет быть из­ме­не­на, ина­че это бу­дет уже дру­гой триг­гер, для соз­да­ния ко­то­ро­го нуж­на дру­гая ко­ман­да create trigger.

• Ес­ли ка­кие-ли­бо оп­ции в alter trigger от­сут­ст­ву­ют, то они ко­пи­ру­ют­ся из су­ще­ст­вую­ще­го опи­са­ния триг­ге­ра.

Из­ме­не­ние за­го­лов­ка триг­ге­ра

При не­об­хо­ди­мо­сти из­ме­нить за­го­ло­вок триг­ге­ра на­до за­дать спи­сок из­ме­няе­мых оп­ций триг­ге­ра (хо­тя бы од­ной оп­ции). При этом ос­таль­ные оп­ции триг­ге­ра не бу­дут ме­нять­ся. Ес­ли ме­ня­ют­ся оп­ции after или before, то долж­на быть ука­за­на и со­от­вет­ст­вую­щая им оп­ция из пе­реч­ня insert, update, delete.

Из­ме­не­ние те­ла триг­ге­ра

При не­об­хо­ди­мо­сти из­ме­нить толь­ко те­ло триг­ге­ра в за­го­ло­воч­ной час­ти сле­ду­ет за­да­вать толь­ко имя триг­ге­ра.

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

Ко­ман­да drop trigger

Ко­ман­да drop trigger ис­поль­зу­ет­ся для фи­зи­че­ско­го уда­ле­ния триг­ге­ра из ба­зы дан­ных. Для вре­мен­но­го от­клю­че­ния триг­ге­ра сле­ду­ет ис­поль­зо­вать ко­ман­ду alter trigger. Ав­то­ма­ти­че­ски соз­дан­ные триг­ге­ры (кон­ст­рук­ци­ей check) не мо­гут уда­лять­ся. Для уда­ле­ния та­ких триг­ге­ров сле­ду­ет ис­поль­зо­вать ко­ман­ду alter table.

drop trigger <name >;

6.6. Ко­ман­ды соз­да­ния, уда­ле­ния, мо­ди­фи­ка­ции триг­ге­ров и ра­бо­та с ни­ми в Oracle

Синтаксис команд создания триггеров в Oracle, хотя и похож на синтаксис InterBase, но имеет ряд отличий и имеет следующий вид

create [or replace] trigger [<schema.>]<trigger_name>{before | after | instead of} {<dml_event_list> | {<ddl_event_list> | <db_event_list>} on {[<schema.>schema} | database} [referencing <list_ref>] [for each row] [when <condition>] {<pl_sql_block> | <call_proc_stmnt>}

 

<schema.> - имя схемы, в которой находится контролируемый объект (к которому относится триггер); после имени следует «.», отделяющая имя схемы от имени объекта.

 

<dml_event_list> задает список условий, при которых срабатывает триггер на изменение данных

<dml_event_list> ::= {delete | insert | update [of <column_list>]} [or <dml_event_list>]

<column_list> задает список столбцов объекта, при обновлении которых срабатывает триггер.

<column_list> ::= <column_name> [,<column_list>]

<ddl_event_list> задает список условий, при которых срабатывает триггер на изменение структуры данных

<ddl_event_list> ::= <ddl_event> [,<ddl_event_list>]

<ddl_ event> ::= {alter | analyze | associate statistics | audit | comment | create | disassociate statistics | drop | grant | noaudit | rename | revoke | truncate}

 

События alter, create, drop, rename возникают при соответствующих действиях с объектами базы данных.

События analyze, associate statistics, audit, disassociate statistics, noaudit возникают при обработке статистической информации об объектах базы данных.

События grant, revoke возникают при попытках предоставления прав.

Событие truncate возникают при попытках очистки таблиц базы (операция truncate делает таблицу пустой и, в отличие от операции delete не допускает возможности отката).

<db _event _list> - события, возникающие при общих операциях в базе данных

<db_event_list> ::= <db_event> [,<db_event_list>]

<db _event> = {servererror, logon, logoff, startup, shutdown}

Перечисленные события возникают при серверных ошибках, подключении или отключении пользователей к базе данных, при старте и завершении работа сервера Oracle соответственно.

referencing <list _ref> используется для управления контекстными переменными внутри тела триггера.

<list_ref> ={old [as] <user_old>| new [as] <user_new> | parent [as] <user_ parent>} [<list_ref>]

for each row задает условие включения триггера при изменении каждой строки объекта; в этом случае внутри триггера доступны значения столбцов объекта. Если режим for each row не задан, то триггер срабатывает на транзакцию целиком, либо перед ее началом при задании режима before, либо после ее завершения началом при задании режима after. В этом случае значения в отдельных столбцах объекта недоступны, но появляется возможность выполнить подготовительные и завершающие действия при операциях, действующих на группу строк.

Отметим, что такая возможность в рамках InterBase отсутствует. При работе с Oracle контролируется ситуация вызова из триггера команд изменения данных в одной и той же таблице, что в принципе является потенциально опасным действием, могущем вызвать «зацикливание» обработке. Таким образом, при создании (изменении) триггера может появиться сообщение о «мутирующей» таблице. В случаях, когда изменение одной из строк таблицы должно согласованно менять данные в других строках той же таблицы можно рекомендовать использование обзора (view), построенного для этой таблицы в триггерах instead of которого можно уже корректно провести согласованные изменения в таблице; угроза «зацикливания» при этом легко обходится.

 when <condition> задает условие, при котором вызывается триггер. Задание конструкций when <condition> позволяет создать для объекта несколько триггеров, из которых в зависимости от значения <condition> вызываются только необходимые. Это позволяет упростить разработку и отладку триггеров, а также действия, связанные с их подключением, отключением, добавлением новых триггеров без отключения существующих.

<call_proc_stmnt> позволяет вместо тела триггера использовать вызов хранимой процедуры.

<call_proc_stmnt> = call <proc_name>[(<par_list>)]

Триггер подобного типа можно проиллюстрировать следующим примером

 … for each row call my_check(:new.field1, :new.field5, :new.field8);

6.7. Ко­ман­ды соз­да­ния, уда­ле­ния, мо­ди­фи­ка­ции хра­ни­мых про­це­дур; ра­бо­та с ни­ми в InterBase

Соз­да­ние хра­ни­мых про­це­дур

Хра­ни­мые про­це­ду­ры соз­да­ют­ся ко­ман­дой create procedure.

Хра­ни­мая про­це­ду­ра со­сто­ит из за­го­лов­ка и те­ла.

За­го­ло­вок хра­ни­мой про­це­ду­ры со­дер­жит:

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

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

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

Те­ло про­це­ду­ры со­дер­жит:

• Не­обя­за­тель­ный спи­сок ло­каль­ных пе­ре­мен­ных с ука­за­ни­ем их ти­пов.

Блок опе­ра­то­ров на язы­ке про­це­дур и триг­ге­ров, за­клю­чен­ный в опе­ра­тор­ные скоб­ки begin - end. Сам блок мо­жет со­дер­жать внут­ри се­бя дру­гие бло­ки. Ко­ли­че­ст­во вло­же­ний бло­ков прак­ти­че­ски не ог­ра­ни­че­но.

При ис­поль­зо­ва­нии для вво­да ко­ман­ды Script-фай­ла не­об­хо­ди­мо пом­нить, что в со­от­вет­ст­вии с син­так­си­сом опе­ра­то­ры внут­ри про­це­ду­ры от­де­ля­ют­ся друг от дру­га сим­во­лом ";". В то же вре­мя ко­ман­ды в Script-фай­ле так­же от­де­ля­ют­ся ";". Что­бы уст­ра­нить не­од­но­знач­ность раз­де­ле­ния ко­манд, не­об­хо­ди­мо ус­та­но­вить ка­кой-ли­бо иной ог­ра­ни­чи­тель тек­ста ко­манд. Для это­го сле­ду­ет не­по­сред­ст­вен­но пе­ред за­да­ни­ем ко­ман­ды create trigger или create procedure вы­пол­нить ко­ман­ду set term, ус­та­нав­ли­ваю­щую та­кой ог­ра­ни­чи­тель, а в кон­це ра­бо­ты той же ко­ман­дой set term вос­ста­но­вить стан­дарт­ный (";").

 

create procedure <name> [(<lict_param_in>)] [returns(>[(<lict_param_out>)] as <procedure_body>;

 

<lict_param_in> ::= <param_name> <datatype> [,<lict_param_in>]

<lict_param_out> ::= <param_name> <datatype> [,<lict_param_out>]

 

 

<procedure_body> ::= [<variable_declaration_list>] <block>

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

<lict_param_in> задает список входных параметров

<lict_param_out> задает список выходных параметров

<param_name> <datatype> - па­ра­метр про­це­ду­ры, пе­ре­да­вае­мый ей вы­зы­ваю­щи­ми при­ло­же­ния­ми и его тип или возвращаемый процедурой.

Про­це­ду­ра воз­вра­ща­ет зна­че­ния вы­ход­ных па­ра­мет­ров вы­зы­ваю­щим при­ло­же­ни­ям по дос­ти­же­нии ее кон­ца или по ко­ман­де suspend в те­ле про­це­ду­ры

as - клю­че­вое сло­во, от­де­ляю­щее за­го­ло­вок про­це­ду­ры от ее те­ла

Объявление переменных и тело процедуры аналогичны объявлению переменных и телу триггера за исключением того, что префиксы «new.» и «old.» здесь, естественно недоступны, а могут использоваться директивы exit и suspend.

 

ПРИМЕР

create procedure pbuthor(code integer) returns (authors varchar(250))
as
 declare variable auname varchar(60);
 declare variable unikey integer;
 declare variable ws integer;
 begin
ws=-1;
for select a.unikey, b.auname from tbook a, tauthor b, tbook_author c
where
(a.unikey=:code and a.unikey=c.bookkey and c.author=b.author)
into :unikey, :auname do
begin
if(ws=-1) then authors=auname; else authors=authors||', '||auname;
ws=unikey;
end
if(ws!=-1) then suspend;
end

Из­ме­не­ние хра­ни­мых про­це­дур

Из­ме­не­ние хра­ни­мых про­це­дур осу­ще­ст­в­ля­ет­ся ко­ман­дой alter procedure. alter procedure из­ме­ня­ет опи­са­ние хра­ни­мой про­це­ду­ры при ус­ло­вии со­хра­не­ния кор­рект­но­сти ссы­лок на нее со сто­ро­ны объ­ек­тов ба­зы дан­ных. Из­ме­не­ния, вне­сен­ные в хра­ни­мую про­це­ду­ру, сра­зу ста­но­вят­ся дос­туп­ны­ми для всех ис­поль­зую­щих ее при­ло­же­ний. Пе­ре­ком­пи­ля­ция со­от­вет­ст­вую­щих при­ло­же­ний не тре­бу­ет­ся.

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

Син­так­сис ко­ман­ды alter procedure ана­ло­ги­чен син­так­си­су ко­ман­ды create procedure:

alter procedure <name>[([(<lict_param_in>)]
[returns >[([(<lict_param_out>)]
|as <procedure_body>;

<name> – имя су­ще­ст­вую­щей хра­ни­мой про­це­ду­ры.

Со­став па­ра­мет­ров и оп­ций ко­ман­ды пол­но­стью ана­ло­ги­чен па­ра­мет­рам и оп­ци­ям ко­ман­ды create procedure.

Уда­ле­ние хра­ни­мых про­це­дур

Уда­ле­ние хра­ни­мых про­це­дур осу­ще­ст­в­ля­ет­ся ко­ман­дой drop procedure.

Ко­ман­да drop procedure уда­ля­ет су­ще­ст­вую­щую в ба­зе дан­ных про­це­ду­ру, и мо­жет быть вы­пол­не­на соз­да­те­лем про­це­ду­ры, поль­зо­ва­те­лем SYSDBA или дру­гим поль­зо­ва­те­лем с ана­ло­гич­ны­ми пра­ва­ми.

drop procedure <name>;

<name> – имя существующей процедуры.

ПРИМЕР

drop procedure pbuthor;

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

Об­ра­ще­ние к вы­пол­ни­мой про­це­ду­ре

При об­ра­ще­нии к вы­пол­ни­мой про­це­ду­ре из дру­гой вы­пол­ни­мой про­це­ду­ры или триг­ге­ра ис­поль­зу­ет­ся сле­дую­щий син­так­сис:

execute procedure <name> [<param_list>]
[returning_values <param_list>;

<name> – имя про­це­ду­ры,

<param_ list> задает список входных или выходных параметров.

<param_list> ::= : <param> [,<param_list>]

<param> – вход­ной па­ра­метр или воз­вра­щае­мое зна­че­ние.

При об­ра­ще­нии к вы­пол­ни­мой про­це­ду­ре из ISQL ис­поль­зу­ет­ся сле­дую­щий син­так­сис.

execute procedure <name> [(<iparam_list>)];

<iparam_list> - список входных параметров.

<iparam_list> ::= <param> [,<iparam_list>]

Об­ра­ще­ние к про­це­ду­ре вы­бо­ра

При об­ра­ще­нии к про­це­ду­ре вы­бо­ра ис­поль­зу­ет­ся сле­дую­щий син­так­сис:

select <col_list> from <name> [(<param_list>)]
[where <search_condition>] [order by <order_list>];

<name> – имя про­це­ду­ры,

<param_list> - список параметров

<param_list> ::= <param> [,<param_list>]

<param> – вход­ной па­ра­метр про­це­ду­ры,

<col_list> – спи­сок вы­би­рае­мых вы­ра­же­ний (дол­жен ба­зи­ро­вать­ся на спи­ске воз­вра­щае­мых зна­че­ний про­це­ду­ры),

<search_condition>, <order_list> – ус­ло­вия вы­бор­ки и спо­соб упо­ря­до­че­ния ре­зуль­та­тов.

В це­лом струк­ту­ра ко­ман­ды select не за­ви­сит от то­го, ка­ким об­ра­зом вы­би­ра­ют­ся дан­ные (см. разд. 3.1). Син­так­си­че­ски ис­поль­зо­ва­ние про­це­ду­ры от таб­ли­цы или об­зо­ра от­ли­ча­ет­ся толь­ко тем, что по­сле име­ни про­це­ду­ры мо­гут ука­зы­вать­ся ее па­ра­мет­ры.

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

Дос­туп к хра­ни­мым про­це­ду­рам

Дос­туп к хра­ни­мым про­це­ду­рам, так же, как к таб­ли­цам и об­зо­рам, ре­гу­ли­ру­ет­ся ме­ха­низ­мом пре­дос­тав­ле­ния прав дос­ту­па ко­ман­да­ми grant и revoke. По­сколь­ку в сво­ей ра­бо­те хра­ни­мые про­це­ду­ры мо­гут об­ра­щать­ся к таб­ли­цам, об­зо­рам и дру­гим хра­ни­мым про­це­ду­рам, са­мим про­це­ду­рам так­же долж­ны быть ус­та­нов­ле­ны пра­ва дос­ту­па. Из­на­чаль­но про­це­ду­ры по­лу­ча­ют те же пра­ва, что и их соз­да­тель. Дос­туп к ним по­лу­ча­ет их соз­да­тель и поль­зо­ва­тель SYSDBA или дру­гой с ана­ло­гич­ны­ми пра­ва­ми.

 

6.8. Ко­ман­ды соз­да­ния, уда­ле­ния, мо­ди­фи­ка­ции хра­ни­мых про­це­дур; ра­бо­та с ни­ми в Oracle

Базовый синтаксис создания процедур имеет следующий вид.

create [or replace] procedure [<schema>.]<proc_name> [(<list_par>)]

as

begin

[<psql_statements>]

end

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

 

<schema> - имя схемы.

<proc_name> - имя процедуры.

<list_par> - список параметров.

<list_par> ::= <param_descr> [, <list_par>]

<param_descr> ::= <param_name> {in | out | in out} <datatype>

<param_name> - имя параметра.

<datatype> - любой допустимый в PL/SQL Oracle тип данных, однако характеристики размера полей (прежде всего, для символьных полей) и точности (для числовых) в описании типа данных недопустимы.

Текст тела процедуры задает алгоритм реализуемый процедурой в соответствии с описанным выше синтаксисом языка PL/SQL.

Синтаксис команды alter procedure имеет вид.

alter procedure [<schema>.]<proc_name> compile [debug];

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

Синтаксис команды удаления процедуры имеет вид.

 

drop procedure [<schema>.]<proc_name>;

 

6.9. Ко­ман­ды соз­да­ния, уда­ле­ния, мо­ди­фи­ка­ции пользовательских функций; ра­бо­та с ни­ми в InterBase

Соз­да­ние функций

Пользовательские функции, как самостоятельный объект InterBase появляются, начиная с версии 3.0.

Пользовательские функции могут использоваться так же, как и встроенные, везде, где по синтаксису допустимо использование выражений. В теле функций допустимы те же операторы, что и в хранимых процедурах за исключением оператора suspend. Работа функции завершается оператором return <expr>.

Пользовательские функции соз­да­ют­ся ко­ман­дой create function.

Пользовательская функция со­сто­ит из за­го­лов­ка и те­ла.

За­го­ло­вок пользовательской функции со­дер­жит:

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

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

Тип воз­вра­щаемого зна­че­ния ука­зы­ва­ет­ся по­сле клю­че­во­го сло­ва returns.

Те­ло функции со­дер­жит:

• Не­обя­за­тель­ный спи­сок ло­каль­ных пе­ре­мен­ных с ука­за­ни­ем их ти­пов.

Блок опе­ра­то­ров на язы­ке про­це­дур и триг­ге­ров, за­клю­чен­ный в опе­ра­тор­ные скоб­ки begin - end. Сам блок мо­жет со­дер­жать внут­ри се­бя дру­гие бло­ки. Ко­ли­че­ст­во вло­же­ний бло­ков прак­ти­че­ски не ог­ра­ни­че­но.

При ис­поль­зо­ва­нии для вво­да ко­ман­ды Script-фай­ла не­об­хо­ди­мо пом­нить, что в со­от­вет­ст­вии с син­так­си­сом опе­ра­то­ры внут­ри про­це­ду­ры от­де­ля­ют­ся друг от дру­га сим­во­лом ";". В то же вре­мя ко­ман­ды в Script-фай­ле так­же от­де­ля­ют­ся ";". Что­бы уст­ра­нить не­од­но­знач­ность раз­де­ле­ния ко­манд, не­об­хо­ди­мо ус­та­но­вить ка­кой-ли­бо иной ог­ра­ни­чи­тель тек­ста ко­манд. Для это­го сле­ду­ет не­по­сред­ст­вен­но пе­ред за­да­ни­ем ко­ман­ды create function вы­пол­нить ко­ман­ду set term, ус­та­нав­ли­ваю­щую та­кой ог­ра­ни­чи­тель, а в кон­це ра­бо­ты той же ко­ман­дой set term вос­ста­но­вить стан­дарт­ный (";").

create function <name> [(<param_list>)]
returns <datatype>
as <function_body>;

<function_body> ::=[ <variable_declaration_list>] <block>

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

<param_list> ::= <param> <datatype> [,<param_list>]

<param> <datatype> - па­ра­метр функции, пе­ре­да­вае­мый ей вы­зы­ваю­щи­ми при­ло­же­ния­ми и его тип.

returns <datatype> - задает тип возвращаемого зна­че­ния.

as -Клю­че­вое сло­во, от­де­ляю­щее за­го­ло­вок функции от ее те­ла

ПРИМЕР

create procedure pbuthor(code integer) returns varchar(250)
as
 declare variable auname varchar(60);
 declare variable unikey integer;
 declare variable ws integer;

 declare variable authors varchar(250);
 begin
ws=-1;

authors= '';
for select a.unikey, b.auname from tbook a, tauthor b, tbook_author c
where
(a.unikey=:code and a.unikey=c.bookkey and c.author=b.author)
into :unikey, :auname do
begin
if(ws=-1) then authors=auname; else authors=authors||', '||auname;
ws=unikey;
end
return authors;
end

Из­ме­не­ние функций

Из­ме­не­ние пользовательских функций осу­ще­ст­в­ля­ет­ся ко­ман­дой alter function. alter function из­ме­ня­ет опи­са­ние пользовательской функции при ус­ло­вии со­хра­не­ния кор­рект­но­сти ссы­лок на нее со сто­ро­ны объ­ек­тов ба­зы дан­ных. Из­ме­не­ния, вне­сен­ные в пользовательскую функцию, сра­зу ста­но­вят­ся дос­туп­ны­ми для всех ис­поль­зую­щих ее при­ло­же­ний. Пе­ре­ком­пи­ля­ция со­от­вет­ст­вую­щих при­ло­же­ний не тре­бу­ет­ся.

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

Син­так­сис ко­ман­ды alter function ана­ло­ги­чен син­так­си­су ко­ман­ды create function:

alter function <name> [(<param_list>)]
returns <datatype>
as <procedure_body>;

<name>– имя су­ще­ст­вую­щей функции.

<param_list> ::= <param> <datatype> [,<param_list>]

 

Со­став па­ра­мет­ров и оп­ций ко­ман­ды пол­но­стью ана­ло­ги­чен па­ра­мет­рам и оп­ци­ям ко­ман­ды create function.

Уда­ле­ние функции

Уда­ле­ние пользовательских функций осу­ще­ст­в­ля­ет­ся ко­ман­дой drop function.

Ко­ман­да drop function уда­ля­ет су­ще­ст­вую­щую в ба­зе дан­ных функцию, и мо­жет быть вы­пол­не­на соз­да­те­лем про­це­ду­ры, поль­зо­ва­те­лем SYSDBA или дру­гим поль­зо­ва­те­лем с ана­ло­гич­ны­ми пра­ва­ми.

drop function <name>;

<name> – имя су­ще­ст­вую­щей про­це­ду­ры.

ПРИМЕР

drop function pbuthor;

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

Об­ра­ще­ние к функции

При об­ра­ще­нии к пользовательской функции из дру­гой вы­пол­ни­мой про­це­ду­ры или триг­ге­ра ис­поль­зу­ет­ся сле­дую­щий син­так­сис:

<name> ( [<par_list>])

<par_list> ::= <param>[,<par_list>]
<name>– имя функции,

< param> – имя вход­ного параметра.

 

6.10. Ко­ман­ды соз­да­ния, уда­ле­ния, мо­ди­фи­ка­ции функций; ра­бо­та с ни­ми в Oracle

Базовый синтаксис создания функции имеет следующий вид.

create [or replace] function [<schema>.]<func_name> [(<list_par>)] return <datatype>

as

begin

[<psql_statements>]

end

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

 

<schema> - имя схемы.

<func_name> - имя процедуры.

<list_par> - список параметров.

<list_par> ::= <param_descr> [, <list_par>]

<param_descr> ::= <param_name> {in | out | in out} <datatype>

<param_name> - имя параметра.

<datatype> - любой допустимый в PL/SQL Oracle тип данных, однако характеристики размера полей (прежде всего, для символьных полей) и точности (для числовых) в описании типа данных недопустимы.

Текст тела функции задает алгоритм реализуемый функцией в соответствии с описанным выше синтаксисом языка PL/SQL.

Работа функции завершается оператором return, таким образом, тело функции должно содержать, по крайней мере, один такой оператор.

Синтаксис команды alter function имеет вид.

alter function [<schema>.]<proc_name> compile [debug];

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

Синтаксис команды удаления функции имеет вид.

 

drop procedure [<schema>.]<proc_name>;

 

Дос­туп к хра­ни­мым про­це­ду­рам

Дос­туп к хра­ни­мым про­це­ду­рам, так же, как к таб­ли­цам и об­зо­рам, ре­гу­ли­ру­ет­ся ме­ха­низ­мом пре­дос­тав­ле­ния прав дос­ту­па ко­ман­да­ми grant и revoke. По­сколь­ку в сво­ей ра­бо­те хра­ни­мые про­це­ду­ры мо­гут об­ра­щать­ся к таб­ли­цам, об­зо­рам и дру­гим хра­ни­мым про­це­ду­рам, са­мим про­це­ду­рам так­же долж­ны быть ус­та­нов­ле­ны пра­ва дос­ту­па. Из­на­чаль­но про­це­ду­ры по­лу­ча­ют те же пра­ва, что и их соз­да­тель. Дос­туп к ним по­лу­ча­ет их соз­да­тель и поль­зо­ва­тель SYSDBA или дру­гой с ана­ло­гич­ны­ми пра­ва­ми.

Гла­ва 7. Рас­ши­рен­ные воз­мож­но­сти для ра­бо­ты с ба­зой

7.1. Об­зо­ры (представления)

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

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

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

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

• На ос­но­ве ком­би­на­ции под­мно­же­ст­ва строк и столб­цов от­дель­ной таб­ли­цы.

• На ос­но­ве ком­би­на­ции под­мно­же­ст­ва строк и столб­цов объ­е­ди­не­ния не­сколь­ких таб­лиц.

Об­зор мо­жет быть так­же соз­дан на ос­но­ве ре­зуль­та­тов ра­бо­ты хра­ни­мой про­це­ду­ры.

Ис­поль­зо­ва­ние об­зо­ров по­зво­ля­ет обес­пе­чить ре­ше­ние ря­да за­дач:

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

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

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

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