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

При возникновении исключительной ситуации в системе происходит автоматическая передача управления в блок 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 суммирована информация о типах параметров.

Определение процедуры начинается с ключевого слова 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 ...