Geek asks and answers

How to combine result sets in Oracle?

I have a procedure that has an in parameter and an out cursor. The results obtained by this cursor are as follows:

0100 | 0
0130 | 1
0200 | 2
0230 | 0
...

The first column is a static time code. The second column is the aggregate of how many times something is planned in this time interval on a given day.

This procedure:

PROCEDURE DAILYLOAD (datep IN DATE, results OUT SYS_REFCURSOR)
   AS
   BEGIN
   Open results for
      SELECT RUN_TIME_C, COUNT (SCH_RPT_I)
    FROM    ITS_SCH_RPT_RUN_TIME
         LEFT OUTER JOIN
            ITS_SCH_RPT
         ON (   RUN_TIME_C = RUN_TIME1_C
             OR RUN_TIME_C = RUN_TIME2_C
             OR RUN_TIME_C = RUN_TIME3_C)
   WHERE EXP_DATE_D IS NULL
         OR datep < exp_date_d AND datep > start_date_d AND SUSPENDED_USER='N'
            AND (   ( (TO_CHAR (datep, 'D') = 1) AND RUN_SUNDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 2) AND RUN_MONDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 3) AND RUN_TUESDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 4) AND RUN_WEDNESDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 5) AND RUN_THURSDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 6) AND RUN_FRIDAY_C = 'Y')
                 OR ( (TO_CHAR (datep, 'D') = 7) AND RUN_SATURDAY_C = 'Y'))
GROUP BY RUN_TIME_C
ORDER BY RUN_TIME_C;
   END DAILYLOAD;

I want to call this procedure from the wrapper procedure several times with different parameters so that I can come with weekly downloads and monthly downloads. It is clear that this would be done by combining separate result sets through something like combining all and groupings, which in the first column summed the second column for each group.

Now i have something like

Dailyload(datep, results1);
Dailyload(datep + 1, results2);
...

OPEN results FOR
  SELECT run_time_c, 
         SUM(rpt_option_i) 
    FROM SELECT * 
           FROM results1 
         UNION ALL 
         SELECT * 
           FROM results2 
         UNION ALL ... 
         GROUP BY run_time_c 
         ORDER BY run_time_c

Oracle? Fetch with bulk collect , .

+3
4

, , . , DailyLoad SP.

 select foo.Mygroup, sum(foo.col1) 
  from
 (
 select 'A' as MyGroup, col1 WHERE ...
 union all
 select 'B' as MyGroup, col1 WHERE ...
 union all
 select 'C' as MyGroup, col1 WHERE ...
 ) as Foo
 group by MyGroup

, SQL-, .

, , , temp MyGroup. select temp.

0

out out ref, , , , OMG Ponies, . , sys_refcursor , :

create package p as
    type tmp_rec_type is record (run_time_c varchar2(4),
        rpt_option_i number);
    type tmp_table_type is table of tmp_rec_type;

    procedure dailyload(p_date in date, p_results out sys_refcursor);
    function func(p_date in date) return tmp_table_type pipelined;
    procedure sumload(p_start_date in date, p_results out sys_refcursor);
end;
/

create package body p as
    /* Your existing procedure, which may be elsewhere */
    procedure dailyload(p_date in date, p_results out sys_refcursor) is
    begin
        open p_results for
            select to_char(created, 'HH24MI') as run_time_c,
                count(*) as rpt_option_i
            from all_objects
            where trunc(created) = trunc(p_date)
            group by to_char(created, 'HH24MI');
    end;

    /* Intermediate pipelined function */
    function func(p_date in date) return tmp_table_type pipelined is
        tmp_cursor sys_refcursor;
        tmp_rec tmp_rec_type;
    begin
        dailyload(p_date, tmp_cursor);
        loop
            fetch tmp_cursor into tmp_rec;
            exit when tmp_cursor%notfound;
            pipe row(tmp_rec);
        end loop;
    end;

    /* Wrapper function to join the result sets together */
    procedure sumload(p_start_date in date, p_results out sys_refcursor) is
    begin
        open p_results for
            select run_time_c, sum(rpt_option_i) from (
                select * from table(func(p_start_date))
                union all
                select * from table(func(p_start_date + 1))
                union all
                select * from table(func(p_start_date + 2))
            )
            group by run_time_c;
    end;
end;
/

, , . SQL * Plus SQL Developer:

var results refcursor;

exec p.sumload(to_date('01-Jun-11','DD-Mon-RR'), :results);

print :results
0

, , :

  • sproc, SYS_REFCURSOR IN IN OUT, OUT.
  • ( , ...)
  • SYS_REFCURSOR.
  • sproc SYS_REFCURSOR , .
  • sproc SYS_REFCURSOR

.

, #/Oracle 10g , .

Another option, if you're on 11g, would be Pipelined Query, as discussed How to create an Oracle stored procedure that can return certain objects, as well as all objects (look at @tbone's answer and the link it provides ...)

0
source

You can use the oracle global temporary table to accumulate and further process the data.

This is a structure in mind and has very little overhead.

0
source

More articles:


All Articles