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

 1/*
 2REM
 3REM q02.sp
 4REM
 5REM Which manufactures make laptops with a hard disk of
 6REM at least one gigabyte?
 7REM
 8*/
 9
10CREATE OR REPLACE
11PROCEDURE q02 IS
12
13  pMAKER products.maker%TYPE;
14
15  no_selection EXCEPTION;
16
17  CURSOR c1 IS
18    SELECT DISTINCT P.maker
19    FROM Product P, Laptop L
20    WHERE P.model=L.model
21      AND L.hd>=1.0;
22
23BEGIN
24
25  DBMS_OUTPUT.PUT_LINE('Which manufactures make laptops with a hard disk of');
26  DBMS_OUTPUT.PUT_LINE('at least one gigabyte?');
27  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
28  DBMS_OUTPUT.PUT_LINE('MAKER');
29  DBMS_OUTPUT.PUT_LINE('-----');
30   
31  OPEN c1;
32
33  LOOP
34    FETCH c1 INTO pMAKER;
35
36    IF c1%ROWCOUNT = 0 THEN
37      RAISE no_selection;
38    END IF;
39
40    IF c1%FOUND THEN
41      DBMS_OUTPUT.PUT_LINE(pMAKER);
42    ELSE
43      EXIT;
44    END IF;
45  END LOOP;
46
47  CLOSE c1;
48
49EXCEPTION
50  WHEN no_selection
51  THEN
52    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
53
54END;
55/