Reputation: 103
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
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