PL SQL print with cursor

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.

Answer

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>