Yurttas/PL/DBL/oracle/F/02/SPJ/q03.sp

 1/*
 2REM
 3REM q03.sp
 4REM
 5REM Get average shipments for each project
 6REM
 7*/
 8
 9DECLARE
10
11  CURSOR c1 IS
12    SELECT jn, AVG(quantity) AS qty
13    FROM Shipments
14    GROUP BY jn;
15
16  proj c1%ROWTYPE;
17
18BEGIN
19
20  DBMS_OUTPUT.PUT_LINE('Average Shipments for each project');
21  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
22  DBMS_OUTPUT.PUT_LINE('JN'||'  '||'AVG(quantity)');
23  DBMS_OUTPUT.PUT_LINE('--'||'  '||'-------------');
24
25  FOR proj IN c1
26  LOOP
27    DBMS_OUTPUT.PUT_LINE(proj.JN||' '||proj.qty);
28  END LOOP;
29
30END;
31/