Yurttas/PL/DBL/oracle/F/02/SPJ/q08.sp

 1/*
 2REM
 3REM q08.sp
 4REM
 5REM Delete all 'Houston' projects and related info from the database
 6REM
 7*/
 8
 9DECLARE
10
11  del_shp NUMBER;
12  del_prj NUMBER;
13
14BEGIN
15
16  DBMS_OUTPUT.PUT_LINE('Delete all Houston projects and related info');
17  DBMS_OUTPUT.PUT_LINE('from the database');
18  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
19 
20  DELETE FROM Shipments
21  WHERE jn IN (SELECT jn
22               FROM Projects
23               WHERE city = 'Houston');
24
25  del_shp := SQL%ROWCOUNT;
26    
27  DELETE FROM Projects
28  WHERE jn IN (SELECT jn
29               FROM Projects
30               WHERE city = 'Houston');
31
32  del_prj := SQL%ROWCOUNT;
33
34  DBMS_OUTPUT.PUT_LINE(del_shp||''||' rows deleted from Shipments');
35  DBMS_OUTPUT.PUT_LINE(del_prj||''||' rows deleted from Projects');
36
37END;
38/