Основы проектирования реляционных баз данных

         

Определение хранимых процедур и функций в 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. Типы параметров процедур и функций

INOUTIN 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;



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


Содержание раздела