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

 1CREATE OR REPLACE
 2PROCEDURE u03 IS
 3
 4  cursor_handle INTEGER;
 5
 6  loan_deleted INTEGER;
 7
 8BEGIN
 9
10  DBMS_OUTPUT.PUT('Deleting all loans with loan numbers between 1300 and 1500...');
11  
12  cursor_handle := DBMS_SQL.OPEN_CURSOR;
13
14  DBMS_SQL.PARSE(cursor_handle, 
15                'DELETE FROM Borrower '||
16                'WHERE loannumber BETWEEN 1300 AND 1500',
17                 DBMS_SQL.V7);
18
19  loan_deleted := DBMS_SQL.EXECUTE(cursor_handle);
20
21  DBMS_SQL.CLOSE_CURSOR(cursor_handle);
22
23  DBMS_OUTPUT.PUT_LINE('Done.');
24
25EXCEPTION
26  WHEN OTHERS
27  THEN
28    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
29    DBMS_OUTPUT.PUT_LINE('Error deleting loans between 1300 and 1500.');
30
31END;
32/