Create OR replace package definitions IS

TYPE emp_payment_type IS RECORD

(

  ename emp.ename%TYPE

, payment VARCHAR2 (10)

, amount NUMBER

);

TYPE payments_type IS TABLE OF emp_payment_type;

TYPE refcursor_type IS REF CURSOR RETURN emp%ROWTYPE;

END definitions;

/

 

CREATE OR REPLACE FUNCTION payments

(emps IN definitions.refcursor_type)

RETURN definitions.payments_type

PIPELINED

IS

in_rec emp%ROWTYPE;

out_rec definitions.emp_payment_type;

BEGIN

LOOP

FETCH emps INTO in_rec; EXIT WHEN emps%NOTFOUND;

out_rec:= NULL;

 

IF in_rec. sal IS NOT NULL

THEN

    out_rec.ename:= in_rec.ename;

    out_rec.payment:= 'SALARY';

    out_rec.amount:= in_rec.sal;

    PIPE ROW (out_rec);

END IF;

 

IF in_rec. comm IS NOT NULL

THEN

    out_rec.ename:= in_rec.ename;

    out_rec.payment:= 'COMMISSION';

    out_rec.amount:= in_rec.comm;

    PIPE ROW (out_rec);

END IF;

END LOOP;

RETURN;

END;

/

 

Пример обращения к функции PAYMENTS:

 

SELECT * FROM TABLE (payments (CURSOR (SELECT * FROM emp)));

 

Эту же задачу можно решить чуть иначе, прибегнув к типу объекта и к системно определенному типу для ссылки на курсор (последнее можно было сделать и в примере выше). Вот один из возможных вариантов:

 

DROP FUNCTION payments;

DROP TYPE payments_type;

DROP TYPE payment_type;

 

CREATE TYPE payment_type AS OBJECT

(

ename  VARCHAR2 (9)

, payment VARCHAR2 (10)

, amount NUMBER

)

/

 

CREATE TYPE payments_type IS TABLE OF payment_type

/

 

CREATE OR REPLACE FUNCTION payments (emps IN sys_refcursor)

RETURN payments_type

PIPELINED

IS

in_rec emp%ROWTYPE;

out_rec payment_type;

BEGIN

LOOP

FETCH emps INTO in_rec; EXIT WHEN emps%NOTFOUND;

  out_rec:= payment_type (NULL, NULL, NULL);

 

IF in_rec. sal IS NOT NULL

THEN

    out_rec.ename:= in_rec.ename;

    out_rec.payment:= 'SALARY';

    out_rec.amount:= in_rec.sal;

    PIPE ROW (out_rec);

  END IF;

 

IF in_rec. comm IS NOT NULL

THEN

    out_rec.ename:= in_rec.ename;

    out_rec.payment:= 'COMMISSION';

    out_rec.amount:= in_rec.comm;

    PIPE ROW (out_rec);

END IF;

END LOOP;

RETURN;

END;

/

 

Снова проверка:

 

SQL> SELECT * FROM TABLE (payments (CURSOR (SELECT * FROM emp)));

 

ENAME PAYMENT   AMOUNT

--------- ---------- ----------

SMITH   SALARY       800

ALLEN   SALARY      1600

ALLEN   COMMISION    300

WARD      SALARY      1250

WARD   COMMISION    500

JONES   SALARY      2975

MARTIN   SALARY      1250

MARTIN   COMMISION   1400

BLAKE   SALARY      2850

CLARK   SALARY      2450

SCOTT   SALARY      3000

KING      SALARY      5000

TURNER   SALARY      1500

TURNER   COMMISION      0

ADAMS   SALARY      1100

JAMES   SALARY       950

FORD SALARY       3000

MILLER SALARY       1300

Rows selected.

 

Обратите внимание, что использование типа объекта не дает возможности сослаться на типы столбцов, а отказ от вспомогательного пакета не позволяет использовать строгую ссылку на курсор.

 

Упражнение. Переписать этот пример, перенеся определение ссылки на курсор в пакет и сделав ссылку строгой. (в пакет же можно перенести и описание типа для вложенной таблицы).

 

Отладка процедур в PL/SQL

Таблицы словаря-справочника

В отладке подпрограмм на PL/SQL полезны следующие таблицы словаря-справочника:

 

Таблица	Описание
USER_OBJECTS	Описание всех объектов пользователя, в том числе PACKAGE и PACKAGE BODY
USER_DEPENDENCIES	Взаимные зависимости объектов (в том числе программных элементов)
USER_OBJECT_SIZE	Описание размеров объектов пользователя, в том числе PL/SQL
USER_SOURCE	Исходный текст хранимых объектов
USER_ERRORS	Список ошибок для хранимых объектов пользователя
USER_TRIGGERS	Триггерные процедуры пользователя

 

В SQL*Plus ошибки последней трансляции хранимого объекта можно посмотреть командой SHOW ERRORS (в своей работе она обращается к USER_ERRORS).

 

Для некоторых таблиц (или даже базовых для них) созданы дополнительные сценарии, повышающие удобство пользования. Так, для просмотра дерева зависимостей объекта (не обязательно программной единицы) можно воспользоваться средствами из utldtree. sql из % ORACLE_ HOME%\ rdbms\ admin.

 

 

Зависимости подпрограмм

Время последней модификации объекта можно посмотреть в поле USER_OBJECTS.LAST_DDL_TIME. Если вызывающая подпрограмма имеет более позднее значение, чем вызываемая, то ее имеет смысл перетранслировать.

 

Ограничение этого подхода в том, что вызываемая подпрограмма может находиться на удаленном сервере и описание ее недоступно.

 

 

Системные пакеты

Проводить отладку программ на PL/SQL помогают системные пакеты DBMS_OUTPUT (отладочная выдача на экран), DBMS_PROFILER, DBMS_TRACE, DBMS_UTILITY (частично) а также DBMS_DEBUG (см. ниже).

 

 

17.3.1. Пакет DBMS_PROFILER

Содержит две функции:

 

Функция	Описание
START_PROFILER	Стартует создание профиля текущего сеанса. Профиль будет сохранен в специальных таблицах. При старте профиль можно именовать.
STOP_PROFILER	Завершает профилирование сеанса

 

 

Среда работы пакета обеспечивается следующими файлами, большей частью в rdbms\ admin:

 

Файл	Описание
dbmspbp. sql	Создает спецификацию пакета DBMS_PROFILER
prvtpbp. sql	Создает тело пакета DBMS_PROFILER (текст файла – объектный код)
profload. sql	Запускает файлы dbmspbp. sql и prvtpbp. sql. Должен выполняться от имени АБД
proftab. sql	Сценарий создания таблиц: - plsql_profiler_runs - plsql_profiler_units - plsql_profiler_data   В эти таблицы будет записываться профиль исполнения подпрограмм на PL/SQL
profrep. sql	Создает набор выводимых таблиц и пакет PROF_REPORT_UTILITIES, который можно использовать для более удобного извлечения нужной информации из созданных таблиц. См. каталог plsql\ demo
profsum. sql	Набор специальных запросов к данным профиля с использованием попрограмм из PROF_REPORT_UTILITIES. См. каталог plsql\ demo

 

Ввиду того, что таблицы профиля заполняются быстро, при регулярном их использовании необходимо выработать процедуру их чистки.

 

 

17.3.2. Пакет DBMS_TRACE

Позволяет включать и выключать трассировку выполнения (вызовы, исключительные состояния) отдельных локальных для сервера подпрограмм (т.е. не удаленных) или же всех подпрограмм определенного сеанса. По умолчанию файл трассировки образуется в каталоге udump. 

 

Старт трассировки:

 

EXEC DBMS_TRACE.SET_PLSQL_TRACE (уровень_трассировки);

 

где уровень_трассировки может быть следующим:

 

DBMS_TRACE.TRACE_ALL_CALLS                    CONSTANT INTEGER:= 1;

DBMS_TRACE.TRACE_ENABLED_CALLS        CONSTANT INTEGER:= 2;

DBMS_TRACE.TRACE_ALL_EXCEPTIONS       CONSTANT INTEGER:= 4;

DBMS_TRACE.TRACE_ENABLED_EXCEPTIONS CONSTANT INTEGER:= 8;

 

Останов трассировки:

 

EXEC DBMS_TRACE.CLEAR_PLSQL_TRACE;

 

При выборочной трассировке она будет распространяться на все создаваемые и заменяемые (CREATE, REPLACE) программы – в том случае, если было выдано

 

ALTER SESSION SET PLSQL_DEBUG = TRUE;

 

Чтобы при выборочной трассировке программы можно было включать в состав трассируемых индивидуально, нужно выдать:

 

ALTER {PROCEDURE | FUNCTION | PACKAGE BODY} имя _программы COMPILE DEBUG;

 

а затем

 

EXEC DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.TRACE_ENABLED_CALLS);

 

(или DBMS_TRACE.TRACE_ENABLED_EXCEPTIONS, если желательно трассировать возникающие при работе этих программ исключительные ситуации).

 

Пакет DBMS_TRACE более прост в работе, чем DBMS_PROFILER, но и имеет более ограниченную функциональность.

 

 

17.3.3. Функции пакета DBMS_UTILITY

Две функции из пакета DBMS_UTILITY могут быть использованы в хранимых процедурах, например, в разделе обработки исключительных состояний, в целях отладки.

 

Функция

 

DBMS_UTILITY.FORMAT_ERROR_STACK RETURN VARCHAR2;

 

возвращает содержимое магазина ошибок (объемом не более 2000 символов).

 

Функция

 

DBMS_UTILITY.FORMAT_CALL_STACK RETURN VARCHAR2;

 

возвращает содержимое магазина вызовов подпрограмм (объемом не более 2000 символов).

 

 

17.3.4. Пакет DBMS_DEBUG

Позволяет производить на сервере отладку указанного сеанса связи с Oracle путем запуска параллельно специального вспомогательного сеанса. Позволяет расставлять в программах отлаживаемого сеанса (в процедурах, функциях, пакетах, телах пакетов, триггерных процедурах, анонимных блоках, объектных типах и в телах объектных типов) контрольные точки, осуществлять пошаговое выполнение (до возникновения интересующего события), а также читать и изменять переменные программ.

 

Наиболее распространенное употребление пакета – в графических отладчиках, преимущественно третьих фирм.