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.
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;