· 8 years ago · Jan 19, 2018, 12:58 PM
1create or replace
2PACKAGE BODY BANKING_PROCS AS
3
4 PROCEDURE createBankLink(
5 p_bic VARCHAR2,
6 p_bankname VARCHAR2,
7 p_linkname VARCHAR2,
8 p_username VARCHAR2,
9 p_password VARCHAR2,
10 p_host VARCHAR2,
11 p_oracle_sid VARCHAR2) IS
12
13 numberOfLinks Number;
14 link_exists EXCEPTION;
15
16 BEGIN
17
18 -- check if linkname already exists
19 SELECT Count(*) INTO numberOfLinks FROM user_db_links WHERE DB_LINK = p_linkname;
20 IF (numberOfLinks > 0) THEN
21 RAISE link_exists;
22 END IF;
23
24 -- create database link here
25 EXECUTE IMMEDIATE 'CREATE DATABASE LINK '|| p_linkname ||' CONNECT TO '|| p_username ||' IDENTIFIED BY '|| p_password ||'
26 USING ''(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
27 (HOST = '|| p_host ||' )(PORT = 1521))) (CONNECT_DATA = (SID = '|| p_oracle_sid ||')))''';
28
29
30 -- store info in config table
31 INSERT INTO bank_config VALUES (p_bic,p_bankname,'remote',p_linkname,p_username,p_password,p_host,p_oracle_sid);
32
33 EXCEPTION
34 WHEN link_exists THEN raise_application_error(-20011,'Error in banking_proc.createDBLink: database link with same name already exists !!');
35
36 END createBankLink;
37
38
39
40
41 PROCEDURE withdraw (
42 p_value NUMBER,
43 p_iban VARCHAR2,
44 p_bic VARCHAR2 ) IS
45
46 numberOfAccounts Number;
47 local_bic VARCHAR2(30);
48 test VARCHAR2(30);
49 unknown_account EXCEPTION;
50 NOT_LOCAL_BIC EXCEPTION;
51 CURRENT_BALANCE NUMBER;
52 not_enough_money EXCEPTION;
53
54 BEGIN
55
56 SELECT BIC INTO local_bic FROM bank_config WHERE location='local';
57
58 IF (p_bic = local_bic) THEN -- local bic
59 SELECT Count(*) INTO numberOfAccounts FROM account WHERE iban = p_iban;
60 IF (numberOfAccounts < 1) THEN
61 RAISE unknown_account;
62 END IF;
63 SELECT BALANCE INTO CURRENT_BALANCE FROM ACCOUNT WHERE IBAN = P_IBAN;
64 IF CURRENT_BALANCE - P_VALUE < 0 THEN
65 raise not_enough_money;
66 END IF ;
67 UPDATE account SET balance = balance - p_value WHERE iban = p_iban;
68 ELSE
69 RAISE not_local_bic;
70 END IF;
71 EXCEPTION
72 WHEN unknown_account THEN raise_application_error(-20001,'Error in banking_proc.withdraw: unknown account !!');
73 WHEN NOT_LOCAL_BIC THEN RAISE_APPLICATION_ERROR(-20002,'Error in banking_proc.withdraw: not a local bic !!');
74 WHEN not_enough_money THEN RAISE_APPLICATION_ERROR(-20002,'Please work more!');
75 END withdraw;
76
77 PROCEDURE deposit (
78 p_value NUMBER,
79 p_iban VARCHAR2,
80 p_bic VARCHAR2 ) IS
81
82 numberOfAccounts Number;
83 LOCAL_BIC VARCHAR2(30);
84 test VARCHAR2(30);
85 unknown_account EXCEPTION;
86 not_local_bic EXCEPTION;
87
88 BEGIN
89
90 SELECT BIC INTO local_bic FROM bank_config WHERE location='local';
91
92 IF (p_bic = local_bic) THEN -- local bic
93 SELECT Count(*) INTO numberOfAccounts FROM account WHERE iban = p_iban;
94 IF (numberOfAccounts < 1) THEN
95 RAISE unknown_account;
96 END IF;
97 UPDATE account SET balance = balance + p_value WHERE iban = p_iban;
98 ELSE
99 RAISE not_local_bic;
100 END IF;
101 EXCEPTION
102 WHEN UNKNOWN_ACCOUNT THEN RAISE_APPLICATION_ERROR(-20001,'Error in banking_proc.deposit: unknown account !!');
103 WHEN not_local_bic THEN raise_application_error(-20002,'Error in banking_proc.deposit: not a local bic !!');
104
105
106
107 -- TODO put your PL/SQL Code here to deposit money from the local bank
108 dbms_output.put_line('not ready yet :-(');
109
110 END deposit;
111
112
113 PROCEDURE transfer (
114 p_value NUMBER,
115 from_iban VARCHAR2,
116 from_bic VARCHAR2,
117 to_iban VARCHAR2,
118 to_bic VARCHAR2
119 ) IS
120
121 NUMBEROFACCOUNTS NUMBER;
122 LOCAL_BIC VARCHAR2(30);
123 BIC_LINK VARCHAR2(30);
124 unknown_bic EXCEPTION;
125
126 BEGIN
127
128 SELECT BIC INTO LOCAL_BIC FROM BANK_CONFIG WHERE LOCATION='local';
129
130 Banking_procs.withdraw(p_value,from_iban,from_bic);
131 IF (TO_BIC = LOCAL_BIC) THEN -- local bic
132 DEPOSIT(P_VALUE,TO_IBAN,TO_BIC);
133 ELSE
134 SELECT count(*) into NUMBEROFACCOUNTS FROM BANK_CONFIG WHERE BIC = TO_BIC;
135 IF NUMBEROFACCOUNTS < 1 THEN
136 RAISE UNKNOWN_BIC;
137 END IF ;
138 SELECT LINKNAME INTO BIC_LINK FROM BANK_CONFIG WHERE BIC = TO_BIC;
139 EXECUTE IMMEDIATE 'BEGIN BANKING_PROCS.deposit@'|| BIC_LINK || '(' || P_VALUE || ',''' || TO_IBAN || ''',''' || TO_BIC ||'''); END;';
140 END IF;
141
142 -- TODO put your PL/SQL Code here to transfer money from the local bank to an remote bank.
143 -- Attention: use the previously defined deposit and withdraw PL/SQL procedures again here.
144 -- You can also use PL/SQL procedures via the database link at the remote bank.
145 DBMS_OUTPUT.PUT_LINE('not ready yet :-(');
146 EXCEPTION
147 WHEN unknown_bic THEN raise_application_error(-20002,'Unknown Bank!');
148
149 END transfer;
150
151END BANKING_PROCS;