oracle-databaseplsqloracle-apexcursors

Optimize PLSQL Cursor Oracle Apex


I am making a HTML report by embedding html into PLSQL. I've done this by 2 cursors and nested loops but i want to optimize it. My Oracle Apex report is generating this output

ACCOUNTING - NEW YORK
King - President
Clark - Manager
Miller - Clerk
RESEARCH - DALLAS
Jones - Manager
Scott - Analyst
Ford - Analyst
Smith - Clerk
Adams - Clerk
SALES - CHICAGO
Blake - Manager
Allen - Salesman
Ward - Salesman
Martin - Salesman
Turner - Salesman
James - Clerk
OPERATIONS - BOSTON
No Employee

my code is

    declare
Cursor c_dept Is Select d.Deptno,d.Dname,d.Loc
      From  eba_demo_load_dept d;

   Cursor c_Emp(v_Deptno Number) Is Select e.Empno,e.Ename,e.Job
      From   eba_demo_load_emp e
      Where  e.Deptno = v_Deptno;
begin

 For i In c_Dept Loop

      Htp.p('<ul>');
      Htp.p('<h4>' || i.Dname ||' - '||i.loc||'</h4>');
      --
      For j In c_Emp(i.Deptno) Loop
        Htp.p('<li>' || initcap(j.Ename) || ' - ' ||initcap(j.job)||'</li>');       
      End Loop;
    Htp.p('</ul>');    
   End Loop;

  Htp.p('<p>No Employee </p>');

end;

I want to optimize it. Is it possible to achieve same output in one loop with inner join in cursor variable. If yes please help.


Solution

  • You can use a single cursor by combining the queries as follows:

    declare
    Cursor c_dept Is 
    Select d.Deptno,d.Dname,d.Loc,,
               Listagg('<li>' || initcap(j.Ename) || ' - ' ||initcap(j.job)||'</li>', chr(10))
                 Within group (order by 1) as emps
          From eba_demo_load_dept d
          Join eba_demo_load_emp e
          On e.Deptno = d.Deptno
          Group by d.Deptno,d.Dname,d.Loc;
    
    begin
    
     For i In c_Dept Loop
    
          Htp.p('<ul>');
          Htp.p('<h4>' || i.Dname ||' - '||i.loc||'</h4>');
          --
    
        Htp.p(i.emps);       
    
        Htp.p('</ul>');    
       End Loop;
    
      Htp.p('<p>No Employee </p>');
    
    end;