Август/Сентябрь 2003
Тема номера: Объектно-реляционная технология Oracle9i
Патрик Барель
Использование подтипов
(Using Subtypes,
by Patrick Barel)
Источник: Oracle Professional, March 2003,
http://www.pinnaclepublishing.com/op/OPmag.nsf/
WebIndexByIssueSecure/ab81e6b7bba8764485256ce100676a60!OpenDocument&Login
При работе с большими объемами данных значительная часть времени тратится на создание основного цикла с курсором, который запрашивает столько данных, сколько требуется извлечь за один раз. Часто из-за этого приходится строить курсор на множестве таблиц, что существенно влияет на производительность. Однако данные можно запрашивать, когда они требуются, и хранить в кэше до тех пор, пока такие же данные не потребуются опять. В этой статье Патрик Барель рассказывает, как подтипы могут помочь при создании пакета, используемого для накопления данных в коллекции.
Предположим, из таблицы EMP требуется извлечь все записи сразу, а из таблицы DEPT только тогда, когда они требуются. После извлечения записи из таблицы DEPT сохраним запись в коллекции, например, PL/SQL-таблице, чтобы когда она потребуется в следующий раз, доступ к ней был проще. В обычном случае необходимо создать тип записи для хранения кэшируемых данных, а затем коллекцию, основанную на этом типе.
Рассмотрим следующую часть кода:
-- тип записи для хранения кэшируемых данных
TYPE dept_rt IS RECORD(
dname dept.dname%TYPE
);
-- таблица для хранения кэшируемых данных
TYPE dept_tt IS TABLE OF dept_rt
INDEX BY BINARY_INTEGER;
-- пакетная переменная для хранения кэшируемых данных
pv_dept dept_tt;
Сначала создается тип записи с полями, которые необходимо кэшировать. Затем создается коллекция (PL/SQL-таблица), основанная на этом типе. Затем создается глобальная переменная для хранения данных. Коллекция заполняется по мере необходимости при помощи функции, похожей на функцию Листинга 1.
Листинг 1. Private-функция для извлечения данных из коллекции или базы данных.
FUNCTION ppf_get_dept( pi_deptno IN dept.deptno%TYPE )
RETURN dept_rt
IS
CURSOR c_dept( p_deptno dept.deptno%TYPE )
IS
SELECT dept.dname
FROM dept
WHERE dept.deptno = p_deptno;
lv_returnvalue dept_rt;
BEGIN
BEGIN
lv_returnvalue := pv_dept( pi_deptno );
EXCEPTION
WHEN NO_DATA_FOUND THEN
--== данных еще нет к коллекции ==--
--== возвращаемся в базу данных, ==--
--== и запрашиваем необходимые данные ==--
IF c_dept%ISOPEN THEN CLOSE c_dept; END IF;
OPEN c_dept( p_deptno => pi_deptno );
FETCH c_dept INTO lv_returnvalue;
IF c_dept%FOUND THEN
--== если запись найдена, ==--
--== добавляем ее в коллекцию ==--
pv_dept( pi_deptno ) := lv_returnvalue;
ELSE
--== выдаем ошибку, т.к. данные не найдены ==--
raise_application_error(
-20001, 'Отдел не найден' );
END IF; -- c_dept%found
IF c_dept%ISOPEN THEN CLOSE c_dept; END IF;
END;
--== возвращаем запись, которая могла быть извлечена как из ==--
--== коллекции, так и из базы данных ==--
RETURN lv_returnvalue;
END ppf_get_dept;
Этот код выглядит как хорошее решение. Но что произойдет, если потребуется кэшировать другие данные таблицы? Тогда придется изменять, как описание записи, так и курсор, используемый в функции, что показано ниже:
TYPE dept_rt IS RECORD(
dname dept.dname%TYPE
, loc dept.loc%TYPE --<-- добавлено
);
...
CURSOR c_dept( p_deptno dept.deptno%TYPE )
IS
SELECT dept.dname
, dept.loc --<-- добавлено
FROM dept
WHERE dept.deptno = p_deptno;
Это не является очень большой проблемой для демонстрационного пакета, так как код можно легко просмотреть и найти места, в которых следует внести изменения. Однако, если описания расположены далеко друг от друга, то легко забыть одно из изменений. Компилятор хотя и обнаруживает ошибку, но не всегда точно указывает, в чем она заключается.
Использование подтипов
Чтобы решить эту "проблему", можно создать глобальный курсор для извлечения данных из базы данных. Затем создать тип, основанный на %ROWTYPE курсора. Для этого необходимо использование описания SUBTYPE. Рассмотрим следующий код:
CURSOR c_dept( p_deptno dept.deptno%TYPE )
IS
SELECT dept.dname
FROM dept
WHERE dept.deptno = p_deptno;
-- Тип записи для хранения кэшируемых данных
SUBTYPE dept_rt IS c_dept%ROWTYPE;
-- Табличный тип для хранения кэшируемых данных
TYPE dept_tt IS TABLE OF dept_rt
INDEX BY BINARY_INTEGER;
-- пакетная переменная для хранения кэшируемых данных
pv_dept dept_tt;
Код private-функции будет таким же, за исключением объявления курсора, которое перенесено в раздел объявления глобальных переменных тела пакета.
Теперь, если потребуется добавить еще одно поле в коллекцию, необходимо модифицировать только описание курсора, как показано ниже:
CURSOR c_dept( p_deptno dept.deptno%TYPE )
IS
SELECT dept.dname
, dept.loc -- добавлено
FROM dept
WHERE dept.deptno = p_deptno;
Таким образом, я упростил себе (и, конечно, другим) поддержку пакета.
Заметьте, что скрипты для создания пакетов базы данных, использующих подтипы, называются subtype_demo.spk, subtype_demo2.spk, subtype_demo3.spk и subtype_demo4.spk.
Производительность подтипов
Если вы решите сравнить время выполнения обычного курсора, построенного на соединении таблиц EMP и DEPT, с версией, когда данные из таблицы DEPT содержатся в коллекции, то обнаружите, что "кэш"-версия медленнее. Существуют, однако, случаи, когда она может быть весьма удобной. Предположим, например, что обрабатываются две таблицы – одна содержит подписки на журнал, а другая используется для связи с подписчиками. Таблица subscription (подписка) состоит из 100,000 записей, а связанная таблица состоит из 400,000 записей. Для 100,000 подписок имеется примерно 50,000 различных записей в связанной таблице. При простом соединении двух таблиц, извлечение всех записей занимает значительное время – иногда около 45 секунд. Если создать версию с механизмом кэширования, то для извлечения тех же данных потребуется только 15 секунд, однако большинство связанных данных будут возвращены из переменной-коллекции.
Другое использование подтипов
Подтипы могут также использоваться для создания типов, основанных на базовых типах, но с заданной точностью. Например, когда требуется вывести данные на экран, можно использовать DBMS_OUTPUT.PUT_LINE. Параметр этой процедуры - символьная строка максимальной длины 255. Чтобы знать это, не требуется запоминать максимальную длину, если создать подтип output:
SUBTYPE output IS VARCHAR2(255);
Затем, когда надо написать код, можно создать переменную, основанную на этом подтипе, и передать ее в DBMS_OUTPUT.PUT_LINE:
DECLARE
SUBTYPE output IS VARCHAR2(255);
lv_output output;
BEGIN
lv_output := 'Эта переменная - подтип'
dbms_output.put_line(lv_output);
END;
Еще подтипы можно использовать, когда надо убедиться, что переменная, представляющая количество денег, всегда будет длиной 20 цифр с точностью 2. Для этого создается подтип money:
SUBTYPE money IS NUMBER(20,2);
Это тривиальные примеры, которые, однако, могут сделать код более документируемым. Рассмотрим две функции, показанные на Листинге 2.
Листинг 2. Пакет subtype_test.
CREATE OR REPLACE PACKAGE subtype_test
IS
SUBTYPE money IS NUMBER( 20, 2 );
FUNCTION add( pi_amount IN NUMBER, pi_add IN NUMBER )
RETURN NUMBER;
FUNCTION add2( pi_amount IN money, pi_add IN money )
RETURN money;
END subtype_test;
CREATE OR REPLACE PACKAGE BODY subtype_test
IS
FUNCTION add( pi_amount IN NUMBER, pi_add IN NUMBER )
RETURN NUMBER
IS
BEGIN
RETURN( pi_amount + pi_add );
END;
FUNCTION add2( pi_amount IN money, pi_add IN money )
RETURN money
IS
BEGIN
RETURN( pi_amount + pi_add );
END;
END subtype_test;
Эти функции выполняют абсолютно одинаковые задачи; различие только во входных переменных и возвращаемом типе. При вызове функции add во входном параметре можно передать любое число, и возвратится число с максимальной точностью. При вызове функции add2 во входном параметре можно передать те же параметры, но возвратится значение с точностью, указанной в подтипе.
SET SERVEROUTPUT ON
DECLARE
lv_TEST NUMBER;
BEGIN
lv_test := subtype_test.add(10.125,2.2542);
dbms_output.put_line('number : '||to_char(lv_test));
lv_test := subtype_test.add2(10.125,2.2542);
dbms_output.put_line('money : '||to_char(lv_test));
END;
/
Результат:
number : 12.3792
money : 12.38
Во второй функции не надо объявлять переменную, округленную до 2 знаков для соответствия количеству денег, т.к. это подразумевается в подтипе. Кроме того, вторая функция говорит, как следует из спецификации, что ожидается входной параметр типа money, и в результате получаются тоже money.
По примеру Oracle ...
Oracle использует в PL/SQL множество подтипов. Если посмотреть на спецификацию пакета SYS.STANDARD, то обнаружится:
type NUMBER is NUMBER_BASE;
Это базовый числовой тип. От него Oracle производит множество различных числовых типов, таких как:
subtype FLOAT is NUMBER;
subtype REAL is FLOAT;
subtype "DOUBLE PRECISION" is FLOAT;
-- другие числовые подтипы
subtype INTEGER is NUMBER(38,0);
subtype INT is INTEGER;
subtype SMALLINT is NUMBER(38,0);
subtype DECIMAL is NUMBER(38,0);
subtype NUMERIC is DECIMAL;
subtype DEC is DECIMAL;
Отсюда видно, что в Oracle есть один базовый числовой тип (number_base), а все подтипы – это остальные типы данных, которые мы знаем (и даже, может быть, не знаем), которые основаны на базовом типе. То же самое происходит с символьными типами, типами даты и т.п. Просмотрите этот пакет, если хотите знать больше о работе Oracle и какие типы данных можно использовать в PL/SQL-коде. В этом пакете можно также найти описание множества функций, которые используются ежедневно, таких как nvl и to_number, и многие другие. Например, знаете ли вы, какое описание имеет функция USER? (Вот именно, это функция!)
function USER return varchar2 is
c varchar2(255);
begin
select user into c from sys.dual;
return c;
end;
Ограничения
Подтипы могут быть основаны на cursor%rowtype, table%rowtype, table.field%type, скалярных типах, а внутри программы - на жестком ref-курсоре. Если подтип объявить в спецификации, то его можно использовать в любом месте программы, добавляя к нему префикс - имя пакета.
Заключение
Кэширование данных может существенно повысить производительность, так как не требуется переключаться
и обращаться к базе данных. Использование подтипов упрощает поддержку пакета и делает его более прозрачным, однако всегда следует проверять, что быстрее: соединение таблиц или Кэширование данных одной из таблиц в коллекции. Если версия с использованием коллекции хотя бы немного быстрее, то я рекомендую выбрать это решение, так как данные имеют тенденцию расти, и вы обнаружите, что разница в производительности будет больше при использовании больших объемов данных. Использование подтипов может сделать код более документированным и более легким в поддержке. А если изменяется точность переменной, то исправление вносится только в одном месте.
| 
