DECLARE
pnum p.pno%type;
p_name p.pname%type;
snum s.sno%type;
s_name s.sname%type := ' ';
s_tempName s.sname%type := ' ';
s_lastKnownValue s.sname%type := ' ';
outputname varchar2(100):= ' ';
cursor getP IS SELECT pno,pname from p;
cursor getsno (pnum p.pno%type ) IS SELECT sno from sp where pno = pnum;
cursor getsupp (snum s.sno%type) IS SELECT sname from s WHERE sno = snum;

BEGIN

open getP;

loop

outputname := ' ';
fetch getP into pnum,p_name;
exit when getP%NOTFOUND;
dbms_output.put_line (pnum ||' is a ' || p_name);
dbms_output.put_line ('----------------------------------');

open getsno(pnum);
open getsupp(snum);

loop

fetch getsno into snum;
fetch getsupp into s_tempName;

exit when getsupp%NOTFOUND;

if (s_name <> ' ') then
outputname := outputname || ' , '|| s_name;
dbms_output.put_line('s_name is in the first if statement ' || s_name);
end if;

fetch getsupp into s_name;

if getsupp%NOTFOUND then s_lastKnownValue := s_tempName;
exit;
else
outputname := outputname || ' , '|| s_tempName;
dbms_output.put_line('s_tempn=Name is in the else statement ' || s_tempName);

s_lastKnownValue := s_name;

end if;


end loop;
close getsno;
close getsupp;

outputname := outputname || ' AND '||s_lastKnownValue;
dbms_output.put_line(outputname);

end loop;

close getP;

END;
/