Опубликован: 02.08.2007 | Уровень: специалист | Доступ: платный
Лекция 12:

Разработка серверного кода

При возникновении исключительной ситуации в системе происходит автоматическая передача управления в блок EXCEPTION программы PL/SQL.

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

Пример

DECLARE
X1 T01_A1%TYPE;
X2 T01_A2%TYPE;
X3 T01_A3%TYPE;
CURSOR CUR1 IS SELECT * FRON T01;
BEGIN
WHILE CUR1%FOUND LOOP
FETCH CUR1 INTO X1,X2,X3;
DBMS_OUTPUT.PUT_LINE (X1||' '||X2||' '||X3);
END LOOP
EXCEPTION
WHEN INVALID_CURSOR THEN
DBMS_OUTPUT.PUT_LINE ('ОШИБКА. НЕ ОТКРЫТ КУРСОР1');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('НЕИЗВЕСТНАЯ ОШИБКА.');
END;
/

Для обработки ситуаций, не входящих в перечень стандартных, можно использовать специальный обработчик PL/SQL OTHERS или описать пользовательскую исключительную ситуацию и запрограммировать ее обработку. Ключевое слово OTHERS блока EXCEPTION определяет универсальный алгоритм обработки исключительных ситуаций, не входящий в список стандартных.

Исключительная ситуация, задаваемая пользователем, должна быть описана в блоке DECLARE, как Имя исключительной ситуации EXCEPTION. После обнаружения исключительной ситуации в программе PL/SQL она должна быть сгенерирована указанием ключевого слова и имени исключительной ситуации - RAISE Имя исключительной ситуации. В блоке EXCEPTION должен быть предусмотрен код для ее обработки.

Например,

DECLARE
…
User_ecx EXCEPTION;
…
BEGIN
IF x1 < 0
THEN RAISE User_exc;
END IF;
…
EXCEPTION
WHEN User_exc THEN
DBMS_OUTPUT.PUT_LINE (ПОЛЬЗОВАТЕЛЬСКАЯ ИСКЛЮЧИТЕЛЬНАЯ СИТУАЦИЯ.');
…

В этом разделе мы рассмотрели основные конструкции расширения SQL в Oracle PL/SQL. Для более глубокого изучения этого языка следует обратиться к списку литературы или документации.

Создание хранимых процедур и функций

Определение хранимых процедур и функций в PL/SQL

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

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

PROCEDURE имя [(параметр [, параметр, ...])] IS
   [объявление локальных переменных, пользовательских типов данных, 
   пользовательских исключительных ситуаций, локальных подпрограмм и функций]
BEGIN
	Исполняемый код
[EXCEPTION
	обработчики исключительных ситуаций]
END [имя];

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

Имя параметра [IN | OUT | IN OUT] тип данных [{:= | DEFAULT} выражение]

В определении параметров нельзя использовать ограничение NOT NULL, а в определении типа данных нельзя использовать никакие ограничения. Для каждого параметра должен быть указан его тип (parameter mode) - IN, OUT или IN OUT. Указание типа IN означает, что значение параметра определяется при обращении к процедуре и не изменяется процедурой. Попытка изменить такой параметр в теле процедуры приведет к возникновению ошибки. Указание типа OUT предполагает изменение значения параметра в процессе выполнения процедуры, т.е. это возвращаемый параметр. Указание типа IN OUT говорит о том, что при вызове процедуры такому параметру должно быть присвоено значение, которое может быть изменено в теле процедуры. Типом по умолчанию считается IN. Ниже в таблице 12.2 суммирована информация о типах параметров.

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

Определение процедуры начинается с ключевого слова PROCEDURE и заканчивается именем процедуры или списком параметров. Тело процедуры начинается с ключевого слова IS и заканчивается ключевым словом END. Тело процедуры состоит из трех частей, которые отвечают блокам программы PL/SQL.

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

PROCEDURE raise_salary (empid INTEGER, increase REAL) IS
	current_salary REAL;
	salary_missing EXCEPTION;
BEGIN
    SELECT sal INTO current_salary FROM employee WHERE 
	empno = emp_id;
    IF current_salary IS NULL THEN
      RAISE salary_missing;
   ELSE
      UPDATE employee SET sal = sal + increase
         WHERE empno = emp_id;
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      INSERT INTO emp_audit VALUES (emp_id, 'Нет сотрудника 
	с таким номером');
   WHEN salary_missing THEN
      INSERT INTO emp_audit VALUES (emp_id, Зарплата не назначена');
END raise_salary;

При вызове процедура принимает номер сотрудника и величину прибавки к зарплате в качестве параметров. Номер сотрудника используется далее для выборки значения текущей зарплаты из таблицы EMPLOYEE в локальную переменную current_salary. Если номер сотрудника не найден в базе данных или значение зарплаты не установлено, то возникают исключительные ситуации, стандартная и определенная пользователем соответственно. В противном случае зарплата обновляется.

Процедура вызывается как команда PL/SQL

raise_salary (emp_num, amount);

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

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

PROCEDURE emp_salary (sName INTEGER, sal_p OUT REAL) IS
	current_salary REAL;
	salary_missing EXCEPTION;
BEGIN
    SELECT sal INTO current_salary FROM employee WHERE ENAME=:sName';
    IF current_salary IS NULL THEN
      RAISE salary_missing;
    ELSE
       sal_p = current_salary
    END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      INSERT INTO emp_audit VALUES (emp_id, 'Нет сотрудника 
      с такой фамилией');
   WHEN salary_missing THEN
      INSERT INTO emp_audit VALUES (emp_id, Зарплата не назначена');
END raise_salary;

Описание функции имеет следующий синтаксис:

FUNCTION имя [(параметр [, параметр, ...])] RETURN тип данных IS
   [объявление локальных переменных, пользовательских типов данных, 
   пользовательских исключительных ситуаций, локальных подпрограмм и функций]
BEGIN
   Исполняемый код
[EXCEPTION
   обработчики исключительных ситуаций]
END [имя];

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

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

FUNCTION sal_ok (salary REAL, title REAL) RETURN BOOLEAN IS
	min_sal REAL;
	max_sal REAL;
BEGIN
   SELECT losal, hisal INTO min_sal, max_sal 
      FROM salaryes WHERE job = title;
   RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok;

При вызове функции она принимает зарплату сотрудника и его должность в качестве входных параметров. Она выбирает установленные нижнюю и верхнюю границы зарплаты из таблицы базы данных salaryes для заданной должности. В предложении RETURN идентификатор функции sal_ok устанавливается к логическому значению в результате проверки.

Функция вызывается как часть выражения PL/SQL, где идентификатор функции действует как переменная, значение которой зависит от параметров, например

IF sal_ok(new_sal, new_title) THEN ...
Александра Каева
Александра Каева
Михаил Забелкин
Михаил Забелкин
Виктория Бычкова
Виктория Бычкова
Россия, Ленинград, Ленинградская лесотехническая академия, 1988
Ivan Klepcsov
Ivan Klepcsov
Россия