ГЛАВА 6
----------------------------------------------------------------
ПОДПРОГРАММЫ
Цивилизация развивается за счет расширения числа важных
операций, которые можно выполнять, не думая о них.
Альфред Норт Уайтхед
Эта глава показывает, как использовать подпрограммы, которые
позволяют вам именовать и инкапсулировать группы предложений.
Тем самым подпрограммы способствуют разработке приложений,
изолируя операции. Они выступают как строительные блоки, с
помощью которых вы можете конструировать надежные, модульные,
сопровождаемые приложения.
Подпрограммы 6-1
----------------
Что такое подпрограммы?
Подпрограмма - это поименованный блок PL/SQL, который принимает
параметры и может быть вызван. PL/SQL имеет два типа
подпрограмм, называемых ПРОЦЕДУРАМИ и ФУНКЦИЯМИ. Обычно
процедуру вызывают для того, чтобы выполнить некоторое действие,
а функцию - для того, чтобы вычислить некоторое значение.
Как и непоименованные (АНОНИМНЫЕ) блоки PL/SQL, подпрограммы
имеют декларативную часть, исполняемую часть и необязательную
часть обработки исключений. Декларативная часть содержит
объявления типов, курсоров, констант, переменных, исключений и
вложенных подпрограмм. Все эти объекты локальны, и перестают
существовать после выхода из подпрограммы. Исполняемая часть
содержит предложения, которые присваивают значения, управляют
выполнением и манипулируют данными ORACLE. Часть обработки
исключений содержит обработчики, которые имеют дело с
исключениями, возбуждаемыми при исполнении.
Рассмотрим следующую процедуру с именем debit_account, которая
дебитует банковский счет:
PROCEDURE debit_account (acct_id INTEREG, amount REAL) IS
old_balance REAL;
new_balance REAL;
overdrawn EXCEPTION;
BEGIN
SELECT bal INTO old_balance FROM accts
WHERE acctno = acct_id;
new_balance := old_balance - amount;
IF new_balance < 0 THEN
RAISE overdrawn;
ELSE
UPDATE accts SET bal = new_balance
WHERE acctno = acct_id;
END IF;
EXCEPTION
WHEN overdrawn THEN
...
END debit_account;
При своем ВЫЗОВЕ эта процедура принимает номер банковского счета
и сумму, которую требуется снять. Она использует номер счета,
чтобы выбрать счет из таблицы базы данных accts, а затем,
используя заданную сумму, вычисляет новый баланс счета. Если
этот баланс отрицателен, возбуждается исключение; в противном
случае банковский счет обновляется.
6-2 Руководство пользователя и справочник по PL/SQL
Где допускаются подпрограммы?
-----------------------------
Подпрограммы можно определять в любом инструменте ORACLE,
который поддерживает PL/SQL. Их можно объявлять в блоках
PL/SQL, процедурах, функциях и пакетах. Однако подпрограммы
должны объявляться в конце декларативной секции, после всех
других программных объектов. Например, следующее объявление
процедуры не на месте:
DECLARE
PROCEDURE award_bonus (...) IS -- не на месте
BEGIN ... END;
rating NUMBER;
...
Обычно инструменты ORACLE, такие как SQL*Forms, которые
инкорпорируют в себя процессор PL/SQL, способны сохранять
подпрограммы для последующего, строго локального, исполнения.
Однако для того, чтобы быть общедоступными для всех инструментов
ORACLE, такие подпрограммы должны быть сохранены в базе данных.
Для дополнительной информации смотрите раздел "Хранимые
подпрограммы" ниже в этой главе.
----------------
Преимущества подпрограмм
Подпрограммы предоставляют РАСШИРЯЕМОСТЬ; иными словами, они
позволяют вам приспосабливать средства PL/SQL для ваших
потребностей. Например, если вам нужна процедура, которая
создавала бы новые отделы, вы легко можете написать ее:
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;
Подпрограммы предоставляют МОДУЛЬНОСТЬ; иными словами, они
позволяют вам разбивать ваши программы на управляемые, хорошо
определенные логические модули. Это поддерживает методы
проектирования сверху вниз и пошагового уточнения, характерные
для структурного подхода к решению проблем,
Помимо этого, подпрограммы способствуют ИСПОЛЬЗУЕМОСТИ и
СОПРОВОЖДАЕМОСТИ. Однажды проверенную подпрограмму можно с
уверенностью использовать в любом количестве приложений. Более
того, лишь одна подпрограмма затрагивается, если изменяется ее
определение. Это упрощает сопровождение и развитие.
Наконец, подпрограммы способствуют АБСТРАКЦИИ, умственному
отделению от частностей. Чтобы использовать подпрограммы, вам
нужно знать, ЧТО они делают, а не КАК они это делают. Поэтому
вы можете проектировать приложения сверху вниз, не заботясь о
деталях реализации. С помощью фиктивных подпрограмм (затычек)
вы можете отложить определение процедур и функций до тех пор,
пока не протестируете и не отладите главную программу.
Подпрограммы 6-3
----------------
Процедуры
Процедура - это подпрограмма, которая выполняет специфическое
действие. Вы пишете процедуры, используя синтаксис
PROCEDURE имя [ (параметр [, параметр, ...]) ] IS
[локальные объявления]
BEGIN
исполняемые предложения
[EXCEPTION
обработчики исключений]
END [имя];
где каждый "параметр" имеет следующий синтаксис:
имя_перем [IN | OUT | IN OUT] тип_данных [{:= | DEFAULT} знач]
В отличие от спецификатора типа данных в объявлении переменной,
спецификатор типа данных для параметра не может иметь
ограничений. Например, следующее объявление name незаконно:
PROCEDURE ... (name CHAR(20)) IS -- незаконно; должно быть CHAR
BEGIN ... END;
Процедура имеет две части: спецификацию и тело. Спецификация
процедуры начинается с ключевого слова PROCEDURE и заканчивается
именем процедуры или списком параметров. Объявления параметров
необязательны. Если процедура не принимает параметров, скобки
также не кодируются.
Тело процедуры начинается с ключевого слова IS и заканчивается
ключевым словом END, за которым может следовать имя процедуры.
Тело процедуры состоит из трех частей: декларативной части,
исполняемой части и необязательной части обработки исключений.
Декларативная часть содержит локальные объявления, которые
помещаются между ключевыми словами IS и BEGIN. Ключевое слово
DECLARE, которое начинает декларативную часть в анонимном блоке
PL/SQL, здесь не используется. Исполняемая часть содержит
предложения, которые помещаются между ключевыми словами BEGIN и
EXCEPTION (или END). В исполняемой части процедуры должно быть
хотя бы одно предложение. Часть обработки исключений содержит
обработчики исключений, которые помещаются между ключевыми
словами EXCEPTION и END.
6-4 Руководство пользователя и справочник по PL/SQL
Рассмотрим процедуру raise_salary, которая увеличивает жалованье
сотрудника:
PROCEDURE raise_salary (emp_id NUMBER, increase REAL) IS
current_salary REAL;
salary_missing EXCEPTION;
BEGIN
SELECT sal INTO current_salary FROM emp
WHERE empno = emp_id;
IF current_salary IS NULL THEN
RAISE salary_missing;
ELSE
UPDATE emp SET sal = sal + increase
WHERE empno = emp_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO emp_audit VALUES (emp_id, 'No such number');
WHEN salary_missing THEN
INSERT INTO emp_audit VALUES (emp_id, 'Salary is null');
END raise_salary;
При своем вызове эта процедура принимает номер сотрудника и
сумму, на которую увеличивается его жалованье. Она использует
номер, чтобы выбрать текущее жалованье из таблицы базы данных
emp. Если номер сотрудника не найден или текущая зарплата не
определена, возбуждается исключение. В противном случае
жалованье обновляется.
Процедура вызывается как предложение PL/SQL. Например,
процедура raise_salary может быть вызвана так:
raise_salary(emp_num, amount);
Подпрограммы 6-5
----------------
Функции
Функция - это подпрограмма, которая вычисляет значение. Функции
структурируются так же, как и процедуры, с той разницей, что
функции содержат фразу RETURN. Вы пишете функции, используя
синтаксис
FUNCTION имя [ (аргумент [, аргумент, ...]) ] RETURN тип_данн IS
[локальные объявления]
BEGIN
исполняемые предложения
[EXCEPTION
обработчики исключений]
END [имя];
где каждый "аргумент" имеет следующий синтаксис:
имя_перем [IN | OUT | IN OUT] тип_данных [{:= | DEFAULT} знач]
В отличие от спецификатора типа данных в объявлении переменной,
спецификатор типа данных для параметра не может иметь
ограничений.
Как и процедура, функция имеет две части: спецификацию и тело.
Спецификация функции начинается с ключевого слова FUNCTION и
заканчивается фразой RETURN, которая специфицирует тип
результирующего значения. Объявления аргументов необязательны.
Если функция не принимает аргументов, скобки также не
кодируются.
Тело функции начинается с ключевого слова IS и заканчивается
ключевым словом END, за которым может следовать имя функции.
Тело функции состоит из трех частей: декларативной части,
исполняемой части и необязательной части обработки исключений.
Декларативная часть содержит локальные объявления, которые
помещаются между ключевыми словами IS и BEGIN. Ключевое слово
DECLARE, которое начинает декларативную часть в анонимном блоке
PL/SQL, здесь не используется. Исполняемая часть содержит
предложения, которые помещаются между ключевыми словами BEGIN и
EXCEPTION (или END). В исполняемой части процедуры должно
встретиться хотя бы одно предложение RETURN. Часть обработки
исключений содержит обработчики исключений, которые помещаются
между ключевыми словами EXCEPTION и END.
6-6 Руководство пользователя и справочник по PL/SQL
Рассмотрим процедуру sal_ok, которая определяет, не выходит ли
жалованье для данной должности за допустимые границы:
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 sals
WHERE job = title;
RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok;
При своем вызове эта функция принимает жалованье сотрудника и
его должность. Она использует должность, чтобы выбрать границы
оклада из таблицы базы данных sals. Если жалованье выходит за
эти границы, sal_ok возвращает FALSE; в противном случае sal_ok
возвращает TRUE.
Функция вызывается как часть выражения. Например, функция
sal_ok может быть вызвана так:
IF sal_ok(new_sal, new_title) THEN
...
END IF;
...
promotable := sal_ok(new_sal, new_title) AND (rating > 3);
В обоих случаях идентификатор функции является выражением,
которое заменяется своим значением.
Ограничение
Вызовы пользовательских функций могут появляться в процедурных
предложениях, но НЕ в предложениях SQL. Например, следующее
предложение INSERT незаконно:
DECLARE
empnum INTEGER;
...
FUNCTION bonus (emp_id INTEGER) RETURN REAL IS
BEGIN ... END bonus;
BEGIN
...
INSERT INTO payroll
VALUES (empnum, ..., bonus(empnum)); -- незаконно
END;
Подпрограммы 6-7
----------------
Предложение RETURN
Предложение RETURN немедленно завершает выполнение подпрограммы
и возвращает управление вызывающей программе. Выполнение
продолжается с предложения, следующего за вызовом подпрограммы.
Подпрограмма может содержать несколько предложений RETURN, ни
одно из которых не обязано быть последним лексическим
предложением в подпрограмме. Выполнение любого из них
немедленно завершает подпрограмму. Однако наличие в
подпрограмме нескольких точек выхода не является хорошей
практикой программирования.
В процедурах предложение RETURN не может содержать выражение.
Это предложение просто возвращает управление вызывающей
программе до достижения нормального конца процедуры.
Однако в функциях предложение RETURN ДОЛЖНО содержать выражение,
которое вычисляется при выполнении предложения RETURN.
Результирующее значение присваивается идентификатору функции.
Поэтому функция должна содержать хотя бы одно предложение
RETURN. В противном случае PL/SQL возбуждает предопределенное
исключение PROGRAM_ERROR во время выполнения. Заметьте, как
функция balance возвращает баланс заданного бухгалтерского
счета:
FUNCTION balance (acct_id NUMBER) RETURN REAL IS
acct_bal REAL;
BEGIN
SELECT bal INTO acct_bal FROM accts WHERE acctno = acct_id;
RETURN acct_bal;
END balance;
Не путайте предложение RETURN с фразой RETURN, которая
специфицирует тип данных результирующего значения в спецификации
функции.
6-8 Руководство пользователя и справочник по PL/SQL
----------------
Упреждающие объявления
PL/SQL требует, чтобы вы объявляли идентификатор до его
использования. Поэтому вы должны объявить подпрограмму, прежде
чем вызывать ее. Например, следующее объявление процедуры
award_bonus незаконно, потому что award_bonus вызывает процедуру
calc_rating, которая еще не объявлена к моменту ее вызова:
DECLARE
PROCEDURE award_bonus (...) IS
BEGIN
calc_rating(...); -- необъявленный идентификатор
...
END;
PROCEDURE calc_rating (...) IS
BEGIN
...
END;
...
В данном случае проблему легко разрешить, поменяв местами
объявления процедур calc_rating и award_bonus. Однако такой
простой способ выручает не всегда. Например, предположим, что
процедуры взаимно рекурсивны (вызывают друг друга), или что вы
хотите расположить их в алфавитном порядке.
PL/SQL разрешает эту проблему, предоставляя специальный вид
объявления подпрограммы - УПРЕЖДАЮЩЕЕ ОБЪЯВЛЕНИЕ. Вы можете
использовать упреждающие объявления, чтобы
* определять подпрограммы в логическом или алфавитном
порядке
* определять взаимно рекурсивные подпрограммы (см. раздел
"Рекурсия" ниже в этой главе)
* группировать подпрограммы в пакет
Упреждающее объявление представляет собой спецификацию
подпрограммы, заканчивающуюся точкой с запятой. В следующем
примере упреждающее объявление указывает PL/SQL, что тело
процедуры calc_rating встретится позже в этом блоке.
Подпрограммы 6-9
DECLARE
PROCEDURE calc_rating (...); -- упреждающее объявление
/* Определить подпрограммы в алфавитном порядке. */
PROCEDURE award_bonus (...) IS
BEGIN
calc_rating(...);
...
END;
PROCEDURE calc_rating (...) IS
BEGIN
...
END;
...
Несмотря на то, что упреждающее объявление содержит список
формальных параметров, этот список должен также появиться в теле
подпрограммы. Вы можете поместить тело подпрограммы в любом
месте после упреждающего объявления, но внутри того же блока,
подпрограммы или пакета.
Пакетированные подпрограммы
---------------------------
Упреждающие объявления также позволяют вам группировать
логически связанные подпрограммы в пакет. Спецификации
подпрограмм идут в спецификацию пакета, а тела подпрограмм - в
тело пакета, где они невидимы приложениям. Таким образом,
благодаря пакетам вы скрываете детали реализации. Пример:
PACKAGE emp_actions IS -- спецификация пакета
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;
PACKAGE BODY emp_actions IS -- тело пакета
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS
BEGIN
INSERT INTO emp VALUES (empno, ename, ...);
END hire_employee;
PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;
Вы можете определять в теле пакета подпрограммы, не объявляя их
спецификаций в спецификации пакета. Однако такие подпрограммы
можно вызывать только изнутри пакета. Для дополнительной
информации обратитесь к главе 7.
6-10 Руководство пользователя и справочник по PL/SQL
----------------
Фактические и формальные параметры
Подпрограммы принимают и передают информацию через ПАРАМЕТРЫ.
Переменные или выражения, которые специфицированы в списке
параметров в вызове подпрограммы, называются ФАКТИЧЕСКИМИ
параметрами. Например, следующий вызов процедуры передает два
фактических параметра, emp_num и amount:
raise_salary(emp_num, amount);
Как показывает следующий вызов процедуры, в некоторых случаях в
качестве фактических параметров можно использовать выражения:
raise_salary(emp_num, merit + cola);
Переменные, объявленные в спецификации подпрограммы и
используемые в теле подпрограммы, называются ФОРМАЛЬНЫМИ
параметрами. Например, следующая процедура объявляет два
формальных параметра с именами emp_id и increase:
PROCEDURE raise_salary (emp_id NUMBER, increase REAL) IS
current_salary REAL;
...
BEGIN
SELECT sal INTO current_salary FROM emp
WHERE empno = emp_id;
...
UPDATE emp SET sal = sal + increase WHERE empno = emp_id;
END raise_salary;
Хотя это и не обязательно, хорошая практика программирования
рекомендует использовать разные имена для фактических и
формальных параметров.
Когда вы вызываете процедуру raise_salary, фактические параметры
вычисляются, и их значения присваиваются соответствующим
формальным параметрам. При этом PL/SQL преобразует значение из
одного типа данных в другой, если необходимо. Например,
следующий вызов процедуры raise_salary законен:
raise_salary(emp_num, '2500');
Фактический параметр и соответствующий ему формальный параметр
должны иметь совместимые типы данных. Например, PL/SQL не может
преобразовать друг в друга типы данных DATE и REAL. Кроме того,
значение результата также должно быть совместимо с новым типом
данных. Следующий вызов процедуры возбудит предопределенное
исключение VALUE_ERROR, потому что PL/SQL не может преобразовать
второй фактический параметр в число:
raise_salary(emp_num, '$2500'); -- из-за знака доллара
Для дополнительной информации обратитесь к разделу
"Преобразования типов данных" в главе 2.
Подпрограммы 6-11
----------------
Позиционная и именная нотация
При вызове подпрограммы можно записывать фактические параметры,
используя позиционную или именную нотацию. Иными словами, вы
можете указывать соответствие между фактическими и формальными
параметрами через позиции этих параметров или через их имена.
Например, при объявлениях
DECLARE
acct INTEGER;
amt REAL;
PROCEDURE credit (acctno INTEGER, amount REAL) IS ...
...
вы можете вызвать процедуру credit четырьмя логически
эквивалентными способами:
BEGIN
credit(acct, amt); -- позиционная нотация
credit(amount => amt, acctno => acct); -- именная нотация
credit(acctno => acct, amount => amt); -- именная нотация
credit(acct, amount => amt); -- смешанная нотация
...
END;
Позиционная нотация
-------------------
Первый вызов процедуры использует позиционную нотацию.
Компилятор PL/SQL ассоциирует первый фактический параметр, acct,
с первым формальным параметром, acctno, а второй фактический
параметр, amt, - со вторым формальным параметром, amount.
Именная нотация
---------------
Второй вызов процедуры использует именную нотацию. Стрелка
ассоциирует формальный параметр слева от стрелки с фактическим
параметром справа от стрелки.
Третий вызов процедуры также использует именную нотацию и
показывает, что вы можете задавать пары параметров в любом
порядке. Поэтому вы не обязаны знать порядок, в котором
перечислены формальные параметры.
Смешанная нотация
-----------------
Четвертый вызов процедуры показывает, что вы можете смешивать
позиционную и именную нотации. В данном случае первый параметр
задан в позиционной, а второй - в именной нотации. Позиционная
нотация в этом варианте должна предшествовать именной. Обратное
не допускается. Например, следующий вызов процедуры незаконен:
credit(acctno => acct, amt); -- незаконно
6-12 Руководство пользователя и справочник по PL/SQL
----------------
Моды параметров
Вы используете моды параметров, чтобы определить поведение
формальных параметров подпрограммы. Все три возможные моды: IN
(умалчиваемая), OUT и IN OUT, могут использоваться в любой
процедуре. Что касается функций, то избегайте использования
моды OUT или IN OUT в функциях. Назначение функции - принять
нуль или более аргументов и возвратить единственное значение.
Возврат функцией нескольких результирующих значений является
плохой практикой программирования. Кроме того, функции должны
быть свободны от ПОБОЧНЫХ ЭФФЕКТОВ, то есть не должны изменять
значений переменных, не локальных для данной функции.
IN
--
Параметр с модой IN передает значение вызываемой подпрограмме.
Внутри подпрограммы такой параметр выступает как константа.
Поэтому ему нельзя присвоить значение. Например, следующее
предложение присваивания вызовет ошибку компиляции:
PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
minimum_purchase CONSTANT REAL := 10.0;
service_charge CONSTANT REAL := 0.50;
BEGIN
IF amount < minimum_purchase THEN
amount := amount + service_charge; -- незаконно
END IF;
...
Фактический параметр, соответствующий формальному параметру с
модой IN, может быть константой, литералом, инициализированной
переменной или выражением.
В отличие от параметров OUT и IN OUT, параметры IN могут
инициализироваться умалчиваемыми значениями. Подробнее об этом
см. в следующем разделе, "Умалчиваемые значения параметров".
OUT
---
Параметр с модой OUT позволяет возвращать значение вызывающей
программе. Внутри подпрограммы такой параметр выступает как
неинициализированная переменная. Поэтому его значение нельзя
присваивать другим переменным или переприсвоить самому себе.
Например, следующее предложение присваивания вызовет ошибку
компиляции:
PROCEDURE calc_bonus (emp_id INTEGER, bonus OUT REAL) IS
hire_date DATE;
BEGIN
SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp
WHERE empno = emp_id;
IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN
bonus := bonus + 500; -- синтаксическая ошибка
END IF;
...
Подпрограммы 6-13
Фактический параметр, соответствующий формальному параметру с
модой OUT, должен быть переменной; он не может быть константой
или выражением. Например, следующий вызов процедуры незаконен:
calc_bonus(7499, salary + commission); -- синтаксическая ошибка
PL/SQL проверяет на такие синтаксические ошибки во время
компиляции, не допуская возможного перекрытия констант и
выражений.
Формальный параметр OUT может (но не обязан) иметь значение в
момент вызова подпрограммы. Однако это значение теряется, когда
вы вызываете подпрограмму. Внутри подпрограммы формальный
параметр OUT нельзя использовать в выражении; единственная
операция, допустимая на таком параметре - это присваивание ему
значения.
Перед выходом из подпрограммы не забывайте явно присвоить
значения параметрам OUT. В противном случае значения
соответствующих фактических параметров будут не определены. При
успешном выходе из подпрограммы PL/SQL присваивает значения
фактическим параметрам. Однако, если вы выходите с
необработанным исключением, PL/SQL НЕ присваивает значений
фактическим параметрам.
IN OUT
------
Параметр IN OUT позволяет вам передавать в подпрограмму
начальные значения и возвращать обновленные значения вызывающей
программе. Внутри подпрограммы такой параметр выступает как
инициализированная переменная. Поэтому ему можно присвоить
значение, а его значение можно присваивать другим переменным.
Иными словами, параметр IN OUT можно рассматривать как обычную
переменную. Вы можете изменять его значение или обращаться к
этому значению любыми способами, как показывает следующий
пример:
PROCEDURE calc_bonus (emp_id INTEGER, bonus IN OUT REAL) IS
hire_date DATE;
bonus_missing EXCEPTION;
BEGIN
SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp
WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE bonus_missing;
END IF;
IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN
bonus := bonus + 500;
END IF;
...
EXCEPTION
WHEN bonus_missing THEN
...
END calc_bonus;
Фактический параметр, соответствующий формальному параметру IN
OUT, должен быть переменной; он не может быть константой или
выражением.
6-14 Руководство пользователя и справочник по PL/SQL
Табл.6-1 суммирует все, что вам нужно знать о модах параметров.
Табл.6-1
Моды параметров
---------------------T--------------------T--------------------¬
¦ IN ¦ OUT ¦ IN OUT ¦
+--------------------+--------------------+--------------------+
¦ по умолчанию ¦ задается явно ¦ задается явно ¦
+--------------------+--------------------+--------------------+
¦ передает значение ¦ возвращает значение¦ передает начальное ¦
¦ подпрограмме ¦ вызывающей програм-¦ значение подпро- ¦
¦ ¦ ме ¦ грамме; возвращает ¦
¦ ¦ ¦ обновленное значе- ¦
¦ ¦ ¦ ние вызывающей про-¦
¦ ¦ ¦ грамме ¦
+--------------------+--------------------+--------------------+
¦ формальный параметр¦ формальный параметр¦ формальный параметр¦
¦ выступает как кон- ¦ не может использо- ¦ выступает как ини- ¦
¦ станта ¦ ваться в выражени- ¦ циализированная пе-¦
¦ ¦ ях; ему должно быть¦ ременная ¦
¦ ¦ присвоено значение ¦ ¦
+--------------------+--------------------+--------------------+
¦ фактический пара- ¦ фактический пара- ¦ фактический пара- ¦
¦ метр может быть ¦ метр должен быть ¦ метр должен быть ¦
¦ константой, иници- ¦ переменной ¦ переменной ¦
¦ ализированной пере-¦ ¦ ¦
¦ менной, литералом ¦ ¦ ¦
¦ или выражением ¦ ¦ ¦
L--------------------+--------------------+---------------------
Подпрограммы 6-15
----------------
Умалчиваемые значения параметров
Как показывает следующий пример, вы можете инициализировать
параметры с модой IN умалчиваемыми значениями. Это позволяет
передавать подпрограмме различное число параметров, принимая или
перекрывая умалчиваемые значения по вашему желанию. Более того,
можно добавлять новые формальные параметры, не требуя каждый раз
изменять все существующие вызовы данной подпрограммы.
PROCEDURE create_dept
(new_dname CHAR DEFAULT 'TEMP',
new_loc CHAR DEFAULT 'TEMP') IS
BEGIN
INSERT INTO dept
VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);
END create_dept;
Если фактический параметр не передан, используется умалчиваемое
значение соответствующего формального параметра. Рассмотрим
следующие вызовы процедуры create_dept:
BEGIN
...
create_dept;
create_dept('MARKETING');
create_dept('MARKETING', 'NEW_YORK');
...
END;
Первый вызов не передает никаких фактических параметров, так что
используются оба умолчания. Второй вызов передает один
фактический параметр, так что умолчание используется только для
new_loc. Третий вызов передает оба параметра, так что
умалчиваемые значения не используются.
В большинстве случаев вы можете использовать позиционную нотацию
для перекрытия умалчиваемых значений формальных параметров.
Однако вы не можете пропустить формальный параметр, опустив для
него соответствующий фактический параметр. Например, следующий
вызов логически некорректен, так как он ассоциирует фактический
параметр 'NEW YORK' с формальным параметром new_dname, а не с
формальным параметром new_loc:
create_dept('NEW_YORK'); -- некорректно
Нельзя обойти эту проблему, указав запятую вместо отсутствующего
позиционного параметра. Например, следующий вызов незаконен:
create_dept(, 'NEW_YORK'); -- синтаксическая ошибка
В таких случаях вы должны использовать именную нотацию,
например:
create_dept(new_loc => 'NEW_YORK');
Не забывайте, что позиционную нотацию нельзя применять после
именной.
6-16 Руководство пользователя и справочник по PL/SQL
----------------
Проблема алиасов
Для того чтобы оптимизировать выполнение, PL/SQL может выбирать
различные методы передачи параметров (копирование или ссылка)
при вызове подпрограммы. ПРОБЛЕМА АЛИАСОВ (легко устраняемая)
возникает, когда один и тот же фактический параметр появляется в
вызове процедуры дважды. Если оба соответствующих формальных
параметра не определены с модой IN, результат не определен,
потому что он зависит от метода передачи параметров, избранного
компилятором. Покажем это на примере:
DECLARE
str CHAR(10);
PROCEDURE reverse (in_str CHAR, out_str OUT CHAR) IS
...
BEGIN
-- обратить порядок символов в строке
...
/* Каково значение in_str в этом месте, 'ABCD' или *
* 'DCBA', зависит от метода передачи параметров, *
* используемого компилятором PL/SQL. */
END reverse;
...
BEGIN
str := 'ABCD';
reverse(str, str); -- результат не определен
...
END;
Проблема алиасов возникает также, когда глобальная переменная
появляется как в вызове процедуры, так и внутри процедуры.
Рассмотрим следующий пример:
DECLARE
rent REAL;
PROCEDURE raise_rent (increase IN OUT REAL) IS
...
BEGIN
rent := rent + increase;
...
END raise_rent;
...
BEGIN
...
raise_rent(rent); -- не определено
END;
И здесь результат непредсказуем.
Подпрограммы 6-17
----------------
Перекрытие имен
PL/SQL позволяет вам ПЕРЕКРЫВАТЬ имена подпрограмм. Иными
словами, вы можете использовать одно и то же имя для нескольких
различных подпрограмм, если только их формальные параметры
различаются по количеству, порядку или семействам типов данных.
(Семейства типов данных приведены на рис.2-1 в главе 2.)
Предположим, вы хотите инициализировать первые n строк в двух
таблицах PL/SQL, которые были объявлены следующим образом:
DECLARE
TYPE DateTabTyp IS TABLE OF DATE
INDEX BY BINARY INTEGER;
TYPE RealTabTyp IS TABLE OF REAL
INDEX BY BINARY INTEGER;
hiredate_tab DateTabTyp;
sal_tab RealTabTyp;
...
Вы могли бы написать следующую процедуру для инициализации
таблицы PL/SQL hiredate_tab:
PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := SYSDATE;
END LOOP;
END initialize;
Далее, вы могли бы написать следующую процедуру для
инициализации таблицы PL/SQL sal_tab:
PROCEDURE initialize (tab OUT RealTabTyp, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := 0.0;
END LOOP;
END initialize;
Поскольку назначение этих двух процедур сходно, логично дать им
одно и то же имя.
6-18 Руководство пользователя и справочник по PL/SQL
Вы можете поместить две перекрывающихся процедуры initialize в
один и тот же блок, подпрограмму или пакет. PL/SQL определяет,
какая из этих процедур вызывается, анализируя их формальные
параметры и сопоставляя их с фактическими параметрами вызова.
Рассмотрим следующий пример. Если вы вызываете initialize с
параметром типа DateTabTyp, PL/SQL использует первую версию
процедуры initialize. Однако, если вы вызываете initialize с
параметром типа RealTabTyp, PL/SQL будет использовать вторую
версию процедуры initialize.
DECLARE
TYPE DateTabTyp IS TABLE OF DATE
INDEX BY BINARY INTEGER;
TYPE RealTabTyp IS TABLE OF REAL
INDEX BY BINARY INTEGER;
hiredate_tab DateTabTyp;
comm_tab RealTabTyp;
indx BINARY_INTEGER;
...
BEGIN
indx := 50;
initialize(hiredate_tab, indx); -- вызывает первую версию
initialize(comm_tab, indx); -- вызывает вторую версию
...
END;
Ограничения
-----------
Перекрывающиеся имена подпрограмм могут появляться только в
блоке, подпрограмме или пакете. Иными словами, нельзя
перекрывать имен независимых подпрограмм.
Нельзя перекрывать две подпрограммы, если их формальные
параметры различаются лишь именами или модами параметров.
Например, следующее перекрытие незаконно:
PROCEDURE reconcile (acctno IN INTEGER) IS
BEGIN
...
END reconcile;
PROCEDURE reconcile (acctno OUT INTEGER) IS
BEGIN
...
END reconcile;
Подпрограммы 6-19
Более того, нельзя перекрывать две подпрограммы, если их
формальные параметры различаются лишь типами данных, причем эти
типы данных относятся к одному и тому же семейству. Например,
следующее перекрытие незаконно, потому что типы данных INTEGER и
REAL входят в одно и то же семейство:
PROCEDURE charge_back (amount INTEGER) IS
BEGIN
...
END charge_back;
PROCEDURE charge_back (amount REAL) IS
BEGIN
...
END charge_back;
Наконец, нельзя перекрывать две функции, если они различаются
лишь типами данных результирующего значения, даже если эти типы
данных относятся к разным семействам. Например, следующее
перекрытие незаконно:
FUNCTION acct_ok (acctno INTEGER) RETURN BOOLEAN IS
BEGIN
...
END acct_ok;
FUNCTION acct_ok (acctno INTEGER) RETURN INTEGER IS
BEGIN
...
END acct_ok;
6-20 Руководство пользователя и справочник по PL/SQL
Как разрешаются вызовы
----------------------
На рис.6-1 показано, как компилятор PL/SQL разрешает вызовы
подпрограмм. Когда компилятор встречает вызов подпрограммы, он
пытается отыскать объявление, удовлетворяющее этому вызову.
Сначала поиск ведется в текущей сфере, а затем, если необходимо,
в последующих окружающих сферах. Поиск прекращается, когда
компилятор обнаруживает одно или несколько объявлений
подпрограмм, в которых имя подпрограммы совпадает с именем
вызываемой подпрограммы.
Чтобы разрешить вызов среди возможно одноименных подпрограмм на
одном и том же уровне сферы, компилятор должен найти ТОЧНОЕ
соответствие между фактическими и формальными параметрами. Это
значит, что параметры должны совпадать по числу, порядку и типам
данных (с учетом того, что для некоторых формальных параметров
могут использоваться умолчания). Если такого соответствия не
найдено, или если найдено более одного соответствия, компилятор
генерирует синтаксическую ошибку.
Рис.6-1
Разрешение вызовов компилятором PL/SQL
--------------------------¬
¦встречен вызов процедуры ¦
L-----------T--------------
¦
------------+-------------¬
¦сравнить имя вызываемой ¦ -----------------------¬
¦процедуры с именами всех +---------+ перейти в окружающую ¦
¦процедур, объявленных в ¦ ¦ сферу ¦
¦текущей сфере ¦ L-----------T-----------
L-----------T--------------
¦ да¦
г---------+---------¬ нет г-----------+----------¬
¦найдено совпадение?¦-------------¦есть окружающая сфера?¦
L---------T---------- L-----------T-----------
¦ да нет¦
------------+--------------------¬ ¦
¦сравнить список фактических ¦ ¦
¦параметров в вызове процедуры ¦ ¦
¦со списком формальных параметров¦ ¦
¦в объявлении каждой процедуры ¦ ¦
L-----------T--------------------- ¦
¦ ¦
г---------+---------¬ нет ¦
¦найдено совпадение?¦-------------------------+
L---------T---------- ¦
¦ да ¦
г---------+-----------¬ да ¦
¦несколько совпадений?¦-----------------------+
L---------T------------
нет ------------+----------¬
----------+-----------¬ ¦ сгенерировать синтак-¦
¦разрешить вызов ¦ ¦ сическую ошибку ¦
L---------------------- L-----------------------
Подпрограммы 6-21
В следующем примере вы из функции valid вызываете процедуру
swap, принадлежащую ОКРУЖАЮЩЕЙ сфере. Однако компилятор
сгенерирует ошибку, потому что внутри ТЕКУЩЕЙ сферы ни одно из
объявлений swap не удовлетворяет вызову процедуры:
PROCEDURE swap (d1 DATE, d2 DATE) IS
date1 DATE;
date2 DATE;
FUNCTION valid (d DATE) RETURN BOOLEAN IS
PROCEDURE swap (n1 INTEGER, n2 INTEGER) IS
BEGIN ... END swap;
PROCEDURE swap (n1 REAL, n2 REAL) IS
BEGIN ... END swap;
BEGIN
...
swap(date1, date2);
END;
BEGIN
...
Предостережение
PL/SQL объявляет встроенные функции глобально в пакете STANDARD.
Переобъявление их чревато ошибками, потому что ваше локальное
объявление перекрывает глобальное объявление. Рассмотрим
следующий пример, в котором вы объявляете функцию с именем SIGN,
а затем, находясь в сфере этого объявления, пытаетесь вызвать
встроенную функцию SIGN:
DECLARE
x NUMBER;
...
BEGIN
DECLARE
FUNCTION SIGN (n NUMBER) RETURN NUMBER IS
BEGIN
IF n < 0 THEN RETURN -1; ELSE RETURN 1; END IF;
END;
BEGIN
...
x := SIGN(0); -- присвоит x значение 1
END;
...
x := SIGN(0); -- присвоит x значение 0
END;
Внутри подблока PL/SQL использует ваше определение SIGN, а НЕ
определение встроенной функции. Чтобы вызвать встроенную
функцию из подблока, вы должны использовать квалифицированную
ссылку, например:
x := STANDARD.SIGN(0); -- присвоит x значение 0
6-22 Руководство пользователя и справочник по PL/SQL
----------------
Рекурсия
Рекурсия является мощным способом упрощения разработки
алгоритмов. По существу, РЕКУРСИЯ означает обращение к самому
себе. В рекурсивной математической последовательности каждый
член выводится путем применения формулы к предыдущим членам.
Ряд Фибоначчи (1,1,2,3,5,8,13,21,...), который был первоначально
использован для моделирования роста колонии кроликов, служит
примером такой последовательности. Каждый член этого ряда,
начиная с третьего, является суммой двух предыдущих членов.
В рекурсивном определении, нечто определяется в терминах более
простых версий этого нечто. Рассмотрим определение факториала
(произведения всех целых от 1 до n):
n! = n * (n-1)!
Рекурсивные подпрограммы
------------------------
Рекурсивная подпрограмма - это подпрограмма, вызывающая себя.
Рассматривайте рекурсивный вызов как вызов некоторой другой
подпрограммы, которая выполняет ту же задачу, что и ваша
подпрограмма. Каждый рекурсивный вызов создает новые экземпляры
всех объектов, объявленных в подпрограмме, включая параметры,
переменные, курсоры и исключения. Аналогично, на каждом уровне
рекурсивного спуска создаются новые экземпляры предложений SQL.
В рекурсивной подпрограмме должно быть по меньшей мере два пути
логики: тот, который ведет к рекурсивному вызову, и тот, который
не ведет к такому вызову. Иными словами, хотя бы один путь
должен приводить к УСЛОВИЮ ЗАВЕРШЕНИЯ. В противном случае
рекурсия (теоретически) продолжалась бы бесконечно. На
практике, если рекурсивная подпрограмма начинает бексонечно
вызывать саму себя, PL/SQL в конце концов переполняет свою
память и возбуждает предопределенное исключение STORAGE_ERROR.
Пример
При решении некоторых задач программирования вы должны повторять
последовательность предложений, пока не будет выполнено
некоторое условие. Для этого можно использовать либо итерацию,
либо рекурсию. Рекурсия применима там, где задача может быть
разбита на более простые версии этой же задачи. Например, вы
можете вычислить 3! следующим способом:
0! = 1
1! = 1 * 0! = 1 * 1 = 1
2! = 2 * 1! = 2 * 1 = 2
3! = 3 * 2! = 3 * 2 = 6
Подпрограммы 6-23
Чтобы реализовать этот алгоритм, вы могли бы написать следующую
рекурсивную функцию, которая вычисляет факториал положительного
целого числа:
FUNCTION fac (n POSITIVE) RETURN INTEGER IS -- возвращает n!
BEGIN
IF n = 1 THEN -- условие завершения
RETURN 1;
ELSE
RETURN n * fac(n - 1); -- рекурсивный вызов
END IF;
END fac;
При каждом рекурсивном вызове n уменьшается. В конце концов, n
станет равным 1, и рекурсия остановится.
Еще один пример
Рассмотрим следующую процедуру, которая отыскивает весь штат для
данного менеджера. Эта процедура объявляет два формальных
параметра, mgr_no и tier, которые представляют соответственно
личный номер сотрудника для менеджера и уровень подчиненности.
Сотрудники, непосредственно подчиненные данному менеджеру,
составляют первый уровень подчиненности. При своем вызове
процедура принимает значение для mgr_no, а для tier использует
значение по умолчанию. Например, вы могли бы вызвать эту
процедуру следующим образом:
find_staff(7839);
Процедура передает mgr_no курсору в курсорном цикле FOR, который
отыскивает сотрудников на последовательно снижающихся уровнях
подчиненности. При каждом рекурсивном вызове создается новый
экземпляр цикла FOR, и открывается очередной курсор. Однако все
предыдущие курсоры остаются позиционированными на очередную
строку в своих активных множествах. Когда очередная операция
FETCH не может извлечь строку, курсор автоматически закрывается,
и осуществляется выход из цикла FOR. Поскольку рекурсивный вызов
находится внутри цикла FOR, условие завершения рекурсии имеет
место.
PROCEDURE find_staff (mgr_no NUMBER, tier NUMBER := 1) IS
boss_name CHAR(10);
CURSOR c1 (boss_no NUMBER) IS
SELECT empno, ename FROM emp WHERE mgr = boss_no;
BEGIN
/* Дать имя босса */
SELECT ename INTO boss_name FROM emp WHERE empno = mgr_no;
IF tier = 1 THEN
INSERT INTO staff -- результир. таблица из 1 столбца
VALUES (boss_name || ' руководит штатом');
END IF;
6-24 Руководство пользователя и справочник по PL/SQL
/* Найти непосредственных подчиненных данного начальника */
FOR ee IN c1 (mgr_no) LOOP
INSERT INTO staff
VALUES (boss_name || ' руководит ' || ee.name
|| ' на уровне ' || to_char(tier));
/* Спуститься на следующий уровень подчиненности */
find_staff(ee.empno, tier + 1); -- рекурсивный вызов
END LOOP;
END;
В отличие от первоначального вызова, каждый рекурсивный вызов
передает процедуре второй фактический параметр (очередной
уровень подчиненности).
Этот пример иллюстрирует лишь рекурсию, а отнюдь не эффективное
использование предложений SQL, ориентированных на множества. Вы
могли бы сравнить производительность рекурсивной процедуры со
следующим предложением SQL, которое выполняет ту же задачу:
INSERT INTO staff
SELECT PRIOR ename || ' руководит ' || ename
|| ' на уровне ' || to_char(LEVEL - 1)
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr;
Предложение SQL работает ощутимо быстрее. Однако процедура
является более гибкой. Например, многотабличный запрос не может
содержать фразу CONNECT BY, а предложение SQL, в отличие от
процедуры, нельзя переделать на выполнение соединений.
(СОЕДИНЕНИЕ комбинирует строки из двух или более таблиц базы
данных.) Кроме того, в процедуре можно обрабатывать данные
такими способами, которые недоступны для предложения SQL.
Предостережение
---------------
Будьте внимательны в выборе места, в которое вы помещаете
рекурсивный вызов. Если вы помещаете его в курсорный цикл FOR
или между предложениями OPEN и CLOSE, то при каждом вызове
открывается очередной курсор. Как следствие, ваша программа
может превысить лимит, устанавливаемый параметром инициализации
ORACLE OPEN_CURSORS.
Подпрограммы 6-25
Взаимная рекурсия
-----------------
Подпрограммы ВЗАИМНО РЕКУРСИВНЫ, если они прямо или косвенно
вызывают друг друга. В следующем примере функции odd и even,
которые определяют соответственно нечетность и четность числа,
прямо вызывают друг друга. Упреждающее объявление odd
необходимо, потому что even вызывает функцию odd, которая еще не
объявлена к моменту ее вызова. (См. раздел "Упреждающие
объявления" выше в этой главе.)
FUNCTION odd (n NATURAL) RETURN BOOLEAN; -- упреждающее объявл.
FUNCTION even (n NATURAL) RETURN BOOLEAN IS
BEGIN
IF n = 0 THEN
RETURN TRUE;
ELSE
RETURN odd(n - 1); -- взаимно рекурсивный вызов
END IF;
END even;
FUNCTION odd (n NATURAL) RETURN BOOLEAN IS
BEGIN
IF n = 0 THEN
RETURN FALSE;
ELSE
RETURN even(n - 1); -- взаимно рекурсивный вызов
END IF;
END odd;
Когда функции odd или функции even передается положительное
целое n, обе эти функции по очереди вызывают друг друга. При
каждом вызове n уменьшается. В конце концов n станет равным 0,
и последний вызов возвратит TRUE или FALSE. Например, передача
числа 4 функции odd приведет к следующей последовательности
вызовов:
odd(4)
even(3)
odd(2)
even(1)
odd(0) -- возвращает FALSE
С другой стороны, передача числа 4 функции even приведет к
следующей последовательности вызовов:
even(4)
odd(3)
even(2)
odd(1)
even(0) -- возвращает TRUE
6-26 Руководство пользователя и справочник по PL/SQL
Сравнение рекурсии и итерации
-----------------------------
В отличие от итерации, рекурсия не является существенной для
программирования на PL/SQL. Любая проблема, которая может быть
решена рекурсией, может быть решена и итерацией. Далее,
концепцию итерации легче усвоить, поскольку примеры рекурсии не
столь часты в повседневной жизни. Как следствие, итеративную
версию подпрограммы обычно легче спроектировать, чем рекурсивную
версию той же программы. Однако рекурсивная версия обычно
проще, меньше, и потому ее легче отладить. Сравните следующие
функции, которые вычисляют n-й член ряда Фибоначчи:
-- рекурсивная версия
FUNCTION fib (n POSITIVE) RETURN INTEGER IS
BEGIN
IF (n = 1) OR (n = 2) THEN
RETURN 1;
ELSE
RETURN fib(n - 1) + fib(n - 2);
END IF;
END fib;
-- итеративная версия
FUNCTION fib (n POSITIVE) RETURN INTEGER IS
pos1 INTEGER := 1;
pos2 INTEGER := 0;
cum INTEGER;
BEGIN
IF (n = 1) OR (n = 2) THEN
RETURN 1;
ELSE
cum := pos1 + pos2;
FOR i IN 3..n LOOP
pos2 := pos1;
pos1 := cum;
cum := pos1 + pos2;
END LOOP;
RETURN cum;
END IF;
END fib;
Рекурсивная версия функции fib более элегантна. Однако
итеративная версия эффективнее; она работает быстрее и
использует меньше памяти. Причина этого в том, что каждый
рекурсивный вызов требует дополнительного времени и памяти. Чем
больше число рекурсивных вызовов, тем больше разница в
эффективности. Тем не менее, если вы ожидаете, что число
рекурсивных вызовов будет невелико, можно предпочесть
рекурсивную версию из-за ее читабельности.
Подпрограммы 6-27
----------------
Хранимые подпрограммы
Важным клиентом PL/SQL является сервер ORACLE. Если у вас есть
Процедурное расширение базы данных, то вы можете компилировать
подпрограммы отдельно и сохранять их в базе данных ORACLE, где
они готовы к выполнению. В этом разделе дается общее описание
хранимых подпрограмм. Вы узнаете о том, в каком окружении
функционируют такие подпрограммы, и что они позволяют делать
вашим приложениям. Для более подробной информации обратитесь к
документу ORACLE7 Server Application Developer's Guide.
Преимущества хранимых подпрограмм
---------------------------------
Хранимые подпрограммы предоставляют более высокую продуктивность
разработки, лучшую производительность, экономию памяти,
целостность приложений и более строгую защиту.
Повышение продуктивности
Проектируя приложения вокруг библиотеки хранимых подпрограмм, вы
можете избежать неоднократного кодирования повторяющихся
операций, повышая продуктивность разработки. Предположим,
например, что несколько различных приложений вызывают хранимую
процедуру, которая управляет таблицей emp в базе данных. Если
метод управления изменится, потребуется пересмотреть лишь одну
процедуру, а не все приложения.
Улучшение производительности
Использование подпрограмм может сократить число обращений ваших
приложений к ORACLE. Например, для исполнения десяти
индивидуальных предложений SQL требуется десять обращений к
ORACLE, но для исполнения подпрограммы, содержащей десять
предложений SQL, необходим лишь один вызов. Уменьшение число
обращений может резко увеличить производительность, особенно
если ваше приложение взаимодействует с ORACLE через сеть.
Экономия памяти
Хранимые подпрограммы используют преимущества разделяемой памяти
ORACLE. Так, лишь одна копия подпрограммы должна быть загружена
в память, чтобы быть доступной всем пользователям. Как
следствие, приложениям требуется меньше памяти.
Целостность приложений
Хранимые подпрограммы улучшают целостность и согласованность
ваших приложений. Создавая приложения вокруг библиотеки
хранимых подпрограмм, вы снижаете вероятность ошибок
кодирования.
Повышенная безопасность
Хранимые подпрограммы повышают безопасность данных. Ваш АБД
может ограничить доступ пользователей к определенным операциям с
базой данных, предоставляя доступ лишь через подпрограммы.
Например, АБД может предоставить пользователям право выполнения
хранимой процедуры, обновляющей таблицу emp, но не давать
доступа к самой таблице emp.
6-28 Руководство пользователя и справочник по PL/SQL
Вызов хранимых подпрограмм
--------------------------
Вы можете вызывать хранимые подпрограммы из триггера базы
данных, другой хранимой подпрограммы, приложения прекомпилятора
ORACLE, приложения OCI или из инструмента ORACLE, такого как
SQL*Plus.
Из другой подпрограммы
Хранимая подпрограмма может вызывать другую хранимую
подпрограмму. Например, в теле пакетированной подпрограммы
может появиться вызов независимой процедуры create_dept:
create_dept(name, location);
Из прикладной программы
Приложение прекомпилятора или приложение OCI может вызывать
хранимые подпрограммы из анонимных блоков PL/SQL. В следующем
примере вы вызываете независимую процедуру create_dept из
программы прекомпилятора ORACLE:
EXEC SQL EXECUTE
BEGIN
create_dept(:name, :location);
END;
END-EXEC;
Фактические параметры :name и :location - это хост-переменные.
В следующем примере, процедура create_dept входит в пакет с
именем emp_actions, так что вы должны использовать
квалифицированную ссылку при ее вызове:
EXEC SQL EXECUTE
BEGIN
emp_actions.create_dept(:name, :location);
END;
END-EXEC;
Из инструмента ORACLE
Вы можете вызывать хранимые подпрограммы интерактивно из
инструментов ORACLE, таких как SQL*Plus, SQL*Forms или SQL*DBA.
Например, из SQL*Plus вы могли бы вызвать независимую процедуру
create_dept следующим образом:
SQL> EXECUTE create_dept('MARKETING', 'NEW YORK');
Этот вызов эквивалентен следующему анонимному блоку PL/SQL:
SQL> BEGIN create_dept('MARKETING', 'NEW YORK'); END;
Предостережение
---------------
Перед выполнением хранимой подпрограммы ORACLE устанавливает
неявную точку сохранения. Если подпрограмма сбивается в
результате необработанного исключения, ORACLE осуществляет откат
к этой точке сохранения. Тем самым отменяется вся работа,
проделанная подпрограммой.
Подпрограммы 6-29
Ограничения
Тело независимой или пакетированной хранимой подпрограммы может
содержать любое предложение SQL или PL/SQL. Однако
подпрограммы, участвующие в распределенной транзакции, триггерах
базы данных и приложениях SQL*Forms, не могут вызывать хранимых
подпрограмм, содержащих предложения COMMIT, ROLLBACK или
SAVEPOINT.
Обращения к хранимым функциям могут появляться в процедурных
предложениях, но НЕ в предложениях SQL.
Удаленный доступ
----------------
Для вызова независимых и пакетированных подпрограмм, хранимых в
удаленной базе данных ORACLE, используйте следующий синтаксис:
имя_подпрограммы@связьБД(параметр1, параметр2, ...);
имя_пакета.имя_подпрограммы@связьБД(параметр1, параметр2, ...);
В следующем примере вызывается хранимая процедура raise_salary,
определенная в пакете emp_actions в базе данных newyork:
BEGIN
emp_actions.raise_salary@newyork(emp_num, amount);
Чтобы обеспечить прозрачность адресации для удаленных
независимых (но не пакетированных) подпрограмм, вы можете
создавать синонимы, как показывает следующий пример:
CREATE SYNONYM create_dept FOR create_dept@newyork;
Ограничения
Хранимые подпрограммы, выполняющиеся внутри сервера ORACLE,
могут вызывать удаленные подпрограммы, тогда как блоки PL/SQL,
выполняющиеся в приложении, не могут этого делать. Кроме того,
ORACLE считает, что все вызовы удаленных подпрограмм выполняют
обновление, что требует двухфазного подтверждения транзакции.
Как следствие, если локальная транзакция откатывается, будет
выполнен и откат работы, проделанной удаленной подпрограммой.
Если удаленная подпрограмма сбивается, может быть возвращена
ошибка, требующая, чтобы ваша программа PL/SQL выполнила откат
локальной транзакции.
Подпрограммы, участвующие в распределенной транзакции, не могут
вызывать хранимых подпрограмм, содержащих предложения COMMIT,
ROLLBACK или SAVEPOINT.
6-30 Руководство пользователя и справочник по PL/SQL
Состояния подпрограмм и зависимости
-----------------------------------
Хранимая подпрограмма всегда находится в одном из двух
состояний: действительна или недействительна. Подпрограмма
ДЕЙСТВИТЕЛЬНА, если ни ее исходный код, ни любой из объектов, к
которым она обращается, не был ни удален (DROP), ни заменен
(REPLACE), ни изменен (ALTER) с момента последней компиляции
этой подпрограммы. Когда вы вызываете действительную
подпрограмму, ORACLE просто загружает ее откомпилированный код
PL/SQL в разделяемый кэш SQL и исполняет этот код.
С другой стороны, хранимая подпрограмма НЕДЕЙСТВИТЕЛЬНА, если ее
исходный код или любой из объектов, к которым она обращается,
был либо удален (DROP), либо заменен (REPLACE), либо изменен
(ALTER) с момента последней компиляции этой подпрограммы.
Хранимая подпрограмма, обращающаяся к объекту базы данных,
называется ЗАВИСИМОЙ от этого объекта. ORACLE отмечает как
недействительную любую подпрограмму, зависящую от удаляемого,
заменяемого или изменяемого объекта, в момент этой операции над
объектом.
Недействительная подпрограмма должна быть перекомпилирована
перед исполнением. Эта перекомпиляция осуществляется в два
этапа. Сначала ORACLE определяет, нет ли недействительных
подпрограмм или пакетов среди тех, к которым обращается данная
подпрограмма. Если есть, они должны быть также
перекомпилированы. Это может привести к каскаду перекомпиляций,
которые должны быть все успешными; в противном случае ORACLE
возвращает ошибку выполнения, а подпрограмма остается
недействительной. На втором этапе перекомпилируется сама
требуемая подпрограмма, после чего она может быть исполнена.
Для дополнительной информации о состояниях подпрограмм и
зависимостях обратитесь к документу ORACLE7 Server Application
Developer's Guide.
Подпрограммы 6-31
Создание хранимых подпрограмм
-----------------------------
Процедурное расширение базы данных позволяет вам создавать
(CREATE) подпрограммы и постоянно сохранять их в базе данных
ORACLE для общего пользования. Вы можете выдавать предложения
CREATE PROCEDURE и CREATE FUNCTION интерактивно из SQL*Plus или
SQL*DBA. Например, вы можете создать процедуру fire_employee:
CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END;
Заметьте, что при создании подпрограмм вы используете в их
спецификациях ключевое слово AS вместо IS. Полный синтаксис
предложения CREATE приведен в документе ORACLE7 Server SQL
Language Reference Manual.
Когда вы создаете подпрограмму для сохранения в базе данных,
ORACLE автоматически компилирует ее исходный код, кэширует
объектный код в РАЗДЕЛЯЕМУЮ ОБЛАСТЬ SQL в глобальной области
системы (SGA) и сохраняет исходный и объектный коды в словаре
данных. Объектный код остается кэшированным в SGA, где он
доступен для исполнения. При необходимости ORACLE применяет
алгоритм LRU (по давности последнего обращения), который
выбирает, какие разделяемые области SQL могут быть освобождены,
чтобы дать место другим процедурам.
Когда вы вызываете хранимую подпрграмму, ORACLE проверяет, нет
ли ее объектного кода в одной из разделяемых областей SQL. Если
нет, ORACLE выделяет разделяемую область SQL и загружает в нее
объектный код подпрограммы. Затем ORACLE выделяет ЛИЧНУЮ
ОБЛАСТЬ SQL, в которой будут храниться специфичные для данной
сессии значения подпрограммы. Если подпрограмму исполняют
одновременно несколько пользователей, то используется лишь одна
разделяемая область SQL, но несколько личных областей SQL - по
одной на каждого пользователя.
Предложения SQL внутри подпрограммы обрабатываются таким же
способом. Они используют разделяемые области SQL для своих
синтаксически разобранных представлений, и личные области SQL
для хранения информации, специфичной для сессии. Разделяемая
область SQL, используемая самой подпрограммой, называется
РОДИТЕЛЬСКИМ КУРСОРОМ; разделяемые области SQL, используемые
предложениями SQL внутри этой подпрограммы, называются
ПОРОЖДЕННЫМИ КУРСОРАМИ.
6-32 Руководство пользователя и справочник по PL/SQL