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

 1/*
 2REM
 3REM q01.sp
 4REM
 5REM Find PCs with speed greater than 150.
 6REM
 7*/
 8
 9CREATE OR REPLACE 
10PROCEDURE q01 IS
11
12  no_selection EXCEPTION;
13
14  CURSOR c1 IS
15    SELECT model
16    FROM PC
17    WHERE speed>=150;
18
19  pMODEL Product.maker%TYPE;
20
21BEGIN
22
23  OPEN c1;
24
25  DBMS_OUTPUT.PUT_LINE('PCs with speed greater than 150.');
26  DBMS_OUTPUT.PUT_LINE('PC Model');
27  DBMS_OUTPUT.PUT_LINE('--------------------------------');
28  DBMS_OUTPUT.PUT_LINE('Model');
29  DBMS_OUTPUT.PUT_LINE('-----');
30   
31  LOOP
32  FETCH c1 INTO pMODEL;
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
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 q01;
53/