Progressive LOOP Statement ORACLE

Question

How to create a function that returns a float(ChargeTotal)?

ChargeTotal is based on a progressive table using number of batches.

num of batches     | charge
----------------------------
1-10               |  0
11-20              |  50
21-30              |  60
31-40              |  70
40+                |  80

If number of batches is 25 then

num of batches     | charge
----------------------------
1-10               |  0
11-20              |  50*10
21-30              |  60*5
----------------------------
total              |  800 <number I need to be returned(ChargeTotal)

So far I have come up with the following, but I'm unsure how to get the total for each loop, or if it is even possible to do more than one FOR statements:

CREATE OR REPLACE FUNCTION ChargeTotal
RETURN FLOAT IS

total FLOAT;

BEGIN 

    FOR a in 1 .. 10 LOOP 

    FOR a in 11 .. 20 LOOP 

    FOR a in 21 .. 30 LOOP 

    FOR a in 40 .. 1000 LOOP 

    RETURN Total;

END ChargeTotal;

Answer 1

Ok so take into consideration that right now I have no DB available to test this (there might be some syntax errors etc).

But I am thinking something along this lines of code...

 function ChargeTotal(xin number) return number is
     cursor mycursor is 
     select lowLimit,highLimit,charge 
     from progressive_table order by lowLimit;
     sum number;
     segment number; 
     x number;
    begin
     sum:=0;
     x  :=xin;
     for i in mycursor loop
       segment := (i.highLimit-i.lowLimit)+1;
       x := greatest ( x - segment,x);
       sum := sum + segment*i.charge;
       if (x<segment) then
         return sum; 
       end if;
      end loop;
    return sum;
   end;

Answer 2

I think you can do the calculation via single sql without complex function

the logic is:

you have weights for each "band"

calculate the "band" each row

count(*) over to calculate number of rows in each "band"

join your weight table to get sub.total for each band

use rollup to get grand total

sql

  select r.num_of_batches
          ,sum(r.subtotal_charge)
     from (
    with weights as
    (select 1 as num_of_batches, 0 as charge from dual
    union all
    select 2 as num_of_batches, 50 as charge from dual
    union all
    select 3 as num_of_batches, 60 as charge from dual
    union all
    select 4 as num_of_batches, 70 as charge from dual
    union all
    select 5 as num_of_batches, 80 as charge from dual
    ) 
    select distinct n.num_of_batches
     , w.charge
     , count(*) over (partition by n.num_of_batches) as cnt
     , count(*) over (partition by n.num_of_batches) * charge as subtotal_charge
    from (
    select num, case when floor(num / 10) > 4 then 5 else floor(num / 10)+1 end as num_of_batches
       from tst_brances b
    ) n
    inner join weights w on n.num_of_batches = w.num_of_batches
    order by num_of_batches
    ) r
    group by ROLLUP(r.num_of_batches)

populate test data

    create table tst_branches(num int);

    declare 
     i int;
    begin 
      delete from tst_brances;
      for i in 1..10 loop
        insert into tst_brances(num) values (i);
      end loop;

      for i in 11..20 loop
        insert into tst_brances(num) values (i);
      end loop;

      for i in 21..25 loop
        insert into tst_brances(num) values (i);
      end loop;

      for i in 31..32 loop
        insert into tst_brances(num) values (i);
      end loop;


      for i in 41..43 loop
        insert into tst_brances(num) values (i);
      end loop;


      for i in 51..55 loop
        insert into tst_brances(num) values (i);
      end loop;

     commit;  
    end;

results

1   1   0
2   2   500
3   3   360
4   4   140
5   5   640
6       1640