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

 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/