How to have a stored procedure do this all in one stored procedure instead of two separate ones

Question

So I am trying to insert aggregated (summed) values into the value table by hour. After I insert the values by hour, I would like to insert the comprehensive sum by day within the same procedure. Is there a way I can have the stored procedure complete the insert by hour and then have it draw a cursor by summing the data just inserted during the 'hourly' process? Would I need to commit the first transaction and then create another cursor shortly after?

Below is some sample data:

Result of query that is already summing / aggregating by hour:

SUM (VALUE_TX)   UTC_OFFSET   DATA_DATE    HR
   5              8           902018      1
  10              8           902018      2
   4              8           902018      3

What I want inserted after the first result set above is inserted into the table. I will be drawing these values from the same table that I'll be inserting on.

SUM (VALUE_TX)   UTC_OFFSET   DATA_DATE    HR
   19                8           902018    null

I was thinking of doing the following but I am not sure if this is the best way to do it. I was going to just group by data_Date to form the second cursor.

Create or replace procedure AGG_HOURLY_DAILY IS
CURSOR c1 is
Select sum(value_Tx) as sum_of_values
     , utc_offset
     , data_date
     , hr
  from value
 group by data_date, utc_offset, hr
where HR is not null;
l_var c1%ROWTYPE;
CURSOR c2 is
Select sum(value_tx)
     , utc_offset
     , data_date
     , hr
 group by data_date, utc_offset, hr
Where HR is null;
k_var c2%rowtype;
BEGIN
Open C1;
LOOP;
  FETCH c1 into l_var;
 EXIT WHEN c1%NOTFOUND;
  Insert into value(value_id, value_tx, utc_offset, data_date, hr)
             values(null, l_var.sum_of_values, l_var.utc_offset, l_var.data_date, l_var.hr);
END LOOP;
CLOSE C1;
OPEN C2;
LOOP;
  FETCH c2 into k_var;
 EXIT WHEN c2%NOTFOUND;
  Insert into value(value_id, value_tx, utc_offset, data_date, hr)
             values(null, l_var.sum_of_values, l_var.utc_offset, l_var.data_date, l_var.hr);
END LOOP;
Close c1;
Close c2;
END AGG_HOURLY_DAILY;

NOTE: Value_ID is auto-generated by a sequence/trigger that I've created

I believe that ORACLE supports the GROUP BY ROLLUP clause so you can probably do the entire thing with one query without needing a cursor. If you put your first query into a common-table-expression then have your second query reference the CTE you should be able to get to the desired dataset. — Dave Poole, Apr 23 '18 at 15:27
@DavePoole Would you be able to show me a brief example please? (sorry) — John Wick, Apr 23 '18 at 15:33
Also, if i don't have a cursor, how would I be able to load the entire result set into the tables without using a loop? — John Wick, Apr 23 '18 at 15:43

William Robertson · Answer 1 · 2018-04-23T16:48:16.337

Perhaps you want something like this:

with demo (value_tx, utc_offset, data_date, hr)
as   ( select 1, 8, '902018', 1 from dual union all
       select 2, 8, '902018', 1 from dual union all
       select 2, 8, '902018', 1 from dual union all
       select 4, 8, '902018', 2 from dual union all
       select 6, 8, '902018', 2 from dual union all
       select 1, 8, '902018', 3 from dual union all
       select 2, 8, '902018', 3 from dual union all
       select 1, 8, '902018', 3 from dual )
select sum(value_tx), utc_offset, data_date, hr
from   demo
group by grouping sets ((utc_offset, data_date, hr), (utc_offset, data_date))
order by hr nulls last;

SUM(VALUE_TX) UTC_OFFSET DATA_DATE         HR
------------- ---------- --------- ----------
            5          8 902018             1
           10          8 902018             2
            4          8 902018             3
           19          8 902018

The GROUPING SETS clause is essentially saying you want two GROUP BY operations: one on (utc_offset, data_date, hr) and one on (utc_offset, data_date).

Read more in the documentation.

krokodilko · Accepted Answer · 2018-04-23T17:30:44.113

In the first step, replace the cursors by simple INSERT ... SELECT statements:

  Insert into value( value_tx, utc_offset, data_date, hr)
  SELECT sum(value_Tx) as sum_of_values
     , utc_offset
     , data_date
     , hr
  from value
 group by data_date, utc_offset, hr
where HR is not null;

Insert into value( value_tx, utc_offset, data_date, hr)
Select sum(value_tx)
     , utc_offset
     , data_date
     , hr
 group by data_date, utc_offset, hr
Where HR is null;

Thanks to this simplification, it is easy to see that both queries differ only in one element: HR is NULL and HR is NOT NULL. So the above two queries which inserts two rows can be simplified into one which inserts the same two rows:

Insert into value( value_tx, utc_offset, data_date, hr)
SELECT sum_tx, utc_offset, data_date, hr
FROM (
   Select CASE WHEN hr IS NULL THEN 1 ELSE 2 END as xxx
            , sum(value_tx) as sum_tx
            , utc_offset
            , data_date
            , hr
   group by CASE WHEN hr IS NULL THEN 1 ELSE 2 END,
         data_date, utc_offset, hr
)
;

so i wouldn't need PL/SQL for this? is there a way i can do this by using PL/SQL?

You can do this in the procedure, you can it without a procedure.
If you want to have a procedure, just do:

Create or replace procedure AGG_HOURLY_DAILY IS
BEGIN
  Insert into value( value_tx, utc_offset, data_date, hr)
  SELECT sum_tx, utc_offset, data_date, hr
    FROM (
       Select CASE WHEN hr IS NULL THEN 1 ELSE 2 END as xxx
                , sum(value_tx) as sum_tx
                , utc_offset
                , data_date
                , hr
       group by CASE WHEN hr IS NULL THEN 1 ELSE 2 END,
             data_date, utc_offset, hr
    )
    ;
END;
/

so i wouldn't need PL/SQL for this? is there a way i can do this by using PL/SQL? The reason i ask is because this is just a simplified version of what I'm trying to do . IN reality i am grabbing a bunch of data and inserting into 3-4 different tables... — John Wick, Apr 23 '18 at 17:10
It an be done in the procedure, it's very ease - I've updated with an example. — krokodilko, Apr 23 '18 at 17:32

How to have a stored procedure do this all in one stored procedure instead of two separate ones

2 Answers2