SQL для триггеров, функций и хранимых процедур в Oracle (PL/SQL)

Хра­ни­мая про­це­ду­ра – от­дель­ная про­грам­ма, на­пи­сан­ная на PL/SQL. Са­ми про­це­ду­ры хра­нят­ся в ба­зе дан­ных. Хра­ни­мые про­це­ду­ры по­зво­ля­ют вес­ти по­иск и об­ра­бот­ку дан­ных не­по­сред­ст­вен­но на сер­ве­ре, обес­пе­чи­вая мак­си­маль­ную не­за­ви­си­мость кли­ент­ской час­ти при­ло­же­ний. В них мо­гут ис­поль­зо­вать­ся лю­бые кон­ст­рук­ции SQL для про­це­дур и триг­ге­ров, кро­ме кон­тек­ст­ных пе­ре­мен­ных :new.column, :old.column, при­ме­ни­мых толь­ко в триг­ге­рах. Они, как обыч­ные про­грам­мы, мо­гут по­лу­чать вход­ные па­ра­мет­ры и воз­вра­щать зна­че­ния вы­звав­шим их при­ло­же­ни­ям. Хра­ни­мая про­це­ду­ра мо­жет вы­зы­вать­ся не­по­сред­ст­вен­но из при­ло­же­ния или дру­гих хра­ни­мых про­це­дур или триг­ге­ров.

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

 

 

Язык PL/SQL вклю­ча­ет:

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

• опе­ра­то­ры SQL и вы­ра­же­ния, вклю­чая функ­ции поль­зо­ва­те­ля (UDF – user defined functions).

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

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

• ариф­ме­ти­че­ские опе­ра­ции "* /", да­лее "+ -"

• опе­ра­ция кон­ка­те­на­ции стро­ко­вых дан­ных "||"

• опе­ра­ции срав­не­ния =, ==, <>, !=, ~=, ^= (не рав­но), >, <, >=, <= , !>, ~>, ^> (не боль­ше), !<, ~<, ^< (не мень­ше)

• ло­ги­че­ские опе­ра­ции not, and, or.

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

• Кон­тек­ст­ные пе­ре­мен­ные до­пус­ти­мы толь­ко в триг­ге­рах.

Вход­ные и вы­ход­ные па­ра­мет­ры, а так­же ин­ст­рук­ции suspend и exit, ко­то­рые воз­вра­ща­ют зна­че­ния, при­ме­ни­мы толь­ко в хра­ни­мых про­це­ду­рах, return <val> в функциях.

 

Программа (триггер, процедура, функция) включает объявление переменных и тело программы.

 

Программа имеет вид:

<declarations>

<exec_block>

 

<declarations> - объявление переменных.

<exec_block> - текст программы

 

Пре­ж­де чем про­дол­жить, уточ­ним тер­ми­но­ло­гию.

Опе­ра­тор declare – это опе­ра­тор объ­яв­ле­ния пе­ре­мен­ных.

Блок (<block>) – это один или не­сколь­ко опе­ра­то­ров (<compound_statement>), за­клю­чен­ных в опе­ра­тор­ные скоб­ки begin-end.

Опе­ра­тор (<compound_statement>) – это про­стой опе­ра­тор или блок.

<exec_block> ::= <block>

 

Фор­ма­ли­зо­ван­ная за­пись:

<block> ::=
begin
<compound_statement>
end

<compound_statement> ::= {<block> | <statement>; [<compound_statement>]}

<statement> - простой оператор.

<statement> ::= {<op_set_val> | <op_excepton> | <op_call> | <op_if> | <op_for> | <op_while> | <op_post> | <op_sql> | <op_suspend> | <op_end> | <op_when>}

Про­стые опе­ра­то­ры:

опе­ра­тор при­свое­ния;

опе­ра­тор вы­зо­ва про­це­ду­ры;

опе­ра­тор ветв­ле­ния if;

опе­ра­тор цик­ла for;

опе­ра­тор цик­ла while;

опе­ра­тор пре­ры­ва­ния цикла exit;

опе­ра­тор цик­ла for для курсоров;

оператор перехода к метке goto;

операторы SQL insert, update, select;

оператор выхода из программы и возврата значения return;

операторы генерации сообщений об ошибках и их обработка;

операторы работы с курсорами open, fetch, close;

задание транзакции.

Кро­ме то­го, для удоб­ст­ва со­про­во­ж­де­ния про­грамм в их текст мо­гут быть вне­се­ны ком­мен­та­рии.

Рас­смот­рим под­роб­нее базовые конструкции пе­ре­чис­лен­ных опе­ра­то­ров.

Опе­ра­тор объ­яв­ле­ния пе­ре­мен­ных

Опе­ра­тор <declarations>при­ме­ня­ет­ся в хра­ни­мых про­це­ду­рах, функциях и триг­ге­рах и раз­ме­ща­ет­ся в на­ча­ле их те­ла не­по­сред­ст­вен­но пе­ред скоб­кой begin, за ко­то­рой раз­ме­ща­ют­ся все ис­пол­ни­мые опе­ра­то­ры. Все ис­поль­зуе­мые пе­ре­мен­ные долж­ны быть объ­яв­ле­ны. Опе­ра­тор объ­яв­ле­ния за­вер­ша­ет­ся ";". Од­ним опе­ра­то­ром мо­жет быть объ­яв­ле­на толь­ко од­на пе­ре­мен­ная, и объ­яв­ле­ния име­ют смысл толь­ко внут­ри хра­ни­мой про­це­ду­ры, функции или триг­ге­ра.

<declarations>::=[declare] <list_decl>

declare – начинает анонимный блок, если для него необходимы переменные; при определении функции, процедуры, модуля после слова is или as не ставится, а в триггере указывается (тело триггера трактуется, как анонимный блок).

 

<list_decl>::={<decl_var> | <decl_cur_type> | <decl_cursor> | <decl_subproc> | <decl_subfun>};[<list_decl>]

 

<declare _var> - объявление переменной

 <decl _var> ::= <var> <type > [not null] [{:= | default} <value>]}

<var> - имя переменной

<type> ::= {<datatype> | {<var _name> | <rel>.<col>% type}}

{<var> | <rel>.<col>% type}} – тип данных по ссылке на ранее объявленный существующий.

<datatype> - стандартный тип данных

<var _name> - имя ранее объявленной переменной

<rel>.<col> - ссылка на поле другой таблицы

<rel> - имя таблицы

<col> - имя поля

<value> ::= {<literal> | null | <context_var>}

Если используется конструкция not null, то обязательно присвоение начального значения.

Если начального значение не присваивается, то переменная получает значение null.

 

<decl _cur _type> - объявляет курсорного тип данных.

<decl_cur_type>::= type <ref_type_name> is ref cursor [return <return_type>];

 

<ref _type _name> - имя курсорного типа.

<return _type> - тип списка выбираемых значений.

 

<decl_cursor> - объявление курсора для последующего чтения данных из набора, выбираемого командой select. Курсорные переменные могут объявляться только внутри процедур, функций и триггеров.

 

<decl_cursor> ::= <var> <ref_type_name>;

 

<decl_subproc>, <decl_subfun> - объявление процедуры, функции. Объявления необходимы в модулях. Функции и процедуры, объявленные в заголовке модуля (package), доступны для использования во внешних командах.

 

<decl_subproc> ::= procedure <proc_name> [(<list_ par>)];

 

<decl_subfun> ::= function <func_name> [(<list_par>)] returns <datatype>;

 

<list_ par>=<par_name>[{in | out | in out}] <datatype>[,<list_ par>]

 

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

<func_name> - имя функции.

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

{in | out | in out} – задает тип параметра: входной, выходной и входной – выходной.

Опе­ра­тор при­свое­ния

<variable> := <expression>;

<variable> – ло­каль­ная пе­ре­мен­ная, вход­ной или вы­ход­ной па­ра­метр, кон­тек­ст­ная пе­ре­мен­ная.

<expression> – лю­бое до­пус­ти­мое в SQL вы­ра­же­ние, вклю­чаю­щее пе­ре­мен­ные, опе­ра­то­ры SQL, поль­зо­ва­тель­ские (UDF) функ­ции и ге­не­ра­то­ры, вы­ра­же­ния в скоб­ках.

 

Опе­ра­тор вы­зо­ва про­це­ду­ры

Опе­ра­тор осу­ще­ст­в­ля­ет вы­зов хра­ни­мой про­це­ду­ры.

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

call procedure [<shema>.]<name> [<list_param>];

    При вызове процедуры из другой процедуры, функции или триггера оператор имеет следующий синтаксис.

 [<shema>.]<name> [<list_param>];

 

<name> – имя вы­зы­вае­мой про­це­ду­ры. Са­ма про­це­ду­ра долж­на быть пред­ва­ри­тель­но соз­да­на в ба­зе ко­ман­дой create procedure.

<shema> - имя схемы, в которой создана процедура.

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

<param> –вход­ной или выходной па­ра­мет­р про­це­ду­ры (ес­ли про­це­ду­ра не тре­бу­ет па­ра­мет­ров, список па­ра­мет­ров мо­жет от­сут­ст­во­вать). От­дель­ные па­ра­мет­ры мо­гут быть кон­стан­та­ми или пе­ре­мен­ны­ми.

Опе­ра­тор ветв­ле­ния

Опе­ра­тор ветв­ле­ния if … then … elsif … else обес­пе­чи­ва­ет вы­пол­не­ние то­го или ино­го дей­ст­вия в за­ви­си­мо­сти от ис­тин­но­сти про­ве­ряе­мо­го ус­ло­вия.

if <condition> then <list_ statement>
[elsif <condition> then <list_ statement>]

[else <list_ statement>]

end if;

 

<condition> – вы­ра­же­ние, ко­то­рое мо­жет при­ни­мать зна­че­ние ис­ти­на или ложь.

<list_ statement> ::= <statement>; [<list_ statement>]

<statement> - простой оператор (см. вы­ше).

Ес­ли ус­ло­вие вы­пол­не­но (зна­че­ние true), то вы­пол­ня­ет­ся опе­ра­тор, сле­дую­щий за кон­ст­рук­ци­ей then.  При невы­пол­не­нии последовательно проверяются, если они присутствуют условия, заданные после elsif, ина­че вы­пол­ня­ет­ся опе­ра­тор, сле­дую­щий за кон­ст­рук­ци­ей else, ес­ли она при­сут­ст­ву­ет.

Присутствие блоков при наличии нескольких операторов после конструкций then или else не требуется. Окончание оператора помечается обязательной конструкцией end if.

Опе­ра­тор цик­ла loop, while, for

Опе­ра­тор цик­ла while обес­пе­чи­ва­ет вы­пол­не­ние опе­ра­то­ров, ука­зан­ных по­сле клю­че­во­го сло­ва loop по­ка ука­зан­ное по­сле while ус­ло­вие ис­тин­но.

while <condition> loop <list_ statement> end loop;

while … loop … end loop – опе­ра­тор вы­пол­не­ния цик­ла, ко­то­рый по­вто­ря­ет опе­ра­торы <list_ statement>, ука­зан­ный по­сле loop до ограничения цикла end loop, по­ка ус­ло­вие <condition> ис­тин­но. Ус­ло­вие про­ве­ря­ет­ся в на­ча­ле ка­ж­до­го цик­ла. Так пример 6.3.4.

s=1;
while (i >0) do
begin s = s * i; i = i - 1; end

    В Oracle примет вид

Пример 6.4.1.

s:=1; i:=12;
while i >0 loop s := s * i; i: = i - 1;

end loop;

 

Оператор «бесконечного» цикла loop.

loop <list_ statement> end loop;

Пример 6.4.1.

s:=1; i:=12;
while i >0 loop s := s * i; i: = i - 1;

end loop;

 

Опе­ра­тор пре­ры­ва­ния цикла exit;

Для досрочного выхода из цикла используется оператор exit.

Оператор имеет следующий синтаксис.

exit [when <condition>];

 

При отсутствии конструкции when происходит безусловный выход из цикла. Если используемся конструкции when, выход осуществляется, когда условие <condition> истинно. Для «бесконечного» цикла использование оператор exit обязательно.

Пример 6.4.2.

s:=1; i:=12;
loop s := s * i; i: = i - 1;

 exit when i=0;

end loop;

Оператор  цикла for с числовой переменной.

for <val> in [reverse] <low_exp>..<high_exp>

loop <list_ statement> end loop;

Оператор обеспечивает выполнение цикла по переменной <val> с начальным значением <low_exp> и конечным <high_exp> с шагом 1, если режим reverse не задан, либо с начальным значением <high_exp> и конечным <low_exp> с шагом -1, если режим reverse задан.

<low_exp>, <high_exp> - выражение приводимые к числовому виду.

С помощью цикла for пример 6.4.1. примет следующий вид.

Пример 6.4.3.

s:=1;
for i in reverse 12..1 loop s: = s * i;

end loop;

Метки, опе­ра­тор перехода к метке goto

Оператор перехода позволяет передать управление к помеченной точке программы.

goto <label_name>;

Сама метка размещается внутри текста программы и имеет вид

<< <label_name> >>

<label_name> - имя метки, а двойные угловые скобки (выделены подчеркиванием) являются элементом синтаксиса.

 

Пример 6.4.4.

loop

 …

if a>5 then goto lbl1; end if;

…

end loop;

<<lbl1>>

…

Метки могут также использоваться для именования циклов. В этом случае цикл имеет вид

<< <label_name> >>

<op_cicle> <label_name>

В этом случае оператор выхода из цикла может иметь вид

exit <label_name> when <condition>;

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

Опе­ра­тор цик­ла for для курсоров

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

Синтаксис цикла чтения из неявного курсора реализуется следующим образом

for <cur_name> in(<select_statement>)

 loop <list_ statement> end loop;

Доступ к считанным данным обеспечивается указанием имени курсора и имени поля.

<cur_name>.<field_name>

Так цикл чтения примера 6.3.3. примет вид

Пример 6.4.5.

 

for cr in(select author, auname from tauthor)
loop

<list_ statement>

if(cr.auname>p1) then if(cr.auname<p2) then <list_ statement>

end if;

<list_ statement>

end loop;

То же самое действие при явном объявлении имеет вид

[declare] cursor <cur_name> is  <select_statement>;

…

for <cur_name>

 loop <list_ statement> end loop;

 

И пример 6.4.5. примет вид

Пример 6.4.6.

 

declare cursor cr is select author, auname from tauthor;

for cr loop

<list_ statement>

if(cr.auname>p1) then if(cr.auname<p2) then <list_ statement>

end if;

<list_ statement>

 end loop;

 

Оператор выхода из программы и возврата значения return;

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

Пример 6.4.7.

 

procedure …

…

begin

<list_ statement>

 if(…) then return;

<list_ statement>

end

Для выхода из функции используется оператор return <val>.

Выражение, указанное в <val> задает возвращаемое значение.

Программа может содержать несколько операторов return.