Yurttas/PL/DBL/oracle/F/02/Bank/u08.sp
Jump to navigation
Jump to search
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/