Yurttas/PL/DBL/oracle/F/02/Bank/u06.sp
Jump to navigation
Jump to search
1CREATE OR REPLACE
2PROCEDURE u06 IS
3
4 cursor_handle INTEGER;
5
6 balance_updated INTEGER;
7
8BEGIN
9
10 DBMS_OUTPUT.PUT_LINE('Increase accounts with balances over $10,000 by 6 percent,');
11 DBMS_OUTPUT.PUT('and increase all others by 5 percent...');
12
13 cursor_handle := DBMS_SQL.OPEN_CURSOR;
14
15 DBMS_SQL.PARSE(cursor_handle,
16 'UPDATE Depositor ' ||
17 'SET balance = balance*1.06 '||
18 'WHERE balance>10000',
19 DBMS_SQL.V7);
20
21 balance_updated := DBMS_SQL.EXECUTE(cursor_handle);
22
23 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
24
25 cursor_handle :=DBMS_SQL.OPEN_CURSOR;
26
27 DBMS_SQL.PARSE(cursor_handle,
28 'UPDATE Depositor ' ||
29 'SET balance = balance*1.05 '||
30 'WHERE balance<=10000',
31 DBMS_SQL.V7);
32
33 balance_updated := DBMS_SQL.EXECUTE(cursor_handle);
34
35 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
36
37 DBMS_OUTPUT.PUT_LINE('Done.');
38
39EXCEPTION
40 WHEN OTHERS
41 THEN
42 DBMS_SQL.CLOSE_CURSOR(cursor_handle);
43 DBMS_OUTPUT.PUT_LINE('Error increasing account balances.');
44
45END;
46/