Frio_Penitencia
Frio_Penitencia

Reputation: 103

Need help in looping output for Oracle PLSQL Function

Am currently working on oracle PLSQL function to list the project numbers, titles and the names of employees who work on each project. So far I am able to get the desired output but can only do so after calling it one by one. How can I implement a loop in my code to allow it to call it all at once.

[Current Code]

create or replace procedure PROJECTGROUPS(projectid IN WorksOn.P#%TYPE)
IS
PID Project.P#%TYPE;
PNAME Project.PTitle%TYPE;
ENAME Employee.Name%TYPE;
finalRow VARCHAR(2000);
CURSOR query is
select Employee.Name from Employee
left outer join WorksOn On Employee.E# = WorksOn.E#
where WorksOn.P# = projectid
order by Employee.Name ASC
fetch first 20 rows only;
--
--
--
--
--
begin
select P#, PTitle into PID, PNAME from project where project.p# = projectid;
finalRow:= PID || ' ' || PNAME || ': ';
--
open query;
loop
fetch query into ENAME;
if query%NOTFOUND then exit;
end if;
finalRow:= finalRow || CASE WHEN SUBSTR(finalRow ,-2) <> ': '  THEN ', ' END || ENAME;
end loop;
close query;
dbms_output.put_line(finalRow);
return;
end PROJECTGROUPS;
/
execute PROJECTGROUPS(1001)
execute PROJECTGROUPS(1002)
execute PROJECTGROUPS(1003)
execute PROJECTGROUPS(1004)
execute PROJECTGROUPS(1005)
execute PROJECTGROUPS(1006)
show error

[Current Output]

SQL> execute PROJECTGROUPS(1001)
1001 Computation: Alvin, Peter

PL/SQL procedure successfully completed.

SQL> execute PROJECTGROUPS(1002)
1002 Study methods: Bob, Robert

PL/SQL procedure successfully completed.

SQL> execute PROJECTGROUPS(1003)
1003 Racing car: Robert

PL/SQL procedure successfully completed.

SQL> execute PROJECTGROUPS(1004)
1004 Football: Douglass, Eadger, Robert

PL/SQL procedure successfully completed.

SQL> execute PROJECTGROUPS(1005)
1005 Swimming: Robert

PL/SQL procedure successfully completed.

SQL> execute PROJECTGROUPS(1006)
1006 Training: Aban

How can I loop my code so that I can call them all using execute PROJECTGROUPS.

Upvotes: 0

Views: 159

Answers (1)

Popeye
Popeye

Reputation: 35900

You can use single query for fetching 20 employees from each project as follows and use them in the loop to print it as follows:

create or replace procedure PROJECTGROUPS(projectid IN WorksOn.P#%TYPE) -- you can remove input parameter as it is not used anywhere now
IS
BEGIN
FOR q in
(Select proj_detail, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY NAME) AS NAMES from
(select project.p# || ' ' || project.ptitle as proj_detail,
       Employee.Name,
       Row_number() over (partition by project.p# order by employee.name) as rn
from project
Join WorksOn on project.p# = WorksOn.P#
join employee On Employee.E# = WorksOn.E#)
Where rn <= 20
GROUP BY PROJ_DETAIL)
LOOP
DBMS_OUTPUT.PUT_LINE(Q.PROJ_DETAIL || ': ' || Q.NAMES);
END LOOP;
END;
/

Upvotes: 1

Related Questions