Резюме: В Oracle8 i была введена функция CAST, которая позволяет обраатывать PL/SQL-коллекции (collection), как обычые таблицы. Когда CAST применяется в сочетании с с табличными функциями, это становится еще более мощным средством манипулирования данными. Эта статья содержит краткое технологическое толкование работы CAST и табличных функций на нескольких реальных примерах, практическое использование которых может представлять интерес.
Я помогал одному из наших разработчиков разобраться с проблемой в довольно сложной хранимой процедуре, которая широко используется нашим головным приложением по вводу данных, чтобы определить все приемлемые кредитные организации, где некие служащие потенцально могут получить кредит на расходы. Вот краткий перечень используемых при этом бизнес-правил:
- служащий - это часть отдела (department), который в свою очередь является частью отделения (division);
- каждый служащий может обратиться в от одной до пяти кредитных организаций;
- каждый отдел имеет до пяти заданных по умолчанию кредитных организаций, в которых можно взять деньги;
- каждое отделение имеет до пяти заданных по умолчанию кредитных организаций, в которых можно взять деньги;
- коль скоро списки потенциально имеющих такое право служащих, заданных по умолчанию кредитных организаций для отделов и отделенияй уже определены, то только те кредитные организации, в которых может обращаться любой служащий, следует сохранить в списке;
- из окончательного списка должны быть удалены дубли имен кредитных организаций.
Чтобы наиболее правильно поставить вопросы, хранимая процедура, которая реализует эти бизнес-правила, должна возвратить ответный набор в форме ссылочного курсора (REF CURSOR), как того требует приложение. К сожалению, приложение не может принять в качестве входного параметра ни одного типа Oracle-коллекций без существенного изменения устаревшего основанного на Powerbuilder кода.
Когда я открыл эту хранимую процедуру, я заметил, что она когда-то была конвертирована из Sybase-оригинала в нашу нынешнюю базу данных Oracle. База данных Sybase имеет несколько интересных возможностей по организации хранения временных данных - огромное, по существу, TEMP-пространство, которое постоянно доступно для использования любой хранимой процедурой. И все, кто конвертировали такую процедуру в Oracle, подражали этой методике, используя GTT (GLOBAL TEMPORARY TABLE - глобальную временную таблицу), чтобы сохранить данные.
GTT-таблицы, конечно, имеются в базе данных Oracle, но они обладают некоторыми недостатками. Во-первых, GTT - все-таки таблица, а, как я заметил, разработчики часто забывают выполнить COMMIT для фиксации изменений после записи в GTT. Кроме того, накладные издержки от создания и поддержания схемы для GTT в ситуациях, подобно описанной, часто являются слишком боьшими. В конце концов, наибольшее число записей, которые когда-либо ко мне возвращались в этом ответом наборе, было 15.
Я также столкнулся с проблемами при попытке открыть базу данных горячего резервирования в режиме READ ONLY, а затем пробовать выполнить хранимые процедуры, которые должны были использовать GTT-таблицы. Поскольку GTT-таблицы принадлежат табличному пространству SYSTEM, и это табличное пространство находится в режиме read-only (только_для_чтения). Когда же резервирная база открывается таким способом для составления отчетов, хранимые процедуры, использовавшиеся для этой цели, просто прекращали работать. Есть обходные пути, чтобы разрешить эту ситуацию, но они не очень элегантны.
К счастью, Oracle реализовал некоторые возможности, которые позволили мне преодолеть зависимость от GTT: функция CAST и способность создавать хранимые функции, которые возвращают типы PL/SQL-коллекций, известные также как табличные функции. Когда эти возможности используется в сочетании друг с другом, то формируется мощный комплект инструментальных средств, который подчиняет себе GTT-таблицы, использовавшиеся до этого способа. (Кроме того, они [CAST + табличные функции] работают настолько хорошо, даже если вам не нужно бороться с какими-то ни было GTT-таблицами, почему бы их не попробовать!)
Функция CAST
CAST часто называют pseudo-table function ( псевдотабличной функцией) , потому что она позволяет мне cast a variable ( приводить переменную ) - а именно, PL/SQL-коллекцию - в другой тип данных (datatype): в табличную структуру. К табличной структуре может быть сделан стандартный SQL-запрос точно так же, как к любой другой таблице Oracle на SQL.
Листинг 1.1 показывает пример, как может быть использована функция CAST в анонимном PL/SQL-блоке, чтобы прочитать PL/SQL-коллекцию, определенную декларируемым TYPE. CAST используется здесь для сортирки результирующих строк в обратном алфавитном порядке. Я мог бы создать TYPE как истинный объект и построить функцию сортировки для этого объекта, но CAST позволяет мне использовать добрый старый SQL, чтобы выполнить сортировку.
Листинг 1.2 показывает другой пример CAST. На сей раз я заполняю PL/SQL - коллекцию набором случайных чисел. Затем я использую CAST, чтобы набрать данные из коллекции и применить различные групповые функции, как-то SUM(), MIN() и MAX() на результирующем наборе. И опять же я мог объявить объектный тип и написать некие специальные функции группировки. Но снова я использовал CAST, чтобы сделать работу при помощи обычных групповых SQL-функций.
Табличные функции
Табличная функция - это хранимая функция, которая возвращает PL/SQL - коллекцию как результирующий набор, и функция CAST может затем этот набор читать и им манипулировать. В качестве примера табличной функции, sf_gather_cost_centers, которую я создал в Листинге 1.3, используется PL/SQL - коллекция, хранящая собранные названия кредитных организаций для указанного служащего и возвращающая список всех кредитных организаций, которыми служащий имеет право пользоваться, в зависимости от отношений его к отделу и отделению.
На Листинге 1.4 показаны три примера, которые собирают результаты табличной функции, которую я только что создал через псевдо-функциональную CAST-таблицу для более поздних манипуляций. Результирующий набор этих SQL-предложений может быть затем возвращен в ссылочном курсоре, сгенерированном с несколькими модификациями внутри существующей хранимой процедуры, как мне предписано в соответствии с первоначальными требованиями.
Конвейерные табличные функции
Табличные функции были доступны, начиная с Oracle 8i , они были расширены в Oracle 9i так, что результирующие наборы могут быть pipelined (конвейерными) . Кратко, конвейерная (pipelined) табличная функция не требует, чтобы псевдотабличная CAST- функция возвращала результирующий набор.
Листинг 1.5 показывает модифицированную версию той же самой функции, которую я создал в Листинге 1.3, а на Листинге 1.6 показаны те же самые примеры, что и на Листинге 1.4, но теперь не связанных с псевдофункцией CAST.
Заключение
CAST и табличные функции стали мощным набором инструментальных средств моей PL/SQL-среды разработки. И я рассчитаваю на ваш интерес в надежде, что вы также захотите поэкспериментировать с этими средствами. Если Вы пожелаете поработать с моими примерами, я на Листинге 2 поместил необходимый язык описания данных и DML-инструкции, чтобы изменить стандартную демонстрационную схему HR.