В большинстве случаев, особенно в средах с хранилищами данных, загружаемые данные требуется преобразовывать для придания им более удобного для проведения анализа вида. Oracle Database 11g может помогать выполнять сложные и эффективные операции по преобразованию данных внутри самой базы данных и тем самым избавлять от необходимости прибегать к помощи внешних процессов или средств. Существует несколько способов, которыми можно выполнять преобразование данных в Oracle Database 11g. Наиболее часто применяемые способы перечислены ниже.
- Получение данных из существующих таблиц. Данные можно получать как путем их объединения или агрегирования из таблиц, находящихся в той же базе данных, так и путем их собирания из таблиц, находящихся во внешней базе данных или базе данных, которая даже не является базой данных Oracle.
- Применение SQL. SQL-методы, наподобие оператора MERGE, многотабличных вставок и табличных функций, позволяют выполнять преобразование данных прямо во время процесса загрузки.
- Использование оператора MODEL. Этот оператор помогает выполнять очень сложные вычисления при помощи наборов взаимосвязанных формул. Его применение позволяет воспринимать таблицу Oracle как n-мерный массив и задавать межстроковые ссылки без использования SQL-операций соединения (join) и объединения (union).
На заметку! Еще для выполнения сложных преобразований данных можно использовать процедурные методики PL/SQL. Главным при таком подходе является наличие в распоряжении достаточного количества времени и опыта по кодированию процессов преобразования. Кроме того, когда речь об очень больших наборах данных, этот подход является не очень эффективным по сравнению с остальными альтернативами.
В следующих разделах статьи каждый из этих основных способов преобразования данных Oracle рассматривается более подробно.
Получение данных из существующих таблиц
Получение новых преобразованных данных из существующих таблиц, находящихся в этой же или в других базах данных, является довольно распространенным подходом. Существуют два основных метода, которые можно применять для получения данных из другой таблицы. В случае если таблица создается в первый раз, можно использовать метод CTAS, а в ситуации, когда таблица существует в текущей или в другой базе данных — метод INSERT /* APPEND */ INTO...SELECT.
Метод CTAS можно применять даже тогда, когда таблицы находятся во внешних базах данных, за счет установки связей баз данных. Этот метод подразумевает просто создание новой таблицы из существующей. Во время создания новой таблицы к столбцам исходной таблицы можно применять определенные SQL-функции и тем самым преобразовывать данные по ходу процесса.
Ниже приведен простой пример, показывающий, как использовать метод CTAS:
SQL> CREATE TABLE new_employees AS SELECT e.empno, INITCAP(e.ename), e.sal*1.1, e.mgr, d.deptno, d.loc, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno; Table created. SQL>
В этом примере параметры преобразования данных указывают, что имена всех сотрудников должны начинаться с заглавной буквы (INITCAP) и что значение в столбце зарплаты должно быть увеличено на 10 процентов (sal*1.1).
В следующем примере демонстрируется использование метода INSERT /* APPEND */INTO...SELECT, т.е. загрузка данных в существующую таблицу из другой таблицы. Наличие параметров NOLOGGING и PARALLEL в этом примере гарантирует выполнение оператора массовой вставки данных чрезвычайно быстрым образом.
SQL> INSERT /*APPEND NOLOGGING PARALLEL */ INTO sales_data SELECT product_id, customer_id, TRUNC(sales_date), discount_rate, sales_quantity, sale_price FROM sales_history; SQL>
Следует обратить внимание на то, что даже несмотря на присутствие в приведенном выше операторе INSERT параметра PARALLEL, Oracle все равно может не выполнять этот оператор INSERT параллельно, потому что по умолчанию режим параллельного выполнения отключен. Для того чтобы любые вводимые DML-операторы могли считаться возможными кандидатами на параллельное выполнение, необходимо сначала воспользоваться следующим оператором:
SQL> ALTER SESSION ENABLE PARALLEL DML; Session altered. SQL>
После включения функции параллельного выполнения DML-операторов можно начинать применять в DML-операторах параметр PARALLEL, и Oracle тогда точно будет выполнять их параллельно.
Внимание! Существует несколько ограничений касательно использования функции параллельного выполнения DML-операторов. Например, ее нельзя применять для таблиц, у которых есть триггеры. Более подробную информацию об условиях, которые могут препятствовать использованию этой функции, можно найти в документации Oracle.
Применение SQL для преобразования данных
SQL-операторы тоже довольно часто применяются для выполнения различных видов преобразования данных. Преобразовывать данные можно, применяя простые операторы UPDATE, хотя в больших таблицах их выполнение может занимать приличное количество времени. Для небольших транзакций в базах данных OLTP операторы UPDATE являются вполне подходящим вариантом при необходимости выполнения преобразования данных в столбце на основании конкретных критериев. В следующих подразделах данной статьи блога рассматриваются другие распространенные способы использования SQL для преобразования данных перед их загрузкой. К их числу относятся оператор MERGE, многотабличные вставки и табличные функции.
Применение оператора MERGE
Оператор MERGE является очень мощным средством для преобразования данных, поскольку обладает функциональной возможностью, позволяющей ему проверять данные и выяснять, нуждается ли действительно данная строка в обновлении. Например, предположим, что требуется загрузить таблицу данными из какого-то источника, и необходимо, чтобы во время этой загрузки данные о клиенте вставлялись в таблицу только в том случае, если этот клиент является новым. То есть нужно, чтобы данные о клиентах, которые уже присутствуют в таблице, повторно в нее загружались, но чтобы там, где требуется, информация о клиентах обновлялась полученными новыми сведениями.
Оператор MERGE на самом деле представляет собой операцию UPDATE-ELSE-INSERT, выполняемую в рамках одного единственного SQL-оператора. Того же самого результата можно добиться и без применения оператора MERGE просто путем выполнения операции, состоящей из двух этапов: обновления всех строк, идентификатор клиента в которых совпадает с одним из тех, что уже существуют в таблице, и вставки строк, идентификатор клиента в которых не совпадает ни с одним из тех, что уже существуют в таблице. Приведенные ниже листинги иллюстрируют применение описанного метода. Этап обновления:
SQL> UPDATE catalog c SET (catalog_name, catalog_desc, catalog_category, catalog_price) = SELECT (catalog_name, catalog_desc, catalog_category, catalog_price) FROM catalog_data d WHERE c.catalog_id=d.catalog_id;
Этап вставки:
SQL> INSERT INTO catalog c SELECT * FROM catalog_data d WHERE c.catalog_id NOT IN (select catalog_id from catalog_data);
Всю предыдущую работу можно было бы выполнить и с помощью длинного блока кода PL/SQL. Процедуры PL/SQL должны были бы сопоставлять каждую строку с таблицей для выяснения, не существует ли она уже там, и на основании результатов этой проверки далее выполнять код либо для ее вставки, либо для ее обновления.
Как при применении SQL, так и при применении PL/SQL, для выполнения обновления и вставки неизбежно приходится иметь дело с непрактичной многочисленной обработкой одних и тех же данных. Оба этих метода являются довольно трудоемкими и отнимают много времени.
Оператор MERGE, иногда также называемый оператором обновления и вставки (upsert), из-за того, что он позволяет выполнять как обновление, так и вставку единственным SQL-оператором, представляет собой гораздо более эффективный способ для выполнения традиционных многоэтапных операций. Его применение почти подобно применению логики if-then-else. В листинге ниже показан пример выполнения процесса обновления и вставки с помощью оператора MERGE. В этом примере оператор MERGE, по сути, инструктирует Oracle обновлять строку, если таковая уже существует в таблице, и вставлять новую, если подобной строки в таблице еще нет.
SQL> MERGE INTO target t USING source s ON (t.product_id=s.product_id) WHEN MATCHED THEN UPDATE SET t.price=s.price, t.discount=s.discount WHEN NOT MATCHED THEN INSERT (product_id, product_code, product_desc, product_price, product_discount) VALUES (s.product_id, s.product_code, s.product_desc, s.product_price, s.product_discount);
Конструкция WHEN MATCHED THEN UPDATE SET определяет то, будет выполняться операция UPDATE или операция INSERT. При применении приведенного выше оператора обновление строки в целевой таблице будет происходить в том случае, если такая строка в ней существует. Если же такой строки нет, Oracle будет вставлять в целевую таблицу новую строку.
Помимо прямых операций вставки и обновления также можно выполнять и условные операции обновления и вставки, и при желании даже удалять некоторые строки, как описано в следующих разделах статьи блога.
Условные операторы UPDATE и INSERT
Вместо безусловной вставки и обновления всех строк таблицы может возникать необходимость сделать так, чтобы данные вставлялись и обновлялись только при соблюдении определенных условий. Оператор MERGE позволяет обеспечивать такое условное поведение, потому что теперь Oracle разрешает использовать в находящихся внутри него конструкциях UPDATE и INSERT конструкцию WHERE и указывать в ней условие, при котором должна выполняться операция обновления или вставки данных, как показано в листинге ниже (где следует также обратить внимание на конструкцию USING).
SQL> MERGE INTO products p /* Целевая таблица */
USING product_changes s /* Исходная таблица */
ON (p.prod_id = s.prod_id) /* Условие поиска/соединения */
WHEN MATCHED THEN UPDATE /* Выполнять обновление,
если условие соединения соблюдено */
SET p.prod_list_price = s.prod_new_price
WHERE p.prod_status <> 'EXPIRED' /* Условное обновление */
WHEN NOT MATCHED THEN
INSERT /* Выполнять вставку, если условие
соединения не соблюдено */
SET p.prod_list_price = s.prod_new_price
WHERE s.prod_status <> 'EXPIRED' /* Условная вставка */
Обратите внимание, что Oracle будет пропускать операцию INSERT или UPDATE в случае не удовлетворения оператором указанного в WHERE условия. То есть, как операции INSERT, так и операции UPDATE будут происходить только в том случае, если продукт не является просроченным (WHERE s.prod_status <> "EXPIRED").
Конструкция DELETE в операторе MERGE
Теперь Oracle позволяет указывать вместе с оператором MERGE необязательную конструкцию DELETE. Использовать эту конструкцию отдельно от оператора MERGE, однако, нельзя, так же как и конструкцию UPDATE или INSERT. Оператор DELETE нужно обязательно помещать внутрь оператора UPDATE. Это означает, что оператор DELETE не является глобальной конструкцией, а скорее работает в отношении только тех данных, на которые распространяется действие содержащейся в операторе MERGE конструкции UPDATE. В листинге ниже показан пример добавления конструкции DELETE внутри конструкции UPDATE.
SQL> MERGE INTO products p USING product_changes s ON (p.prod_id = s.prod_id) WHEN MATCHED THEN UPDATE SET p.prod_list_price = s.prod_new_price, p.prod_status = s.prod_new_status DELETE WHERE (p.prod_status = 'OLD_ITEM') WHEN NOT MATCHED THEN INSERT (prod_id, prod_list_price, prod_status) VALUES (s.prod_id, s.prod_new_price, s.prod_new_status);
Этот оператор будет сначала обновлять столбцы prod_list_price и prod_status таблицы product всякий раз, когда условие соединения будет оказываться истинным. В данном случае условие соединения (p.prod_id = s.prod_id) предусматривает соединение двух таблиц: product (которая является исходной) и product_changes (которая является целевой).
Ниже перечислены два аспекта, о которые следует помнить при использовании оператора DELETE.
- Действие конструкции DELETE распространяется только на те строки, которые были обновлены оператором MERGE.
- Оператор MERGE будет удалять только те строки, которые охватывает условие соединения, указанное в конструкции ON.
В приведенном примере при применении показанного оператора MERGE сначала в действие будет вступать конструкция UPDATE и, возможно, приводить к установке некоторых из значений prod_new_status в expired. После этого в действие будет вступать конструкция DELETE и приводить к удалению всех строк, значение prod_new_status которых было установлено в expired конструкцией UPDATE. Никакие другие строки со значением expired она удалять не будет, если только они не являются частью условия, определенного в конструкции ON.
Применение многотабличных вставок
Предположим, что требуется выполнить вставку данных из исходной таблицы сразу в несколько целевых таблиц, и необходимо, чтобы загрузка этих данных происходила на основании различных условий, т.е. чтобы в случае соблюдения условия A данные загружались в таблицу X, в случае соблюдения условия B — в таблицу Y и т.д. Раньше для выполнения вставки из исходной таблицы в несколько целевых нужно было писать несколько операторов INSERT. При наличии очень большого объема подлежащих вставке данных это приводило к серьезному замедлению процесса загрузки. Еще для выполнения той же задачи можно было писать код PL/SQL, но такой подход тоже замедлял процесс.
Сегодня в Oracle Database 11g предлагается специальный SQL-оператор, который называется многотабличной вставкой (multitable insert) и позволяет очень быстро выполнять условную загрузку данных из одного источника сразу в несколько целевых таблиц. Из-за того, что он все равно представляет собой обычный SQL-оператор, его можно выполнять параллельно и тем самым еще больше ускорять процесс загрузки. Многотабличные вставки могут быть как условными, так и безусловными, а также сочетать в себе одновременно и условные и безусловные операции вставки. Их структура варьируется в зависимости от того, подлежат ли загрузке в целевые таблицы все или только некоторые из строк исходной таблицы.
На заметку! Выигрыш в плане производительности от использования многотабличной вставки напрямую зависит от сложности данных и количества целевых таблиц. В Oracle заявляют, что скорость обработки от этого может повышаться на 400 и больше процентов.
Загрузка всех строк из исходной таблицы
При загрузке всех строк таблицы можно применять как безусловную вставку всех строк, так и условную вставку всех строк. Ниже приведен пример, в котором предполагается, что исходной является таблица sales_activity, и ее данные требуется загрузить сразу в две целевых таблицы: sales и cost. Для выполнения этой задачи применяется безусловная вставка с ключевыми словами INSERT ALL, означающими, что загрузке в таблицы sales и cost подлежат абсолютно все строки из исходной таблицы sales_activity.
SQL> INSERT ALL INTO target1 VALUES (product_id, customer_id, sysdate, product_quantity) INTO target2 VALUES (product_id,sysdate,product_price,product_discount) SELECT s.product_id, s.customer_id, sysdate, s.product_quantity, s.product_price, s.product_discount FROM source s;
Сразу следом за ключевыми словами INSERT ALL идут два оператора INTO, каждый из которых отвечает за выполнение вставки в свою определенную таблицу. Обратите внимание на то, что в операторе SELECT содержатся все необходимые столбцы, требующиеся обоим операторам INSERT для выполнения вставки данных в две указанных таблицы
Условная вставка всех строк из исходной таблицы похожа на безусловную, но предусматривает использование еще и ключевого слова WHEN, в котором определяются условия, при которых должна происходить вставка данных. Ниже приведен пример, показывающий как можно выполнять такой вид вставки:
SQL> INSERT ALL WHEN product_id IN(SELECT product_id FROM primary) THEN INTO target1 VALUES (product_id, customer_id, sysdate, product_quantity) WHEN product_id IN (SELECT product_id FROM secondary) THEN INTO target2 VALUES (product_id, sysdate, product_price, product_discount) SELECT s.product_id, s.customer_id, sysdate, s.product_quantity, s.product_price, s.product_discount FROM source s;
В этом примере тоже производится вставка всех строк из таблицы sales_data из-за того, что используются ключевые слова INSERT ALL.
Загрузка выборочных строк из исходной таблицы
Порой бывает необходимо загружать только некоторые строки из таблицы, как на основании соблюдения определенных условий, так и без соблюдения таковых. Делать подобное в многотабличной вставке позволяют ключевые слова INSERT FIRST. В листинге ниже приведен пример, показывающий, как выполнять загрузку в каждую целевую таблицу только некоторых строк из исходной таблицы на основании отдельного условия для каждой таблицы.
SQL> INSERT FIRST WHEN (quantity_sold > 10 AND product_id <1000) THEN INTO targetA VALUES (sysdate,product_id, customer_id, quantity_sold)) WHEN quantity_sold <= 10 and product_id >10000 THEN INTO targetB VALUES (sysdate,product_id, customer_id, quantity_sold) ELSE INTO targetC VALUES (time_id, cust_id, prod_id, sum_quantity_sold) SELECT s.time_id, s.cust_id, s.prod_id, p.prod_weight_class, SUM(amount_sold) AS sum_amount_sold, SUM(quantity_sold) AS sum_quantity_sold FROM sales s, products p WHERE s.prod_id = p.prod_id AND s.time_id = TRUNC(sysdate) GROUP BY s.time_id, s.cust_id, s.prod_id, p.prod_weight_class;
Применение табличных функций для преобразования данных
Для выполнения эффективных операций по преобразованию данных можно использовать предлагаемые Oracle табличные функции. Эти табличные функции генерируют коллекцию преобразованных строк, к которым потом можно выполнять запросы точно таким же образом, как и к обычным данным таблиц. Они являются замечательным примером предоставляемой Oracle изощренной парадигмы по выполнению преобразования во время загрузки. Табличные функции могут принимать набор строк в качестве входных данных и затем возвращать его в преобразованном виде. При выполнении запроса к табличной функции в операторе функция возвращает экземпляр типа коллекции, представляющий строки в таблице. Это может быть либо экземпляр массива VARRAY, либо экземпляр вложенной таблицы. Табличные функции позволяют без всяких проблем использовать вместе с SQL-операторами код на языке PL/SQL, C или Java.
Табличные функции делают традиционное применение вспомогательных таблиц излишним. В случае их использования, для выполнения преобразования данных перед их загрузкой в конечные таблицы внутри хранилищ данных никакие промежуточные таблицы создавать не требуется. Мощным средством для быстрого выполнения преобразования данных табличные функции делают три следующих функциональных возможности.
- Организация потока (streaming). Это возможность позволяет напрямую передавать результаты из одного процесса в другой безо всяких промежуточных шагов. Способ, которым табличная функция упорядочивает или объединяет в кластер строки, которые извлекает из аргументов курсора, называется организацией потока данных (data streaming).
- Параллельное выполнение (parallel execution). Это возможность позволяет выполнять функции одновременно в многопроцессорных системах.
- Конвейерная обработка (pipelining). Эта возможность позволяет просматривать результаты запроса итеративным образом, а не дожидаться, пока будет упакован и возвращен весь набор результатов. Следовательно, она может помочь табличным функциям сократить время отклика за счет отправки результатов частями по мере их генерации. Еще с ее помощью табличную функцию можно заставлять немедленно возвращать строки из коллекции. Исключение (иногда множества) вспомогательных таблиц и отсутствие необходимости в написании какого-либо кода вручную для обеспечения параллельной обработки делают поддерживаемую табличными функциями возможность выполнения обработки параллельным конвейерным образом очень привлекательной при загрузке и преобразовании очень больших объемов данных.
Вкратце, табличные функции могут помочь решить перечисленные ниже задачи.
- Возврат набора строк.
- Возврат набора строк инкрементным образом, так чтобы результаты можно было
- обрабатывать постепенно.
- Принятие в качестве входных данных курсора.
- Возврат результатов непрерывно, пока идет процесс преобразования.
- Обеспечение параллелизма.
Легче всего понять, что собой представляет табличная функция, можно, взяв обычную функцию Oracle. Функция Oracle вроде SUBSTR или TRANSLATE позволяет преобразовывать данные. Например, функцию SUBSTR можно использовать для отсечения части строки, как показано в следующем примере:
SQL> SELECT sysdate FROM dual; SYSDATE ======== 20-MAY-08 SQL> SELECT SUBSTR(sysdate,4,3) FROM dual; SUBSTRING(SYSDATE) =================== MAY SQL>
Табличные функции работают тем же самым образом, что и обычные функции Oracle, позволяющие преобразовывать данные. Единственное отличие между ними состоит в том, что табличные функции могут иметь гораздо более сложный вид и способны принимать в качестве входных данных курсоры и возвращать после преобразования множество строк.
Предположим, что требуется выполнить загрузку данных из таблицы с помощью оператора INSERT и что не нужно, чтобы они были в том же формате, что и в исходной таблице. В таком случае можно построить оператор INSERT с одним дополнительным (автоматическим) шагом, а именно — табличной функцией для выполнения преобразования данных после извлечения строк из исходной таблицы, но до того, как они будут вставлены в целевую таблицу. То есть применить вместо обычного оператора:
INSERT INTO целевая_таблица SELECT * FROM исходная_таблица;
такой:
INSERT INTO целевая_таблица SELECT * FROM (табличная функция(исходная_таблица));
Показанный выше оператор INSERT будет брать строки из исходной таблицы и вставлять их в целевую, но формат данных, вставляемых в целевую таблицу, будет отличаться от формата исходных данных. Табличная функция будет изменять этот формат перед выполнением оператором INSERT вставки данных в целевую таблицу.
Для примера давайте предположим, что есть исходная таблица по имени sales_data, в которой хранятся данные о магазинах холдинговой компании и количестве произведенных в каждом из них продаж с 2001 по 2002 гг.:
SQL> SELECT * FROM sales_data; STORE_NAME SALES_2001 SALES_2002 ------------------------- ---------- --------------------------------- shoe city 500000 trinkets galore 1400000 1500000 modern tools 1000000 1200000 toys and toys 800000 SQL>
Возникает необходимость извлечь данные из этой таблицы в целевую в другом формате. Новая таблица должна называться yearly_store_sales и отображать данные по количеству продаж компании другим образом, а именно — по ежегодному количеству продаж в каждом из магазинов. Например, в исходной таблице для магазина modern tools данные по продажам отображаются в одной строке и выглядят как 1000000 и 1200000. В новой же преобразованной таблице они должны отображаться в разных строках, т.е., по сути, данные должны отображаться в виде комбинаций “магазин/год продаж”. В таком случае имя компании может появляться в этой таблице и более одного раза:
SQL> CREATE TABLE yearly_store_sales 2 (store_name VARCHAR2(25), 3 sales_year NUMBER, 4* total_sales NUMBER); Table created. SQL>
Из-за того, что табличные функции возвращают наборы записей, чтобы использовать их для преобразования данных, сначала необходимо создать кое-какие специальные объектные структуры. В первую очередь потребуется создать объектный тип (object type) по имени yearly_store_sales_row для представления записей. Обратите внимание на то, что структура этого типа совпадает со структурой целевой таблицы yearly_store_sales.
SQL> CREATE TYPE yearly_store_sales_row AS 2 OBJECT( 3 store_name varchar2(25), 4 sales_year number, 5* total_sales number); Type created. SQL>
Далее необходимо создать табличный тип (table type) по имени yearly_store_sales_table на основе только что созданного объектного типа.
SQL> CREATE TYPE yearly_store_sales_table 2 AS 3 TABLE OF yearly_store_sales_row; Type created. SQL>
Оператор создания пакета, показанный в листинге ниже, выглядит немного сложно, но играет в механизме табличных функций центральную роль. Табличная функция сначала использует тип REF CURSOR для осуществления выборки входных строк, а затем преобразует данные и передает их интерактивным образом (т.е. по конвейеру).
SQL> CREATE OR REPLACE PACKAGE sales_package 2 AS 3 TYPE sales_cursor_type IS REF CURSOR 4 RETURN sales_data%ROWTYPE; 5 FUNCTION modify_sales_data 6 (INPUTDATA IN sales_cursor_type) 7 RETURN yearly_store_sales_table 8 PIPELINED; 9* END; SQL> / Package created. SQL> 1 CREATE OR REPLACE PACKAGE BODY sales_package 2 AS 3 FUNCTION modify_sales_data( 4 inputdata IN sales_cursor_type) 5 RETURN yearly_store_sales_table 6 PIPELINED IS 7 inputrec sales_data%ROWTYPE; 8 outputrow_2001 yearly_store_sales_row := yearly_store_sales_row(NULL,NULL,NULL); 9 outputrow_2002 yearly_store_sales_row := yearly_store_sales_row(NULL,NULL,NULL); 10 BEGIN 11 LOOP 12 FETCH inputdata INTO inputrec; 13 EXIT WHEN inputdata%NOTFOUND; 14 IF INPUTREC.SALES_2001 IS NOT NULL THEN 15 outputrow_2001.store_name := inputrec.store_name; 16 outputrow_2001.sales_year := 2001; 17 outputrow_2001.total_sales:= inputrec.sales_2001; 18 pipe row (outputrow_2001); 19 END IF; 20 IF INPUTREC.SALES_2002 IS NOT NULL THEN 21 outputrow_2002.store_name := inputrec.store_name; 22 outputrow_2002.sales_year := 2002; 23 outputrow_2002.total_sales:= inputrec.sales_2002; 24 pipe row (outputrow_2002); 25 END IF; 26 END LOOP; 27 RETURN; 28 END; 29* END; SQL> / Package body created. SQL>
Давайте теперь более внимательно рассмотрим каждую часть этого пакета.
- Чтобы наборы строк из исходной таблицы возвращались в качестве входных данных для табличной функции, необходимо создавать основанный на строках исходной таблицы тип REF CURSOR (ссылочный курсор). В данном примере этот тип называется sales_cursor.
- Функция modify_sales_data представляет собой табличную функцию. Она принимает только один входной параметр — REF CURSOR-тип sales_cursor, и возвращает данные в формате целевой таблицы yearly_store_sales.
- Идущее в конце ключевое слово PIPELINED означает, что данные должны равномерно протекать через процесс преобразования. По мере обработки входных данных преобразованные результаты должны непрерывно поступать в целевую таблицу.
- В теле пакета отображаются детали функции modify_sales_data. Эта функция будет преобразовывать исходную структуру данных из целевой таблицы в желаемый формат и затем вставлять их в целевую таблицу.
Ниже приведен оператор INSERT, в котором уже используется функция modify_sales_data. Обратите внимание на то, как эта функция применяется к данным из строк исходной таблицы sales_data. Она преобразует их перед вставкой в целевую таблицу yearly_store_sales.
SQL> INSERT INTO yearly_store_sales t 2 SELECT * 3 FROM TABLE(sales_package.modify_sales_data( 4 CURSOR(select store_name,sales_2001,sales_2002 5 FROM sales_data))); 6 rows created. SQL> COMMIT; Commit complete. SQL>
Применение табличных функций для добычи данных веб-служб
Веб-службы (web services) представляют собой самодостаточные модульные приложения, которые могут публиковаться и вызываться через Веб. Они могут выполнять сложные бизнес-процессы или выступать в роли поставщиков информации. Например, в Интернете часто можно встретить службы, предоставляющие информацию о погоде или состоянии дел на рынке ценных бумаг. Табличные функции могут помогать добывать данные из таких веб-служб.
Ниже приведено краткое описание того, как табличная функция может применяться для добычи информации с рынка ценных бумаг, которая публикуется в веб-сети, для обеспечения системы уведомления о ценах на акции.
- К приватной веб-службе, которая предлагается организацией, занимающейся предоставлением информации о состоянии дел на рынке ценных бумаг, осуществляется доступ для сбора сведений о ценах на акции.
- Табличная функция, принимающая в качестве входных данных тип REF CURSOR с символами акций, вызывает хранимую процедуру Java для извлечения информации о состоянии дел на рынке акций из веб-службы и преобразует необходимые сведения о ценах на акции в реляционные табличные данные. Она обрабатывает содержащуюся в REF CURSOR информацию по одной строке за раз и загружает ее в таблицу потоковым образом. Эта информация может обновляться через регулярные промежутки времени.
- Далее может использоваться код SQL или PL/SQL для добычи данных, собранных на шаге 2. Например, ниже приведен типичный SQL-оператор, который решает эту задачу:
SQL> SELECT AVG(price), MIN(price), MAX(price) FROM table(stock_service_pack.to_table (cursor(select stock_symbol from stocks )));
В листинге ниже показан внешний вид данных в новой таблице. Обратите внимание на то, как исходные данные из таблицы sales_data были преобразованы табличной функцией в другой формат.
SQL> SELECT * FROM yearly_store_sales; STORE_NAME SALES_YEAR TOTAL_SALES ---------------- ---------- ---------- shoe city 2002 500000 trinkets galore 2001 1400000 trinkets galore 2002 1500000 modern tools 2001 1000000 modern tools 2002 1200000 toys and toys 2002 800000 6 rows selected. SQL>
Вывод последнего оператора SELECT показывает, что компоновка данных в таблице yearly_store_sales выглядит не так, как в исходной таблице sales_data. Теперь у каждого магазина имеется новый столбец года, и данные по продажам за каждый год отображаются в отдельных строках. Это упрощает сравнение ежегодных показателей по продажам различных магазинов.
Этот пример довольно тривиален, но ясно иллюстрирует, как за счет применения табличных функций можно легко выполнять преобразование данных прямо во время их загрузки в другую таблицу.
Использование SQL-конструкции MODEL
Пользователи Oracle довольно часто применяют для обработки данных инструменты сторонних производителей из-за того, что у предлагаемого Oracle механизма SQL всегда не хватало изощренных возможностей моделирования для генерирования сложных отчетов. Типичным примером является использование электронных таблиц, которые позволяют применять формулы для преобразования данных в различные формы. В предыдущих версиях Oracle для генерации отчетов наподобие таких электронных таблиц требовалось либо загружать данные в соответствующие программы, например Microsoft Excel, либо использовать выделенные серверы для многомерной оперативной аналитической обработки данных (OLAP), вроде серверов Oracle Express. Использовать, к примеру, программу Excel с различными макросами для преобразования бизнес-данных в управляемые с помощью правил бизнес-модели, конечно, можно. Но предлагаемые сторонними производителями инструменты для работы с электронными таблицами являются довольно громоздкими в плане использования и требуют тратить приличное количество времени и сил на постоянное импортирование в них обновленных данных Oracle.
Специалисты по Oracle при работе со сложными хранилищами данных чаще всего прибегают к интенсивному применению многочисленных операций по соединению (join) и объединению (union) таблиц. Такие операции, конечно, помогают производить очень сложные вычисления, но обычно медленно выполняются и являются довольно дорогостоящими в вычислительном плане.
Сегодня Oracle предлагает мощную конструкцию MODEL, которая позволяет при помощи SQL-операторов распределять данные по категориям и применять к ним сложные формулы и тем самым генерировать развитые отчеты прямо внутри базы данных. Благодаря ей, теперь стало возможно создавать очень полезные аналитические запросы Oracle, обходя недостатки предоставляемого Oracle механизма SQL. С ее помощью обычные операторы SQL теперь можно применять для создания многомерных массивов и проведения над элементами массивов сложных межстроковых и межмассивных вычислений.
Конструкция MODEL обеспечивает возможность выполнения межстроковых вычислений за счет того, что позволяет создавать многомерные массивы из запрашиваемых данных и получать доступ к входящим в их состав элементам произвольным образом. Способ, которым она обращается к отдельным элементам, называется символическим обращением к элементам (symbolic cell addressing), а способ, которым она преобразует отдельные элементы при помощи формул (называемых в ней правилами) — символическим вычислением массива (symbolic array computation).
Конструкция MODEL позволяет применять к существующим данным бизнес-модели. При использовании конструкции MODEL в виде части запроса, Oracle передает все извлекаемые для выполнения этого запроса данные конструкции MODEL, а та затем преобразует их в многомерный массив и применяет к его отдельным элементам указанные бизнес-правила. После применения различных указанных пользователем бизнес-правил, Oracle получает обновленные, а также созданные новые данные. В конечном выводе, однако, массив на самом деле отображаться не будет, поскольку Oracle будет преобразовывать все новые и обновленные данные в формат строк перед тем, как передавать вывод конструкции MODEL пользователю.
Таким образом, получается, что первым шагом в запросе на базе MODEL является создание многомерного массива. В следующем разделе рассказывается об основных связанных с этим моментах.
Создание многомерных массивов
Конструкция MODEL создает многомерные массивы, которые играют центральную роль в предоставляемых ею функциональных возможностях, за счет отображения всех столбцов запроса, в котором она содержится, на три следующих группы.
Разделы (partitions). По сути, под разделом подразумевается результат, передаваемый конструкции MODEL предыдущими операциями группировки. Конструкция MODEL всегда применяется отдельно к содержащимся внутри каждого такого раздела данным.
Измерения (dimensions). Под измерением подразумевается слой метаданных, которые могут применяться к таблице для определения иерархических отношений между ее столбцами. Например, измерение REGION (регион) может содержать иерархию, состоящую из столбцов STATE (штат), COUNTY (округ) и CITY (город). Для каждой таблицы может определяться по несколько таких измерений, например, REGION (регион), TIME (время) и PRODUCT (продукт).
Показатели (measures). Под показателями подразумеваются данные таблицы фактов, на основании которых моделируется отчет, наподобие данных по продажам или инвентаризации. Совокупный показатель состоит из ряда ячеек показателей, каждая из которых охватывает свою уникальную комбинацию измерений. Например, если считать показателем данные по продажам, тогда данные по продажам моющих средств в нью-йоркском регионе за третий квартал 2008 г. будут представлять собой одну ячейку этого показателя, поскольку может существовать только одна такая уникальная комбинация из трех измерений: PRODUCT, которое в данном случае представляют моющие средства, TIME, отражающее третий квартал 2008 г., и REGION, которое соответствует нью-йоркскому региону.
В следующем разделе показано, как в механизме MODEL используются правила для изменения данных многомерного массива.
Преобразование данных массивов с помощью правил
Правилом (rule) в контексте конструкции MODEL называется любое бизнес-правило или формула, которую требуется применить к данным, содержащимся в создаваемом конструкцией MODEL массиве. Например, может возникнуть желание применить формулу для получения прогноза по продажам на следующий год на основании данных за два предыдущих года. В таком случае потребуется создать простую формулу составления прогноза, выражающую бизнес-аргументацию, и затем передать ее конструкции MODEL в виде правила. Перед вводом правил, которые конструкция MODEL должна применять к данным создаваемого ею многомерного массива, нужно обязательно указывать ключевое слово RULES. Ниже приведен пример задания простого правила в конструкции MODEL:
MODEL . . . RULES . . . (sales['ProductA', 2009] = sales['ProductA', 2007] + sales['ProductA', 2008] . . .
Это правило указывает, что количество продаж продукта ProductA в 2009 г. должно получаться путем суммирования данных по продажам этого же продукта в 2007 и 2008 гг.
Вместе с ключевым словом RULES еще можно указывать, будут ли задаваемые далее правила подразумевать выполнение преобразования существующих данных или вставку совершенно новых строк данных. По умолчанию ключевое слово RULES предусматривает применение спецификации UPSERT. То есть если ячейка показателя, указанная в левой части правила, существует, Oracle будет обновлять ее, а если нет, тогда Oracle будет создавать новую строку с соответствующими указанной ячейке значениями. Ниже приведен пример:
MODEL
. . .
RULES UPSERT
sales ('ProductA', 2009) = sales ('ProductA', 2007) + sales ('ProductA', 2009)
. . .
/* ЗДЕСЬ ИДУТ ЕЩЕ КАКИЕ-НИБУДЬ ПРАВИЛА */)
При такой спецификации правил, если в таблице или представлении уже существует строка, отображающая количество продаж продукта ProductA за 2009 г., Oracle обновит ее значениями, полученными в результате применения указанной в правиле формуле. А если такой строки не существует, Oracle создаст новую строку, отображающую прогнозируемое количество продаж продукта ProductA на 2009 г.
Если не нужно, чтобы Oracle вставляла новые строки, а только обновляла строки, которые уже существуют, можно изменять принятое по умолчанию поведение конструкции RULES, указав для всех правил опции UPDATE:
MODEL
. . .
RULES UPDATE
Sales ('ProductA', 2009) = sales ('ProductA', 2007) + sales ('ProductA', 2008)
. . .
/* ЗДЕСЬ ИДУТ ЕЩЕ КАКИЕ-НИБУДЬ ПРАВИЛА */
Два предыдущих примера продемонстрировали, как применять к правилам различные опции на уровне конструкции MODEL. Однако эти опции можно также применять и на уровне отдельных правил, как показано ниже:
RULES
(UPDATE sales ('ProductA', 2009) = sales ('ProductA', 2007) +
sales ('ProductA', 2008)
При указании опции на уровне отдельного правила, как в приведенном выше примере, использовать ключевое слово RULES не обязательно.
На заметку! В случае указания той или иной опции на уровне отдельных правил, она будет перекрывать спецификацию RULES, заданную на уровне конструкции MODEL. В случае не указания никаких опций на уровне правил, ко всем правилам будет применяться опция, которая была определена на уровне MODEL. В случае не указания никакой опции и на уровне MODEL, по умолчанию будет превалировать опция UPSERT.
При желании можно также указать, в каком порядке Oracle должна вычислять правила, с помощью следующих ключевых слов.
- SEQUENTIAL_ORDER. В таком случае Oracle будет вычислять правила в том порядке, в котором они идут в самой конструкции MODEL. Именно этот порядок и применяется для обработки правил в конструкции MODEL по умолчанию.
- AUTOMATIC_ORDER. В таком случае вместо того, чтобы вычислять правила на основании порядка их следования, Oracle будет вычислять их на основании зависимостей, которые существуют между ними в конструкции MODEL. То есть если правило A зависит от правила B, Oracle будет сначала вычислять правило B, даже несмотря на то что правило A идет перед правилом B в следующем после ключевого слова RULES перечне.
Оформление конечного вывода
В качестве вывода конструкция MODEL будет возвращать результаты применения заданных правил к многомерным массивам, которые она создала из указанных данных таблиц. Обычно в самом конце аналитического SQL-запроса на базе MODEL используется конструкция ORDER BY для более точного упорядочения вывода.
Еще при желании можно использовать после ключевого слова MODEL конструкцию RETURN UPDATED ROWS для определения того, что в выводе должны возвращаться только новые значения, которые были созданы оператором MODEL. Этими новыми значениями могут быть как просто обновленные значения столбца, так и совершенно новые созданные строки. Обратите внимание на то, что когда говорится, что конструкция MODEL будет создавать или обновлять строки, на самом деле имеется в виду, что изменения будут появляться в выводе конструкции MODEL. Конструкция MODEL не приводит ни к обновлению, ни к вставке строк в таблицы или представления. Для реального изменения данных таблицы необходимо использовать традиционные операторы INSERT, UPDATE или MERGE.
Использование конструкции MODEL
Давайте рассмотрим простой пример, демонстрирующий возможности конструкции MODEL. SQL-запрос в этом примере выглядит так:
SQL> SELECT country, product, year, sales
FROM sales_view
WHERE country IN ('Mexico', 'Canada')
MODEL
PARTITION BY (country) DIMENSION BY (product, year)
MEASURES (sale sales)
RULES
(sales['ProductA', 2009] = sales['ProductA', 2008] +
sales['ProductA',2007],
sales['ProductB', 2009] = sales['ProductB', 2008],
sales['All_Products', 2009] = sales['ProductA', 2009] +
sales['ProductB',2009])
ORDER BY country, product, year;
Показателем в этом примере являются единицы продаж. Как не трудно заметить, запрос предусматривает разбиение данных на разделы по стране и образование ячеек показателей, состоящих из комбинаций “продукт-год”. Содержащиеся в нем правила, указывают следующее:
- прогнозируемый общий объем продаж продукта ProductA в 2009 г. должен получаться путем сложения объемов продаж этого продукта в 2007 и 2008 гг.;
- прогнозируемый общий объем продаж продукта ProductB в 2009 г. должен совпадать с тем, что был в 2008 г.;
- общий объем продаж продуктов в 2009 г. должен вычисляться путем сложения прогнозируемых объемов продаж продуктов ProductA и ProductB в 2009 г.
Ниже приведен вывод, генерируемый при применении приведенного выше SQL-оператора с конструкцией MODEL:
COUNTRY PRODUCT YEAR SALES -------- ------------ ----- --------- Mexico ProductA 2006 2474.78 Mexico ProductA 2007 4333.69 Mexico ProductA 2008 4846.3 Mexico ProductA 2009 9179.99 Mexico ProductB 2006 15215.16 Mexico ProductB 2007 29322.89 Mexico ProductB 2008 81207.55 Mexico ProductB 2009 81207.55 Mexico All_Products 2009 90387.54 Canada ProductA 2006 2961.3 Canada ProductA 2007 5133.53 Canada ProductA 2008 6303.6 Canada ProductA 2009 11437.13 Canada ProductB 2006 22161.91 Canada ProductB 2007 45690.66 Canada ProductB 2008 89634.83 Canada ProductB 2009 89634.83 Canada All_Products 2009 101071.96
В процессе генерации этого вывода на самом деле происходит вот что. Конструкция SELECT сначала извлекает данные о продуктах, годе и продажах по двум странам (Мексике и Канаде) и передает их конструкции MODEL. Конструкция MODEL далее берет эти исходные данные и упорядочивает их в многомерный массив на основании значений, указанных в конструкции PARTITION BY (в которой содержится значение, представляющее название страны) и конструкции DIMENSION BY (где содержатся значения, представляющие продукт и год). После создания массива она применяет к его данным три тех правила, которые перечислены в конструкции RULES. И, наконец, после этого она генерирует результирующие строки данных, предварительно упорядочив их по стране, продукту и году. Обратите внимание на то, что в данном случае конструкция MODEL предусматривает отображение в выводе как исходных данных таблицы или представления, так и новых, которые были вычислены ею самой.
