repeat_orders.sql

Oracle PLSQL for DBAs 源代码

DROP TYPE repeat_region_location_t;

CREATE OR REPLACE TYPE repeat_region_location_o AS OBJECT ( region_id      NUMBER,
                                                            location_id    NUMBER,
                                                            first_type_id  NUMBER,
                                                            repeat_type_id NUMBER );
/
CREATE TYPE repeat_region_location_t AS TABLE OF repeat_region_location_o;
/

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;

END;
/

CREATE OR REPLACE FUNCTION repeat_order_finder ( p_curs cursors.repeat_orders_curs )
                  RETURN repeat_region_location_t
                  PIPELINED
                  PARALLEL_ENABLE ( PARTITION p_curs BY RANGE(region_id) )
                  ORDER p_curs BY (location_id, order_date) IS

  /*
    ||
    || Work through a set of orders determining if they are repeat ones as defined by
    || the regions criteria. For each order determine if it is a potential duplicate
    || because it matches the initial type of a repeat criteria and then see if it
    || matches asny second types in an existing criteria.
    ||
    || That may seem like a lot of work to go through every single order like that but
    || the power of parallel processing makes short work of it.
    ||
  */

  v_last_region NUMBER := 0;
  v_order cursors.repeat_orders_rec;

  CURSOR curs_get_criteria ( cp_region NUMBER ) IS
  SELECT *
    FROM repeat_order_criteria
   WHERE region_id = cp_region;

  TYPE v_criteria_rec IS TABLE OF repeat_order_criteria%ROWTYPE;
  v_criteria v_criteria_rec;

  TYPE v_rec IS RECORD ( location_id NUMBER,
                         first_type_id NUMBER,
                         repeat_type_id NUMBER );
  TYPE v_tab IS TABLE OF v_rec
    INDEX BY BINARY_INTEGER;
  v_potential_repeat v_tab;

  v_repeat NUMBER;

      /*------------------------------------------------------------------*/
      PROCEDURE load_potential_repeat ( p_location_id NUMBER,
                                        p_type_id     NUMBER,
                                        p_date        DATE ) IS
      /*------------------------------------------------------------------*/

        /*
          || Is the order a potential for future repeats?
        */

        v_hash NUMBER;

      BEGIN
        -- for every criteria...
        FOR counter IN 1..v_criteria.LAST LOOP
          -- if the order type of the order matches that of the criteria
          IF v_criteria(counter).first_type_id = p_type_id THEN
            -- if date range is valid
            IF v_criteria(counter).start_date <= p_date THEN
               -- create a hash based on the location and two repeat criteria
               v_hash := DBMS_UTILITY.GET_HASH_VALUE(p_location_id || ':' ||
                                                     v_criteria(counter).first_type_id || ':' ||
                                                     v_criteria(counter).repeat_type_id,
                                                     -32767,65533);
              -- if the criteria is not already in the potential list then
              -- put it there
              IF NOT v_potential_repeat.EXISTS(v_hash) THEN
                v_potential_repeat(v_hash).location_id := p_location_id;
                v_potential_repeat(v_hash).first_type_id := v_criteria(counter).first_type_id;
                v_potential_repeat(v_hash).repeat_type_id := v_criteria(counter).repeat_type_id;
              END IF;
            END IF; -- date range is valid
          END IF; -- order type matches
        END LOOP; -- every criteria
      END load_potential_repeat;

      /*------------------------------------------------------------------*/
      FUNCTION order_is_a_repeat ( p_location_id NUMBER,
                                   p_type_id     NUMBER,
                                   p_date        DATE )
               RETURN NUMBER IS
      /*------------------------------------------------------------------*/

        v_hash NUMBER;

        /*
          || Is the order an actual repeat?
        */

      BEGIN
        -- for every criteria...
        FOR counter IN 1..v_criteria.LAST LOOP
          -- if order type matches the repeat order type of a criteria
          IF v_criteria(counter).repeat_type_id = p_type_id THEN
            -- calculate a hash of the location, first and repeat order types
           v_hash := DBMS_UTILITY.GET_HASH_VALUE(p_location_id || ':' ||
                                                 v_criteria(counter).first_type_id || ':' ||
                                                 v_criteria(counter).repeat_type_id,
                                                 -32767,65533);
           -- if logged as a potential repeat then its safe to assume
           -- we are repeating now
           IF v_potential_repeat.EXISTS(v_hash) THEN
             RETURN(v_hash);
           END IF;
         END IF; -- order type match
       END LOOP; -- every criteria
       RETURN(NULL);
     END order_is_a_repeat;

BEGIN

  -- for every order...
  LOOP

    FETCH p_curs INTO v_order;
    EXIT WHEN p_curs%NOTFOUND;

    IF NVL(v_last_region,0) <> v_order.region_id THEN

      -- set the local region ID and bulk load
      -- its criteria
      v_last_region := v_order.region_id;
      OPEN curs_get_criteria(v_order.region_id);
      FETCH curs_get_criteria BULK COLLECT INTO v_criteria;
      CLOSE curs_get_criteria;

    END IF; -- new region

    -- check potential repeat
    load_potential_repeat ( p_location_id => v_order.location_id,
                            p_type_id     => v_order.type_id,
                            p_date        => v_order.order_date );

    v_repeat := order_is_a_repeat ( p_location_id => v_order.location_id,
                                    p_type_id     => v_order.type_id,
                                    p_date        => v_order.order_date );
    IF v_repeat IS NOT NULL THEN
      PIPE ROW(repeat_region_location_o(1,1,1,1));
    END IF; -- PIPE ROW( );

  END LOOP; -- every order

  RETURN;

END;
/

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)));