Лабораторная работа №4. Секция исключений. Функции.

Цель работы: изучить возможности по обработке исключений в модулях PL/SQL, научиться создавать и вызывать функции.

Ошибки времени выполнения могут возникнуть из-за ошибок в алгоритме, ошибок в исходных текстах, ошибок аппаратного обеспечения и по многим другим причинам. Несмотря на то, что невозможно предугадать все возможные ошибки, в модуле PL/SQL все-таки возможно обработать определенные, наиболее вероятные виды ошибок. В PL/SQL существует механизм, который называется обработкой исключительных ситуаций (EXCEPTION HANDLING). С помощью этого механизма возможно создавать модули PL/SQL, которые могут продолжить выполнять действия при возникновении ошибок, например, пользователь, который вызвал данную модуль PL/SQL, в случае возникновения ошибки должен быть проинформирован о возникновении исключения и о причинах, его вызвавших.

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

/* ЗАГОЛОВОЧНАЯ ЧАСТЬ */

/* СЕКЦИЯ ОБЪЯВЛЕНИЙ */

Begin

/ * ВЫПОЛНЯЕМАЯ СЕКЦИЯ */

/ * СЕКЦИЯ ИСКЛЮЧЕНИЙ */

EXCEPTION

WHEN ТИП_ИСКЛЮЧЕНИЯ1 THEN

Операторы; /* обработка исключения 1 */

Операторы;

WHEN ТИП_ИСКЛЮЧЕНИЯ2 THEN

Операторы; /* обработка исключения 2 */

Операторы;

End.

Выполнение операторов в секции исключений называется обработкой исключений, например, обработка исключения ТИП_ИСКЛЮЧЕНИЯ1. При возникновении ошибки все операторы, находящиеся между оператором, вызвавшим ошибку, и секцией исключений игнорируются. Выполнение подпрограммы переходит непосредственно в секцию исключений. Если в данной подпрограмме PL/SQL секции исключений нет либо в ней отсутствует обработка возбужденного исключения, то СУБД ищет обработку исключения во внешних блоках PL/SQL по цепочке. Если и там обработка данного исключения отсутствует, то вызывается типовая обработка исключений ORACLE, которая просто прерывает выполнение модуля и выводит сообщение об ошибке. После обработки исключения выполнение программы перейдет к следующему оператору внешнего блока кода сразу после вызова подпрограммы, вызвавшей исключение.

Процесс, включающий в себя обнаружение ошибки, определение, какой тип исключений описывает данную ошибку наилучшим образом, и передачу PL/SQL информации, позволяющей найти соответствующий код в секции исключений, называется возбуждением исключений (raising exceptions). При обработке исключений могут быть полезными две команды, которые возвращают параметры ошибки:

1. SQLCode – функция СУБД ORACLE, которая возвращает код ошибки;

2. SQLErrm – функция СУБД ORACLE, возвращает текстовое сообщение, описывающее ошибку.

Все Исключения делятся на две большие группы:

1. системные исключения;

2. пользовательские исключения.

Системные исключения определены непосредственно в СУБД и возбуждаются автоматически при той или иной ошибке. Перечислим некоторые из системных исключений:

1. CURSOR_ALREADY_OPEN - ошибка при попытке открыть уже открытый курсор;

2. DUP_VAL_ON_INDEX – ошибка при попытке вставить повторяющее значение в колонку имеющую ограничение уникальности;

3. INVALID_CURSOR – ошибка при попытке применить команду FETCH к неоткрытому курсору или попытка закрыть неоткрытый курсор;

4. NO_DATA_FOUND – ошибка при попытке выполнить запрос SELECT INTO, когда SELECT возвращает нулевой НД;

5. TOO_MANY_ROWS – ошибка, которая возникает, когда запрос SELECT INTO вернул более одной строки;

6. ZERO_DIVIDE – ошибка при делении на нуль;

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

Рассмотрим несколько примеров:

create or replace procedure NIZ(salary in out integer)

as

I integer;

begin

select count(*) into i from employees where salary = 22;

salary:= salary / i;

DBMS_output.put_line('Average salary = ' || salary);

exception

when ZERO_DIVIDE THEN dbms_output.put_line('Ошибка деления на 0!');

when NO_DATA_FOUND THEN

dbms_output.put_line('Данные по заработной плате не найдены!');

end;

 

create or replace procedure VERH as

S number(12,1);

begin

select sum(max_salary) into s from jobs where max_salary = 1111;

S:= 0;

dbms_output.put_line('S = ' || S);

niz(S);

dbms_output.put_line('S = ' || S);

exception

when ZERO_DIVIDE THEN

dbms_output.put_line('Ошибка деления на 0!');

end.

Пользовательские исключения в отличие от системных исключений объявляются и возбуждаются пользователем БД самостоятельно. Обычно они применяются в тех случаях, когда нужно определить некие логические условия, выход за которые должны интерпретироваться как ошибка. Например, количество товаров в заказе всегда должно быть целым положительным числом.

Работа с пользовательскими исключениями в СУБД ORACLE включает три операции: объявление исключения, возбуждение исключения и обработка исключения. Определяется исключение, как и локальные переменные, в секции объявлений:

ИМЯ_ИСКЛЮЧЕНИЯ EXCEPTION;

EKOLVOR EXCEPTION.

Чтобы возбудить исключение определенного типа используется команда RAISE:

RAISE ИМЯ_ИСКЛЮЧЕНИЯ;

IF KOLVOR <= 0 THEN RAISE EKOLVOR; END IF.

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

EXCEPTION WHEN EKOLVOR THEN

DBMS_OUTPUT.PUT_LINE(‘Количество товара должно быть больше нуля!’).

Рассмотрим пример работы с пользовательским исключением:

create or replace procedure GET_DEPARTMENT_SALARY(ADEPT_ID IN INTEGER, SALARY OUT NUMBER) as

I INTEGER;

EDEPARTMENT_NOT_FOUND EXCEPTION;

begin

SELECT COUNT(*) INTO I FROM EMPLOYEES WHERE DEPARTMENT_ID = ADEPT_ID;

if I <= 0 then

raise EDEPARTMENT_NOT_FOUND;

else

SELECT SUM(SALARY) INTO SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = ADEPT_ID;

END IF;

EXCEPTION

WHEN EDEPARTMENT_NOT_FOUND THEN

DBMS_OUTPUT.PUT_LINE('Отдел не найден');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLErrM);

end.

Хранимая функция – это подпрограмма на языке PL/SQL, которая может быть вызвана по своему имени. Хранимые функции очень похожи на хранимые процедуры, за исключением того, что хранимая функция всегда возвращает одно значение в среду, из которой функция была вызвана. В отличие от хранимых процедур хранимые функции могут использоваться в SQL-запросах!

Для создания хранимых функций используется команда CREATE FUNCTION:

CREATE OR REPLACE FUNCTION НАЗВ_ФУНКЦИИ (ВХОД.ПАРАМЕТРЫ) RETURN ТИП_ДАННЫХ AS

BEGIN

Операторы;

Операторы;

RETURN ЗНАЧЕНИЕ;

END.

Возвращаемое функцией значение определяется с помощью команды RETURN. После этой команды выполнение функции заканчивается! Например, при выполнении функции ниже строка “TEST2” никогда не будет выведена на экран.

create or replace function sum1000(a in integer) return integer

as

begin

DBMS_output.put_line('test1');

return a + 1000;

DBMS_output.put_line('test2');

end.

После создания функции ее можно использовать в подпрограммах PL/SQL и SQL-запросах. Например:

SELECT SUM1000(MAX_SALARY) FROM JOBS;

SELECT * FROM JOBS WHERE SUM1000(MAX_SALARY) > 2000.

Для удаления функции используется команда DROP FUNCTION:

DROP FUNCTION SUM1000.

Функции, как и хранимые процедуры, могут иметь входные параметры. Рассмотрим пример создания и вызова такой функции:

create or replace function GetDEPTAvarageSalary(ADEPT_ID IN INTEGER) RETURN NUMBER AS

cnt integer;

s number;

begin

select count(*) into cnt from employees Where department_id = ADEPT_ID;

Select sum(salary) into s from employees where department_id = ADEPT_ID;

if cnt > 0 then

return s/ cnt;

else

return null; end if;

end;

 

select department_name, GetDEPTAvarageSalary(department_id) from departments order by 2.

 

Задания для самостоятельной работы:

1. Создайте таблицу-справочник цветов. Она содержит два поля: ID (первичный ключ) и наименование цвета (символьное, уникальное). Создайте последовательность и триггер для заполнения первичного ключа при вставке новой записи. Добавьте в таблицу три записи.

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

3. Создайте хранимую процедуру, которая бы добавляла запись в таблицу предметов. На входе хранимой процедуры: наименование предмета, ключ записи цвета, размер, стоимость и количество. Для заполнения первичного ключа используйте созданную в п.2 последовательность. Обработайте исключение OTHERS таким образом, чтобы, если добавляется предмет с ключом несуществующего цвета, то выводилась бы соответствующая информация на экран. Добавьте в таблицу пять записей с помощью хранимой процедуры. Вызовите процедуру с такими значениями параметров, чтобы вызвать исключение.

4. Создайте хранимую процедуру, которая бы выводила с помощью цикла на экран цвета из справочника. Для каждого цвета рассчитывается стоимость товаров этого цвета из таблицы предметов и выводится второй колонкой. Если стоимость товаров цвета равна нулю, то возбуждается пользовательское исключение с текстом «Неиспользуемый цвет -» + название цвета. Выполните процедуру. Добейтесь того, чтобы вызывалось исключение при обработке процедуры.

5. Создайте хранимую функцию, которая бы считала общую стоимость предмета. На входе функции имеем два параметра: стоимость и количество, а на выходе — их произведение. Напишите запрос SELECT, который бы выводил на экран наименование предмета и его общую стоимость (с помощью функции). Отсортируйте НД по второй колонке по возрастанию.

6. Удалите хранимые процедуры и функции, последовательности и таблицы.