date_parser.sql

Oracle PLSQL for DBAs 源代码

CREATE TABLE orders( order_number NUMBER,
                     create_date  DATE,
                     assign_date  DATE,
                     close_date   DATE);

BEGIN
  FOR counter IN 1..3 LOOP
    INSERT INTO orders
    VALUES(counter,
           SYSDATE,
           SYSDATE + 1,
           SYSDATE + 2);
  END LOOP;
END;
/

CREATE OR REPLACE TYPE order_date_o AS OBJECT ( order_number NUMBER,
                                                date_type    VARCHAR2(1),
                                                year         NUMBER,
                                                quarter      NUMBER,
                                                month        NUMBER );
/
CREATE TYPE order_date_t AS TABLE OF order_date_o;
/

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

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

  v_order_rec orders%ROWTYPE;
  v_ret_val order_date_t := order_date_t( );

BEGIN

  -- for every order in the cursor...
  LOOP

    FETCH p_curs INTO v_order_rec;
    EXIT WHEN p_curs%NOTFOUND;

    -- extend the array by 3 and populate with cmoponents of the
    -- orders creation, assignment and close date
    v_ret_val.EXTEND(3);
    v_ret_val(v_ret_val.LAST - 2) := 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'));
    v_ret_val(v_ret_val.LAST - 1) := 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'));
    v_ret_val(v_ret_val.LAST) := 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 ths cursor

  RETURN(v_ret_val);

END;
/

SELECT *
  FROM TABLE(date_PARSE(CURSOR(SELECT * FROM orders)))
/