next_in_line.sql

Oracle PLSQL for DBAs 源代码

CREATE TYPE next_o AS OBJECT ( summary_value NUMBER );
/
CREATE TYPE next_t AS TABLE OF next_o;
/
CREATE OR REPLACE FUNCTION next_in_line ( p_curs SYS_REFCURSOR )
                  RETURN next_t
                  PIPELINED IS

   /*
      || Demonstrate nested table functions
   */

   v_ret_val next_t := next_t( );

   -- local variables for cursor results
   v_on NUMBER;
   v_dt VARCHAR2(1);
   v_yr NUMBER;
   v_qt NUMBER;
   v_mt NUMBER;

BEGIN

  -- for all date components from the cursor...
  LOOP

    FETCH p_curs INTO v_on, v_dt, v_yr, v_qt, v_mt;
    EXIT WHEN p_curs%NOTFOUND;

    -- pipe out the sum of the components
    PIPE ROW(next_o(v_on + v_yr + v_qt + v_mt));

  END LOOP; -- every date component

 RETURN;

END;
/

SELECT *
  FROM TABLE(next_in_line(CURSOR(SELECT *
                                   FROM TABLE(date_parse(CURSOR(SELECT *
                                                                  FROM orders))))))
/