oracle 使用表函数进行数据转换

可使用Oracle的表函数来完成有效的数据转换。表函数产生转换过的行的一个集合，这个集合可以像普通表那样査询。Oracle表函数是Oracle复杂的装载时转换模式的一个出色例子。表函数可取一组行作为输入，并返回一组转换过的行。在语句中査询一个表函数时，函数返回一个代表表中行的集合类型的实例。此集合类型可以是一个VARRAY或嵌套表。表函数允许使用PL/SQL、C或Java与SQL,而没有任何问题。

表函数使传统的临时表成为多余。在将数据装载到最终的数据仓库表之前进行数据转换不需要创建任何中间表。下面三个特性使表函数成为快速转换数据集的强有力的手段。
★ 流：指从一个过程到另一个过程的结果直接转换而不需要任何中间步骤。表函数排序或集群从游标参数中获取的行的方法称为数据流(data streaming).
★ 并行执行：指多个处理器系统上函数的并发执行。
★ 流水线技术(pipelining)：此技术让你递归地看到一个査询的结果，而不是等待整个结果集成批返回。通过在结果分批产生时发送结果，流水线技术可帮助表函数减少响应时间。还可以通过流水线技术，选择使表函数立即从一个集合中返回行。在大规模的数据装载和转换中，取消临时表(有时是多次取消)和不需要手动编写并行处理代码，使由表函数提供的流水线并行处理非常有吸引力。

表函数可完成任务的简要汇总：
★ 返回一组行；
★ 增量返回一个结果集，使得可以逐渐地处理结果；
★ 接受游标作为输入；
★ 在进行转换时不断地返回结果；
★ 并行执行。

回想一下普通的Oracle函数，很容易理解表函数是什么。像SUBSTR或TRANSLATE这样的Oracle函数对数据进行转换。例如，可使用SUBSTR函数切出串的一部分，如下面的例子所示：
SQL> SELECT sysdate FROM dual;
SYSDATE
------------------
20-MAY-08

SQL> SELECT SUBSTR(sysdate,4,3) FROM dual;
SUBSTRING(SYSDATE)
-----------------------
MAY

表函数的工作方式与转换数据的普通Oracle函数相同。唯一不同的是，表函数可能更复杂，它们可以取游标作为输入，并在转换后返回多行。

假如需要使用INSERT语句从一个表装载数据，并且假定数据格式不需要与源表中的相同。可以使用INSERT语句与一个额外(自动)的步骤：在从源中提取数据行后，将数据插入到目标表之前，用一个表函数来转换数据。不使用语句：
INSERT INTO 目标表名 SELECT * FROM 源表名;

而应使用下面的INSERT语句：
INSERT INTO 目标表名 SELECT * FROW (表函数名(源表名));

上面的INSERT语句将从源表取出行并将它们插入目标表，被插入的数据在目标表中的格式与原来的格式不同。表函数将在INSERT操作将数据插入到目标表之前修改数据的格式。

举一个例子，假如有一个名为salesjata的原始表保存了某控股公司20OL和2002两年的货物和销售数据：
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

我们的目标是从这个表将数据提取到一个不同格式的目标表中。新表名为yearly_store_sales,它用一种不同的方式列出公司的销售数据一每个公司的销吿数据都是按年列出的。例如，在原来的表中，货物modern tools在相同的行上给出了两年的销售数据：1000000和1200000。在新的转换过的表中，这些数据应该出现在不同的行中，即数据应该示出货物/销售和年份的组合。为此，公司名在这个表中可能出现不止一次：
CREATE TABLE yearly_store_sales
(
store_name VARCHAR2(25),
sales_year NUMBER,
total_sales NUMBER
);

因为表函数返回记录组，所以需要创建某个特殊的对象结构来使用表函数进行数据转换。需要创建的第一个对象是一个名为yearly_store_sales_row的对象类型，它反映记录。请注意，此类型的结构与目标表yearly_store_sales相同.
CREATE type yearly_store_sales_row as object
(
store_name VARCHAR2(25),
sales_year NUMBER,
total_sales NUMBER
);

下一步是创建一个名为yearly_store sales_table的表类型。这个表类型是基于刚创建的对象类型的。
CREATE TYPE yearly store_sales_table
AS
TABLE OF yearly_store_sales_row;

代码清单13-12中的程序包创建语句有点复杂，但它是表函数特性的核心。表函数使用REF CURSOR来取输入行。然后转换数据并交互式地送出（即流水线输岀数据）。

创建表函数
CREATE OR REPLACE PACKAGE sales_package
AS
TYPE sales_cursor_type IS REF CURSOR
RETURN sales_data%ROWTYPE;
FUNCTION modify_sales_data
(INPUTDATA IN sales_cursor_type)
RETURN yearly_store_sales_table
PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BOOY sales_package
AS
FUNCTION modify_sales_data(
inputdata IN sales_cursor_type)
RETURN yearly_store_sales_table
PIPELINED IS
inputrec sales_data%ROWTYPE;
outputrow_2001 yearly_store_sales_row := yearly_store_sales_row(NULL,NULL,NULL);
outputrow_2002 yearly_store_sales_row := yearly_store_sales_row(NULL,NULL,NULL);
BEGIN
LOOP
FETCH inputdata INTO inputrec;
EXIT WHEN inputdata%NOTFOUND;
IF INPUTREC.SALES_2001 IS NOT NULL THEN
outputrow_2001.store_name := inputrec.store_name;
outputrow_2001.sales_year := 2001;
outputrow_2001.total_sales:= inputrec.sales_2001;
pipe row (outputrow_2001);
END IF;
IF INPUTREC.SALES_2002 IS NOT NULL THEN
outputrow_2002.store_name := inputrec.store_name;
outputrow_2002.sales_year := 2002;
outputrow_2002.total_sales:= inputrec.sales_2002;
pipe row (outputrow_2001);
END IF;
END LOOP;
RETURN;
END;
END;
/

我们来仔细看一下此程序包的每个部分。
★ 为了从源表返回行组作为表函数的输入，需要基于源表的行创建REF CURSOR。例子中的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表之前进行转换。
INSERT INTO yearly_store_sales t
SELECT *
FROM TABLE(sales_package.modify_sales_data(
CURSOR(select store_name,sales_2001,sales_2002 FROM sales_data)
));

commit;

使用表函数挖掘Web服务数据
Web服务是自初含的、模块化的应用，它可以在Web上发布和铜浦。Web服务可执行复杂的业务处理或起信息提供者的作用(例如，气象信息服务和股票市场报价服务)。表函数可帮助挖掘Web服务数据。

下面是一个如何使用表函数，挖掘在Web上发布的股票市场信息，以提供一个股票价格预警系统的提纲.
(1)访问提供股票市场信息服务的专门的Web服务，收集股票价格信息.
(2)一个表函数，使用股票符号的REF CURSOR作为输入，调用一个Java存储过程从Web服务收 集股票信息.此表函数将必要的股票价格信息转换为关系表数据.此表函数一次一行地处理REF CURSOR中的信息，并以流方式将其装载到表中。可定期更新这个信息.
(3)使用SQL和PL/SQL代码挖掘步骤2中收集的数据。例如，下面是一个使用下載到数据库表中的Web服务数据的典型的SQL语句：
SELECT AVG(price), MIN(price), MAX(price)
FROM
table(stock_service_pack.to_table(cursor(select stock_symbol from stocks)));

请注意，sales_data表中的原数据是如何被表函数转换为不同的格式的。
转换过的表
SELECT * FROM yearly_store_sales;
STORE_NAME SALES_YEAR TOTAL_SALES
------------------------------------------
shoe city 2002 500000
trinkets galore 20OL 1400000
trinkets galore 2002 1500000
modern tools 20OL 1000000
modern tools 2002 1200000
toys and toys 2002 800000

yearly_store_sales表的最后一条SELECT语句给出了一个不同于原表sales_data的数据布局。现在，每个货物都有一个新的year列，每年的销售数据位于不同的行中。这样就更容易比较各种货物每年的销售数据。

这个例子相当琐碎，但它清楚地说明了在将数据装载到另一表时，如何使用表函数方便地转换数据。