Oracle Pipelined Table Functions

最新推荐文章于 2024-12-05 16:13:56 发布

转载最新推荐文章于 2024-12-05 16:13:56 发布 · 99 阅读

本文介绍Oracle数据库中使用管道表函数创建动态数据集的方法。通过示例展示了如何定义类型、创建包及函数，并实现股票数据表的数据转换。同时，还演示了如何生成日期列表和将逗号分隔的字符串转换为行输出。

from psoug[@more@]

Oracle Pipelined Table Functions
		Version 10.2

General

Data dictionary Objects

source$

dba_procedures	all_procedures	user_procedures
dba_source	all_source	user_source
dba_types	all_types	user_types

System Privileges

alter any procedure
create any procedure
create procedure
debug any procedure
drop any procedure
execute any procedure

select any table

Tables And Data For Demo

Table Definition

CREATE TABLE stocktable (
ticker VARCHAR2(4),
open_price NUMBER(10),
close_price NUMBER(10));

Demo Data

INSERT INTO stocktable VALUES ('ORA', 13, 14);
INSERT INTO stocktable VALUES ('MSC', 85, 92);
INSERT INTO stocktable VALUES ('SUN', 23, 18);
COMMIT;

Type Definition

CREATE OR REPLACE TYPE TickerType AS OBJECT (
ticker VARCHAR2(4),
pricetype VARCHAR2(1),
price NUMBER(10));
/

Create Table Type

CREATE OR REPLACE TYPE TickerTypeSet AS TABLE OF TickerType;
/

Create Package

CREATE OR REPLACE PACKAGE refcur_pkg IS

TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE;

END refcur_pkg;
/

Create Table Function

CREATE OR REPLACE FUNCTION stockpivot(
p refcur_pkg.refcur_t)
RETURN TickerTypeSet PIPELINED IS

out_rec TickerType := TickerType(NULL,NULL,NULL);
in_rec p%ROWTYPE;

BEGIN
LOOP
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;

out_rec.ticker := in_rec.Ticker;
out_rec.pricetype := 'O';
out_rec.price := in_rec.Open_Price;
PIPE ROW(out_rec);

out_rec.PriceType := 'C';
out_rec.price := in_rec.Close_Price;
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN;
END stockpivot;
/

desc stockpivot

col pipelined format a10

SELECT object_name, pipelined, authid
FROM user_procedures;

Sample Query

SELECT *
FROM TABLE(stockpivot(CURSOR(SELECT * FROM StockTable)));

Generate Date List

CREATE OR REPLACE TYPE date_array AS TABLE OF DATE;
/

CREATE OR REPLACE FUNCTION date_table(sdate DATE, edate DATE)
RETURN date_array PIPELINED AS

BEGIN
FOR i IN 0 .. (edate - sdate)
LOOP
PIPE ROW(sdate + i);
END LOOP;
RETURN;
END date_table;
/

desc date_table

col pipelined format a10

SELECT object_name, pipelined, authid
FROM user_procedures;

SELECT *
FROM TABLE(CAST(date_table(TRUNC(SYSDATE-30), TRUNC(SYSDATE))
AS date_array));

-- joined with another table

CREATE TABLE testdata (
datecol DATE,
someval NUMBER);

INSERT INTO testdata VALUES (TRUNC(SYSDATE-25), 25);
INSERT INTO testdata VALUES (TRUNC(SYSDATE-20), 20);
INSERT INTO testdata VALUES (TRUNC(SYSDATE-15), 15);
INSERT INTO testdata VALUES (TRUNC(SYSDATE-10), 10);
INSERT INTO testdata VALUES (TRUNC(SYSDATE-5), 5);
COMMIT;

SELECT * FROM testdata;

SELECT da.column_value AS DATECOL, td.someval
FROM TABLE(CAST(date_table(TRUNC(SYSDATE-30), TRUNC(SYSDATE))
AS date_array)) da, testdata td
WHERE da.COLUMN_VALUE = td.datecol(+);

Turning a comma delmimited list into a row-by-row output

CREATE OR REPLACE TYPE str_array AS TABLE OF VARCHAR2(10);
/

CREATE OR REPLACE FUNCTION tf(stringin VARCHAR2) RETURN str_array PIPELINED IS
i PLS_INTEGER;
str VARCHAR2(100);
tab sys.dbms_utility.uncl_array;
BEGIN
str := '"' || REPLACE(stringin, ',', '","') || '"';
sys.dbms_utility.comma_to_table(str, i, tab);

FOR j IN 1 .. 5 LOOP
PIPE ROW(TRANSLATE(tab(j),'A"','A'));
END LOOP;
RETURN;
END tf;
/

SELECT *
FROM TABLE(CAST(tf('1001,1002,1003,1004,1005')
AS str_array));

Drop Pipelined Table Functions

Drop PTF

DROP FUNCTION ;

DROP FUNCTION stockpivot;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/82387/viewspace-1026527/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/82387/viewspace-1026527/