Treat oracle что это

Обновлено: 05.07.2024

В промышленных системах часто требуется выполнить преобразования данных с использованием pl/sql кода с возможностью обращения к этим данным в sql запросе. Для этого в oracle используются табличные функции.

Табличные функции – это функции возвращающие данные в виде коллекции, к которой мы можем обратиться в секции from запроса, как если бы эта коллекция была реляционной таблицей. Преобразование коллекции в реляционный набор данных осуществляется с помощью функции table().

Однако такие функции имеют один недостаток, так как в них сначала полностью наполняется коллекция, а только потом эта коллекция возвращается в вызывающую обработку. Каждая такая коллекция храниться в памяти и в высоконагруженных системах это может стать проблемой. Так же в вызывающей обработке происходит простой на время наполнения коллекции. Решить данный недостаток призваны табличные конвейерные функции.

Конвейерными функциями называются табличные функции, которые возвращают данные в виде коллекции, но делают это асинхронно, то есть получена одна запись коллекции и сразу же эта запись отдается в вызывающий код в котором она сразу же обрабатывается. В этом случае память сохраняется, простой по времени ликвидируется.

Рассмотрим, как создаются такие функции. В данном примере будет использована учебная схема hr и три ее таблицы: employees, departments, locations.

• employees — таблица сотрудников.
• departments — таблица отделов.
• locations — таблица географического местонахождения.

Данная схема и таблицы есть в каждой базовой сборке oracle по умолчанию.

В схеме hr я создам пакет test, в нем будет реализован наш код. Создаваемая функция будет возвращать данные по сотрудникам в конкретном отделе. Для этого в спецификации пакета нужно описать тип возвращаемых данных:

• employee_id – ид сотрудника
• first_name – имя
• last_name – фамилия
• email – электронный адрес
• phone_number – телефон
• salary – зарплата
• salary_recom – рекомендуемая зарплата
• department_id – ид отдела
• department_name — наименование отдела
• city – город

Далее опишем саму функцию:

Функция принимает на вход ид отдела и возвращает коллекцию созданного нами типа t_employees_table. Ключевое слово pipelined делает эту функцию конвейерной. В целом спецификация пакета следующая:

Рассмотрим тело пакета, в нем описано тело функции get_employees_dep:

В функции мы получаем набор данных по сотрудникам конкретного отдела, каждую строчку этого набора анализируем на предмет того, что если зарплата сотрудника меньше 8 000, то рекомендуемую зарплату устанавливаем в значение 10 000, дальше каждая строчка не дожидаясь окончания наполнения всей коллекции, отдается в вызывающую обработку. Обратите внимание, что в теле функции отсутствует ключевое слово return и присутствует pipe row (rec).

Осталось вызвать созданную функцию в pl/sql блоке:

Вот так вот просто с помощью конвейерных табличных функций мы получаем возможность сделать выборку, наполненную сколько угодно сложной логикой за счет использования pl/sql кода и не просесть в плане производительности, а в ряде случаем даже ее увеличить.

Стивен Фернстайн
(Substituting and Converting Object Types in a Hierarchy, by Steven Feuerstein)
Oracle Magazine RE - Июнь 2002
Источник: Oracle Professional: March 2002

В этой статье Стивен Фернстайн рассматривает преимущества и гибкость иерархий объектных типов, исследуя подставляемость и преобразование типов.

В одном из препдыдущих выпусков Oracle Professional я рассматривал одно из наиболее важных расширений языка SQL и PL/SQL: поддержку наследования объектных типов. При наследовании подтип наследует все атрибуты и методы своих супертипов - причем наследует их не только непосредственно этот подтип, но и любой подтип или потомок в результирующей иерархии объектного типа. Наследование позволяет реализовать бизнес логику на нижних уровнях иерархии и затем сделать ее автоматически доступной во всех объектных типах, выводимых из этих супертипов. Вам не придется кодировать бизнес-правила много раз, чтобы использовать их в различных объектных типах в иерархии.

Наследование также позволяет разработчикам использовать преимущество "динамического полиморфизма", это означает, что во время запуска программы, Oracle находит и выполняет "ближайший" или наиболее характерный метод в объектной иерархии, который соответствует вызову данного метода.

Что такое подставляемость?

При объявлении иерархии типов, мы начинаем с корневого типа, из которого выводятся все другие подтипы. Например, в языке Java все классы (приблизительно аналогичные "объектным типам" Oracle) происходят от корневого класса Object. В Oracle, где объектная модель располагается на вершине реляционной базы данных, не существует встроенной глобальной иерархии. Поэтому, каждый раз, когда мы работаем с объектными типами, необходимо объявлять свой собственный корень.

В этой статье мы рассмотрим очень простую иерархию типов, показанную на Рисунке 1 (и объявленную в файле food.ot, который находится в прилагаемом Download файле).

В этой иерархии тип еда (food), food_t, является корнем. Тип десерт (dessert), dessert_t, является подтипом еды, а пирожное (cake), описанное как cake_t, является в свою очередь подтипом dessert_t. Ниже приведены объявления этих типов (показаны только атрибуты, без связанных с ними PL/SQL методов):

Каждый тип имеет свои собственные характерные для данного типа атрибуты. Каждый подтип, не забывайте, также наследует атрибуты своего супертипа(ов). Поэтому, если нужно присвоить значение объекту типа cake, то необходимо указать семь атрибутов, как показано ниже:

Обратите внимание, что я отображаю и ссылаюсь на атрибуты как базового типа еда, так и подтипа пирожное. Они все одинаково доступны мне в объекте типа пирожное.

Эту иерархию нужно понимать следующим образом: пирожное является типом десерта, который, в свою очередь является типом еды. Но не все десерты являются пирожными, и не всякая еда является десертом (отложим сейчас очевидные культурные сложности, например, что-нибудь, что не считается десертом в Соединенных Штатах, может являться таковым, скажем, в Эквадоре). Любые характеристики еды приложимы к пирожному, но не все характеристики пирожного обязательно имеют смысл для еды, к примеру, такой как огурец.

После того как иерархия объявлена, мы можем работать с ней и выполнять изменения типов в этой иерархии. В некоторых случаях, возможно, потребуется выбирать и просматривать все типы по всей иерархии. В других случаях - только обновлять определенный уровень иерархии, такой как пирожные. И, наконец, бывают ситуации, когда необходимо работать с, скажем, всеми десертами, которые не являются пирожными. И это приводит нас прямо к концепции подставляемости (substitutability)

Супертип является подставляемым, если один из его подтипов может заменить его в некоторых ситуациях, например, при присваивании столбцу или программной переменной, объявленной как супертип (а не как этот конкретный подтип).

Допустим, я создаю таблицу объектов типа food_t:

Я могу теперь вставить строки в эту таблицу следующим образом:

После запуска этого кода в моей таблице будет три строки: объект еда, объект десерт и объект пирожное. В этом блоке кода, я подставляю мои подтипы вместо супертипа в двух вставках. Это не вызывает ошибки, поскольку пирожные и десерты являются типами еды.

Я могу выполнить запрос к этой таблице в SQL*Plus и он покажет мне все атрибуты типа еда (и только) для трех строк.

Я могу также использовать преимущества подставляемости в PL/SQL блоках. В следующем коде, я объявляю еду, но инициализирую ее десертом, более конкретным типом еды.

А вот пример подставляемости в PL/SQL коллекциях:

Теперь рассмотрим вставки, которые не работают. Допустим, я создал объектную таблицу десертов:

Если теперь я попытаюсь вставить объект типа еда, Oracle выдаст ошибку, как показано ниже:

Я получил эту ошибку потому, что любой десерт является едой, но не любая еда является десертом. Я не могу вставить объект типа food_t в столбец типа dessert_t.

Теперь рассмотрим аналогичную ситуацию, в PL/SQL. Я объявляю в своей программе объект типа еда и инициализирую его десертом. Обратите внимание, что я указал Y или "Yes, it sure does!" ("Да, конечно, содержит!") для атрибута contains_chocolate (содержит_шоколад). Однако, если я попытаюсь в своем коде указать этот атрибут, характерный для десерта, PL/SQL выдаст мне ошибку.

Как вы можете заметить, типы являются, как правило, подставляемыми (то есть, вы можете подставить подтип для его супертипа). Преимущества подставляемости можно использовать в объектных типах, объявленных как атрибуты объектных типов, столбцы таблицы или строки в таблицах и коллекциях.

В описании самого объектного типа Oracle не предоставляет никакого способа для отключения подставляемости; любой объектный тип является теоретически или потенциально подставляемым. С другой стороны, Oracle предлагает способ ограничить подставляемость и даже сделать ее невозможной при объявлении использования этого объектного типа.

Как отключить подставляемость

Зачем нужно отключать или ограничивать подставляемость? Я могу захотеть, чтобы таблица содержала только объекты конкретного типа внутри иерархии, а не всякие подтипы. Для обеспечения этого требования, Oracle предоставляет возможность отключать подставляемость на любом уровне для столбца или атрибута, включая встроенные атрибуты и вложенные коллекции. Для этого используется следующее предложение:

Предположим, что я создал другую таблицу, чтобы описать прием пищи, который я обслуживаю в данный день. Вот таблица:

Предложение NOT SUBSTITUTABLE используется, чтобы указать, что при задании значения для столбца appetizer нельзя использовать подтип еды. Я не хочу, чтобы кто-нибудь вставил десерт в качестве закуски.

Рассмотрим теперь код в Листинге 1. Я пытаюсь вставить два различных приема пищи. В первом INSERT'е я указываю объект типа food_t в качестве закуски. Во втором insert'е я пытаюсь подсунуть десерт в качестве закуски. Результатом выполнения является следующая ошибка:

Предложение NOT SUBSTITUTABLE можно применить и к целой объектной таблице. Листинг 2 демонстрирует эту возможность. Я создал таблицу объектов food_t, с именем brunches. В нее можно успешно вставлять объекты типа food_t, но при попытке вставить десерт в таблицу возникает та же ошибка "несовместимые типы данных".

Не существует механизма отключения подставляемости для REF столбцов.
Столбец должен быть столбцом верхнего уровня, чтобы предложение NOT SUBSTITUTABLE AT ALL LEVELS было применимо к нему. Это предложение нельзя применить к атрибуту объектного типа.

Ограничение подставляемости для конкретного подтипа

Итак, я могу отключить все уровни подставляемости, но что если необходимо отключить всю подставляемость кроме конкретного подтипа? Предположим, например, что я хочу создать PL/SQL коллекцию десертов, которая может содержать только пирожные. Или я хочу ввести правило в моей таблице meals, что все десерты должны быть пирожными. Oracle предоставляет предложение IS OF для этой цели. Вот новое объявление таблицы meals, в которой существует два различных типа ограничения подставляемости:

И теперь я смогу добавлять только такие приемы пищи, в которых десерты объявлены как пирожные. Поэтому следующий INSERT отвергается:

Оператор IS OF type можно использовать только для того, чтобы ограничить объекты строки и столбца для одного подтипа, не для нескольких. Необходимо также использовать ключевое слово ONLY, даже если это единственная альтернатива, доступная сейчас. Вы можете использовать либо IS OF type, либо NOT SUBSTITUTABLE AT ALL LEVELS для ограничения объектного столбца, но нельзя использовать и то и другое для одного и того же столбца. Очевидно, что эти ограничения можно применять к различным столбцам, как показано раньше.

Расширение и сужение объектных типов

Расширение - это присвоение, в котором объявленный тип источника является более конкретным, чем объявленный тип места назначения. Если я присваиваю объект (или экземпляр объектного типа, если говорить точнее) типа cake_t переменной типа dessert_t, то я выполняю расширение.
Сужение - это присвоение, в котором объявленный тип источника является более общим, чем объявленный тип места назначения. Если я присваиваю объект типа dessert_t переменной типа cake_t, то я выполняю операцию сужения.

Расширение является фактически "родным" для иерархий объектных типов Oracle и их свойства подставляемости. Любое пирожное является также десертом и едой. Следовательно, до тех пор, пока вы явно не ограничите подставляемость, подтип может трактоваться, храниться и обрабатываться как любой из его супертипов. Вы уже видели несколько примеров этой обработки в статье.

Давайте рассмотрим, как выполняется более сложный шаг - сужение - в SQL и PL/SQL в Oracle9i.

Сужение с TREAT

Oracle предоставляет специальную функцию TREAT, которая позволяет выполнять операцию сужения. Функция TREAT явно изменяет объявленный тип источника в присваивании на более специализированный тип или подтип в иерархии места назначения.

Для успешного сужения нужно использовать TREAT; без использования этой функции, вы не сможете сослаться на специфичные для подтипа атрибуты и методы.

Вот общий синтаксис этой функции:

где <object instance> - это значение столбца или строки коллекции данного конкретного супертипа в объектной иерархии, а <object type> - это подтип в этой иерархии.

Давайте рассмотрим несколько примеров использования TREAT. Предположим, что я вставил три строки в таблицу meal, как показано в Листинге 3. Обратите внимание, что в третьей строке я передал десерт в качестве главного блюда, одно из любимых занятий моего сына во время еды! Я смог сделать это, поскольку не ограничил подставляемость столбца main_course.

Даже если все выбранные главные блюда являются десертами, у Oracle нет возможности узнать это; столбец main_course объявлен как тип food_t. Итак, что я должен делать? Использовать функцию TREAT в списке SELECT также как в запросе! Этот запрос и результаты представлены в Листинге 4.

Я могу также использовать TREAT в DML операциях, таких как INSERT'ы и UPDATE'ы. Предположим, например, что я не хочу разрешать вставлять в таблицу meal такие строки, в которых десерт является основным блюдом. Я могу добавить ограничение на таблицу, чтобы предотвратить это, но я также могу удалить все такие строки, используя UPDATE вместе с TREAT.

Только помните, что мы пока не можем использовать TREAT вне SQL оператора, напрямую в родном PL/SQL коде. Возможно, мы получим PL/SQL TREAT в Oracle9i версии 2.

Программный компромисс относительно иерархий типов

Реализация наследования в Oracle без сомнения значительно улучшает полезность и мощность объектных типов в языке PL/SQL. Означает ли это, что многие и многие разработчики PL/SQL будут теперь использовать преимущества объектных типов и, в частности, эти замечательные новые возможности? У меня есть сомнения на этот счет, и на это - две причины:

Во-первых, многие разработчики и группы разработчиков совершенно счастливы с чисто реляционной моделью. Она делает то, что нужно, она проста, и ей легко управлять как разработчикам, так и администраторам. Конечно, объектная модель имеет некоторые преимущества, но вероятно не такие, чтобы оправдать стоимость дополнительного обучения и изменения мышления, которые потребуются.

Во-вторых, без сомнения, работа с объектной моделью включает написание более сложного кода. Вам придется иметь дело с конструкторами, и другими специальными операторами, типа TREAT, FINAL, SUBSTITUTABLE, и так далее. Вы можете потратить немало времени, чтобы стать профессионалом во всем этом, и потом все же перестать писать код, который является достаточно трудным для понимания и управления.

Итак, прежде всего, рассмотрите, что вам могут дать иерархии объектных типов. Возможно, вы обнаружите, что объектные типы прекрасно вам подходят и имеют важные преимущества. В таком случае, глубоко изучите эти возможности и обеспечьте их использование во всей их мощности. Если вы не видите в этом ничего хорошего, не расстраивайтесь. Просто оставайтесь со своими реляционными таблицами и более простыми структурами данных. Они работали на протяжении 25 лет (юбилей, который Oracle сейчас отмечает); и, вероятно, будут хороши в течение еще долгого времени.

Листинг 1. Попытка указать два приема пищи.

Листинг 2. Ограничение подставляемости в объектной таблице

Язык PL/SQL всегда поддерживал традиционные процедурные стили программирования, в частности структурное проектирование и функциональную декомпозицию. Пакеты PL/SQL позволяют использовать также объектно-ориентированный подход, применяя в работе с реляционными таблицами принципы абстракции и инкапсуляции. В новых версиях Oracle введена непосредственная поддержка объектно-ориентированного программирования (ООП). Программистам стали доступны богатая и сложная система типов, иерархия, а также взаимозаменяемость типов.

Хотя тема объектно-ориентированного программирования в Oracle могла бы стать предметом отдельной книги, мы рассмотрим лишь несколько примеров, демонстрирующих важнейшие аспекты объектно-ориентированного программирования на PL/SQL :

создание и использование объектных типов;
наследование и взаимозаменяемость;
эволюция типов;
выборка данных на основе REF -ссылок;
объектные представления, в том числе INSTEAD OF .

Не рассчитывайте найти в этой статье:

История объектных возможностей Oracle

Впервые появившиеся в 1997 году как дополнение к Oracle8 , объектные возможности позволили разработчикам расширить набор встроенных типов данных Oracle абстрактными типами данных. Также в Oracle8 были введены определяемые программистом коллекции, оказавшиеся очень удобными. Объектная модель Oracle обеспечивает много интересных возможностей, в частности доступ к данным через указатели, но она не поддерживает ни наследования, ни динамического полиморфизма, и поэтому объектно-ориентированные средства Oracle8 вряд ли произведут впечатление на приверженцев настоящего ООП. Сложность и низкая производительность объектных функций также не способствуют их успеху.

В Oracle8i была введена поддержка хранимых процедур Java , которые позволяли программировать на менее специализированном языке, чем PL/SQL , и упростили разработку хранимых процедур для сторонников ООП. Появился способ преобразования объектных типов, определенных на сервере, в Java -классы, что делало возможным совместную работу с данными в Java и в базе данных. Версия Oracle8i вышла в период наивысшего интереса к языку Java , поэтому мало кто заметил, что объектные функции Oracle почти не изменились, разве что начали понемногу интегрироваться с базовым сервером. В то время я спросил одного из представителей Oracle о будущем ООП на языке PL/SQL , и тот ответил: «Если вам требуется настоящее объектно-ориентированное программирование, пользуйтесь Java ».

Однако в Oracle9i встроенная поддержка объектов была значительно расширена. Введена поддержка наследования и полиморфизма в базах данных, PL/SQL был оснащен новыми объектными средствами. Имеет ли смысл расширять объектную модель системы на структуру базы данных? Следует ли переписать существующие приложения клиентского и промежуточного уровней? Как показано в табл. 1, в Oracle были реализованы значительные достижения в ООП, и переход на эту технологию выглядит очень заманчиво. Также в таблице перечислены полезные возможности, которые еще не реализованы .

Таблица 1. Возможности Oracle

Таблица 1 (продолжение)

В Oracle Database 10g было включено несколько полезных улучшений в области коллекций, но только одна новая возможность, относящаяся к объектным типам: она описана во врезке «Псевдостолбец OBJECT_VALUE » (см. с. 936).

Если вы еще не применяете объектно-ориентированное программирование в своих разработках, многие термины в этой таблице покажутся вам незнакомыми. Однако из оставшейся части этой статьи вы поймете их смысл и получите представление о более масштабных архитектурных решениях, которые вам придется принимать.

Я понимаю, что ROWID является уникальным значением для каждой строки в результате, возвращаемом запросом.

зачем нам этот ROWID? В ORACLE уже есть ROWNUM.

кто-нибудь использовал ROWID в SQL-запросе?

ROWID-это физическое расположение строки. Следовательно, это самый быстрый способ поиска строки, даже быстрее, чем поиск первичного ключа. Поэтому он может быть полезен в определенных типах транзакций, где мы выбираем некоторые строки, храним их ROWIDs, а затем используем ROWIDs в where предложения для DML против тех же строк.

выбор Oracle . Для синтаксиса обновления неявно используется ROWID, когда мы обновляем заблокированную строку, используя where CURRENT OF. Также Таблица исключений (ссылка при применении ограничений с исключениями в предложение) имеет столбец ROW_ID. Это позволяет нам быстро идентифицировать строки, которые нарушают наше ограничение.

этот последний пример указывает на другое общее использование: когда мы пишем некоторый общий кусок кода и нуждаемся в механизме для хранения uid без проблем относительно типа данных, составных ключей и т. д.

ROWNUM, с другой стороны, является псевдо-столбцом, который помечает строку в заданном результирующем наборе. Оно имеет никакого постоянного значения.

редактировать

ROWID для данной записи может изменяться в течение всего времени существования системы, например, через перестроение таблицы. Кроме того, если одна запись удалена, новой записи может быть присвоен этот ROWID. Следовательно, ROWIDs не подходят для использования в качестве UIDs в долгосрочной перспективе. Но они достаточно хороши для использования в рамках транзакции.

теперь я знаю пример для этого.

предположим, что у вас есть таблица без первичных ключей. таким образом, эта таблица может иметь повторяющиеся строки. Как бы вы удалили повторяющиеся строки, но сохранили точно одну из них?

Oracle предоставляет ROWID в качестве замены первичного ключа. Вы можете написать вложенный запрос, который имеет коррелированный тип [(group by all columns in The row и take MIN (ROWID) в каждой группе во внутреннем запросе, для каждой группы удалите другие строки в группе в outerquery)]

обратите внимание, что ROWID не сохраняется в цикле экспорта и импорта базы данных. вы никогда не должны хранить rowid в своих таблицах в качестве ключевого значения.

ROWID однозначно идентифицирует строку в таблице. ROWNUM дает номер строки результата для определенного запроса. Они очень различны и не взаимозаменяемы.

также есть ROW_NUMBER, который является более современной версией ROWNUM, и ведет себя немного иначе. Проверьте в этой статье что объясняет разницу.

ROWID состоит из (Но не обязательно в этом порядке, хотя часть ROWNUM является последней частью ROWID, насколько я помню):

OBJID Уникальный идентификатор объекта.
FILENO Относительное число файла данных в табличном пространстве.
в BLOCKNO Относительный номер блока в файле данных после fileheader.
этот параметр rownum Относительный rownum внутри блока.

вы можете легко сломать ROWID в составные поля (OBJID, FILENO, BLOCKNO, ROWNUM) с помощью SQL-функции ROWIDTOCHAR () или используйте:

обратите внимание, что поле ROWNUM (или строка в приведенном выше запросе) -не тот же ROWNUM, что и псевдо-столбец SQL ROWNUM, который вы используете в запросе SELECT, который является динамически генерируемым номером строки в результирующем наборе.

обратите внимание, что из-за этой реализации строки, блоки, экстенты и сегменты не переносятся не нарушая ROWID, который делает индексы недействительными.

ROWID-это самый прямой путь доступа к блоку, в котором находится строка, и уникально индентифицирует строку, потому что он кодирует уникальный файл и уникальный блок в этом файле и уникальную строку в этом блоке.

посмотреть: Примечания СУБД по формату ROWID

Если у вас есть немного понимания того, как оракул структур файлы и блоки базы данных, а также знать некоторые программирования C, вы можете довольно легко сделать программу, которая отображает содержимое блока, заданного ROWID (8k, или любой размер блока используется в базе данных, блок, который начинается с fileheadersize + BLOCKNO * BLOCK_SIZE. Блок содержит заголовок блока, а затем (если таблица не кластеризована) rowdir, который для каждой строки дает относительное смещение внутри блока для каждой строки. Так, например, в позиции 0 в rowdir является относительное смещение 0-й строки внутри блока, в позиции 1 в rowdir относительной позиции 1-й строки и т. д. Само количество строк хранится где-то в заголовке блока (см. документацию orale по компоновке блока).

имея немного знаний программирования и просматривая документацию по файлам баз данных oracle и блокам для точного расположения блоков, вы можете увидеть, как строки хранятся на диске, и даже восстановить все значения, которые хранит строка для каждый столбец. Каждая строка содержит метаданные для длины строки и количества столбцов, а для каждого столбца-указание типа столбца, байт-размера и последующего значения. Bytesize 0 означает, что данные столбца пусты (или: NULL).

ROWID в основном позволяет иметь две строки с точно такими же данными. Хотя вы обычно хотите, чтобы ваш первичный ключ был немного более значимым, чем RowID, это просто простой способ автоматического обеспечения уникальности между строками.

Читайте также: