Yurttas/PL/DBL/oracle/F/02/PC/q05.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 q05.sp REM REM Find those manufacturers that sell Laptops, but not PC's. REM: CREATE OR REPLACE PROCED...")
1/*
2REM
3REM q05.sp
4REM
5REM Find those manufacturers that sell Laptops, but not PC's.
6REM
7*/
8
9CREATE OR REPLACE
10PROCEDURE q05 IS
11
12 pMAKER Product.maker%TYPE;
13
14 no_selection EXCEPTION;
15
16 CURSOR c1 IS
17 (SELECT P.maker
18 FROM Product P, Laptop L
19 WHERE P.model=L.model)
20 MINUS
21 (SELECT P.maker
22 FROM Product P, PC
23 WHERE P.model=PC.model);
24
25BEGIN
26
27 DBMS_OUTPUT.PUT_LINE('Manufacturers that sell laptops, but not PCs.');
28 DBMS_OUTPUT.PUT_LINE('Laptop manufactuers');
29 DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
30 DBMS_OUTPUT.PUT_LINE('Manufacturer');
31 DBMS_OUTPUT.PUT_LINE('------------');
32
33 OPEN c1;
34
35 LOOP
36 FETCH c1 INTO pMAKER;
37
38 IF c1%ROWCOUNT = 0 THEN
39 RAISE no_selection;
40 END IF;
41
42 IF c1%FOUND THEN
43 DBMS_OUTPUT.PUT_LINE(pMAKER);
44 ELSE
45 EXIT;
46 END IF;
47 END LOOP;
48
49 CLOSE c1;
50
51EXCEPTION
52 WHEN no_selection
53 THEN
54 DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
55
56END;
57/