Yurttas/PL/DBL/oracle/F/02/Bank/u04.sp

 1CREATE OR REPLACE
 2PROCEDURE u04 IS
 3
 4  cursor_handle INTEGER;
 5
 6  depositor_deleted INTEGER;
 7
 8BEGIN
 9
10  DBMS_OUTPUT.PUT('Deleting all accounts at branches located in Perryridge...');
11  
12  cursor_handle := DBMS_SQL.OPEN_CURSOR;
13
14  DBMS_SQL.PARSE(cursor_handle, 
15                'DELETE FROM Depositor '                 ||
16                'WHERE branchname IN (SELECT branchname '||
17                                     'FROM Branch '      ||
18                                     'WHERE branchcity = ''Perryridge'')',
19                 DBMS_SQL.V7);
20
21  depositor_deleted := DBMS_SQL.EXECUTE(cursor_handle);
22
23  DBMS_SQL.CLOSE_CURSOR(cursor_handle);
24
25  DBMS_OUTPUT.PUT_LINE('Done.');
26
27EXCEPTION
28  WHEN OTHERS
29  THEN
30    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
31    DBMS_OUTPUT.PUT_LINE('Error deleting all accounts at branches located in Perryridge.');
32
33END;
34/