Yurttas/PL/DBL/oracle/F/02/PC/q13.sp
Jump to navigation
Jump to search
1/*
2REM
3REM q13.SP
4REM
5REM Delete all Laptops made by a manufacturer that doesn't make
6REM printers.
7REM
8*/
9
10CREATE OR REPLACE
11PROCEDURE q13 IS
12
13 no_selection EXCEPTION;
14
15 deleted NUMBER;
16
17BEGIN
18 DBMS_OUTPUT.PUT_LINE('Delete all Laptops made by a manufacturer that doesn''t make');
19 DBMS_OUTPUT.PUT_LINE('printers.');
20
21 DELETE FROM Product
22 WHERE type = 'laptop'
23 AND maker NOT IN (SELECT maker
24 FROM Product
25 WHERE type = 'printer');
26
27 deleted := SQL%ROWCOUNT;
28
29 DBMS_OUTPUT.PUT_LINE(deleted||''||' rows deleted from Laptop');
30
31
32 DELETE FROM Laptop
33 WHERE model NOT IN (SELECT model
34 FROM Product);
35
36EXCEPTION
37 WHEN no_selection
38 THEN
39 DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
40
41END q13;
42/