PLSQL流函数代码 540页 【我】

540页 流函数代码

 

-- Create table
create table STOCKTABLE
(
  TICKER      VARCHAR2(10),
  TRADE_DATE  DATE,
  OPEN_PRICE  NUMBER,
  CLOSE_PRICE NUMBER
)
tablespace JTORDER
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );

-- Create table
create table TICKERTABLE
(
  TICKER    VARCHAR2(10),
  PRICEDATE DATE,
  PRICETYPE VARCHAR2(1),
  PRICE     NUMBER
)
tablespace JTORDER
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    minextents 1
    maxextents unlimited
  );



CREATE TYPE TickerType AS OBJECT(
     ticker VARCHAR2(10),
     pricedate DATE,
     pricetype VARCHAR2(1),
     price NUMBER);
     
CREATE TYPE TickerTypeSet AS TABLE OF TickerType;
     
CREATE PACKAGE refcur_pkg
IS
   TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE;
END refcur_pkg;


CREATE FUNCTION stockpivot(dataset refcur_pkg.refcur_t)
         RETURN TickerTypeSet
IS
    l_row_as_object tickertype := tickertype(NULL,null,null,null);
    l_row_from_query dataset%ROWTYPE;
    retval tickertypeset := tickertypeset();
BEGIN
    LOOP 
           FETCH dataset
                 INTO l_row_from_query;
           EXIT WHEN dataset%NOTFOUND;
           l_row_as_object.ticker := l_row_from_query.ticker;
           retval.EXTEND;
           retval(retval.LAST) := l_row_as_object;
    END LOOP;
    CLOSE dataset;
    RETURN retval;
END stockpivot;


BEGIN
    INSERT INTO tickertable
         select * from TABLE (stockpivot(CURSOR(SELECT * from stocktable)));

END;