Yurttas/PL/DBL/oracle/F/02/SPJ/q10.sp
Jump to navigation
Jump to search
1/*
2REM
3REM q10.sp
4REM
5REM Get suppliers of part 'P01' of project 'J01'
6REM
7*/
8
9DECLARE
10
11 sSN Suppliers.SN%TYPE;
12
13 no_selection EXCEPTION;
14
15 CURSOR c1 IS
16 SELECT DISTINCT sn
17 FROM Shipments
18 WHERE pn = 'P01'
19 AND jn = 'J01';
20
21BEGIN
22
23 DBMS_OUTPUT.PUT_LINE('Suppliers of part P01 of Project J01');
24 DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
25 DBMS_OUTPUT.PUT_LINE('SN');
26 DBMS_OUTPUT.PUT_LINE('--');
27
28 OPEN c1;
29
30 LOOP
31 FETCH c1 INTO sSN;
32
33 IF c1%ROWCOUNT=0 THEN
34 RAISE no_selection;
35 END IF;
36
37 IF c1%FOUND THEN
38 DBMS_OUTPUT.PUT_LINE(sSN);
39 ELSE
40 EXIT;
41 END IF;
42
43 END LOOP;
44
45 CLOSE c1;
46
47EXCEPTION
48 WHEN no_selection
49 THEN
50 DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
51
52END;
53/