Добавил:
Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
Скачиваний:
32
Добавлен:
17.04.2018
Размер:
103.42 Кб
Скачать

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 позволяют убедиться, что программа делает именно то, для чего она предназначалась, и определить, в чем состояли причины возникших ошибок.

Соседние файлы в папке Lab5