October 2005
Intermediate to advanced
454 pages
14h 44m
English
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 *2FROM ...