Базы данных Oracle - статьи
Использование CAST и табличных функций в PL/SQL
Джим Козупрински
статья была опубликована в журнале ORACLE MAGAZINE Русское издание
Оригинал: CASTing About For a Solution: Using CAST and Table Functions in PL/SQL, by Jim Czuprynski, журнал DataBase, 20 мая, 2004
Резюме. В 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-таблицами, почему бы их не попробовать!)
