Oracle PL/SQL for DBAs

The Totaling Function

Next, I’ll build another table function to total the repeat orders by region, and then I’ll nest it right into the SQL shown in the previous section. The new function looks like this.

    /* File on web: repeat_orders_summary.sql */
    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

      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;

The algorithm is a straightforward loop through the fetched repeat orders, summarizing them by region. Whenever the region ID value changes in the repeat order, a result must be piped out.

The summary function is activated using the SQL SELECT statement shown here.

    SQL> SELECT *
      2    FROM ...