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

 1/*
 2REM
 3REM q04.sp
 4REM
 5REM Find the model numbers of all color laser printers.
 6REM
 7*/
 8
 9CREATE OR REPLACE
10PROCEDURE q04 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 color='true'
20      AND type='laser';
21
22BEGIN
23
24  DBMS_OUTPUT.PUT_LINE('Find the model numbers of all color laser printers.');
25  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
26  DBMS_OUTPUT.PUT_LINE('MODEL');
27  DBMS_OUTPUT.PUT_LINE('-----');
28   
29  OPEN c1;
30
31  LOOP
32    FETCH c1 INTO pMODEL;
33
34    IF c1%ROWCOUNT = 0 THEN
35      RAISE no_selection;
36    END IF;
37
38    IF c1%FOUND THEN
39      DBMS_OUTPUT.PUT_LINE(pMODEL);
40    ELSE
41      EXIT;
42    END IF;
43  END LOOP;
44
45  CLOSE c1;
46
47EXCEPTION
48  WHEN no_selection
49  THEN
50    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
51
52END;
53/