pipelined.sql

Oracle PLSQL for DBAs 源代码

CREATE FUNCTION super_complex_validation ( p_dt DATE,
                                           p_amt NUMBER )
       RETURN BOOLEAN IS
BEGIN
  RETURN(TRUE);
END;
/

CREATE TABLE acct_transactions
(area VARCHAR2(10),
 trx_date DATE,
 trx_amt NUMBER);

CREATE TYPE area_summary_o AS OBJECT ( area VARCHAR2(10),
                                       amt  NUMBER );
/
CREATE TYPE area_summary_t AS TABLE OF area_summary_o;
/

CREATE OR REPLACE FUNCTION area_summary ( p_cursor SYS_REFCURSOR )
                  RETURN area_summary_t
                  PIPELINED
                  PARALLEL_ENABLE ( PARTITION p_cursor BY ANY ) AS

  /*
    || Demonstrate pipelined functions when complex processing is required
    || on each and every queried record
  */

  v_row acct_transactions%ROWTYPE;
  v_total NUMBER := NULL;
  v_area acct_transactions.area%TYPE;

BEGIN

  -- for every transaction
  FETCH p_cursor INTO v_row;
  LOOP
    EXIT WHEN p_cursor%NOTFOUND;

    -- if we pass the extensive validation check
    IF super_complex_validation(v_row.trx_date,v_row.trx_amt) THEN

    -- set initial total or add to current area total
    -- or return an area total as required
      IF v_total IS NULL THEN
        v_total := v_row.trx_amt;
        v_area := v_row.area;
      ELSIF v_row.area = v_area THEN
        v_total := v_total + v_row.trx_amt;
      ELSE
        PIPE ROW(area_summary_o(v_area,v_total));
        v_total := v_row.trx_amt;
        v_area := v_row.area;
      END IF;
    END IF; -- extensive validation

    FETCH p_cursor INTO v_row;

  END LOOP; -- every transaction

  PIPE ROW(area_summary_o(v_area,v_total));

END;
/
SELECT *
  FROM TABLE(area_summary(CURSOR(SELECT *
                                   FROM acct_transactions)));