spool p2.out
set echo on 
SET pagesize 6000 
set newpage 0
set head off
set serveroutput on size 6000 
declare
  isum integer;
  jsum integer;
Begin
  For i IN (select distinct s.* From s order by s# )
  LooP
    DBMS_output.put_line(i.s#||'  '||i.sname||'  '||to_char(i.status)||'  '||
    i.city);
      
  For l IN (select distinct p.* from p, spj where spj.s#=i.s# and p.p#=spj.p# 
  order by p.p#)
  Loop
    DBMS_output.put_line('****'||l.p#||'  '||l.pname||'  '||l.color||'  '||
    to_char(l.weight)||'  '||l.city);
    For k in (select  distinct j.*,spj.qty  From j,spj where j.j#=spj.j# and      spj.p#=l.p# and  spj.s#=i.s#  order by j.j#)  
  Loop
    DBMS_output.put_line('********'||k.j#||'  '||k.jname||'  '||k.city||'  '||
    k.qty);
  end loop;
  select sum(qty) into jsum from spj where p#=l.p# and s#=i.s# group by p#;
  DBMS_output.put_line('******** SUM of '||l.p#||' is '||jsum);
  end loop;
    select sum(qty) into isum from spj where s#=i.s# group by s#; 
    DBMS_output.put_line('**** SUM of '||i.s#||' is '||isum); 
  end loop;
DBMS_output.put_line('End of job');
end;
/
spool off 

