Yurttas/PL/DBL/oracle/F/02/PC/q03.sp
Jump to navigation
Jump to search
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/