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

 1/*
 2REM
 3REM q08.sp
 4REM
 5REM Find the printers with the highest price.
 6REM
 7*/
 8
 9CREATE OR REPLACE
10PROCEDURE q08 IS
11
12  pMODEL printer.model%TYPE;
13
14  no_selection EXCEPTION;
15
16  CURSOR c1 IS
17    SELECT model
18    FROM Printer
19    WHERE price = (SELECT MAX(price)
20                   FROM Printer);
21
22BEGIN
23
24  DBMS_OUTPUT.PUT_LINE('Find the printers with the highest price.');
25  DBMS_OUTPUT.PUT_LINE('MODEL');
26  DBMS_OUTPUT.PUT_LINE('-----');
27   
28  OPEN c1;
29
30  LOOP
31    FETCH c1 INTO pMODEL;
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(pMODEL);
39    ELSE
40      EXIT;
41    END IF;
42  END LOOP;
43
44  CLOSE c1;
45
46EXCEPTION
47  WHEN no_selection
48  THEN
49    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
50
51END q08;
52/