INSERT INTO GRUP236 VALUES(7)

ERROR at line 1:

ORA-20002: Слишком большое уклонение

ORA-06512: at " SYSTEM”.”GRUP236", line 9

ORA-04088: error during execution of trigger PREVISHENIE

SQL> SELECT * FROM GRUP236;

ID FIO DOLGNOST ZVANIE OKLAD

-------------------- ------------------------------------------------------------------------------------------------ 1 Alijalin Denis Urievich ryadovoi kursant 3000

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

Antonov Igor Urievich rjadovoi kursant 3000

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

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

SQL> CREATE OR REPLACE TRIGGER OTCHET

BEFORE INSERT ON GRUP236 FOR EACH ROW

DECLARE

4 StatAvg NUMBER;

5 StatStd NUMBER;

6 StatN NUMBER;

7 Special_case EXCEPTION;

8 BEGIN SELECT COUNT(ID), SUM(ID), STDDEV(ID) INTO StatN, StatAvg, StatStd FROM GRUP236;

9 IF (ABS(StatAvg-StatN*(: new.ID))/(SQRT(StatN)*StatStd)> 3)

10 THEN RAISE Special_case;

11 END IF;

12 EXCEPTION WHEN Special_case THEN

13 DBMS_OUTPUT.PUT_LINE('Слишком большой ID');

WHEN OTHERS THEN

15 DBMS_OUTPUT.PUT_LINE('He диагностируемая

16 ошибка');

17 END;

18 /

Trigger created.

Листинг 37. Пример создания триггера, который запускается так же, как триггер PREVISHENIE, но проводит иную обработку ситуации

При вводе значения атрибута равного 7, соответствующая статистика указывает на большое уклонение, происходит срабатывание триггера OTCHET. Выводится диагностическое сообщение и вводится новая строка. Представленная в листинге 38 операция выборки подтверждает ожидаемое изменение в таблице.

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

Слишком большой ID

Row created.

SQL> SELECT * FROM GRUP236;

ID FIO DOLGNOST ZVANIE OKLAD

-------------------- ------------------------------------------------------------------------------------------------ 1 Alijalin Denis Urievich ryadovoi kursant 3000

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

Antonov Igor Urievich rjadovoi kursant 3000

Zapolskas Denis Genricovich rjadovoi kursant 3000

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

На предложения языка SQL, включенные в код триггера Oracle, наложены некоторые ограничения. Тело триггера не может включать в себя явное использование управляющих операторов COMMIT, ROLLBACK и SAVEPOINT, операторов языка определения данных CREATE, ALTER и DROP, операторов, управляющих разграничением доступа GRANT и REVOKE, а также неявное выполнение перечисленных операторов через вызовы процедур и функций.

УПРАВЛЕНИЕ ВЫПОЛНЕНИЕМ ПРОГРАММ

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

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

Оператор IF...THEN...ELSE позволяет проверить условие и, в зависимости от результатов проверки (TRUE или FALSE), выполнить различные группы операторов. Альтернативная последовательность операторов определяется ключевым словом ELSE. Границы действия оператора IF определяются закрывающей операторной скобкой END IF. Длярасширения структуры ветвления дополнительнопредусмотрены операторные скобки ELSIF, задающие структуры ветвления более глубокого уровня. Oracle использует следующий синтаксис конструкции ветвления в PL/SQL:

* - •

IF условие_1 THEN

операторы_1; -- ветвь!

ELSIF условие_2 THEN

операторы_2; — ветвь2

ELSIF

ELSE операторы_п; -- операторы альтернативы

END IF;

Рассмотрим пример, иллюстрирующий механизм ветвления в программах на PL/SQL. Программа выводит сообщение о классе излучения в зависимости от значения вводимого параметра длины волны (длина волны предполагается заданной в микронах). Для ввода данных используется стандартное соглашение SQL*Plus: переменная, имя которой предваряется знаком " & ", вводится с терминала пользователя.

SQL> DECLARE

Lamda NUMBER; — Длина волны

3 Text1 VARCHAR2(30): ='Инфракрасное излучение';

4 Text2 VARCHAR2 (30.): = 'Видимый свет ';

5 Text3 VARCHAR2(30): = 'Ультрафиолет';

Исполнительный блок

BEGIN

8 Lamda: = & Input_Data;

9 DBMS_OUTPUT.PUT_LINE('');

10 IF (Lamda > 0.65)

11 THEN DBMS_OUTPUT.PUT_LINE(Text1);

12 ELSIF (Lamda < 0.41)

13 THEN DBMS_OUTPUT.PUT_LINE(Text3);

ELSE

15 DBMS_OUTPUT.PUT_LINE(Text2);

16 END IF;

17 END;

Enter value for input_data:. 0.33

old 8: Lamda: = & Input_Data;

new 8: Lamda: = 0.33;

Ультрафиолет

PL/SQL procedure successfully completed.

Листинг 19.Пример программы на PL/SQL, использующей ветвление

Операторы цикла

Организация цикла оформляется в программе на PL/SQL несколькими способами. Самый простой — использование оператора LOOP. Выйти из цикла можно несколькими способами. Конструкция EXIT WHEN обеспечивает выход из цикла при выполнении условия в соответствующем операторе. Рассмотрим пример определения числа, факториал которого является наименьшим числом, большим заданной константы (например, 1 000 000 000).

SQL> DECLARE

Arg NUMBER; — Перем. для выч. факториала

I NUMBER; — Переменная-счетчик

4 Limit NUMBER: = 1000000000; — Граница

5 Textl VARCHAR2 (80)-: = 'Факториал числа,

впервые превышающий 1 000 000 000 ';

Исполнительный блок

1 BEGIN

8 I: = 0;

9 Arg: = 1;

LOOP

11 EXIT WHEN ARG > Limit;

12 Arg: = Arg*(I + 1);

13 I: = I + 1;

14 END LOOP;

15 DBMS_OUTPUT.PUT_LINE(Textl);

16 DBMS_OUTPUT.PUT_LINE(TO_CHAR(Arg) );

17 DBMS_OUTPUT.PUT_LINE(' Искомое число =’

|| TO_CHAR ( I ) );

18 END;

Факториал числа, впервые превышающий 1000 000 000

Искомое число = 13

PL/SQL procedure successfully completed.

Листинг 20.Пример процедуры на PL/SQL, использующей цикл, управляемый оператором EXIT WHEN

Еще одним распространенным вариантом организации цикла является цикл, управляемый ключевым словом WHILE. Управление данного типа обеспечивает выполнение цикла до тех пор, пока условие, определенное ключевым словом WHILE, является истинным (TRUE). Модифицируем предыдущий пример, изменив константу и задав условие выхода из цикла ключевым словом WHILE.

SQL> DECLARE

Arg NUMBER; — Перем. для выч. факториала

I NUMBER; — Переменная-счетчик

4 Limit NUMBER: = 1000000000000; — Граница

5 Textl VARCHAR2(80): = 'Факториал числа,

впервые превышающий 1 000 000 000 000 ';

Исполнительный блок

BEGIN

8 I: = 0;

9 Arg: = I;

10 WHILE Arg < 1000000000000 LOOP

11 Arg: = Arg*(I+1);

12 I: = I + 1;

13 END LOOP;

14 DBMS_OUTPUT.PUT_LINE( Text 1);

15 DBMS_OUTPUT.PUT_LINE(TO_CHAR(Arg));.

16 DBMS_OUTPUT.PUT_LINE('Искомое число =’| |TO_CHAR(I)};.

17 END;

Факториал числа, впервые превышающий

1 000 000 000 000

Искомое число = 15

PL/SQL procedure successfully completed.

Листинг 21.Пример программы на PL/SQL с циклом, управляемым оператором WHILE

Цикл, управляемый оператором FOR, используется в том случае, когда точно известно, сколько раз нужно выполнять итерацию цикла. Рассмотрим пример расчета факториала заданного числа. Обратите внимание, что переменную цикла (в данном случае I) описывать в блоке DECLARE не нужно.

SQL> DECLARE

2 Arg NUMBER: = 1; — Перем. для выч. факториала

3 Limit NUMBER: = 20;.— Граница

4 Textl VARCHAR2(30): = 'Факториал числа 20=';

Исполнительный блок

BEGIN

FOR I IN 1..Limit LOOP

8 Arg: = Arg*I;

9 END LOOP;

10 DBMS_OUTPUT.PUT_LINE(Text||TO_CHAR(Arg));

11 END;

Факториал числа 20 = 2432902008176640000

PL/SQL procedure successfully completed.

Листинг 22. Пример процедуры на PL/SQL, с циклом, управляемым оператором FOR

Оператор GOTO

Оператор перехода GOTO позволяет осуществить переход по метке, присутствующей в теле программы. С помощью уникального идентификатора, заключенного в двойные угловые скобки, можно пометить любую часть программы PL/SQL для организации безусловного перехода по метке. Апеллируя к классической аргументации Э. Дейкстры, можно утверждать, что использование данного оператора в программах не является хорошим стилем для языков, обладающих развитыми средствами обработки событий и управления потоком команд.

КУРСОРЫ

Ключевым понятием языка PL/SQL является курсор. Курсор — это поименованный запрос, содержащий некоторое фиксированное число строк в выборке. По существу курсор является окном, через которое пользователь получает доступ к информации базы данных. Курсоры, в частности, могут использоваться для присваивания конкретных значений переменным программы. PL/SQL неявно объявляет курсор для всех SQL-предложений манипулирования данными, включая запросы, возвращающие ровно одну строку. Допустимое количество курсоров на сессию устанавливается параметром инициализации OPEN_CURSOR в файле параметров. Пусть в базе данных существует таблица ТаЫ, созданная и заполненная предложениями:

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

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

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

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

Существует три основных типа курсоров: неявный, явный и курсорный цикл FOR. Неявный курсор создается автоматически при выполнении оператора вида SELECT... INTO. В процессе выполнения курсор открывается, после чего из него выбираются данные, и закрывается. Все эти действия производятся сервером за один шаг. Если неявный курсор возвращает более чем одну строку, возбуждается предопределенная исключительная ситуация TOO_MANY_ROWS и выдается сообщение об ошибке. Если неявный курсор вообще не возвращает строк, возбуждается другая предопределенная исключительная ситуация NO_DATA_FOUND и также выдается сообщение об ошибке. Пример выборки данных с использованием неявного курсора приведен в листинге 77.

SQL> DECLARE

2 Arg1 NUMBER;

3 Arg2 VARCHAR2(30);

BEGIN

⇐ Предыдущая 1 2 345 Следующая ⇒