СОЗДАНИЕ ПОЛЬЗОВАТЕЛЬСКИХ ПРОЦЕДУР И ФУНКЦИЙ

⇐ ПредыдущаяСтр 3 из 5Следующая ⇒

Процедуры и функции — это объекты базы данных и, следовательно, они создаются командой CREATE и уничтожаются командой DROP. При создании процедуры или функции должны быть определены имя объекта, перечень и тип параметров и логика работы программы, закодированная на языке PL/SQL. Чтобы создать процедуру или функцию, необходимо иметь системные привилегии CREATE PROCEDURE. Для создания процедуры, или функции в схеме, отличной от своей схемы, требуется системная привилегия CREATE ANY PROCEDURE.

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

Вид параметра OUT предполагает изменение значения параметра в процессе работы программы, то есть параметр вида OUT — это возвращаемый параметр. Параметр IN OUT— это параметр, которому при вызове должно быть присвоено значение, которое может быть изменено в теле программы. Дополнительно к определениям, необходимым для процедуры, в определении функции должен быть указан тип данных возвращаемого функцией значения. Возврат значения функции выполняется оператором RETURN. Оператор определения процедуры Oracle использует следующий синтаксис:

CREATE [OR REPLACE] PROCEDURE

[имя_ схемы.]имя_процедуры

[(имя_параметра [{IN | OUT J IN OUT}] тип_данных

[, имя_параметра [{IN | OUT | IN OUT}] тип_даннык

...])']

{IS I AS} программа_на_PL/SQL

Ключевое слово OR REPLACE указывает на безусловное замещение старого текста процедуры. Если ключевое слово OR REPLACE не указано и процедура определена, то замещения старого значения кода процедуры не происходит и возвращается сообщение об ошибке. Обратите внимание, что при описании переменных процедуры не используется ключевое слово DECLARE. Блок определения данных начинается сразу после ключевого слова AS (или IS, по выбору пользователя). Рассмотрим пример создания процедуры, которая выводит список значений таблицы. Пусть таблица GRUP236 создана предложением:

SQL> CREATE TABLE GRUP236 (ID NUMBER, FIO VARCHAR2(30), DOLGNOST VARCHAR2(20), ZVANIE VARCHAR2(20), OKLAD NUMBER);

2 INSERT INTO GRUP236 VALUES(1, ’Alijakin Denis Urievich’, ’ryadovoi’, ’kursant’, 3000);

3 INSERT INTO GRUP236 VALUES(2, ’Anshukov Anton Olegovich’, ’ryadovoi’, ’efreitor’, 3100);

4 INSERT INTO GRUP236 VALUES(3, ’Bagenov Petr Igorevich’, ’ryadovoi’, ’efreitor’, 3000);

SQL> CREATE OR REPLACE PROCEDURE " SYSTEM"." VIBORKA" as
2 p number;
3 outstr1 number;
4 outstr2 varchar2(50);
5 outstr3 varchar2(50);
6 outstr4 varchar2(50);
7 outstr5 number;
8 begin
9 p: =1;
10 while p < 7 loop
11 select " ID", " FIO", " DOLGNOST", " ZVANIE", " OKLAD" into

12 outstr1, outstr2, outstr3, outstr4, outstr5 from " SYSTEM"." GRUP236" where

13 " OKLAD" =3000;
14 dbms_output.put_line(outstr1||' '||outstr2||' '||outstr3||' '||outstr4||' '||outstr5);
15 p: =p+1;
16 end loop;
17 end;

Procedure created.

Листинг 30. Протокол создания процедуры, выполняющей выборку записей из таблицы GRUP236

Процедура VIBORKA может быть отправлена на выполнение командой EXEC утилиты SQL*Plus, вызовом из анонимного блока или другой программы. Последующая выборка из таблицы GRUP236 иллюстрирует изменения в базе данных, осуществленные вызовом процедуры VUBORKA.

SQL> BEGIN

2 VIBORKA (1)

3 END;

4 /

Alijalin Denis Urievich ryadovoi kursant 3000

Anshukov Anton Olegovich ryadovoi efreitor 3100 3 Bagenov Petr Igorevich ryadovoi efreitor 3100

PL/SQL procedure successfully completed.

Листинг 31. Протокол выполняемых созданной процедурой действий.

В большинстве случаев первая попытка скомпилировать программу на PL/SQL (процедуру, функцию, пакет или триггер) приводит к получению сообщения о наличии в программе ошибок. Чтобы уточнить выявленные в процессе синтаксического анализа ошибки, можно воспользоваться командой SQL*Plus SHOW ERRORS. Эта команда показывает ошибки, обнаруженные в процессе выполнения CREATE PROCEDURE, CREATE FUNCTION, CREATE TYPE BODY, CREATE PACKAGE, CREATE PACKAGE BODY и CREATE TRIGGER. Если команда SHOW ERRORS используется без параметров, то возвращаются ошибки последней компилированной процедуры, функции, пакета, тела пакета или триггера. Рассмотрим пример обнаружения и исправления ошибки. В процедуре добавим ошибочный оператор, изменяющий значение параметра вида IN (напомним, что параметры вида IN не должны изменяться). Листинг 32 представляет протокол компиляции и вывод команды SHOW ERRORS.

SQL> CREATE OR REPLACE PROCEDURE PROC

Arg1 IN NUMBER)

4 Coeff CONSTANT NUMBER: = 0.5;

BEGIN

6 Arg1: = Arg1 + 234;

7 INSERT INTO GRUP236 VALUES(Coeff*Arg1, SYSDATE);

8 END;

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS

Errors for PROCEDURE INSREC:

LINE/COL ERROR

-------- -----------------------------------------------------------------

PL/SQL: Statement ignored

PL/SQL: SQL Statement ignored

Листинг 32. Протокол, иллюстрирующий способ диагностики синтаксических ошибок в тексте процедуры

Конечно, выявление синтаксических ошибок — это первый, самый простой шаг в процессе отладки процедур и функций. Для удобства отладки программ на языке PL/SQL можно использовать специализированные средства, например интегрированную среду SQL Navigator производства Quest Software. Обсуждение методов и средств отладки семантики программ и комплексной отладки выходит за рамки этого руководства. Напомним, что функции PL/SQL отличаются от процедур тем, что возвращают в вызывающую среду значение параметра. Оператор определения функции Oracle использует следующий синтаксис:

CREATE [OR REPLACE] FUNCTION

[ишг_схемы. ] имя_ функции

[ (имя_параметра [{IN | OUT | INOUT}] тип_данных

[, имя_параметра [{IN | OUT | INOUT}] тип_данных

....])]

RETURN тип_данных

{IS | AS} программа_на_РЬ/SQL

Ключевое слово OR REPLACE указывает на безусловное замещение старого текста функции. Если ключевое слово OR REPLACE не указано и функция определена, то замещения старого значения кода функции не происходит и возвращается сообщение об ошибке. Описание типа данных для возвращаемого функцией значения требуется обязательно. При описании переменных функции так же, как и при описании переменных процедуры, не используется ключевое слово DECLARE. Блок определения данных начинается сразу после ключевого слова IS (или AS, по выбору пользователя). Рассмотрим пример создания функции, которая вычисляет сумму значений атрибутов, таких, что дата попадает в заданный параметрами функции интервал. Пусть таблица GRUP237 создана и заполнена предложениями:

CREATE TABLE GRUP237 (At1 NUMBER, At2 DATE);

INSERT INTO GRUP237 VALUES(5, SYSDATE);

INSERT INTO GRUP237 VALUES(6, SYSDATE);

INSERT INTO GRUP237 VALUES(7, SYSDATE+1);

\

Протокол создания функции представлен в листинге 33.

SQL> CREATE OR REPLACE FUNCTION SumRecInt

Arg1 IN DATE, Arg2 IN DATE) RETURN NUMBER AS

3 SumVar NUMBER: = 0;

BEGIN

SELECT Sum(At1) INTO SumVar FROM GRUP237

6 WHERE At2 BETWEEN Arg1 AND Arg2;

7 RETURN SumVar;

8 END;

9 /

Function created.

SQL> BEGIN

2 DBMS_OUTPUT.PUT_LINE(SumRecInt(SYSDATE-

1/2, SYSDATE+1/2));

3 END;

4 /

PL/SQL procedure successfully completed.

Листинг 33. Протокол создания функции и обращения к ней из среды PL/SQL

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

DROP PROCEDURE [имя_схемы.]имя_процедуры

Для уничтожения функции Oracle использует следующий

синтаксис:

DROP FUNCTION [имя_схемы.]имя_функции

• • \

Рассмотрим пример уничтожения функции Oracle:

SQL > DROP FUNCTION SumRecInt;

Function dropped;

Листинг 34. Протокол уничтожения функции в среде PL/SQL

ТРИГГЕРЫ БАЗЫ ДАННЫХ

Триггер базы данных — это процедура, которая автоматически запускается при возникновении определенных событий, связанных с выполнением операций вставки, удаления или модификации данных таблицы. Событие, управляющее запуском триггера, описывается в виде логических условий. Когда возникает событие, соответствующее условиям триггера, сервер Тгасе автоматически запускает триггер. Триггеры особенно полезны там, где необходимо обеспечить сложный контроль защиты данных или специальный аудит. Они могут также осуществлять контроль целостности данных, если требуется более сложная проверка, чем проверка, обеспечиваемая декларативными ограничениями целостности. Триггер запускается при выполнении одной из трех операций изменения содержимого таблицы: INSERT, DELETE или UPDATE. Триггер может запускаться и несколькими операторами, но хотя бы один оператор из трех должен быть обязательно указан в условии запуска триггера. Если перечень операторов, запускающих триггер, включает оператор UPDATE, то для условий срабатывания могут быть указаны конкретные изменяемые столбцы. Код триггера может выполняться либо до, либо после тех операторов, которые инициировали запуск триггера. Например, если триггер запускается для проверки полномочий пользователя на право выполнения операции, то, конечно, нужно использовать триггер с запуском до выполнения операции (с ключевым словом BEFORE). Если триггер применяется для формирования данных для аудиторской записи, то разумно использовать триггер с запуском после выполнения операции (с ключевым словом AFTER). Иногда возникает необходимость отключения триггеров, например, при проведении массовых операций над данными.Для этого предназначены следующие операторы, которые могут переключать режим, разрешая или запрещая запуск триггера — ALTER TRIGGER (изменяется режим указанного триггера) с опцией DISABLE или ENABLE и ALTER TABLE (в этом случае переключается режим у всех триггеров, связанных с таблицей). Код триггера может быть ассоциирован либо с операцией над таблицей в целом, либо с каждой строкой, над которой выполняется операция. В зависимости от этого триггеры подразделяют на операторные триггеры и строчные триггеры. Операторные триггеры обычно используют для проверки правил, оперирующих таблицей в целом, а строчные триггеры часто используют для проверки ограничений целостности при вставке строк. Условие запуска Строчного триггера может быть уточнено дополнительным логическим условием.

Чтобы создать триггер, необходимо иметь системную привилегию CREATE TRIGGER. Для создания триггера в схеме, отличной от текущей схемы пользователя, требуется системная привилегия CREATE ANY TRIGGER. Оператор определения триггера Oracle использует следующий синтаксис:

CREATE [ORREPLACE ]TRIGGER [имя_схемы] имя_триггера

{BEFORE | AFTER}

{INSERT | DELETE | UPDATE [OF имя_столбца

[, имя_столбца ... ]] }

[OR {INSERT.| DELETE | UPDATE [OF имя_столбца

[, имя_столбца ... ]] }...]

ON [имя_схемы.]{имя_таблицы \ имя_представления }

[FOR EACH ROW][WHEN условие ]

спецификация_программы_на_ PL/SQL

Ключевое слово OR REPLACE указывает на безусловное замещение старого текста триггера. Если ключевое слово OR REPLACE не указано и триггер уже определен, то замещения старого кода триггера не происходит и возвращается сообщение об ошибке. Ключевые слова BEFORE или AFTER указывают на выполнение кода триггера либо до, либо после операторов манипулирования данными, инициировавших запуск триггера. Ключевые слова INSERT, DELETE или UPDATE определяют конкретный оператор, запускающий триггер. Необязательное ключевое слово OR присоединяет дополнительный оператор, запускающий триггер. Ключевое слово ON задает имя таблицы, ассоциированной с триггером. Необязательное ключевое слово FOR EACH ROW определяет триггер как строчный. Необязательное ключевое слово WHEN задает дополнительное логическое условие, сужающее область событий, при наступлении которых триггер запускается. Прежде чем перейти к примеру построения триггера, приведем некоторые дополнительные сведения об обработке исключительных ситуаций в Oracle. Для аварийного завершения программ PL/SQL применяется процедура RAISE_APPLICATION_ERROR. С ее помощью можно обработать до 1000 определяемых пользователем ошибок с номерами в диапазоне от -20000 до -20999. Вызов процедуры RAISE_APPLICATION_ERROR приводит к генерации исключительной ситуации и завершению выполнения вызвавшей процедуру программы (сравните с рассмотренным выше оператором PL/SQL RAISE). При этом в среду, вызвавшую программу, возвращается номер и текстовое сообщение о типе ошибки. Рассмотрим пример триггера, который выполняется, если значение вводимого атрибута " слишком уклоняется" от среднего значения для текущего состояния таблицы. В роли меры " слишком большого уклонения" выберем широко применяемое в инженерной практике правило " трех сигм". Пусть таблица GRUP236 создана и заполнена предложениями:

SQL> CREATE TABLE GRUP236 (ID NUMBER, FIO VARCHAR2(30), DOLGNOST VARCHAR2(20), ZVANIE VARCHAR2(20), OKLAD NUMBER);

2 INSERT INTO GRUP236 VALUES(1, ’Alijakin Denis Urievich’, ’ryadovoi’, ’kursant’, 3000);

3 INSERT INTO GRUP236 VALUES(2, ’Anshukov Anton Olegovich’, ’ryadovoi’, ’efreitor’, 3100);

4 INSERT INTO GRUP236 VALUES(3, ’Bagenov Petr Igorevich’, ’ryadovoi’, ’efreitor’, 3000);

Протокол создания триггера представлен в листинге 24. При срабатывании триггера предусмотрена генерация стандартной обработки ошибки, которой присваивается номер 20002 с соответствующим диагностирующим сообщением. Обратите внимание на предопределенную переменную: new.ID, содержащую (по ее смыслу) вводимое значение атрибута P.

SQL> CREATE OR REPLACE TRIGGER " SYSTEM ".” PREVISHENIE ”

2 BEFORE INSERT ON " SYSTEM "." GRUP 236" REFERENCING OLD AS OLD NEW 3 AS NEW FOR EACH ROW

4 declare
5 p number;
6 begin
7 select id into p from grup236 where id = 7;
8 if (p < (: new.id)) then raise_application_error(-20002, 'COLICHESTVO KURSANTOV PREVISHENO');
9 end if;
10 end;

Trigger created.

Листинг 35. Пример создания триггера, который запускается при превышении допустимого идентификационного номера ID у новой записи

Работу механизма триггера проиллюстрируем на примере. При вводе значения, меньше допустимого (в нашем случае - 7), триггер не запускается и " ничего не происходит". При вводе значения атрибута, равного 7 соответствующая статистика указывает на недопустимое значение ID, происходит срабатывание триггера и новая строка не включается. Представленная в листинге 36 операция выборки подтверждает ожидаемое изменение в таблице.

SQL> INSERT INTO GRUP236 VALUES(4, ’Antonov Igor Urievich’, ’rjadovoi’, ’kursant’, 3000);

Row created.

SQL> INSERT INTO GRUP236 VALUES(7, ’Zapol’skas Denis Genricovich’, ’rjadovoi’, ’kursant’, 3000);

⇐ Предыдущая 1 234 5 Следующая ⇒