Создание хранимых процедур и функций
Хранимая процедура или функция есть объект реляционной базы данных, который является поименнованным набором операторов SQL и, в случае СУБД Oracle, набором операторов PL/SQL, который может быть скомпилирован и необязательно сохранен в базе данных. Если процедура сохраняется в базе данных, то она называется хранимой процедурой или функцией. Описание хранимых процедур и функций хранится в словаре данных реляционной базы данных.
Обычно хранимые процедуры используются для выполнения набора типовых действий, характерных для достижения целей создания базы данных, а хранимые функции - для вычисления значений.
Код хранимых процедур и функций хранится в базе данных. Поэтому использование хранимых процедур и функций для выполнения типовых операций, характерных для приложения базы данных, приводит к упрощению приложений за счет передачи обработки на сервер базы данных. Такой подход уменьшает накладные расходы за счет сокращения фазы синтаксического анализа и уменьшения времени передачи запроса на конкретное действие по вычислительной сети, что приводит в целом к снижению трафика сети. Обычно при этом улучшается производительность, но это необязательно для маломощных серверов баз данных.
Хранимые процедуры и функции, как объекты базы данных, создаются командой CREATE и уничтожаются командой DROP. Команда создания хранимой процедуры имеет следующий синтаксис:
CREATE [OR REPLACE] PROCEDURE [имя схемы].имя процедуры [имя [(параметр [, параметр, ...])] {IS|AS} программа на PL/SQL;
Ключевое слово OR REPLACE указывает на безусловное замещение старого текста процедуры. Если процедура с таким именем уже определена, но ключевое слово OR REPLACE не указано, то возвращается сообщение об ошибке и замещения старого текста не происходит.
При описании переменных хранимой процедуры ключевое слово DECLARE не используется. Блок описания переменных начинается сразу после ключевого слова IS или AS.
Пример.
PROCEDURE create_dept (new_dname CHAR, new_loc CHAR) IS BEGIN INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc); END create_dept;
Исполнение созданной процедуры может быть выполнено оператором EXEC PL/SQL, как показано ниже:
EXEC ;
Команда создания хранимой процедуры имеет следующий синтаксис:
CREATE [OR REPLACE] FUNCTION [имя схемы].имя функции [имя [(параметр [, параметр, ...])] RETURN тип данных {IS|AS} программа на PL/SQL;
Как можно увидеть, синтаксис команды создания хранимой функции похож на синтаксис команды создания хранимой процедуры, за исключением того, что в описании функции используется предложение RETURN. Описание типа данных для возвращаемого значения требуется обязательно.
Пример. Для нашей учебной базы данных создадим функцию, которая считает число сотрудников, поступивших в организацию за определенный период:
CREATE OR REPLACE FUNCTION emp_cnts (data1 IN date, data2 IN date) RETURN Integer IS I_count number:=0; BEGIN SELECT COUNT(*) INTO i_count FRON employee WHERE hiredate BETWEEN date1 AND date2; RETURN i_count; END;
Вызвать созданную хранимую функцию можно следующим образом:
EXEC DBMS_OUTPUT.PUT_LINE(emp_cnts('01-may-02', ''01-jul-02'));
Для уничтожения хранимой процедуры или функции в базе данных используется команда DROP в формате DROP [имя схемы].имя процедуры;
Или
DROP [имя схемы].имя функции;
