Yurttas/PL/DBL/oracle/F/02/PC/q02.sp
Revision as of 23:29, 4 November 2013 by MassBot1 (talk | contribs) (Created page with "<syntaxhighlight lang="text" line start="1" enclose="div"> →REM REM q02.sp REM REM Which manufactures make laptops with a hard disk of REM at least one gigabyte? REM: CRE...")
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/