i have a table with a column for categories, date and price. Like this:
group 1 - 03.03.2019 - 5.00
group 1 - 03.02.2018 - 4.00
group 2 - 05.05.2019 - 2.25
group 2 - 05.05.2018 - 1.00
So there are (almost) always two dates per group with two different prices. I have a sql statement which picks the row with the closest date to the given date but i don't know how to print them all with a cursor. So the output for 06.06.2019 should look like this:
group 1 - 03.03.2019 - 5.00
group 2 - 05.05.2019 - 2.25
So it only prints one categorie + the correct Price (from the correct date) but 10 times.
Of course it does, as you told it to. You're looping 10 times, and - for every loop iteration - you open/close the same cursor and print values it fetches.
What you should do is to loop through cursor itself; moreover, as you put it, that should be two nested loops. Something like this (pseudocode to make it clearer):
begin
for cur_1 as (select whatever that makes the first cursor) loop
for cur_2 as (select whatever that makes the second cursor) loop
dbms_output.put_line(value from cur_1 || value from cur_2);
end loop;
end loop;
end;
Applied to your code:
Procedure print_inf_value (closingDate Date) is
begin
for cur_1 as (select t.attr
from informationvalues t
where t.dateofValue <= closingDate
and not exists (select 1
from informationvalues t1
where t1.attr = t.attr
and t1.dateofValue <= closingDate
and t1.dateofValue > t.dateofValue
)
loop
for cur_2 as (select t.price
from informationvalues t
where t.dateofValue <= closingDate
and not exists (select 1
from informationvalues t1
where t1.attr = t.attr
and t1.dateofValue <= closingDate
and t1.dateofValue > t.dateofValue
)
loop
dbms_output.put_line('Attr: ' || cur_1.attr || ' Price: ' || cur_2.price);
end loop;
end loop;
end;
Though, reading what you actually asked, perhaps you'd want to look at the following code which is rather simpler than yours:
Sample data:
SQL> alter session set nls_date_format = 'dd.mm.yyyy';
Session altered.
SQL> select * From test order by grp, cdate;
GRP CDATE PRICE
---------- ---------- ----------
1 03.02.2018 4
1 03.03.2019 5
2 05.05.2018 1
2 05.05.2019 2,25
Procedure:
SQL> create or replace procedure print_inf_value (par_cdate in date)
2 is
3 begin
4 for cur_r in (select a.grp, a.cdate, a.price
5 from test a
6 where (a.grp, a.cdate) in (select b.grp, max(b.cdate)
7 from test b
8 where b.cdate <= par_cdate
9 group by b.grp
10 )
11 )
12 loop
13 dbms_output.put_line(cur_r.grp ||' '|| cur_r.cdate ||' '|| cur_r.price);
14 end loop;
15 end;
16 /
Procedure created.
Testing:
SQL> set serveroutput on;
SQL> exec print_inf_value(date '2019-01-01');
1 03.02.2018 4
2 05.05.2018 1
PL/SQL procedure successfully completed.
SQL>