How to Iterate through a nested table type?

user_14952957 0
Viewed 249

I am relatively new to PL/SQL and don't really understand it.
I originally come from JAVA and am looking for a function that is similar to foreach.

My problem is:

  • I have dates stored in a table, which are unique and I query with a primary key.
  • I want to be able to compare these dates with other dates, which can be several.

I found out that we can use nested tables for this, but unfortunately I have no idea how to even start.

How can I address the dates individually and compare if they match my unique date?

plsql foreach oracle sql
1 Answers

Try following reproducible example, it helps to start with the nested tables:

create table dates (col unique) as
    select date'2021-01-01'+(rownum-1) from dual connect by level<=31
/
create or replace  type datent is table of date
/
declare
    dt datent := datent (date'2021-01-01', date'2021-01-11', date'2021-01-21');
begin 
    for i in 1..dt.count loop
        dbms_output.put_line ('date='||dt(i)); 
    end loop;
    dbms_output.put_line ('QUERY');
    for r in (
        select col
        from dates 
        join table (dt) dt on dt.column_value = dates.col
    ) loop  dbms_output.put_line ('date='||r.col); end loop; 
end;
/

date=2021-01-01 00:00:00
date=2021-01-11 00:00:00
date=2021-01-21 00:00:00
QUERY
date=2021-01-01 00:00:00
date=2021-01-11 00:00:00
date=2021-01-21 00:00:00
user_65710200
Related