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

 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/