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

 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/