· 7 years ago · Jan 04, 2019, 08:32 AM
1
2DROP PACKAGE BANKING_PROCS;
3
4CREATE PACKAGE BANKING_PROCS AS
5
6 PROCEDURE createBankLink(
7 p_bic VARCHAR2,
8 p_bankname VARCHAR2,
9 p_linkname VARCHAR2,
10 p_username VARCHAR2,
11 p_password VARCHAR2,
12 p_host VARCHAR2,
13 p_oracle_sid VARCHAR2
14 );
15
16 PROCEDURE withdraw (
17 p_value NUMBER,
18 p_iban VARCHAR2,
19 p_bic VARCHAR2
20 );
21
22 PROCEDURE deposit (
23 p_value NUMBER,
24 p_iban VARCHAR2,
25 p_bic VARCHAR2
26 );
27
28 PROCEDURE transfer (
29 p_value NUMBER,
30 from_iban VARCHAR2,
31 from_bic VARCHAR2,
32 to_iban VARCHAR2,
33 to_bic VARCHAR2
34 );
35
36END BANKING_PROCS;
37/
38
39SHOW ERRORS;
40
41
42
43CREATE PACKAGE BODY BANKING_PROCS AS
44
45 PROCEDURE createBankLink(
46 p_bic VARCHAR2,
47 p_bankname VARCHAR2,
48 p_linkname VARCHAR2,
49 p_username VARCHAR2,
50 p_password VARCHAR2,
51 p_host VARCHAR2,
52 p_oracle_sid VARCHAR2)
53
54 IS
55
56 numberOfLinks Number;
57 link_exists EXCEPTION;
58
59 BEGIN
60
61 -- check if linkname already exists
62 SELECT Count(*) INTO numberOfLinks FROM user_db_links WHERE DB_LINK = p_linkname || p_bic || '.CS.UNIBAS.CH';
63 IF (numberOfLinks > 0) THEN
64 RAISE link_exists;
65 END IF;
66
67 -- create database link here
68 EXECUTE IMMEDIATE 'CREATE DATABASE LINK '|| p_linkname || p_bic ||' CONNECT TO '|| p_username ||' IDENTIFIED BY '|| p_password ||'
69 USING ''(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
70 (HOST = '|| p_host ||' )(PORT = 1521))) (CONNECT_DATA = (SID = '|| p_oracle_sid ||')))''';
71
72
73 -- store info in config table
74 INSERT INTO bank_config VALUES (p_bic,p_bankname,'remote',p_linkname,p_username,p_password,p_host,p_oracle_sid);
75
76 dbms_output.put_line('Created link to bank ' || p_bic);
77
78 EXCEPTION
79 WHEN link_exists THEN raise_application_error(-20011,'Error in banking_proc.createDBLink: database link with same name already exists !!');
80
81 END createBankLink;
82
83
84
85 -- WITHDRAW --
86 PROCEDURE withdraw (
87 p_value NUMBER,
88 p_iban VARCHAR2,
89 p_bic VARCHAR2)
90 IS
91
92 numberOfAccounts Number;
93 current_balance NUMBER;
94 local_bic VARCHAR2(30);
95 --test VARCHAR2(30);
96 unknown_account EXCEPTION;
97 not_local_bic EXCEPTION;
98 negative_value EXCEPTION;
99 not_enough_balance EXCEPTION;
100
101 BEGIN
102
103 dbms_output.enable;
104
105 IF (p_value < 0) THEN
106 RAISE negative_value;
107 END IF;
108
109 SELECT BIC INTO local_bic FROM bank_config WHERE location='local';
110
111 IF (p_bic = local_bic) THEN -- local bic
112 SELECT Count(*) INTO numberOfAccounts FROM account WHERE iban = p_iban;
113 IF (numberOfAccounts < 1) THEN
114 RAISE unknown_account;
115 END IF;
116 SELECT balance INTO current_balance FROM account WHERE iban = p_iban;
117
118 IF (current_balance - p_value < 0) THEN
119 RAISE not_enough_balance;
120 END IF;
121
122 UPDATE account SET balance = balance - p_value WHERE iban = p_iban;
123 dbms_output.put_line('Withdraw: ' || p_value || ' to local bank ' || p_bic);
124 ELSE
125 dbms_output.put_line('Not allowed to deposit to remote bank ' || p_bic);
126 RAISE not_local_bic;
127 END IF;
128 EXCEPTION
129 WHEN unknown_account THEN raise_application_error(-20001, 'Error withdraw: unknown account ' || p_iban);
130 WHEN not_local_bic THEN raise_application_error(-20002, 'Error in withdraw: ' || p_bic || ' not a local bic');
131 WHEN negative_value THEN raise_application_error(-20003, 'Error in withdraw: negative value ' || p_value);
132 WHEN not_enough_balance THEN raise_application_error(-20004, 'Error in withdraw: not enough balance (' || current_balance || '-' || p_value || ')');
133 END withdraw;
134
135 -- DEPOSIT --
136 PROCEDURE deposit (
137 p_value NUMBER,
138 p_iban VARCHAR2,
139 p_bic VARCHAR2 )
140
141 IS
142
143 str VARCHAR2(128);
144 numberOfLinks Number;
145 linkToBank VARCHAR2(30);
146 numberOfAccounts Number;
147 local_bic VARCHAR2(30);
148 test VARCHAR2(30);
149 unknown_account EXCEPTION;
150 not_local_bic EXCEPTION;
151 negative_value EXCEPTION;
152
153 BEGIN
154
155 dbms_output.enable;
156
157 IF (p_value < 0) THEN
158 RAISE negative_value;
159 END IF;
160
161
162 SELECT BIC INTO local_bic FROM bank_config WHERE location='local';
163 linkToBank := 'LINKTOBANK' || p_bic || '.CS.UNIBAS.CH';
164
165 IF (p_bic = local_bic) THEN -- local bic
166 SELECT Count(*) INTO numberOfAccounts FROM account WHERE iban = p_iban;
167 IF (numberOfAccounts < 1) THEN
168 RAISE unknown_account;
169 END IF;
170 UPDATE account SET balance = balance + p_value WHERE iban = p_iban;
171 dbms_output.put_line('Deposit: ' || p_value || ' to local bank ' || p_bic);
172 ELSE
173 SELECT count(*) INTO numberOfLinks FROM user_db_links WHERE DB_LINK = linkToBank;
174 IF (numberOfLinks <= 0) THEN
175 dbms_output.put_line('Creating new link to bank' || p_bic);
176 createBankLink(p_bic, p_bic || ' Bank', 'LinkToBank', 'cs341_8', 'BaLyA3Ka', p_bic || '.dbis.cs.unibas.ch', 'orcl' || p_bic);
177 END IF;
178
179 str := 'BEGIN banking_procs.deposit@LINKTOBANK' || p_bic || '.CS.UNIBAS.CH( ' || p_value || ',''' || p_iban || ''',''' || p_bic || '''); END;';
180 --dbms_output.put_line('executing: ' || str);
181 execute immediate str;
182 dbms_output.put_line('Deposit: ' || p_value || ' to remote bank ' || p_bic);
183
184 END IF;
185 EXCEPTION
186
187 WHEN unknown_account THEN raise_application_error(-20001,'Error in deposit: unknown account ' || p_iban);
188 WHEN not_local_bic THEN raise_application_error(-20002,'Error in deposit: ' || p_bic || ' not a local bic');
189 WHEN negative_value THEN raise_application_error(-20003, 'Error in deposit: negative value ' || p_value);
190 END deposit;
191
192 -- TRANSFER --
193 PROCEDURE transfer (
194 p_value NUMBER,
195 from_iban VARCHAR2,
196 from_bic VARCHAR2,
197 to_iban VARCHAR2,
198 to_bic VARCHAR2)
199 IS
200 local_bic VARCHAR2(30);
201 same_account EXCEPTION;
202
203 unknown_account EXCEPTION;
204 not_local_bic EXCEPTION;
205
206
207
208 BEGIN
209 dbms_output.enable;
210 SELECT BIC INTO local_bic FROM bank_config WHERE location='local';
211
212 IF (from_iban = to_iban and from_bic = to_bic) THEN
213 RAISE same_account;
214 ELSE
215 withdraw(p_value, from_iban, from_bic);
216 deposit(p_value, to_iban, to_bic);
217 COMMIT;
218 dbms_output.put_line('COMMIT: Transfered ' || p_value || ' from ' || from_iban || '@' || from_bic || ' to ' || to_iban || '@' || to_bic);
219 END IF;
220
221 EXCEPTION
222 WHEN same_account THEN raise_application_error(-20003, 'No Transfer: from and to same account on same bank');
223 WHEN unknown_account THEN
224 ROLLBACK;
225 dbms_output.put_line('ROLLBACK: Unkown Account');
226 WHEN not_local_bic THEN
227 ROLLBACK;
228 dbms_output.put_line('ROLLBACK: widthdraw from remote bank');
229 END transfer;
230
231END BANKING_PROCS;
232/
233
234SHOW ERRORS;