date_parser_pipelined.sql

Oracle PLSQL for DBAs 源代码

CREATE OR REPLACE FUNCTION date_parse ( p_curs SYS_REFCURSOR )
                  RETURN order_date_t
                  PIPELINED AS 

  /*
    || Disassemble portions of date field in an order so they
    || can be sent upstream for more processing.
  */

  v_order_rec orders%ROWTYPE;

BEGIN

  -- for every order in the cursor...
  LOOP
    FETCH p_curs INTO v_order_rec;
    EXIT WHEN p_curs%NOTFOUND;

    -- pipe out the components of the orders open data
    PIPE ROW(order_date_o(v_order_rec.order_number,'O',
                          TO_CHAR(v_order_rec.create_date,'YYYY'),
                          TO_CHAR(v_order_rec.create_date,'Q'),
                          TO_CHAR(v_order_rec.create_date,'MM')));

    -- pipe out the components of the orders assign date
    PIPE ROW(order_date_o(v_order_rec.order_number,'A',
                          TO_CHAR(v_order_rec.assign_date,'YYYY'),
                          TO_CHAR(v_order_rec.assign_date,'Q'),
                          TO_CHAR(v_order_rec.assign_date,'MM')));

    -- pipe out the components of the orders close date
    PIPE ROW(order_date_o(v_order_rec.order_number,'C',
                          TO_CHAR(v_order_rec.close_date,'YYYY'),
                          TO_CHAR(v_order_rec.close_date,'Q'),
                          TO_CHAR(v_order_rec.close_date,'MM')));

  END LOOP; -- every order in the cursor

  RETURN;

END;
/