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

 1/*
 2REM
 3REM q14p
 4REM
 5REM For each laptop made by 'E', add one inch
 6REM to the screen size and subtract $100
 7REM from the price.
 8REM
 9*/
10
11CREATE OR REPLACE 
12PROCEDURE q14 IS
13
14  no_selection EXCEPTION;
15
16  pMODEL Product.maker%TYPE;
17
18BEGIN
19
20  UPDATE Laptop
21  SET screen = screen + 1
22  WHERE model IN (SELECT Laptop.model
23                  FROM Product, Laptop
24                  WHERE Product.model = Laptop.model
25                    AND Product.maker = 'E');
26  UPDATE Laptop
27  SET price = price - 100
28  WHERE model IN (SELECT Laptop.model
29                  FROM Product, Laptop
30                  WHERE Product.model = Laptop.model
31                    AND Product.maker = 'E');
32
33  DBMS_OUTPUT.PUT_LINE('Records updated');
34  
35EXCEPTION
36  WHEN no_selection
37  THEN
38    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
39
40END q14;
41/