from psoug[@more@]
Oracle Pipelined Table Functions | |
Version 10.2 |
General | ||||||||||
Data dictionary Objects | source$
| |||||||||
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/