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

 1CREATE OR REPLACE
 2PROCEDURE u08 IS
 3
 4  cursor_handle INTEGER;
 5
 6  savings_inserted INTEGER;
 7
 8BEGIN
 9
10  DBMS_OUTPUT.PUT_LINE('Provide all loan customers in the ''Perryridge'' branch with');
11  DBMS_OUTPUT.PUT_LINE('a $200 savings account.(Let the loannumber serve as the');
12  DBMS_OUTPUT.PUT('accountnumber for the new savings account)...');
13  
14  cursor_handle := DBMS_SQL.OPEN_CURSOR;
15
16  DBMS_SQL.PARSE(cursor_handle, 
17                'INSERT INTO Depositor '||
18                '  SELECT branchname, loannumber, customername, 200 '||
19                '  FROM Borrower '||
20                '  WHERE branchname = ''Perryridge''',
21                 DBMS_SQL.V7);
22
23  savings_inserted := DBMS_SQL.EXECUTE(cursor_handle);
24
25  DBMS_SQL.CLOSE_CURSOR(cursor_handle);
26
27  DBMS_OUTPUT.PUT_LINE('Done.');
28
29EXCEPTION
30  WHEN OTHERS
31  THEN
32    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
33    DBMS_OUTPUT.PUT_LINE('Error creating new savings accounts.');
34
35END;
36/