Функции и процедуры (подпрограммы) представляют собой оформленные специальным образом именованные блоки PL/SQL, которые могут быть вызваны для выполнения и которым могут быть переданы параметры. Как правило, процедуры и функции реализуют определенное законченное действие над некоторым объектом базы данных.
Типы процедур и функций. Существуют два вида процедур и функций: локальные и хранимые. Локальные процедуры и функции могут использоваться только в тех блоках, где они определены. Хранимые процедуры и функции компилируются и хранятся в базе данных в скомпилированном виде. При необходимости они могут быть вызваны для выполнения анонимными и именованными блоками PL/SQL, процедурами и функциями обоих видов, триггерами, а также из интерактивной среды SQL* PLUS. Помимо этого хранимая функция может быть вызвана и в операторе SQL.
Создание хранимых процедур и функций.Для создания хранимой процедуры используется следующий общий синтаксис:
CREATE [OR REPLACE] PROCEDURE имя_процедуры
[(параметр1 [, параметр2,…..])] IS
[раздел локальных объявлений]
BEGIN
исполняемый раздел
[ EXCEPTION
раздел обработки исключений]
END [имя процедуры];
Для создания хранимой функции используется следующий общий синтаксис:
CREATE [OR REPLACE] FUNCTION имя_функции
[(параметр1[,параметр2,…]] RETURN тип_данных IS
[раздел локальных объявлений]
BEGIN
исполняемый раздел
[ EXCEPTION
раздел обработки исключений]
END [имя функции];
Хранимые процедуры и функции, вызываемые блоками PL/SQL, процедурами и функциями, триггерами, вызываются так же, как и локальные, заданием имени функции или процедуры с указанием списка фактических параметров. Если вызывается функция, то она должна быть частью выражения; если вызывается процедура, то она вызывается как отдельный оператор.
Для вызова из SQL* PLUS хранимой процедуры используется следующая форма записи:
Поскольку функция из интерактивного редактора не может быть вызвана непосредственно, для ее вызова необходимо использовать блок PL/SQL, анонимный или именованный.
Параметры процедур и функций (подпрограмм).Для передачи информации в подпрограмму используются параметры. Переменные или выражения, перечисленные в списке параметров в спецификации подпрограммы, называются формальными параметрами, а перечисленные в списке параметров при вызове подпрограммы называются фактическими аргументами. При вызове подпрограммы фактические аргументы вычисляются и результирующие значения присваиваются формальным параметрам, причем производятся необходимые преобразования типов, поэтому формальные параметры и фактические аргументы должны иметь совместимые типы.
Список параметров представляет собой перечисление этих параметров через запятую. Каждый формальный параметр может быть описан следующим синтаксисом:
Имя_параметра [вид] тип [{:= ï DEFAULT } значение];
Параметрвидопределяет режим передачи параметра. Имеются три режима передачи параметров: IN (по умолчанию), OUT и IN OUT. Они используются для обозначения соответствия входных, выходных и модифицируемых параметров. Желательно не использовать режимы OUT и IN OUT при написании функций, чтобы избежать побочных эффектов.
Фактический аргумент, указываемый на месте IN-параметра, должен быть константой, литералом, проинициализированной переменной либо выражением, и в отличие от OUT- и IN OUT-параметров, IN-параметр может иметь значение по умолчанию. Если параметр передается с вариантом IN, то в подпрограмме ему нельзя присваивать значение.
На месте OUT- или IN OUT-параметра может быть указана только переменная. Как и переменные, OUT-параметры инициализируются NULL-значением, и тип OUT-параметра не может быть подтипом, определенным как NOT NULL. В противном случае генерируется исключение VALUE_ERROR.
Если при выполнении процедуры или функции возникают исключительные ситуации, то управление передается в вызывающий блок. Когда осуществляется нормальный выход из подпрограммы, то фактическим OUT- и IN OUT- аргументам присваиваются значения, а если возникают необработанные исключения, то значения не присваиваются.
Параметртипопределяет допустимый тип данных для параметра. В качестве типа параметра могут использоваться практически все основные типы данных языка. Однако если используются типы CHAR, VARCHAR2 или NUMBER, то нельзя указывать размерность для этих типов данных, а для типа NUMBER – точность и масштаб.
Порядок задания параметров.При вызове подпрограмм можно записывать список фактических аргументов, используя либо позиционную, либо именованную, либо смешанную нотации, либо используя передачу параметров по умолчанию:
1) позиционная нотация – это передача списка параметров простым перечислением, причем типы, количество и порядок следования параметров должны соответствовать объявленным раньше;
2) в именованной нотации стрелка => используется как оператор ассоциации, который связывает формальный параметр слева от стрелки с фактическим аргументом справа от стрелки. При именованной нотации параметры могут указываться в любом порядке;
3) нотации могут смешиваться (смешанная нотация), но в этом случае позиционное указание параметров должно предшествовать именованному;
4) существует возможность передачи параметров по умолчанию. При этом формальным параметрам должны быть присвоены значения либо оператором присваивания, либо через ключевое слово DEFAULT, и они в списке фактических параметров должны быть записаны последними.
Подпрограммы и зависимости. Перекомпиляция подпрограмм.Хранимые функции и процедуры хранятся в скомпилированном виде в базе данных. При этом, как правило, их исполнение затрагивает некоторые объекты базы данных. Для обеспечения достоверности работы таких процедур или функций система постоянно отслеживает для каждой процедуры или функции состояние объектов, с которыми она связана. Если какой-то из связанных с ней объектов подвергается модификации с помощью оператора DDL, то процедура или функция объявляется системой недействительной или недостоверной (NO VALID). В этом случае процедуру или функцию, объявленную недостоверной, надо обязательно перекомпилировать.
Для того чтобы перекомпилировать хранимую процедуру используется команда:
ALTER PROCEDURE имя_процедуры COMPILE;
Команда ALTER FUNCTION перекомпилирует хранимую функцию:
ALTER FUNCTION имя_функции COMPILE;
Удаление подпрограмм из базы данных осуществляется следующей командой:
DROP {PROCEDURE ï FUNCTION} имя_подпрограммы;
Получение информации о хранимых процедурах и функциях.Процедуры и функции хранятся в скомпилированном виде в базе данных, поэтому информацию о них можно получить из представления словаря данных USER_OBJECTS, например, следующей командой:
SELECT * FROM USER_OBJECTS;
Примеры.
1.Создать хранимую процедуру, увеличивающую стоимость указанной книги в таблице KNIGA на 10 %. Параметр: код книги.
Вариант 1.
CREATE OR REPLACE PROCEDURE UVEL(КОД_КНИГИ KNIGA.ЦЕНА%TYPE) AS
Q NUMBER(1) := 0;
BEGIN
SELECT COUNT(*) INTO Q FROM KNIGA
WHERE KNIGA.КОД_КНИГИ = UVEL.КОД_КНИГИ;
IF Q<>0 THEN
UPDATE KNIGA SET ЦЕНА = ЦЕНА + ЦЕНА*0.01
WHERE KNIGA.КОД_КНИГИ = UVEL.КОД_КНИГИ;
ELSE
INSERT INTO KNIGA (КОД_КНИГИ) VALUES
(UVEL.КОД_КНИГИ);
END IF;
END UVEL;
Выполнение процедуры (вариант 1):
EXECUTE UVEL(1);
Вариант 2.
CREATE OR REPLACE PROCEDURE UVEL (КОД_КНИГИ NUMBER) AS
BEGIN
UPDATE KNIGA SET ЦЕНА = ЦЕНА*1.5
WHERE KNIGA.КОД_КНИГИ = UVEL.КОД_КНИГИ;
IF SQL%NOTFOUND THEN
INSERT INTO KNIGA (КОД_КНИГИ) VALUES
(UVEL.КОД_КНИГИ);
END IF;
END UVEL;
Выполнение процедуры (вариант 2):
EXECUTE UVEL (1);
2.Создать хранимую процедуру, которая при поступлении книг к продавцу либо добавляет указанное количество к количеству уже имеющихся у продавца таких же книг, обновляя соответствующую запись в таблице KNIGA_POSTAVKA, либо вставляет новую запись в таблицу KNIGA_POSTAVKA, если у продавца книг такого наименования нет. Параметры: код книги, количество единиц, фамилия продавца.
Создание процедуры:
CREATE OR REPLACE PROCEDURE DOBAV (КОД_КНИГИ NUMBER, КОЛ_ЕДИНИЦ NUMBER, ПРОДАВЕЦ VARCHAR2) IS
Q NUMBER(5) := 0;
BEGIN
SELECT COUNT(*) INTO Q FROM KNIGA_POSTAVKA
WHERE
KNIGA_POSTAVKA.КОД_КНИГИ = DOBAV.КОД_КНИГИ
AND KNIGA_POSTAVKA.ПРОДАВЕЦ = DOBAV.ПРОДАВЕЦ;
IF Q <> 0 THEN
UPDATE KNIGA_POSTAVKA SET
КОЛ_ЕДИНИЦ = КОЛ_ЕДИНИЦ + DOBAV.КОЛ_ЕДИНИЦ
WHERE
KNIGA_POSTAVKA.КОД_КНИГИ = DOBAV.КОД_КНИГИ
AND KNIGA_POSTAVKA.ПРОДАВЕЦ = DOBAV.ПРОДАВЕЦ;
ELSE
SELECT MAX(КОД_ОПЕРАЦИИ) INTO Q FROM
KNIGA_POSTAVKA;
Q := Q + 1;
INSERT INTO KNIGA_POSTAVKA VALUES (Q,
DOBAV.КОД_КНИГИ, DOBAV.ПРОДАВЕЦ,
DOBAV.КОЛ_ЕДИНИЦ, SYSDATE);
END IF;
END DOBAV;
Выполнение процедуры:
EXECUTE DOBAV(1, 10, 'Иванов И.И.');
EXECUTE DOBAV(7, 10, 'Иванов И.И.');
При первом вызове процедуры осуществляется добавление 10 книг к тем, что уже имеются у указанного продавца. При втором вызове в таблицу KNIGA_POSTAVKA вставляется новая запись.
3.Создать хранимую функцию, которая проверяет, входит ли для указанного продавца общее количество имеющихся у него книг в определенный диапазон, и если нет, то выдает соответствующее сообщение. Параметр: фамилия продавца. Минимальное и максимальное значения для проверки устанавливаются в теле функции с помощью переменных MIN_KOL и MAX_KOL.
Создание функции:
CREATE OR REPLACE FUNCTION PROV (ПРОДАВЕЦ VARCHAR2)
RETURN BOOLEAN IS
MIN_KOL NUMBER(5) := 10;
MAX_KOL NUMBER(5) := 100;
Q NUMBER(5);
BEGIN
SELECT SUM(КОЛ_ЕДИНИЦ) INTO Q FROM
KNIGA_POSTAVKA WHERE
KNIGA_POSTAVKA.ПРОДАВЕЦ = PROV.ПРОДАВЕЦ;
RETURN (Q > MIN_KOL AND Q < MAX_KOL);
END PROV;
Вызов функции PROV осуществляется с помощью тестирующей программы, приведенной ниже.
DECLARE
DOBAV_ПРОДАВЕЦ VARCHAR2(20);
DOBAV_КОЛ_ЕДИНИЦ NUMBER(5);
DOBAV_КОД_КНИГИ NUMBER(5);
BEGIN
DOBAV_ПРОДАВЕЦ := 'Иванов';
DOBAV_КОЛИЕСТВО := 20;
DOBAV_КОД_КНИГИ := 5;
IF PROV(DOBAV_ПРОДАВЕЦ) THEN
UPDATE KNIGA_POSTAVKA SET
КОЛ_ЕДИНИЦ = КОЛ_ЕДИНИЦ + DOBAV_КОЛ_ЕДИНИЦ
WHERE
KNIGA_POSTAVKA.КОД_КНИГИ=DOBAV_КОД_КНИГИ
AND KNIGA_POSTAVKA.ПРОДАВЕЦ=DOBAV_ПРОДАВЕЦ;
END IF;
END;
Программа добавляет указанное количество (переменная DOBAV_КОЛ_ЕДИНИЦ) книг указанному продавцу (переменная DOBAV_ПРОДАВЕЦ), если общее количество имеющихся у него книг не превосходит 100.
