Yurttas/PL/DBL/oracle/F/02/SPJ/q07.sp
Revision as of 23:32, 4 November 2013 by MassBot1 (talk | contribs) (Created page with "<syntaxhighlight lang="text" line start="1" enclose="div"> ββREM REM q07.sp REM REM Count the number of shipments by each supplier to 'Austin' REM city projects REM: DECL...")
1/*
2REM
3REM q07.sp
4REM
5REM Count the number of shipments by each supplier to 'Austin'
6REM city projects
7REM
8*/
9
10
11DECLARE
12
13 sSN Suppliers.SN%TYPE;
14
15 cnt_ship NUMBER;
16
17 no_selection EXCEPTION;
18
19 CURSOR c1 IS
20 SELECT SPJ.sn, COUNT(*)
21 FROM Suppliers S, Projects P, Shipments SPJ
22 WHERE S.sn = SPJ.sn
23 AND P.jn = SPJ.jn
24 AND P.city = 'Austin'
25 GROUP BY SPJ.sn;
26
27BEGIN
28
29 DBMS_OUTPUT.PUT_LINE('No. of shipments by each supplier to Austin');
30 DBMS_OUTPUT.PUT_LINE('city projects');
31 DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
32 DBMS_OUTPUT.PUT_LINE('SN'||' '||'Count(Shipments)');
33 DBMS_OUTPUT.PUT_LINE('--'||' '||'----------------');
34
35 OPEN c1;
36
37 LOOP
38 FETCH c1 INTO sSN, cnt_ship;
39
40 IF c1%ROWCOUNT =0 THEN
41 RAISE no_selection;
42 END IF;
43
44 IF c1%FOUND THEN
45 DBMS_OUTPUT.PUT_LINE(sSN||' '||cnt_ship);
46 ELSE
47 EXIT;
48 END IF;
49 END LOOP;
50
51 CLOSE c1;
52
53EXCEPTION
54 WHEN no_selection
55 THEN
56 DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
57
58END;
59/