PL/SQL loop over table and add up results with dash (-) sign-CodePudding

i would like to loop over a table column of type date that returns the date which must be equal or greater than todays date. For example if there are 6 matches i would like to concatenate these 6 dates as follows

20220812-20220814-20220815-20220818-20220906-20220916

dates separated with a dash sign (-)

how would the loop look like?

thanks!

CodePudding user response：

As OldProgrammer commented, you don't need a loop. If you're on 11g or higher (I hope you are; 11g is old, lower versions are ancient), use listagg function.

For example:

SQL> select hiredate from emp order by hiredate;

HIREDATE
----------
<snip>
03.12.1981
23.01.1982     --> I want dates after 01.01.1982
09.12.1982
12.01.1983

14 rows selected.

SQL> select listagg(to_char(hiredate, 'yyyymmdd'), '-') within group (order by hiredate) result
  2  from emp
  3  where hiredate >= date '1982-01-01';

RESULT
--------------------------------------------------------------------------------
19820123-19821209-19830112

SQL>

In your case, you'd modify condition to where hiredate >= trunc(sysdate).

If it must be a loop, then

SQL> set serveroutput on
SQL> declare
  2    l_result varchar2(50);
  3  begin
  4    for cur_r in (select to_char(hiredate, 'yyyymmdd') hiredate
  5                  from emp
  6                  where hiredate >= date '1982-01-01'
  7                  order by hiredate
  8                 )
  9    loop
 10      l_result := l_result ||'-'|| cur_r.hiredate;
 11    end loop;
 12    l_result := ltrim(l_result, '-');
 13    dbms_output.put_line(l_result);
 14  end;
 15  /
19820123-19821209-19830112

PL/SQL procedure successfully completed.

SQL>