Отправить E-Mail
http://www.interface.ru
По техническим вопросам обращайтесь к вебмастеру
Документ опубликован: 11.10.01
| СТАТЬЯ |
11.10.01
|
Глава 7. Использование процедур и пакетов
Содержание части
Чтобы помочь в отладке хранимых процедур, пакетов и триггеров, предоставляется общий пакет с именем DBMS_OUTPUT, который позволяет выдавать отладочную информацию. Процедуры в этом пакете предоставляют почти те же возможности, что и функция printf() в языке C. Полное объяснение процедур этого пакета содержится в приложении A этого руководства.
Чтобы создать пакет DBMS_OUTPUT, запустите скрипт DBMSOTPT.SQL от имени пользователя SYS. Этот скрипт запускается автоматически скриптом CATPROC.SQL.
Включение отладочной информации
Чтобы включить отладочную информацию в вашу хранимую процедуру, пакет или триггер, используйте процедуры PUT или PUT_LINE. Процедура PUT позволяет вам добавлять в буфер множественные порции информации, прежде чем включить конец строки с помощью вызова процедуры NEW_LINE. Процедура PUT_LINE добавляет в буфер одну строку информации.
Чтобы выдавать эту информацию во время исполнения триггера или процедуры, вы можете использовать любую из следующих опций:
Ниже приведен пример функции, которая опрашивает таблицу сотрудников и возвращает суммарную зарплату для указанного отдела. Эта функция включает несколько вызовов процедуры PUT_LINE:
CREATE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER AS
CURSOR emp_cursor IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11,2) := 0;
counter NUMBER(10) := 1;
BEGIN
FOR emp_record IN emp_cursor LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal +
emp_record.comm;
PUT_LINE('Loop number = ' || counter || '; Wages = ' ||
TO_CHAR(total_wages)); /* отладочная строка */
counter := counter + 1; /* отладочный счетчик */
END LOOP;
/* отладочная строка */
PUT_LINE('Total wages = ' || TO_CHAR(total_wages));
RETURN total_wages;
END dept_salary;
Предположим, что таблица EMP содержит следующие строки:
EMPNO . . . SAL COMM DEPT
----- ---- ----- ----
1002 1500 500 20
1203 1000 30
1289 1000 10
1347 1000 250 20
Предположим, что в SQL*DBA выданы средующие предложения:
SQLDBA> set server output on
SQLDBA> execute dept_salary(20);
В результате будет выдана следующая информация:
Loop number = 1; Wages = 2000
Loop number = 2; Wages = 3250
Total wages = 3250 PL/SQL procedure successfully executed.
Процедуры можно вызывать из многих различных окружений. Например:
Ниже приведены некоторые типичные примеры вызова процедур из этих окружений.
Вызов процедуры из другой процедуры или триггера
Процедура или триггер могут вызывать другую хранимую процедуру. Например, в тело процедуры может быть включена строка:
. . .
sal_raise(emp_id, 200);
. . .
Эта строка вызывает процедуру SAL_RAISE. EMP_ID - это переменная, определенная в контексте вызывающей процедуры. Заметим, что в PL/SQL разрешаются рекурсивные вызовы процедур, т.е. процедура может вызывать саму себя.
Интерактивный вызов процедур из инструментов ORACLE
Процедуры можно вызывать интерактивно из инструмента ORACLE, такого как SQL*DBA. Например, чтобы вызвать процедуру с именем SAL_RAISE, принадлежащую вам, используйте команду EXECUTE:
EXECUTE sal_raise(1043, 200);
Некоторые интерактивные инструменты позволяют создавать переменные сессии. Например, в SQL*DBA следующее предложение создает переменную сессии:
VARIABLE assigned_empno NUMBER;
После определения, любая переменная сессии может использоваться на всем протяжении сессии. Например, вы могли бы выполнить функцию и перехватить возвращаемое значение в переменную сессии:
EXECUTE :assigned_empno := hire_emp('TSMITH', 'CLERK', \
1032, SYSDATE, 500, NULL, 10);
PRINT assigned_empno;
ASSIGNED_EMPNO
--------------
2893
За описанием SQL*DBA обратитесь к документу ORACLE7 Server Utilities User's Guide. О том, как выполнять аналогичные операции в прикладных инструментах ORACLE, см. в ваших руководствах по соответствующим инструментам.
Явный вызов процедур в приложениях
Приложение базы данных (такое как программа прекомпилятора, или программа, использующая интерфейсы OCI), может вызывать процедуры из своего кода.
Чтобы исполнить процедуру в блоке PL/SQL приложения, просто вызовите эту процедуру. Следующая строка внутри блока PL/SQL вызывает процедуру FIRE_EMP:
fire_emp(:empno);
Здесь :EMPNO - хост-переменная (связная переменная) в контексте вашего приложения.
Чтобы исполнить процедуру внутри кода приложения прекомпилятора, вы должны использовать интерфейс вызова EXEC. Например, следующее предложение вызывает процедуру FIRE_EMP в коде приложения прекомпилятора:
EXEC SQL EXECUTE
BEGIN
fire_emp(:empno);
END;
END-EXEC;
Здесь :EMPNO - хост-переменная (связная переменная).
Разрешение имен при вызове процедур
Ссылки на процедуры и пакеты разрешаются согласно алгоритму, описанному в секции "Разрешение имен объектов, адресуемых в предложениях SQL" на странице 2-35.
Привилегии, требуемые для исполнения процедуры
Если вы владелец независимой процедуры или пакета, то вы можете выполнить независимую или пакетированную процедуру, а также любую общую независимую или пакетированную процедуру, в любой момент, как описано в предыдущих секциях.
Если вы хотите выполнить независимую или пакетированную процедуру, принадлежащую другому пользователю, то вы должны:
включить в вызов процедуры имя владельца, например:
EXECUTE jward.fire_emp (1043); EXECUTE jward.hire_fire.fire_emp (1043);
Специфицирование значений для аргументов процедуры
Вызывая процедуру, специфицируйте значение или параметр для каждого из аргументов этой процедуры. Вы можете идентифицировать значения аргументов с помощью любого из следующих методов или их комбинации:
Например, каждое из следующих предложений вызывает процедуру UPDATE_SAL, чтобы увеличить жалованье работника с номером 7369 на 500:
sal_raise(7369, 500); sal_raise(sal_incr=>500, emp_id=>7369); sal_raise(7369, sal_incr=>500);
Первое предложение идентифицирует значения аргументов, перечисляя их в том порядке, в котором они появляются в объявлении процедуры.
Второе предложение идентифицирует значения аргументов по именам, независимо от порядка, в котором они появляются в объявлении процедуры. При этом способе вы можете задавать аргументы в любом порядке.
Третье предложение идентифицирует значения аргументов, используя комбинацию обоих методов. При таком способе, значения, идентифицируемые своим порядком, должны предшествовать значениям, идентифицируемым по именам.
Если вы использовали опцию DEFAULT для определения умалчиваемых значений для входных (IN) параметров подпрограммы (см. документ PL/SQL User's Guide and Reference), то вы можете передавать этой подпрограмме различное количество фактических параметров, принимая или перекрывая их умалчиваемые значения по своему выбору. Если фактическое значение не передается, будет использоваться соответствующее умалчиваемое значение. Если вы хотите передать значение аргументу, следующему за опущенным аргументом (для которого будет использоваться умолчание), то вы должны явно указать как имя, так и значение такого аргумента.
Вызывайте удаленные процедуры, используя подходящую связь баз данных и имя процедуры. Следующее предложение SQL*DBA выполняет процедуру FIRE_EMP, расположенную в базе данных, на которую указывает локальная связь баз данных с именем NY:
EXECUTE fire_emp@NY(1043);
О том, как обрабатывать исключения при вызовах удаленных процедур, см. страницу 11-5.
Обращения к удаленным объектам
Внутри тела локально определенной процедуры можно обращаться к удаленным объектам. Следующая процедура удаляет строку из удаленной таблицы сотрудников:
CREATE PROCEDURE fire_emp(emp_id NUMBER) IS
BEGIN DELETE FROM emp@sales WHERE empno = emp_id;
END;
Следующий перечень объясняет, как правильно вызывать удаленные процедуры, в зависимости от вызывающего окружения:
Удаленные процедуры (независимые и пакетированные) можно вызывать из процедуры, приложения OCI или приложения прекомпилятора путем указания имени удаленной процедуры, связи баз данных и аргументов для удаленной процедуры.
CREATE PROCEDURE local_procedure(arg1, arg2) AS
BEGIN
. . .
remote_procedure@dblink(arg1, arg2);
. . .
END;
В предыдущем примере, вы могли бы создать синоним для REMOTE_PROCEDURE@DBLINK. Это позволило бы вам вызывать данную удаленную процедуру как из приложения инструмента ORACLE, такого как приложение SQL*Forms, так и из процедуры, приложения OCI или приложения прекомпилятора.
CREATE PROCEDURE local_procedure(arg1, arg2) AS
BEGIN . . . synonym(arg1, arg2); . . . END;
Если вы не хотите использовать синоним, напишите локальную процедуру, которая просто передает свои параметры удаленной процедуре, и всегда вызывайте вместо удаленной эту локальную процедуру:
BEGIN local_procedure(arg1, arg2); END;
Здесь LOCAL_PROCERURE определена так, как в первом пункте этого перечня.
Замечание: С помощью синонимов можно обеспечить прозрачность местоположения для ассоциированных удаленных процедур.
[!] В отличие от хранимых процедур, которые используют связывание во время компиляции, при обращении к удаленным процедурам применяется привязка во время выполнения. Учетное имя, с которым вы соединяетесь в удаленной базе данных, зависит от связи баз данных.
Предполагается, что любой вызов удаленной хранимой процедуры будет выполнять обновления; такой тип ссылок всегда использует двухфазное подтверждение транзакции (даже если удаленная процедура ничего не обновляет). Более того, если транзакция, включающая вызов удаленной процедуры, откатывается, то работа, проделанная удаленной процедурой, также откатывается. Процедура, вызываемая удаленно, не может выполнять предложений COMMIT, ROLLBACK или SAVEPOINT.
РАСПРЕДЕЛЕННОЕ ОБНОВЛЕНИЕ модифицирует данные на двух или более узлах. Распределенное обновление можно осуществлять путем вызова процедуры, которая включает два или более удаленных обновлений данных на различных узлах. Предложения в таком конструкте посылаются в удаленные узлы, и исполнение всего конструкта заканчивается успешно или безуспешно как единица. Если одна часть распределенного обновления успешна, а другая часть сбилась, то для продолжения необходим откат (всей транзакции или к точке сохранения). Имейте это в виду, когда создаете процедуры, выполняющие распределенные обновления.
Будьте особенно внимательны, когда используете локальную процедуру, вызывающую удаленную процедуру. Если во время исполнения локальной процедуры будет обнаружено несоответствие отметок времени с удаленной процедурой, то удаленная процедура не исполняется, а локальная процедура становится недействительной.
Синонимы для процедур и пакетов
Синонимы могут создаваться для независимых процедур и пакетов для того, чтобы:
Когда привилегированный пользователь хочет вызвать процедуру, он может использовать ассоциированный синоним. Так как процедуры, определенные внутри пакета, не являются индивидуальными объектами (объектом является сам пакет), нельзя создавать синонимы для отдельных процедур внутри пакета.
предыдущая часть | содержание | следующая часть
Дополнительную информацию Вы можете получить в компании Interface Ltd.
Обсудить на форуме Oracle
Отправить ссылку на страницу по e-mail
| Interface Ltd. Отправить E-Mail http://www.interface.ru |
|
| Ваши замечания и предложения отправляйте автору По техническим вопросам обращайтесь к вебмастеру Документ опубликован: 11.10.01 |