repeat_orders_summary.sql

Oracle PLSQL for DBAs 源代码

CREATE OR REPLACE PACKAGE cursors AS

  TYPE repeat_orders_rec IS RECORD (order_number orders.order_number%TYPE,
                                    order_date   orders.order_date%TYPE,
                                    region_id    orders.region_id%TYPE,
                                    type_id      orders.type_id%TYPE,
                                    location_id  orders.location_id%TYPE );
  TYPE repeat_orders_curs IS REF CURSOR RETURN repeat_orders_rec;

  TYPE repeat_summary_rec IS RECORD (region_id      orders.region_id%TYPE,
                                     location_id    orders.location_id%TYPE,
                                     first_type_id  orders.type_id%TYPE,
                                     repeat_type_id orders.type_id%TYPE );
  TYPE repeat_summary_curs IS REF CURSOR RETURN repeat_summary_rec;

END;
/

DROP TYPE repeat_summary_t;
CREATE OR REPLACE TYPE repeat_summary_o AS OBJECT( region_id    NUMBER,
                                                   repeat_count NUMBER );
/
CREATE TYPE repeat_summary_t AS TABLE OF repeat_summary_o;
/

CREATE OR REPLACE FUNCTION summarize_repeat_orders ( p_curs cursors.repeat_summary_curs )
                  RETURN repeat_summary_t
                  PIPELINED
                  PARALLEL_ENABLE ( PARTITION p_curs BY RANGE(region_id) ) AS

  /*
    ||
    || Assemble a per region summary of the  repaet order count as assembled
    || by the repeat_order_finder fucntion
    ||
  */

  v_summary_rec cursors.repeat_summary_rec;
  v_last_region NUMBER;
  v_count       NUMBER := 0;

BEGIN

  -- for every repeat order
  LOOP

    -- fetch the repeat order
    FETCH p_curs INTO v_summary_rec;
    EXIT WHEN p_curs%NOTFOUND;

    -- if this is the first record then set the local
    -- region ID
    IF p_curs%ROWCOUNT = 1 THEN
      v_last_region := v_summary_rec.region_id;
    END IF;

    -- if this is a new region then pipe the region count
    -- out and reset the local variables
    IF v_summary_rec.region_id <> v_last_region THEN
      PIPE ROW(repeat_summary_o(v_last_region,v_count));
      v_last_region := v_summary_rec.region_id;
      v_count := 0;
    END IF;

    v_count := v_count + 1;

  END LOOP; -- every repeat order

  -- don't forget the last record
  IF v_count > 0 THEN
    PIPE ROW(repeat_summary_o(v_last_region,v_count));
  END IF;

  RETURN;

END;
/

SELECT *
  FROM TABLE(summarize_repeat_orders(CURSOR(SELECT *
                                              FROM TABLE(repeat_order_finder(CURSOR(SELECT order_number,
                                                                                           order_date,
                                                                                           region_id,
                                                                                           type_id,
                                                                                           location_id
                                                                                      FROM orders
                                                                                     WHERE order_date >= SYSDATE - 30))))));