1REM
2REM q09.sp
3REM
4REM Find the maker of the color printer with the lowest price.
5REM
6
7
8CREATE OR REPLACE
9PROCEDURE q09 IS
10
11 no_selection EXCEPTION;
12
13 CURSOR c1 IS
14 SELECT P.maker, R.price
15 FROM Product P, Printer R
16 WHERE P.model=R.model
17 AND R.price IN (SELECT MIN(price)
18 FROM Printer
19 WHERE color='true');
20
21 prices c1%ROWTYPE;
22
23BEGIN
24
25 DBMS_OUTPUT.PUT_LINE('Find the maker of the color printer with the lowest price.');
26 DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
27 DBMS_OUTPUT.PUT_LINE('MODEL'||' '||'PRICE');
28 DBMS_OUTPUT.PUT_LINE('-----'||' '||'-----');
29
30 OPEN c1;
31
32 LOOP
33 FETCH c1 INTO prices;
34
35 IF c1%ROWCOUNT = 0 THEN
36 RAISE no_selection;
37 END IF;
38
39 IF c1%FOUND THEN
40 DBMS_OUTPUT.PUT_LINE(prices.maker || ' ' || prices.price);
41 ELSE
42 EXIT;
43 END IF;
44 END LOOP;
45
46 CLOSE c1;
47
48EXCEPTION
49 WHEN no_selection THEN
50 DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
51
52END;
53/