Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page