Використання CAST і табличних функцій в PL / SQL, Інші СУБД, Бази даних, статті

Резюме: В Oracle8 i була введена функція CAST, яка дозволяє обраативать PL / SQL-колекції (collection), як звичаю таблиці. Коли CAST застосовується в поєднанні з з табличними функціями, це стає ще більш потужним засобом маніпулювання даними. Ця стаття містить короткий технологічне тлумачення роботи CAST і табличних функцій на кількох реальних прикладах, практичне використання яких може представляти інтерес.

Я допомагав одному з наших розробників розібратися з проблемою в досить складній збереженій процедурі, яка широко використовується нашим головним додатком по вводу даних, щоб визначити всі прийнятні кредитні організації, де якісь службовці потенцально можуть отримати кредит на витрати. Ось короткий перелік використовуваних при цьому бізнес-правил:


Щоб найбільш правильно поставити питання, збережена процедура, яка реалізує ці бізнес-правила, повинна повернути відповідний набір в формі посилального курсора (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.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*