7. Дополнительные средства языка pl/sql
7.1. Объявление динамических типов переменных и записи pl/sql
Любое взаимодействие PL/SQL и сервера Oracle, выполнившего оператор языка SQL, осуществляется через курсор. Курсор, которому посредством объявления присвоено имя, называется явный (explicit). Курсор, созданный самим PL/SQL для выполнения некоторой операции, называется неявным (implicit).
В разделах 6.3 и 6.4 темы 6 было показано, как данные из курсора поместить в переменные PL/SQL. Кроме переменных данные могут размещаться в записях PL/SQL, которые позволяют собирать разные элементы данных в одно целое и тем самым скрывать сложность этих данных. Кроме того, передавать несколько переменных менее удобно, чем использовать одну запись, поля которой могут содержать информацию сразу из всех переменных.
В PL/SQL есть другая очень полезная возможность — объявление переменных динамического, или привязанного (anchored), типа, что позволяет автоматически определять, какие из хранимых программных модулей PL/SQL потребуют модификации при изменении объектов базы данных, от которых модули зависят. PL/SQL будет пытаться автоматически скомпилировать все эти модули, а те из них, которые скомпилировать не удастся, будут отмечены как непригодные для использования, или недействительные (invalid). Попытка вызвать недействительный программный модуль приводит к выводу сообщения об ошибке. Потом можно модифицировать этот модуль, чтобы привести его в соответствие с изменениями в объектах базы данных. Например, если хранимая функция использовала запись, основанную на таблице, и из таблицы был удален столбец, который использовался функцией, потребуется переписать тело функции.
Если требуется создать запись, поля которой совпадают со столбцами таблицы, или переменную, имеющую тот же тип, что и столбец таблицы, или запись, поля которой совпадают со столбцами, выбранными курсором, то PL/SQL предоставляет все необходимые для этого средства:
• синтаксис для объявления переменной PL/SQL с типом столбца:
имя_переменной имя_таблицы.имя_столбца%ТYРЕ;
• синтаксис для объявления записи с такими же полями, как и в строке таблицы:
имя_записи имя_таблицы%ROWТYРЕ;
• синтаксис для объявления записи с такими же полями, как и в курсоре:
имя_записи имя_курсора%ROWТYРЕ;
В курсорном цикле FOR используется запись на основе курсора, которую PL/SQL создает автоматически. Программист должен только дать ей имя. Запись, использовавшаяся в примере курсорного оператора FOR, приведенном в разделе 6.3, называлась product_rec. Ее поля совпадали по порядку, имени и типу со столбцами, выбранными курсором.
Чтобы создать свою собственную запись, нужно сообщить PL/SQL ее имя и структуру. Это делается в секции объявлений при помощи следующего синтаксиса:
TYPE имя_типа_записи IS RECORD
( имя_поля_1 тип_поля_1,
имя_поля_2 тип_поля_2,
…
);
Фактическое объявление записи имеет вид:
имя_переменной имя_типа_записи
Приведенный ниже комплексный пример иллюстрирует все сказанное выше.
/* Эффективность работы (performance) продавца — это текущая средняя сумма заказа в процентах от исторической средней суммы заказа для того же продавца. Status возвращает сообщение об ошибках или их отсутствии.
*/
SET SERVEROUTPUT ON
DECLARE
ТYРЕ performance_type IS RECORD
(person_code person.person_code%TYPE,
person_name person.last_name%TYPE,
current_sales NUMBER (8, 2),
perform_percent NUMBER (8,1),
status varchar2(30)
) ;
one_perform performance_type;
CURSOR person_cur IS
SELECT * FROM person;
/* Эта процедура вычисляет эффективность и текущий суммарный объем продаж для одного продавца. Информация о продавце передается через запись a_person. Если в течение дня
продавец ничего не продал, current_sales устанавливается в нуль. Если история продавца отсутствует (например, он начал работу лишь сегодня) , perform_percent устанавливается в нуль.
*/
PROCEDURE current_performance
(a_person person%ROWTYPE,
a_perform OUT performance_type)
CURSOR history_cur (pers varchar2) IS
SELECT AVG(tab2.product_price * tab1.quantity) avg_order
FROM purchase_archive tab1, product tab2
WHERE tab1.product_name = tab2.product_name
GROUP BY tab1.salesperson
HAVING tab1.salesperson = pers;
hist_rec history_cur%ROWTYPE;
current_avg_sales NUMBER(8,2) := 0;
BEGIN
a_perform.person_code := a_person.person_code;
a_perform.person_name := a_person.last_name;
a_perform.status := NULL;
BEGIN
SELECT SUM(tbl2.product_price * tbl1.quantity),
AVG tbl2.product_price * tbl1.quantity)
INTO a_perform.current_sales,
current_avg_sales
FROM purchase tbl1, product tbl2
WHERE tbl1.product_name = tbl2.product_name
GROUP BY tbl1.salesperson
HAVING tbl1.salesperson = a_person.person_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
a_perform.status := 'Current purchases exception';
a_perform.current_sales := 0;
END;
OPEN history_cur (a_person.person_code);
FETCH history_cur INTO hist_rec;
IF (history_cur%NOTFOUND)
THEN
a_perform.perform_percent := 0;
IF (a_perform.status IS NULL)
THEN
a_perform.status := 'Erroneous or no history';
END IF;
ELSE
a_perform.perform_percent := 100 * (current_avg_sales - hist_rec.avg_order)/
hist_rec.avg_order;
a_perform.status := 'All fine';
END IF;
CLOSE history_cur;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
a_perform.status := 'Exceptions found';:
END current_performance;
BEGIN
FOR person_rec IN person_cur
LOOP
current_performance(person_rec, one_perform);
dbms_output.put_line(one_perform.person_code || ' ' ||
one_perform.person_name || ' ' ||
one_perform.current_sales || ' ' ||
one_perform.perform_percent || ' ' ||
one_perform.status);
END LOOP;
END;
/
SELECT * FROM person;
SELECT * FROM purchase;
SELECT * FROM purchase_archive;
SELECT * FROM product;
В самом начале этого сценария объявлен тип записи с именем PERFORMANCE_TYPE. Добавление %TYPE к именам полей, извлекаемых из таблицы PERSON, гарантирует, что типы данных в таблице и процедуре всегда будут совпадать, даже если табличные типы данных в будущем изменятся. Затем объявляется переменная типа записи с именем ONE_PERFORM, а следом за ней — простой курсор, который выбирает все строки из таблицы PERSON.
Процедура CURRENT_PERFORMANCE принимает два параметра. Один параметр — это запись о продавце, тип которой совпадает с типом строки таблицы PERSON, а также с типом записей, выбираемых ранее объявленным курсором PERSON_CUR. Таким образом, этой процедуре можно безопасно передавать записи из курсора PERSON_CUR.
Второй параметр, A_PERFORM, объявлен как OUT, и это означает, что процедура будет производить в него запись. Процедура содержит явный курсор HISTORY_CUR, соединяющий таблицы PRODUCT и PURCHASE_ARCHIVE, чтобы найти среднюю сумму заказа для данного продавца. Это архивированные (исторические) данные, отсюда имя HISTORY_CUR. На основе этого значения будет определяться, насколько успешно продавец выполнял свои текущие продажи. HIST_REC— это переменная типа записи, основанная на курсоре HISTORY_CUR. CURRENT_AVG_SALES — среднее значение по всем текущим заказам продавца.
Выполняемая секция процедуры начинается с кода, который копирует фамилию продавца и его личный код в выходной параметр A_PERFORM. Затем статусу присваивается начальное значение NULL.
Далее создается неявный курсор, который подсчитывает общий объем текущих продаж и текущую среднюю сумму заказа для продавца, данные о котором получены из входного параметра A_PERSON. Эта часть кода расположена в своем собственном базовом блоке, чтобы можно было перехватывать исключения, возбуждаемые только этим кодом.
Затем курсор HISTORY_CUR открывается и из него выбираются данные. Производится единственная выборка, поскольку для одного продавца может существовать только одна сводная запись. Обратите также внимание на использование псевдонима столбца AVG_ORDER. Курсор принимает личный код продавца в качестве параметра, поэтому код курсора может многократно использоваться для разных продавцов. В архиве может не окажется данных для указанного продавца, если он был только что принят на работу. Могут возникнуть и другие ошибки, в результате которых курсор не вернет ни одной записи. Если найдены верные исторические данные, то вычисляется процентное отношение текущей средней суммы заказа к ее историческому значению и помещаем его в поле A_PERFORM.PERFORM_PERCENT.
В заключение курсор HISTORY_CUR закрывается. Секция исключений предназначена для перехвата непредвиденных исключений NO_DATA_FOUND, которые не были перехвачены ранее.
Выполняемая секция главного анонимного блока циклически выбирает записи из курсора PERSON_CUR, вызывает для каждой записи процедуру и распечатывает результаты.
Заключительные операторы SELECT позволяют убедиться, что программа делает именно то, для чего она предназначалась, и определить, в чем состояли причины возникших ошибок.