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

 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/