Oracle функция возвращающая таблицу

Oracle функция возвращающая таблицу

Я создаю функцию, которая возвращает таблицу в результате. Эта функция в основном выполняет сравнение между двумя таблицами и возвращает набор данных, если две таблицы содержат разные данные. P_min_id и p_max_id являются неосведомленными и будут реализованы в будущем, как только эта функция будет работать. Я изменил исходный код с http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html. Сообщения об ошибках комментируются в следующем коде.

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

Oracle Pipelined Table Functions

Табличные функции
(Table Functions)

Табличные функции используются для возврата PL/SQL-коллекций, которые имитируют таблицы. Они могут быть запрошены как обычные таблицы с помощью функцию TABLE во фразе FROM. Обычные табличные функции требуют, чтобы коллекции перед возвращением были полностью наполнены (населены). Так как коллекции хранятся в памяти, это может стать проблемой, поскольку на большие коллекции впустую тратится много памяти и времени в ожидании возвращения первой строки. Эти узкие возможности делают обычные табличные функции непригодными в случаях масштабных ETL-операций (ETL — Extraction Transformation Load — Извлечение-Преобразование-Загрузка). Обычные табличные функции требуют создания именованной строки и табличных типов как объектов базы данных.

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

Конвейерные табличные функции)
(Pipelined Table Functions

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

Конвейерные табличные функции включают фразу PIPELINED и используют вызов PIPE ROW, чтобы вытолкнуть строки из функции, как только они создадутся, вместо построения табличной коллекции. Заметим, что вызов RETURN пустой, поскольку нет никакой коллекции, возвращаемой из функции.

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

Исключение NO_DATA_NEEDED
(NO_DATA_NEEDED Exception)

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

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

Если имеется обработчик исключений OTHERS, то он захватит исключение NO_DATA_NEEDED и выполнит некоторый код обработки ошибок, что не нужно.

Если вы планируете использовать обработчик исключений OTHERS, то для исключения NO_DATA_NEEDED необходимо задействовать специальное прерывание.

Исключение NO_DATA_NEEDED может быть также использовано для выполнения операций очистки (cleanup).

Сравнение использования памяти
(Memory Usage Comparison)

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

Сначала мы протестируем обычную табличную функцию, создав новое соединение и запросив большую коллекцию. Проверяя выделение памяти PGA как до, так и после, тест позволит нам увидеть, сколько памяти было выделено в результате проведения теста.

Затем мы повторим тест для конвейерной табличной функции.

Читайте также:  Led 55 hisense h55a6100

Сокращение памяти, используемой конвейерной табличной функцией, обусловлено тем, что она не требует разместить целую коллекцию в памяти.

Кардинальность
(Cardinality)

Oracle оценивает кардинальность (мощность, количество элементов) конвейерной табличной функции, базируясь на размере блока базы данных. Когда используется размер блока по умолчанию, оптимизатор всегда предполагает, что кардинальность — 8168 строк.

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

Есть 4 способа, чтобы исправить оценку кардинальности конвейерной табличной функции:

  • хинт CARDINALITY (9i+): Недокументирован (Undocumented)
  • хинт OPT_ESTIMATE (10g+): Недокументирован (Undocumented)
  • хинт DYNAMIC_SAMPLING (11gR1+): Вызывает полное сканирование конвейерной табличной функции для оценки количества элементов в самом запросе перед его запуском. Это очень расточительно.
  • Расширенный Оптимизатор (Extensible Optimizer) (9i+): возможность расширения оптимизатора позволяет нам сообщить ему, что нужно поддерживать кардинальность.
  • Обратная связь по количеству элементов (Cardinality Feedback): в Oracle Database 11gR2 оптимизатор замечает, отличается ли фактическое количество элементов от ожидаемой кардинальности. Последующие запросы будут нести свою кардинальность, скорректированную на основе этой обратной связи. Если предложение выбирается из разделяемого пула или экземпляр стартует заново, эта обратная связь по количеству элементов теряется. В Oracle Database 12c обратная кардинальность по количеству элементов сохраняется в табличной области SYSAUX.

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

Заметим, что параметр p_cardinality нигде не используется непосредственно в функции.

Затем мы строим тип и тело типа, чтобы установить кардинальность (количество элементов) вручную. Обратите внимание на ссылку на параметр p_cardinality в типе.

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

Мы знаем, что функция возвращает 10 строк, но оптимизатор этого не знает. Независимо от числа строк, возвращенных функцией, оптимизатор использует значение параметра p_cardinality как оценку количества элементов (кардинальности).

Неявные (теневые) типы
(Implicit (Shadow) Types)

В отличие от обычных табличных функций, конвейерные табличные функции могут быть определены с использованием типов "table" и "record", определенных в спецификации пакета.

Это представляется более правильным решением, чем построение всех типов базы данных вручную, а Oracle по умолчанию строит теневые объектные типы неявно.

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

Конвейерные табличные функции, запускаемые параллельно
(Parallel Enabled Pipelined Table Functions)

Чтобы включить параллельные конвейерные табличные функции, должны быть выполнены следующие условия.

  • Должна быть включена фраза PARALLEL_ENABLE.
  • Должен присутствовать один или более входных параметров REF CURSOR.
  • Должна иметь место фраза PARTITION BY, чтобы указать метод секционирования рабочей нагрузки. Слабо связанные ref-курсоры (weakly typed ref cursors) могут использовать только фразу PARTITION BY ANY, которая определяет секционирование рабочей нагрузки случайный образом.

Основной синтаксис показан ниже.

Чтобы увидеть это в действии, сначала надо создать и населить тестовую таблицу.

Следующий пакет определяет включенные параллельные конвейерные табличные функции, которые принимают ref-курсоры по запросу из тестовой таблицы и возвращают те же самые строки вместе с SID (системный идентификатор) сессии, которая их обработала. Можно было бы использовать слабо связанный ref-курсор, подобный SYS_REFCURSOR, но тогда мы были бы ограничены только методом секционирования SYS_REFCURSOR. Следующие три функции представляют три метода секционирования.

Читайте также:  Login invalid credentials or imap is disabled

Следующий запрос использует функцию CURSOR, чтобы преобразовать запрос к тестовой таблице в ref-курсор, который передан табличной функции в качестве параметра. Результаты группируются по SID сессий, которые обрабатывают строки. Отметим, что все строки обработаны одной и той же сессией. Почему? Потому что, хотя эта функция включена как параллельная, мы не указали ей работать параллельно.

Следующие запросы включают хинт параллельности и вызывают все эти функции.

Уровень параллелизма (DOP — degree of parallelism) может быть ниже чем тот, который указан в хинте.

Дополнительная фраза управления выходным потоком быть использована для упорядочения или кластеризации (объединение в группы) — order or cluster — данных, основанной на списке столбцов, в процессе серверной обработки. Это может быть необходимым, если существуют зависимости в данных. Например, нужно секционировать по определенному столбцу, но также и требовать, чтобы строки были обработаны в определенном порядке в рамках этого же секционирования. Расширенный синтаксис такого случая показан ниже.

Можно сделать нечто подобное:

Трансформация конвейеров
(Transformation Pipelines)

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

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

Сначала в виде плоского файла нужно выкачать из файловой системы сервера базы данных какие-либо тестовые данные.

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

Заметим, что в таблице назначения по сравнению с внешней таблицей есть два дополнительных столбца. Каждый из этих столбцов представляет шаг преобразования. Фактические преобразования в этом примере тривиальны, но следует представить, что они могут быть сложными и невыполнимыми одним SQL-предложением. Следовательно, имеет место потребность в табличных функциях.

Пакет ниже определяет два шага процесса преобразования и процедуры для его запуска.

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

  • Строки запрашиваются из внешней таблицы.
  • Строки конвертируются в ref-курсор с помощью функции CURSOR.
  • Этот ref-курсор передается на первом этапе преобразования (STEP_1).
  • Возвращаемая на шаге STEP_1 коллекция запрашивается, когда используется функция TABLE.
  • Вывод этого запроса преобразуется в ref-курсор с помощью функции CURSOR.
  • Этот ref-курсор передается на второй этап преобразования (STEP_2).
  • Возвращаемая на шаге STEP_2 коллекция запрашивается, когда используется функция TABLE.
  • Этот запрос используется для выполнения вставки в результирующую таблицу.

Применяя процедуру LOAD_DATA, можно как преобразовывать, так и загружать данные.

Читайте также:  Sega genesis gopher 2 green

Заметим, что этот пример не содержит процедуры обработки ошибок и что в нем нет хинтов параллельности, чтобы упростить запрос в процедуре LOAD_DATA.

Для получения дополнительной информации обратитесь к документации:

В промышленных системах часто требуется выполнить преобразования данных с использованием 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 кода и не просесть в плане производительности, а в ряде случаем даже ее увеличить.

Вы можете помочь и перевести немного средств на развитие сайта

Ссылка на основную публикацию
Mysql вывести последнюю запись
Это пример кода для выбора всех записей из таблицы. Может ли кто-нибудь показать мне, как выбрать последнюю запись этой таблицы?...
M3u8 чем открыть на андроид
вкл. 13 Август 2018 . Опубликовано в Менеджеры закачек M3U8 Loader - Программа скачает видео в несколько потоков. Для того...
M2040dn печатает белые листы
Сделаем качество печати Kyocera как по заводу В блоге я уже неоднократно говорил о своей любви к печатным аппаратам японской...
Mysql query error select distinct
I keep running into the same error, using MySQL. The totaal value is marked as wrong, but i have no...
Adblock detector