Oracle вернуть таблицу из функции
Я ищу, чтобы вернуть таблицу результатов из функции Oracle. Использовать курсор было бы проще всего, но приложение, в которое я должен это сделать, не будет принимать курсор в качестве возвращаемого значения. Альтернативой является создание типа (вероятно, завернутого в пакет), который будет сопровождать эту функцию. Однако кажется несколько излишним создавать несколько типов (у меня есть 4 + функции для записи) только для того, чтобы я мог возвращать результаты таблицы. Есть ли альтернатива, которую я упускаю?
2 ответа
Я пытаюсь вставить данные в таблицу oracle из пользовательского кода magento plugin/code. ниже, но я не могу вставить одинарную кавычку со значениями. <?php $headerSql = INSERT INTO MAGE_ORDER_INTF_HEADERS_DV (HEADER_REC_ID, IMPORT_SOURCE, IMPORT_SOURCE_INSTANCE)VALUES.
Я определил пользовательский тип, который состоит, среди прочего, из неэкспортированного массива. Мне интересно, есть ли способ переопределить реализацию функции len() для моего пользовательского типа без необходимости экспорта массива? Спасибо.
UPDATE: См.Первый комментарий для решения TABLE без ограничения размера.
Возвращайте VARRAY или используйте функцию PIPELINED для запроса от них.
Для VARRAY подробности смотрите в этой статье. Пример кода оттуда:
Для PiPELINED функций проверьте здесь . Пример кода:
Вы всегда можете вернуть XML из своей функции, если это подходит разработчикам приложений.
XML может быть сгенерирован несколькими способами в Oracle, в зависимости от того, что вы установили и какую версию используете.
XMLTYPE очень полезен в определенных контекстах и может быть сгенерирован из SQL с помощью встроенных функций XMLElement, XMLAttributes, XMLAgg и т. Д. Если клиент не поддерживает XMLTYPE, его можно легко привести к значениям CLOB.
Возможно, самый простой, хотя и не лучший вариант (IMO) - использовать пакет dbms_xmlgen:
Это дает вам ваши результаты "table" в одном значении CLOB.
Похожие вопросы:
Я пытаюсь реализовать функцию, которая возвращает таблицу с той же структурой, что и входная таблица в параметре, используя PL/pgSQL (PostgreSQL 9.3). В принципе, я хочу обновить таблицу и вернуть.
У меня есть функция Oracle, которая возвращает вложенную таблицу пользовательского типа Oracle. Если я вызову функцию от разработчика TOAD или SQL с таким синтаксисом, как этот: SELECT * FROM.
Можно ли создать таблицу, имеющую столбец, но столбец без типа данных? То есть есть ли какой-то способ сделать это косвенно? Если да, то не могли бы вы привести мне один пример.
Я пытаюсь вставить данные в таблицу oracle из пользовательского кода magento plugin/code. ниже, но я не могу вставить одинарную кавычку со значениями. <?php $headerSql = INSERT INTO.
Я определил пользовательский тип, который состоит, среди прочего, из неэкспортированного массива. Мне интересно, есть ли способ переопределить реализацию функции len() для моего пользовательского.
У меня есть такой тип, как CREATE OR REPLACE TYPE MY_TYPE AS OBJECT ( id NUMBER(10, 0), name VARCHAR2(4), lastName VARCHAR2(13), address VARCHAR2(30), previousAddress VARCHAR2(80) ); и таблица из.
Как можно обновить таблицу, используя номер строки или столбца в MySQL или Oracle? Как update test set name='Ranjan' where rownum=1; это работало в Oracle, но не для любой другой строки. Вот.
В приложении GUI-элементы могут быть инициализированы с помощью операторов SQL. SELECT name $GUIElement FROM myTable WHERE Поскольку базовая структура базы данных может измениться, я.
Я использую Oracle 12c и Oracle SQL Developer для создания пакета. Мое намерение состоит в том, чтобы передать значение пакету из оператора SQL, например SELECT * FROM table(test2.get_ups(0)); . Я.
Можно ли будет вернуть тип данных SQL из класса пользовательского типа пользователя hibernate на основе данных source(SQL/PostgreSQL). заранее спасибо.
Табличной функцией SQL называется функция, которая может вызываться из секции FROM запроса, как если бы она была реляционной таблицей. Коллекции, возвращаемые табличными функциями, можно преобразовать оператором TABLE в структуру, к которой можно обращаться с запросами из языка SQL. Табличные функции особенно удобны в следующих ситуациях:
- Выполнение очень сложных преобразований данных, требующих использования PL/SQL , но с необходимостью обращаться к этим данным из команд SQL .
- Возвращение сложных результирующих наборов управляющей среде (отличной от PL/SQL ). Вы можете открыть курсорную переменную для запроса, основанного на табличной функции, чтобы управляющая среда могла выполнить выборку данных через курсорную переменную.
Табличные функции открывают массу полезных возможностей для разработчиков PL/SQL . Чтобы продемонстрировать некоторые из этих возможностей, мы поближе познакомимся с потоковыми и конвейерными табличными функциями.
Потоковые табличные функции
Потоковая передача данных позволяет переходить между процессами или стадиями без использования вспомогательных структур данных. Табличные функции в сочетании с выражением CURSOR позволяют организовать потоковую передачу данных через несколько промежуточных преобразований в одной команде SQL .
Конвейерные табличные функции
Эти функции возвращают результирующий набор в конвейерном режиме, то есть данные поступают, пока функция продолжает выполняться. Добавьте секцию PARALLEL_ENABLE в заголовок конвейерной функции — и у вас появляется функция, которая будет выполняться параллельно в параллельном запросе.
До выхода Oracle Database 12c табличные функции могли возвращать только вложенные таблицы и VARRAY . Начиная с версии 12.1 появилась возможность определения табличных функций, возвращающих ассоциативные массивы с целочисленными индексами, тип которых определяется в спецификации пакета.
Давайте посмотрим, как определяются табличные функции и как использовать их в приложениях.
Вызов функции из секции FROM
Чтобы вызвать функцию из секции FROM , необходимо сделать следующее:
- Определить тип данных RETURN функции как тип коллекции (вложенная таблица или VARRAY ).
- Убедиться в том, что все остальные параметры функции имеют режим IN и тип данных SQL . (Например, из запроса не удастся вызвать функцию, аргумент которой относится к логическому типу или типу записи.)
- Встроить вызов функции в оператор TABLE (в Oracle8i придется использовать оператор cast).
Рассмотрим простой пример использования табличной функции. Мы начнем с создания типа вложенной таблицы на базе объектного типа pets :
Затем создается функция с именем pet_family . В ее аргументах передаются два объекта pet . Далее в зависимости от значения breed возвращается вложенная таблица с информацией обо всем семействе, определенной в коллекции:
Функция pet_family тривиальна; здесь важно понять, что функция PL/SQL может содержать сколь угодно сложную логику, которая реализуется средствами PL/SQL и выходит за рамки выразительных возможностей SQL .
Теперь эта функция может вызываться в секции FROM запроса :
Часть выходных данных:
Передача результатов вызова табличной функции в курсорной переменной
Табличные функции помогают решить проблему, с которой разработчики сталкивались в прошлом, — а именно как передать данные, полученные в программе PL/SQL (то есть данные, не хранящиеся в таблицах базы данных), в управляющую среду без поддержки PL/SQL ? Курсорные переменные позволяют легко передать результирующие наборы на базе SQL , допустим, в программу Java, потому что курсорные переменные поддерживаются в JDBC . Но если сначала нужно провести сложные преобразования в PL/ SQL , как вернуть эти данные вызывающей программе?
Теперь эта проблема решается объединением мощи и гибкости табличных функций с широкой поддержкой курсорных переменных в средах без поддержки PL/SQL . Допустим, я хочу сгенеририровать данные семейства животных (полученные вызовом функции pet_family из предыдущего раздела) и передать строки данных интерфейсному приложению, написанному на Java.
Это делается очень просто:
В этой программе я воспользуюсь преимуществами предопределенного слабого курсорного типа SYS_REFCURSOR (появившегося в Oracle9i Database ) для объявления курсорной переменной. Курсорная переменная открывается вызовом OPEN FOR и связывается с запросом, построенным на базе табличной функции pet_family .
Затем курсорная переменная передается интерфейсной части Java . Так как JDBC распознает курсорные переменные, код Java легко выполняет выборку строк данных и интегрирует их в приложение.
Создание потоковой функции
Потоковая функция получает параметр с результирующим набором (через выражение CURSOR ) и возвращает результат в форме коллекции. Так как к коллекции можно применить оператор TABLE , а затем запросить данные командой SELECT , эти функции позволяют выполнить одно или несколько преобразований данных в одной команде SQL . Потоковые функции, поддержка которых добавилась в Oracle9i Database , позволяют скрыть алгоритмическую сложность за интерфейсом функции, и упростить SQL приложения. Приведенный ниже пример объясняет различные действия, которые необходимо выполнить для такого использования табличных функций.
Представьте следующую ситуацию: имеется таблица с информацией биржевых котировок, которая содержит строки с ценами на моменты открытия и закрытия биржи:
Эту информацию необходимо преобразовать в другую таблицу:
Иначе говоря, одна строка stocktable превращается в две строки tickertable . Эту задачу можно решить многими способами. Самое элементарное и традиционное решение на PL/SQL выглядит примерно так:
Также возможны решения, полностью основанные на SQL :
А теперь предположим, что для перемещения данных из stocktable в tickertable требуется выполнить очень сложное преобразование, требующее использования PL/ SQL . В такой ситуации табличная функция, используемая для передачи преобразуемых данных, потребует намного более эффективного решения.
Прежде всего, при использовании табличной функции нужно будет возвращать вложенную таблицу или массив VARRAY с данными. Я выбрал вложенную таблицу, потому что для VARRAY нужно задать максимальный размер, а я не хочу устанавливать это ограничение в своей реализации. Тип вложенной таблицы должен быть определен как тип на уровне схемы или в спецификации пакета, чтобы ядро SQL могло разрешить ссылку на коллекцию этого типа. Конечно, хотелось бы вернуть вложенную таблицу, основанную на самом определении таблицы, — то есть чтобы определение выглядело примерно так:
К сожалению, эта команда завершится неудачей, потому что %ROWTYPE не относится к числу типов, распознаваемых SQL. Этот атрибут доступен только в разделе объявлений PL/SQL . Следовательно, вместо этого придется создать объектный тип, который воспроизводит структуру реляционной таблицы, а затем определить тип вложенной таблицы на базе этого объектного типа:
Чтобы табличная функция передавала данные с одной стадии преобразования на другую, она должна получать аргумент с набором данных — фактически запрос. Это можно сделать только одним способом — передачей курсорной переменной, поэтому в списке параметров функции необходимо будет использовать тип REF CURSOR.
Я создал пакет для типа REF CURSOR , основанного на новом типе вложенной таблицы:
Работа завершается написанием функции преобразования:
Как и в случае с функцией pet_family , конкретный код не важен; в ваших программах логика преобразований будет качественно сложнее. Впрочем, основная последовательность действий с большой вероятностью будет повторена в вашем коде, поэтому я приведу краткую сводку в следующей таблице.
Итак, теперь у меня имеется функция, которая будет проделывать всю нетривиальную, но необходимую работу, и я могу использовать ее в запросе для передачи данных между таблицами:
Внутренняя команда SELECT извлекает все строки таблицы stocktable . Выражение CURSOR , в которое заключен запрос, преобразует итоговый набор в курсорную переменную, которая передается stockpivot. Функция возвращает вложенную таблицу, а оператор TABLE преобразует ее к формату реляционной таблицы, к которой можно обращаться с запросами.
Никакого волшебства, и все же выглядит немного волшебно, правда? Но вас ждет нечто еще более интересное — конвейерные функции!
Создание конвейерной функции
Конвейерной функцией называется табличная функция, которая возвращает результирующий набор как коллекцию, но делает это асинхронно с завершением самой функции. Другими словами, база данных уже не ожидает, пока функция отработает до конца и сохранит все вычисленные строки в коллекции PL/SQL, прежде чем выдать первые строки. Каждая запись, готовая к присваиванию в коллекцию, передается функцией как по конвейеру. В этом разделе описаны основы построения конвейерных табличных функций. Чтобы лучше понять, что необходимо для построения конвейерных функций, мы переработаем функцию stockpivot :
В следующей таблице перечислены некоторые изменения в исходной функциональности.
| Строки | Описание |
| 2 | По сравнению с исходной версией stockpivot добавлено ключевое слово PIPELINED |
| 4-5 | Объявление локального объекта и локальной записи, как и в первой версии. В этих строках интересно то, что не объявляется, — а именно вложенная таблица, которая будет возвращаться функцией. Намек на то, что будет дальше… |
| 7-9 | Начало простого цикла с выборкой каждой строки из курсорной переменной; цикл завершается, когда в курсоре не остается данных |
| 12-15 и 19-21 | Заполнение локального объекта для строк tickertable (на моменты открытия и закрытия) |
| 16-21 | Команда PIPE ROW (допустимая только в конвейерных функциях) немедленно передает объект, подготовленный функцией |
| 25 | В конце исполняемого раздела функция ничего не возвращает! Вместо этого она вызывает RETURN без указания значения (что прежде разрешалось только в процедурах) для возврата управления вызывающему блоку. Функция уже вернула все свои данные командами PIPE ROW |
Конвейерная функция вызывается так же, как и неконвейерная. Внешне никакие различия в поведении не проявляются (если только вы не настроили конвейерную функцию для параллельного выполнения в составе параллельного запроса — см. следующий раздел — или не включили логику, использующую асинхронное возвращение данных). Возьмем запрос, использующий псевдостолбец ROWNUM для ограничения строк, включаемых в выборку:
Мои тесты показывают, что в Oracle Database 10g и Oracle Database 11g при преобразовании 100 000 строк в 200 000 и последующем возвращении только первых 9 строк конвейерная версия завершает свою работу за 0,2 секунды, тогда как выполнение неконвейерной версии занимает 4,6 секунды.
Как видите, конвейерная передача строк работает, и обеспечивает существенный выигрыш!
Активизация параллельного выполнения функции
Одним из огромных достижений PL/SQL , появившихся в Oracle9i Database, стала возможность выполнения функций в контексте параллельных запросов. До выхода Oracle9i Database вызов функции PL/SQL в SQL переводил запрос в режим последовательного выполнения — существенная проблема для хранилищ данных большого объема. Теперь в заголовок конвейерной функции можно добавить информацию, которая подскажет исполнительному ядру, каким образом передаваемый функции набор данных следует разбить для параллельного выполнения.
В общем случае функция, предназначенная для параллельного выполнения, должна иметь один входной сильнотипизованный параметр REF CURSOR .
- Функция может выполняться параллельно, а данные, передаваемые этой функции, могут разбиваться произвольно:
В этом примере ключевое слово ANY выражает утверждение программиста о том, что результаты не зависят от порядка получения входных строк функцией. При использовании этого ключевого слова исполнительная система случайным образом разбивает данные между процессами запроса. Это ключевое слово подходит для функций, которые получают одну строку, работают с ее столбцами, а затем генерируют выходные строки по содержимому столбцов только этой строки. Если в вашей программе действуют другие зависимости, результат становится непредсказуемым.
- Функция может выполняться параллельно, все строки заданного отдела должны передаваться одному процессу, а передача осуществляется последовательно:
Oracle называет такой способ группировки записей кластерным; столбец, по которому осуществляется группировка (в данном случае department ), называется кластерным ключом. Здесь важно то, что для алгоритма несущественно, в каком порядке значений кластерного ключа он будет получать кластеры, и Oracle не гарантирует никакого конкретного порядка получения. Тем самым обеспечивается ускорение работы алгоритма по сравнению с кластеризацией и передачей строк в порядке значений кластерного ключа. Алгоритм выполняется со сложностью N вместо N log(N) , где N — количество записей.
В данном примере в зависимости от имеющейся информации о распределении значений можно выбрать между HASH (department) и RANGE (department). HASH работает быстрее, и является более естественным вариантом для использования с CLUSTER.. .BY .
- Функция должна выполняться параллельно, а строки, передаваемые конкретному процессу в соответствии с PARTITION. BY (для этого раздела), будут проходить локальную сортировку этим процессом.
Фактически происходит параллелизация сортировки, поэтому команда SELECT , используемая для вызова табличной функции, не должна содержать секции ORDER. BY (так как ее присутствие будет противоречить попытке параллелизации сортировки). Следовательно, в данном случае естественно использовать вариант RANGE в сочетании с ORDER. ..BY . Реализация будет работать медленнее, чем CLUSTER. BY , поэтому этот вариант следует использовать только в том случае, если алгоритм зависит от него.
Конструкция CLUSTER. BY не должна использоваться вместе с ORDER. BY в объявлении табличной функции. Это означает, что алгоритм, зависящий от кластеризации по одному ключу cl с последующим упорядочением набора записей с заданным значением cl, скажем, по c2, должен проходить параллелизацию с использованием ORDER. BY в объявлении табличной функции.
Продолжаем осваивать PL/pgSQL и сегодня мы рассмотрим примеры того, как можно написать функцию, которая возвращала бы не одно значение, а целую таблицу, то есть, табличные функции. Вам это может потребоваться, например, тогда, когда Вам нужно передавать в функцию параметры, выполнять какие-то расчеты и при этом нужно чтобы она вернула нам целую таблицу, так сказать уже рассчитанную, к которой в свою очередь, также можно обращаться и писать сложные запросы с различными объединениями.
Начнем с самого простого примера, в котором мы просто напишем функцию и передадим ей параметр, в результате выполнения она вернет нам таблицу, учитывая наш параметр.
Допустим, у нас есть вот такая таблица:
| id | number | name | znach |
| 1 | 111 | mike | 10 |
| 2 | 222 | peter | 20 |
| 3 | 333 | eric | 10 |
Создание табличной функции на SQL в PostgreSQL
Давайте представим, что нам нужно выбрать всех с тем значением, которое мы укажем в передаваемом параметре, допустим по полю znach. Функция будет выглядеть следующим образом:
Ключевым моментом здесь является то, что мы указываем тип возвращаемого значения TABLE и перечисляем все поля, которые мы хотим получить с указанием их типа.
Для того чтобы увидеть результат выполнения функции, выполните вот такой запрос:
где, 10 и есть то значение параметра, по которому мы хотим сделать выборку.
Передавать параметров можно много, также можно в самом запросе на выборку использовать и другие функции.
В этом примере мы использовали язык SQL без использования различных приемов программирования, например, условий (if then else), циклов (for) и других.
Создание табличной функции на PL/pgSQL
Теперь давайте попробуем написать тот же самый пример, но уже на PL/pgSQL.
Функция будет выглядеть следующим образом:
Здесь таблица возвращается с помощью цикла.
Но таким способом мы не можем передать параметр в запрос, приходиться писать статическое значение, но здесь мы можем уже использовать все возможности PL/pgSQL.
Вызывается она таким же способом, но без параметров, но параметры можно передавать для других действий в этой функции.
Создание табличной функции на PL/pgSQL с использованием курсора
А если Вы все-таки хотите предавать параметры в запрос, это можно сделать с помощью курсоров.
Кстати, можно легко написать две функции, первая просто возвращала бы таблицу, с передачей параметров, а вторая, например, как в предыдущем примере, обрабатывала таблицу, которую вернет первая функция, и соответственно выполняла какие-нибудь другие действия, но вместо запроса, как в предыдущем примере, мы будем использовать cursor, кстати, познакомимся еще и с курсорами (если Вы не хотите писать две функции, вместо вызова первой функции в курсоре, напишите запрос). Функция будет выглядеть следующим образом:
Вызывается она также как и все предыдущие:
Здесь мы с Вами объявили курсор и записали в него результат выполнения нашей функции select * from work.fun_test (var), а потом также циклом перебрали все значения. Помимо всего этого в теле цикла можно выполнять различного рода операции, доступные в PL/pgSQL, например, отработка условий if then else.
В итоге мы написали три разные функции, но результат их выполнения один и тот же. Теперь Вы знаете, как сделать так, чтобы функция могла возвращать таблицу, надеюсь, это вам поможет. Удачи!
Начиная с Oracle 8i, существуют так называемые "табличные функции" возвращающие набор данных, который можно рассматривать, как реляционную таблицу в предложении FROM. Проще говоря, конвейерные функции – это просто код, с которым вы можете обращаться как с таблицей базы данных. Конвейерные функции позволяют вам использовать конструкции типа SELECT * FROM <ФУНКЦИЯ_PLSQL>.
Oracle не позволит вернуть из произвольной табличной функции любой из обычных типов данных. Указанная в качестве возвращаемого типа функции коллекция должна иметь в качестве элемента табличный тип.
Создание табличной функции в Oracle ничем не отличается от создания обычной функции. Пример создания табличной функции представлен на рис.2.
Рис. 2 Пример табличной функции
Теперь функция может быть вызвана из оператора SELECT при помощи ключевого слова TABLE, сообщающего Oracle, что возвращаемую коллекцию следует интерпретировать как набор записей. Вызов через select предоставляет возможность использовать возможности простых запросов, такие как операторы group by, where и т.д. Пример вызова табличной функции представлен на рис.
Конвейеризованные табличные функции появились в 9 версии Oracle. Они являются надстройкой над табличными функциями.
Конвейеризованная табличная функция - это функция, которая возвращает результирующее множество в виде коллекции, но делает это итеративно. Другими словами, Oracle не ждет, когда выполнение функции закончится, накапливая все полученные строки в коллекции PL/ SQL, прежде чем вернуть их. Вместо этого записи по мере их готовности к включению в коллекцию возвращаются из функции.
Как и при работе с табличными функциями, при работе с конвейеризованными функциями необходимо предварительно создать табличный тип.
Между неконвейеризованной и конвейеризованной версиями имеются четыре синтаксических различия:
• Ключевое слово PIPELINED добавляется в заголовок функции с целью сообщить Oracle о необходимости возвращать результат немедленно, а не накапливать предварительно все результирующее множество.
• Команда PIPE ROW обозначает место, в котором функция возвращает отдельную запись.
• Ключевое слово RETURN осталось, но не делает ничего, осуществляя лишь выход из функции. Все результаты уже были переданы по конвейеру командой PIPE ROW.
• В предложении RETURN определить тип возвращаемых функцией данных как коллекцию (вложенную таблицу или VARRAY)
• Необходимо указывать типы SQL, а не типы PLSQL. Нужно создать типы SQL с помощью оператора create or replace type.
Конвейерные функции позволяют:
- получить строку, когда она будет готова. В условиях больших объёмов обрабатываемых данных, занимающих время алгоритмов обработки строк и нескольких этапов обработки это довольно важно.
- распараллелить обработку строк функцией. Например, при вставке результатов вида INSERT SELECT FROM PIPELINED FUNCTION или их последующей обработке другой функцией.
- экономия используемой памяти. Вся коллекция не нужна, хватит только той части, которая будет возвращаться из функции.
- исключение NO_DATA_NEEDED позволяет корректно освободить ресурсы и завершить процесс обработки в определённых ситуациях.
Loader
SQL*Loader инструмент для загрузки данных из внешних файлов в таблицы БД ORACLE. SQL*Loader обрабатывает широкое разнообразие форматов входных файлов и дает вам возможность управлять загрузкой записей в таблицы ORACLE. SQL*Loader загружает данные в различных форматах, выполняет фильтрацию (выборочную загрузку записей в зависимости от значений данных), и может загружать несколько таблиц одновременно. Во время выполнения SQL*Loader формирует детальный файл отчета со статистикой загрузки и может также создавать файл отброшенных записей (записи, отброшенные из-за ошибок в данных) и файл пропущенных записей (записи, которые не соответствуют критерию выбора).
Утилита загрузки SQL*Loader обладает следующими возможностями:
1. Загрузки данных по сети.
2. Загрузки данных из нескольких файлов данных в течение одной операции загрузки.
3. Загрузки данных одновременно в несколько таблиц одной операцией загрузки.
4. Указания символьного набора данных.
5. Выборочной загрузки данных (можно загружать записи в зависимости от значений отдельных полей).
6. Изменять данные перед их загрузкой, используя функции SQL.
7. Генерации уникальных последовательных значений ключа в указанных столбцах.
8. Загрузки данных с диска, кассеты или именованного шлюза.
9. Генерации замысловатых отчетов об ошибках, которые могут значительно помочь в локализации ошибок.
10. Загрузки сколь угодно сложных объектно-реляционных данных.
11. Для загрузки данных типа LOB и коллекций из вспомогательных файлов данных.
Чтобы загрузить данные из внешних файлов в БД ORACLE, нужно подготовить для SQL*Loader входную информацию 2 типов: сами данные и управляющую информацию, описывающую, как выполнять загрузку. Данные, загружаемые в БД ORACLE, должны находиться в файлах на диске. Эти файлы данных SQL*Loader должны распознаваться при загрузке. Информация для этого находится в управляющем файле. Данные и управляющая информация может быть подготовлены в отдельных файлах или вместе в одном и том же файле.
Контрольный файл
Назначение этого файла – описать данные, которые должно быть загружены. Например, он описывает:
§ имена файлов данных
§ формат файлов данных
§ поля данных в этих файлах
§ как загружать данные в таблицы (какие таблицы и колонки должно быть загружены).
Некоторая информация является обязательной (где найти данные и как они соответствуют таблицам БД), однако имеется также много опций для описания и манипулирования данными. Например, инструкции могут включать указание, как форматировать или фильтровать данные, или как генерировать уникальный № ID.
Выходная информация
1. Файл отчета (log file)
2. Файл отброшенных записей (bad file)
3. Файл пропущенных данных
Порядок загрузки данных
1. Создать таблицу в которую будут загружаться данные
2. Создать файл данных в своем текущем рабочем каталоге. Данные в файле должны быть разделены. Роль разделителей могут играть любые знаки препинания. Содержимое файла может быть следующим:
"Абалхассанбейнги Араш";"Магистрант";"Факультет технологии органических веществ\Магистранты";Абалхассанбейнги Араш.bmp
3. Создать контрольный файл SQL*Loader формата ctl в своем текущем рабочем каталоге. Его можно создать в любом текстовом редакторе. Содержимое файла:
INFILE personal.txt --имя файла с данными
INTO TABLE personal --имя таблицы
REPLACE -- метод загрузки
--задается что разделителем является “;”
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
( fio, jobtitle, subdn, img FILLER CHAR(100),
photo LOBFILE (img) TERMINATED BY EOF
Следует отметить, что колонки содержащие имя файла отмечены как FILLER, они не загружаются, но используются при определении LOBFILE, для определения места содержания LOB информации.
Ø INSERTМетод используемый по умолчанию. При этом предполагается, что таблица перед загрузкой пустая. Если в таблице есть строки данных, то выполнение загрузки будет прекращено.
Ø APPENDЭтот метод позволяет добавлять строки в таблицу таким образом, чтобы они не оказывали воздействия на уже существующие строки данных.
Ø REPLACE При использовании этого метода вначале удаляются все имеющиеся в таблице строки, а затем загружаются новые.
4. Из текущего рабочего каталога выполняется следующая команду SQL*Loader в приглашении командной строки
sqlldr имя_пользователя/пароль CONTROL=контрольный_файл.ctl
IMP/EXP, PUMP
Утилита экспорта exp.exe
Представляет собой исполняемый модуль, который находится в каталоге определенным переменной ORACLE_HOME.
Утилита читает базу данных, включая словарь данных, и записывает результаты в двоичный файл, который именуется как файл дампа экспорта (export dump file). В данном смысле можно экспортировать всю базу данных, конкретных пользователей (схемы) или конкретные таблицы вашей БД. В процессе экспорта, вы можете определиться есть ли необходимость экспортировать связанную с таблицами информацию словаря данных, такую как привилегии, индексы и ограничения. Созданный утилитой экспорта файл, будет содержать команды необходимые для полного восстановления всех выбранных объектов. Можно осуществлять полный (complete) экспорт всех таблиц БД, или только тех, которые были изменены, со времени последнего экспорта. Во втором случае экспорт будет инкрементальным или кумулятивным.
Инкрементальный (incremental) экспорт приведет к записи всех таблиц изменившихся со времени последнего экспорта, а кумулятивный (cumulative) - всех таблиц изменившихся со времени последнего полного экспорта. Также утилита предоставляет вам возможность сжимать свободные экстенты сильно фрагментированных сегментов данных. Например, когда какая-либо схема более менее сформировалась можно уничтожить ее табличное пространство предварительно все слив в экспорт, а далее восстановив табличное пространство импортировать данные.
Для работы с данным инструментом пользователь Oracle должен обладать привилегиями dba.
Формат исполнения команд выглядит таким образом:
exp … parameter_name = value … ,
exp – имя утилиты, parameter_name – список параметров с указанными значениями value.
Утилита импорта imp.exe
Утилита импорта тоже представляет собой исполняемый модуль, как и exp.exe. Однако применяется она для импорта данных в БД. Это означает, что imp.exe считывает двоичный дамп файл, созданный при экспорте утилитой exp, и запускает все находящиеся в нем команды на исполнение. Формат исполнения ничем не отличается от exp. Отличие заключается лишь в списке параметров.
Утилита экспорта expdp.exe
Утилита экспорта Oracle Pump позволяет экспортировать данные и метаданные в набор файлов операционной системы. Набор файлов дампа состоит из одного или нескольких файлов, содержащих табличные данные, метаданные объектов базы данных и управляющую информацию. Эти файлы записываются в специальном бинарном формате, поэтому они могут быть импортированы только impdp.exe.
Необходимо заметить, что данные утилиты являются более совершенными по сравнению с традиционными утилитами exp и imp. В поставку Oracle они входят начиная с версии Oracle Database 10gR1. К основным нововведениям можно отнести:
• Существенные архитектурные и функциональные усовершенствования.
• Поддержка внешних таблиц и предоставление PL/SQL API.
• Один поток expdp примерно в два раза быстрее exp.
• В отличие от утилит IMP и EXP, все файлы Data Pump создаются на сервере Oracle, а не на клиентской машине.
• Возможность параллельного выполнения извлечения или загрузки данных.
Так как файлы дампа записываются сервером базы данных, а не самой помпой данных (клиентом), то необходимо создать объекты с типом directory, для тех каталогов, в которые эти файлы будут записаны. Объектdirectory - это объект базы данных, который является синонимом соответствующего каталога в файловой системе сервера.
Можно также осуществлять экспорт по сети. При этом, данные из экземпляра базы данных записываются в набор файлов дампа на сервере.
Утилита импорта impdp.exe
Помпа данных импорта позваляет импортировать набор файлов дампа в целевую базу данных Oracle. Набор файлов дампа может быть импортирован в ту же самую базу данных, откуда был произведен, или в другую базу данных Oracle на другой системе. При импорте по сети, данные загружаются в целевую базу данных из базы-источника прямо по сети, минуя стадию файлов дампа. С помощью этого механизма можно запускать экспорт и импорт параллельно, минимизируя время, необходимое для всей этой операции. По сравнению с утилитой imp impdp в 15-45 раз быстрее. Нужно сказать, что данное отличие явно заметно при импорте очень большого объема данных.
JOB, SHEDULE
Это может быть и блок PL/SQL, и хранимая процедура, и внешняя процедура на C или JAVA.Частота выполнения задается или как однократное задание или регулярное.
Важное свойство пакета – задание попадает в очередь только после фиксации транзакции. То есть, если задание включено в транзакцию, а транзакция подверглась откату, то задание выполняться не будет.
Управление фоновыми заданиями реализуется отдельными серверными процессами (SNP), которые должны быть запущены, прежде чем будут активизированы задания. Эти процессы активизируются с установленной периодичностью, просматривают очередь и выполняют те задания, у которых настало время активизации. Одновременно может работать до 10 процессов SNP, которые являются неотъемлемой частью нашего экземпляра.
Поэтому сначала необходимо установить количество фоновых процессов для выполнения наших заданий.
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=NN,
где NN – желаемое количество процессов (допустимо от 0 до 10).
NN зависит от интенсивности использования пакета. Если у вас много снапшотов, различных заданий, то может потребоваться увеличение NN.
Еще один параметр инициализации JOB_QUEUE_INTERVAL интервал активизации фоновых процессов в секундах. Допустимо от 1 до 3600.
Основные методы:
· DBMS_JOB.SUBMIT – создание задания
· DBMS_JOB.ISUBMIT – создание задания с указанием номера
· DBMS_JOB.INSTANCE – выбор экземпляра для выполнения
· DBMS_JOB.REMOVE – удаление задания
· DBMS_JOB.WHAT – изменение задания
· DBMS_JOB.NEXT_DATE – изменение следующей даты выполнения
Задания выполняются последовательно в соответствии с заданным временем выполнения. Если у вас один процесс обработки очереди (смотрите параметр JOB_QUEUE_PROCESSESв init.ora) , а в очереди у вас несколько заданий на одно и тоже время, то очевидно задания будут выполняться последовательно, с некоторым смещение относительно заданного времени. И к тому же очередь просматривается периодически, например 30 сек. Исходя из всего этого очевидно, что если задавать относительный момент времени (например, sysdate+1), то получим медленное смещение времени выполнения регулярного задания. Поэтому, если важно точно выполнять задание в конкретный момент времени, то используйте функцию, которая всегда возвращает фиксированный момент времени (например, trunc(sysdate)+1+2/24).
Dbms_sсhedule
К версии 11 такое устройство имевшегося планировщика заданий было сочтено слишком примитивным, и в ней появился новый планировщик, DBMS_SHEDULE ,значительно более проработанный. Он использует следующие основные понятия:
q Schedule (расписание)
q Program (программа)
q Job (плановое задание = расписание + программа)
В отличие от старого планировщика, в новом «программой» может быть не только блок PL/SQL, но и хранимая процедура на PL/SQL или на Java, внешняя процедура на С или даже команда ОС. Последнее означает, что Oracle отменяет необходимость использовать специфичные для разных платформ планировщики заданий ОС. Вдобавок, сам запуск заданий получил возможность учета текущей вычислительной обстановки в СУБД, а также желаемой приоритетности среди прочих заданий.
Расписание (Schedule)
Расписание - объект базы данных, применяемый для хранения определенного расписания выполнения заданий.
Программа (Program)
Программа - объект базы данных, хранящий определенный набор действий, который впоследствии будет сопоставлен расписанию.
DBLINK
Database Link (dblink):объект базы данных (СУБД), предназначенный для доступа к объектам базы данных, управляемой другим сервером
dblink:привилегии
Dblink: user1-user2
Dblink: shared user1-user2
Dblink: global
Секцинирвание
Секционирование – метод, позволяющий хранить сегмент данных (таблица, индекс) в виде нескольких секций, причём секции-сегменты при общности логической структуры могут иметь собственные физические атрибуты.
К секционированию предъявляют следующие требования:
Прозрачность для пользователя (пользователь приложения не должен знать, работает ли он с секционированными данными или нет)
Прозрачность для разработчика (запросы в приложении не должны специальным образом модифицироваться для работы с секционированными данными)
Удобство администрирования (секционирование данных должно не осложнять жизнь администратора базы данных, а упрощать ее)
Диапазонное секционирование
Диапазонное (range) секционирование – это секционирование, при котором для каждой секции определяется диапазон значений ключа секционирования.
Ключ секционирования в диапазонном секционировании может принимать значение даты и времени, числа или текста. Для задания диапазона используется ключевое слово less than. Используются секции небольшого, примерно равного размера.
Читайте также: