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

 1/*
 2REM
 3REM q03.sp
 4REM
 5REM Find the model number and price of all products
 6REM (of any type) made by manufacturer 'B'.
 7REM
 8*/
 9
10CREATE OR REPLACE 
11PROCEDURE q03 IS
12
13  no_selection EXCEPTION;
14
15  CURSOR c1 IS
16    (SELECT PC.model, price
17     FROM Product P, PC
18     WHERE maker='B'
19       AND P.model=PC.model )
20    UNION
21    (SELECT L.model, L.price
22     FROM Product P, Laptop L
23     WHERE maker='B'
24       AND P.model=L.model)
25    UNION
26    (SELECT R.model, R.price
27     FROM Product P, Printer R
28     WHERE P.maker='B'
29       AND P.model=R.model);
30
31  PCM c1%ROWTYPE;
32
33BEGIN
34
35  OPEN c1;
36
37  DBMS_OUTPUT.PUT_LINE('Find the model number and price of all products');
38  DBMS_OUTPUT.PUT_LINE('(of any type) made by manufacturer ''B''. ');
39  DBMS_OUTPUT.PUT_LINE('PC Models');
40  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
41  DBMS_OUTPUT.PUT_LINE('Model'||'  '||'Price');
42  DBMS_OUTPUT.PUT_LINE('-----'||'  '||'-----');
43   
44  LOOP
45    FETCH c1 INTO PCM;
46
47    IF c1%ROWCOUNT = 0 THEN
48      RAISE no_selection;
49    END IF;
50
51   IF c1%FOUND THEN
52      DBMS_OUTPUT.PUT_LINE(PCM.model||'     '|| PCM.price);
53    ELSE
54      EXIT;
55    END IF;
56  END LOOP;
57
58  CLOSE c1;
59
60EXCEPTION
61  WHEN no_selection
62  THEN
63    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
64
65END q03;
66/