CREATE TABLE acct(acct_no NUMBER, balance NUMBER);
CREATE TABLE locking(acct_no NUMBER, type VARCHAR2(8));
CREATE TABLE holding(acct_no NUMBER, amount NUMBER);
INSERT INTO acct VALUES(10001, 2000);
INSERT INTO acct VALUES(10002, 500);
INSERT INTO acct VALUES(10003, 1500);
INSERT INTO acct VALUES(10004, 300);
INSERT INTO locking VALUES(10004, 'locked');
INSERT INTO holding VALUES(10001, 1000);
INSERT INTO holding VALUES(10003, 800);
commit;
/************************************************
使用源表名称:holding 冻结账户信息表
Locking锁定账户信息表
acct账户信息表
功能描述:账户间转账
a) 确定转出账户没有被锁。
b) 确定转入账户的存在。
c) 确定转入账户没有被锁。
d) 确定转出账户中有足够余额满足转账
e) 确定转出账户在转账后满足冻结额的要求
f) 确定在转出账户上成功地扣去了转账的金额
g) 确定在转入账户上成功地加上了转账的金额
参数说明:transfer_in 资金转入账户
transfer_out 资金转出账户
transfer_balance 转账金额
oi_flag 返回标志 0-正常 -1 失败
os_msg 返回信息
************************************************/
create or replace
PROCEDURE transfer_account(transfer_in IN NUMBER,
transfer_out IN NUMBER,
transfer_balance IN NUMBER,
oi_flag OUT PLS_INTEGER,
os_msg OUT VARCHAR2) AS
balance_after_transfer acct.balance%TYPE; --转账后的余额
BEGIN
oi_flag := 0;
os_msg := 'success';
IF transfer_in = transfer_out THEN
oi_flag := -1;
os_msg := '转入、转出账户相同,不必转账';
RETURN;
END IF;
--a) 确定转出账户没有被锁。
IF (is_account_lock(transfer_out) = 0) THEN
oi_flag := -1;
os_msg := '转出账户被锁定';
RETURN;
END IF;
--b) 确定转入账户的存在。
IF (is_account_exist(transfer_in) = -1) THEN
oi_flag := -1;
os_msg := '转入账户不存在';
RETURN;
END IF;
--c) 确定转入账户没有被锁。
IF (is_account_lock(transfer_in) = 0) THEN
oi_flag := -1;
os_msg := '转入账户被锁定';
RETURN;
END IF;
--d) 确定转出账户中有足够余额满足转账
DECLARE
num_transfer_balance acct.balance%TYPE;
BEGIN
SELECT balance
INTO num_transfer_balance
FROM acct
WHERE acct_no = transfer_out;
IF num_transfer_balance < transfer_balance THEN
oi_flag := -1;
os_msg := '转出账户余额不足,当前余额为' || num_transfer_balance;
RETURN;
END IF;
balance_after_transfer := num_transfer_balance - transfer_balance;
EXCEPTION
WHEN no_data_found THEN
oi_flag := -1;
os_msg := '转出账户不存在';
RETURN;
WHEN too_many_rows THEN
oi_flag := -1;
os_msg := '转出账户不唯一';
RETURN;
--注意这里不加when others,通用异常放外围
END;
--e) 确定转出账户在转账后满足冻结额的要求
--对冻结账户执行判断
IF is_account_holding(transfer_out) = 0 THEN
DECLARE
num_hoding_balance holding.amount%TYPE;
BEGIN
SELECT amount
INTO num_hoding_balance
FROM holding
WHERE acct_no = transfer_out;
IF num_hoding_balance > balance_after_transfer THEN
oi_flag := -1;
os_msg := '该账户为冻结账户,转账后余额低于冻结额,当前冻结额为' || num_hoding_balance;
RETURN;
END IF;
EXCEPTION
WHEN no_data_found THEN
oi_flag := -1;
os_msg := '冻结账户不存在' || '输入账号为' || transfer_out;
RETURN;
WHEN too_many_rows THEN
oi_flag := -1;
os_msg := '冻结账户不唯一' || '输入账号为' || transfer_out;
RETURN;
END;
END IF;
--f) 确定在转出账户上成功地扣去了转账的金额
UPDATE acct
SET balance = balance - transfer_balance
WHERE acct_no = transfer_out;
IF SQL%ROWCOUNT <> 1 THEN
oi_flag := -1;
os_msg := '更新转出账户余额失败';
RETURN;
END IF;
--g) 确定在转入账户上成功地加上了转账的金额
UPDATE acct
SET balance = balance + transfer_balance
WHERE acct_no = transfer_in;
IF SQL%ROWCOUNT <> 1 THEN
oi_flag := -1;
os_msg := '更新转入账户余额失败';
RETURN;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
oi_flag := -1;
os_msg := '转账出现异常,异常信息为' || SQLERRM || '|' || SQLCODE;
ROLLBACK;
--此处日志未处理,有兴趣可自行参考前面的例子补充上
END;
/************************************************
使用源表名称:locking锁定账户信息表
功能描述:账号是否被锁,返回0时被锁
参数说明:acctno 账号
************************************************/
create or replace
FUNCTION is_account_lock(acctno acct.acct_no%TYPE) RETURN PLS_INTEGER AS
lock_acc_num PLS_INTEGER;
BEGIN
SELECT COUNT(1) INTO lock_acc_num FROM locking WHERE acct_no = acctno;
IF lock_acc_num > 0 THEN
RETURN 0;
ELSE
RETURN - 1;
END IF;
END is_account_lock;
/************************************************
使用源表名称:acct锁定账户信息表
功能描述:账号是否存在,返回-1不存在
参数说明:acctno 账号
************************************************/
create or replace
FUNCTION is_account_exist(acctno acct.acct_no%TYPE) RETURN PLS_INTEGER AS
acc_num PLS_INTEGER;
BEGIN
SELECT COUNT(1) INTO acc_num FROM acct WHERE acct.acct_no = acctno;
IF acc_num > 0 THEN
RETURN 0;
ELSE
RETURN - 1;
END IF;
END is_account_exist;
/************************************************
使用源表名称:holding 冻结账户信息表
功能描述:账号是否冻结
参数说明:acctno 账号
************************************************/
create or replace
FUNCTION is_account_holding(acctno acct.acct_no%TYPE) RETURN PLS_INTEGER AS
acc_num PLS_INTEGER;
BEGIN
SELECT COUNT(1) INTO acc_num FROM holding WHERE acct_no = acctno;
IF acc_num > 0 THEN
RETURN 0;
ELSE
RETURN - 1;
END IF;
END is_account_holding;
/
另附测试程序
CREATE OR REPLACE PACKAGE account_test IS
--转账程序测试集
PROCEDURE transfer_account_test(oi_flag OUT PLS_INTEGER,
os_msg OUT VARCHAR2);
--测试
PROCEDURE is_account_lock_test(oi_flag OUT PLS_INTEGER,
os_msg OUT VARCHAR2);
END account_test;
/
CREATE OR REPLACE PACKAGE BODY account_test IS
--测试案例集
PROCEDURE transfer_account_test(oi_flag OUT PLS_INTEGER,
os_msg OUT VARCHAR2) AS
BEGIN
oi_flag := 0;
os_msg := 'success';
--a) 确定转出账户没有被锁。
is_account_lock_test(oi_flag, os_msg); --测试函数is_account_lock
IF oi_flag = -1 THEN
RETURN;
END IF;
--传入锁定转出账户
account.transfer_account(10001, 10004, 1, oi_flag, os_msg);
IF oi_flag = 0 THEN
RETURN;
END IF;
--传入非锁定转出账户
account.transfer_account(10001, 10002, 1, oi_flag, os_msg);
IF oi_flag = -1 THEN
RETURN;
END IF;
--b) 确定转入账户的存在。
--传入非法的转入账户10009
account.transfer_account(10009, 10002, 1, oi_flag, os_msg);
IF oi_flag = 0 THEN
RETURN;
END IF;
--传入合法的转入账户10001
account.transfer_account(10001, 10002, 1, oi_flag, os_msg);
IF oi_flag = -1 THEN
RETURN;
END IF;
--c) 确定转入账户没有被锁。
--传入锁定的转入账户
account.transfer_account(10004, 10002, 1, oi_flag, os_msg);
IF oi_flag = 0 THEN
RETURN;
END IF;
--传入非锁定的转入账户
account.transfer_account(10001, 10002, 1, oi_flag, os_msg);
IF oi_flag = -1 THEN
RETURN;
END IF;
--d) 确定转出账户中有足够余额满足转账
--传入超大的金额
account.transfer_account(10001, 10002, 100000, oi_flag, os_msg);
IF oi_flag = 0 THEN
RETURN;
END IF;
--传入正常的金额按(f)、(g)的案例覆盖
/*account.transfer_account(10001, 10002, 1, oi_flag, os_msg);
IF oi_flag = -1 THEN
RETURN;
END IF;*/
--e) 确定转出账户在转账后满足冻结额的要求
--传入冻结账户并且不满足冻结金额要求 10003账户转出1000后将不满足要求
account.transfer_account(10001, 10003, 1000, oi_flag, os_msg);
IF oi_flag = 0 THEN
RETURN;
END IF;
--传入冻结账户满足冻结金额要求
account.transfer_account(10002, 10001, 1, oi_flag, os_msg);
IF oi_flag = -1 THEN
RETURN;
END IF;
--传入非冻结账户以下案例可覆盖
--f) 确定在转出账户上成功地扣去了转账的金额
account.transfer_account(10003, 10002, 1, oi_flag, os_msg);
IF oi_flag = -1 THEN
RETURN;
END IF;
--g) 确定在转入账户上成功地加上了转账的金额
account.transfer_account(10003, 10002, 1, oi_flag, os_msg);
IF oi_flag = -1 THEN
RETURN;
END IF;
END;
--测试账户是否锁定
PROCEDURE is_account_lock_test(oi_flag OUT PLS_INTEGER,
os_msg OUT VARCHAR2) AS
BEGIN
/*测试函数is_account_lock
*/
oi_flag := 0;
os_msg := 'success';
DECLARE
expect_account locking.acct_no%TYPE := 10004; --已锁定的账户
expect_result PLS_INTEGER := 0; --期望为锁定
real_resutl PLS_INTEGER;
BEGIN
real_resutl := account.is_account_lock(expect_account);
IF expect_result = real_resutl THEN
RETURN;
ELSE
oi_flag := -1;
--此处加上拼接,便于在测试集中调用且不会丢失其他测试程序的结果,注意要加上字符数限制
os_msg := substrb(os_msg || '|bug in is_account_lock,期望值:' ||
expect_result || '实际值:' || real_resutl || '|',
1,
1000);
END IF;
END;
END;
END account_test;
/