补充今天早上发的ATM
下午就直接发写的代码了我整合了一下 和之前可能有点小小的差别不过还是可以实现的。---------------多多支持一下，谢谢！--实现ATM机相应的功能
/*账户表、交易记录表、转账记录表*/
--1.取款/转帐/存款
--2.跨行转帐，2h内到帐
--3.每笔交易存入交易记录表
--全球唯一编码,作为主键生成
select sys_guid() from dual;
create table account_bank(
pk_serial varchar2(32),
account_no varchar2(4),
acc_pwd varchar2(6),
money number(11,2) default 0.0,
bank_type varchar2(1),
created_by varchar2(50),
created_date date,
updated_by varchar2(50),
updated_date date
);
comment on table account_bank is '账户表';
comment on column account_bank.pk_serial is '主键';
comment on column account_bank.account_no is '帐号';
comment on column account_bank.acc_pwd is '密码';
comment on column account_bank.money is '金额';
comment on column account_bank.bank_type is '1.工商银行2.农业银行3.建设银行4.中国银行';
comment on column account_bank.created_by is '创建人';
comment on column account_bank.created_date is '创建时间';
comment on column account_bank.updated_by is '修改人';
comment on column account_bank.updated_date is '修改时间';
create unique index un_ix_account_bank on account_bank(pk_serial);
create unique index un_ix_account_no on account_bank(account_no);
alter table account_bank
add constraint pk_account_bank primary key(pk_serial)
using index un_ix_account_bank;
create table record_deal(
pk_serial varchar2(32),
account_no varchar2(4),
deal_money number(11,2),
money number(11,2) default 0.0,
deal_type varchar2(1),
deal_date date,
deal_account_no varchar2(4),
created_by varchar2(50),
created_date date,
updated_by varchar2(50),
updated_date date
);
comment on table record_deal is '交易记录表';
comment on column record_deal.pk_serial is '主键';
comment on column record_deal.account_no is '帐号';
comment on column record_deal.deal_money is '交易金额';
comment on column record_deal.money is '金额';
comment on column record_deal.deal_type is '1.存款2.取款3.转入4.转出';
comment on column record_deal.deal_date is '交易时间';
comment on column record_deal.deal_account_no is '交易账户';
comment on column record_deal.created_by is '创建人';
comment on column record_deal.created_date is '创建时间';
comment on column record_deal.updated_by is '修改人';
comment on column record_deal.updated_date is '修改时间';
create unique index un_ix_record_deal on record_deal(pk_serial);
alter table record_deal
add constraint pk_record_deal primary key(pk_serial)
using index un_ix_record_deal;
create table record_transfer(
pk_serial varchar2(32),
src_account_no varchar2(4),
target_account_no varchar2(4),
deal_money number(11,2),
deal_type varchar2(1),
deal_date date,
status varchar2(1),
created_by varchar2(50),
created_date date,
updated_by varchar2(50),
updated_date date
);
comment on table record_transfer is '转帐记录表';
comment on column record_transfer.pk_serial is '主键';
comment on column record_transfer.src_account_no is '转出帐号';
comment on column record_transfer.target_account_no is '转入帐号';
comment on column record_transfer.deal_money is '交易金额';
comment on column record_transfer.deal_type is '1.同行2.跨行';
comment on column record_transfer.deal_date is '交易时间';
comment on column record_transfer.status is '1.待处理2.完成3.失败4.失效';
comment on column record_transfer.created_by is '创建人';
comment on column record_transfer.created_date is '创建时间';
comment on column record_transfer.updated_by is '修改人';
comment on column record_transfer.updated_date is '修改时间';
create unique index un_ix_record_transfer on record_transfer(pk_serial);
alter table record_transfer
add constraint pk_record_transfer primary key(pk_serial)
using index un_ix_record_transfer;
CREATE OR REPLACE PACKAGE deal_bank_pkg IS
/*
--作用：实现账户存款和取款功能
--p_acc_no：帐号
--p_deal_money:交易金额
--p_deal_type: 0存款1取款
*/
PROCEDURE proc_my_deal(p_acc_no IN VARCHAR2,
p_deal_money IN NUMBER,
p_deal_typeIN VARCHAR2);
FUNCTION func_check_deal(p_acc_no IN VARCHAR2,
p_deal_money IN NUMBER,
p_deal_typeIN VARCHAR2) RETURN BOOLEAN;
PROCEDURE proc_transfer_deal(p_src_acc_no IN VARCHAR2,
p_target_acc_no IN VARCHAR2,
p_deal_money IN NUMBER);
--检查用户是否存在
FUNCTION func_check_account_exists(p_acc_no IN VARCHAR2) RETURN BOOLEAN;
--检查用户余额是否满足交易
FUNCTION func_check_money(p_acc_no IN VARCHAR2, p_deal_money IN NUMBER)
RETURN BOOLEAN;
--给job调用的定时任务
PROCEDURE proc_bank_for_job;
END deal_bank_pkg;
/
CREATE OR REPLACE PACKAGE BODY deal_bank_pkg IS
/*
--作用：实现账户存款和取款功能
--p_acc_no：帐号
--p_deal_money:交易金额
--p_deal_type: 0存款1取款
*/
PROCEDURE proc_my_deal(p_acc_no IN VARCHAR2,
p_deal_money IN NUMBER,
p_deal_typeIN VARCHAR2) IS
v_now DATE := SYSDATE;
v_money NUMBER(11, 2);
v_proc_name VARCHAR2(50) := 'deal_bank_pkg.proc_my_deal';
v_deal_type VARCHAR2(1);
BEGIN
IF func_check_deal(p_acc_no, p_deal_money, p_deal_type) THEN
IF p_deal_type = '0' THEN
v_deal_type := '1';
--存款
UPDATE account_bank
SET money = money + p_deal_money,
updated_by = v_proc_name,
updated_date = v_now
WHERE account_no = p_acc_no;
ELSIF p_deal_type = '1' THEN
v_deal_type := '2';
UPDATE account_bank
SET money = money - p_deal_money,
updated_by = v_proc_name,
updated_date = v_now
WHERE account_no = p_acc_no;
END IF;
--获得余额
SELECT money
INTO v_money
FROM account_bank
WHERE account_no = p_acc_no;
--写交易记录
INSERT INTO record_deal
(pk_serial,
account_no,
deal_money,
money,
deal_type,
deal_date,
created_by,
created_date,
updated_by,
updated_date)
VALUES
(sys_guid(),
p_acc_no,
p_deal_money,
v_money,
v_deal_type,
v_now,
v_proc_name,
v_now,
v_proc_name,
v_now);
END IF;
END proc_my_deal;
PROCEDURE proc_transfer_deal(p_src_acc_no IN VARCHAR2,
p_target_acc_no IN VARCHAR2,
p_deal_money IN NUMBER) IS
v_src_bankVARCHAR2(1); --转出账户的银行
v_tgt_bankVARCHAR2(1); --转入账户的银行
v_src_money NUMBER(11, 2); --转出账户的余额
v_tgt_money NUMBER(11, 2); --转入账户的余额
v_proc_name VARCHAR2(50) := 'deal_bank_pkg.proc_transfer_deal';
v_now DATE := SYSDATE;
BEGIN
--判断两个账户是否存在
IF func_check_account_exists(p_src_acc_no) AND
func_check_account_exists(p_target_acc_no) THEN
IF func_check_money(p_src_acc_no, p_deal_money) THEN
--获得转出行
SELECT bank_type
INTO v_src_bank
FROM account_bank
WHERE account_no = p_src_acc_no;
--获得转入行
SELECT bank_type
INTO v_tgt_bank
FROM account_bank
WHERE account_no = p_target_acc_no;
--同行转帐
IF v_src_bank = v_tgt_bank THEN
--转出账户支出
UPDATE account_bank
SET money = money - p_deal_money,
updated_by = v_proc_name,
updated_date = v_now
WHERE account_no = p_src_acc_no;
--获得余额
SELECT money
INTO v_src_money
FROM account_bank
WHERE account_no = p_src_acc_no;
--写入交易记录
INSERT INTO record_deal
(pk_serial,
account_no,
deal_money,
money,
deal_type,
deal_date,
deal_account_no,
created_by,
created_date,
updated_by,
updated_date)
VALUES
(sys_guid(),
p_src_acc_no,
p_deal_money,
v_src_money,
'4', --1.存款2.取款3.转入4.转出
v_now,
p_target_acc_no,
v_proc_name,
v_now,
v_proc_name,
v_now);
--转入账户入帐
UPDATE account_bank
SET money = money + p_deal_money,
updated_by = v_proc_name,
updated_date = v_now
WHERE account_no = p_target_acc_no;
--获得余额
SELECT money
INTO v_tgt_money
FROM account_bank
WHERE account_no = p_target_acc_no;
--写入交易记录
INSERT INTO record_deal
(pk_serial,
account_no,
deal_money,
money,
deal_type,
deal_date,
deal_account_no,
created_by,
created_date,
updated_by,
updated_date)
VALUES
(sys_guid(),
p_target_acc_no,
p_deal_money,
v_tgt_money,
'3', --1.存款2.取款3.转入4.转出
v_now,
p_src_acc_no,
v_proc_name,
v_now,
v_proc_name,
v_now);
--写入转帐记录表，记录转出转入用户
INSERT INTO record_transfer
(pk_serial,
src_account_no,
target_account_no,
deal_money,
deal_type,
deal_date,
status,
created_by,
created_date,
updated_by,
updated_date)
VALUES
(sys_guid(),
p_src_acc_no,
p_target_acc_no,
p_deal_money,
'1', --1.同行2.跨行
v_now,
'2', --1.待处理2.完成3.失败
v_proc_name,
v_now,
v_proc_name,
v_now);
ELSE
--跨行转帐，2小时到帐
--转出账户支出
UPDATE account_bank
SET money = money - p_deal_money,
updated_by = v_proc_name,
updated_date = v_now
WHERE account_no = p_src_acc_no;
--写入转帐记录表
INSERT INTO record_transfer
(pk_serial,
src_account_no,
target_account_no,
deal_money,
deal_type,
deal_date,
status,
created_by,
created_date,
updated_by,
updated_date)
VALUES
(sys_guid(),
p_src_acc_no,
p_target_acc_no,
p_deal_money,
'2', --1.同行2.跨行
v_now,
'1', --1.待处理2.完成3.失败
v_proc_name,
v_now,
v_proc_name,
v_now);
END IF;
END IF;
END IF;
END proc_transfer_deal;
--检查用户是否存在
FUNCTION func_check_account_exists(p_acc_no IN VARCHAR2) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(1)
INTO v_count
FROM account_bank
WHERE account_no = p_acc_no;
IF v_count = 1 THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END func_check_account_exists;
--检查用户余额是否满足交易
FUNCTION func_check_money(p_acc_no IN VARCHAR2, p_deal_money IN NUMBER)
RETURN BOOLEAN IS
v_money NUMBER(11, 2);
BEGIN
SELECT money
INTO v_money
FROM account_bank
WHERE account_no = p_acc_no;
IF v_money >= p_deal_money THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END func_check_money;
FUNCTION func_check_deal(p_acc_no IN VARCHAR2,
p_deal_money IN NUMBER,
p_deal_typeIN VARCHAR2) RETURN BOOLEAN IS
v_result BOOLEAN := FALSE;
BEGIN
IF func_check_account_exists(p_acc_no) THEN
--判断账户是否存在且只有1
IF p_deal_type = '1' THEN
v_result := func_check_money(p_acc_no, p_deal_money);
ELSIF p_deal_type = '0' THEN
v_result := TRUE;
END IF;
END IF;
RETURN v_result;
END func_check_deal;
PROCEDURE proc_bank_for_job IS
CURSOR cur_transfer IS
SELECT * FROM record_transfer a WHERE a.status IN ('1', '3');
v_cru_transfer cur_transfer%ROWTYPE; --游标变量
v_proc_name VARCHAR2(50) := 'deal_bank_pkg.proc_bank_for_job';
v_now DATE := SYSDATE;
v_src_money NUMBER(11, 2); --转出账户的余额
v_tgt_money NUMBER(11, 2); --转入账户的余额
BEGIN
OPEN cur_transfer; --打开游标
LOOP
--循环提取记录，一行行提取
FETCH cur_transfer
INTO v_cru_transfer;
EXIT WHEN cur_transfer%NOTFOUND;
IF v_cru_transfer.status = '1' THEN
--转入账户入帐
UPDATE account_bank
SET money = money + v_cru_transfer.deal_money,
updated_by = v_proc_name,
updated_date = v_now
WHERE account_no = v_cru_transfer.target_account_no;
--获得余额
SELECT money
INTO v_src_money
FROM account_bank
WHERE account_no = v_cru_transfer.src_account_no;
--写入交易记录
INSERT INTO record_deal
(pk_serial,
account_no,
deal_money,
money,
deal_type,
deal_date,
deal_account_no,
created_by,
created_date,
updated_by,
updated_date)
VALUES
(sys_guid(),
v_cru_transfer.src_account_no,
v_cru_transfer.deal_money,
v_src_money,
'4', --1.存款2.取款3.转入4.转出
v_now,
v_cru_transfer.target_account_no,
v_proc_name,
v_now,
v_proc_name,
v_now);
--获得余额
SELECT money
INTO v_tgt_money
FROM account_bank
WHERE account_no = v_cru_transfer.target_account_no;
--写入交易记录
INSERT INTO record_deal
(pk_serial,
account_no,
deal_money,
money,
deal_type,
deal_date,
deal_account_no,
created_by,
created_date,
updated_by,
updated_date)
VALUES
(sys_guid(),
v_cru_transfer.target_account_no,
v_cru_transfer.deal_money,
v_tgt_money,
'3', --1.存款2.取款3.转入4.转出
v_now,
v_cru_transfer.src_account_no,
v_proc_name,
v_now,
v_proc_name,
v_now);
--转帐数据状态变更
UPDATE record_transfer
SET deal_date = v_now,
status = '2',
updated_by = v_proc_name,
updated_date = v_now
WHERE pk_serial = v_cru_transfer.pk_serial;
ELSIF v_cru_transfer.status = '3' THEN
--失败数据处理
--转出账户入帐
UPDATE account_bank
SET money = money + v_cru_transfer.deal_money,
updated_by = v_proc_name,
updated_date = v_now
WHERE account_no = v_cru_transfer.src_account_no;
--转帐数据状态变更
UPDATE record_transfer
SET deal_date = v_now,
status = '4', --作废
updated_by = v_proc_name,
updated_date = v_now
WHERE pk_serial = v_cru_transfer.pk_serial;
END IF;
END LOOP;
CLOSE cur_transfer;
END proc_bank_for_job;
END deal_bank_pkg;
/
--创建JOB
declare
v_job_id binary_integer;
begin
dbms_job.submit(job =>v_job_id ,--jobID
what => 'deal_bank_pkg.proc_bank_for_job();' ,--调用的过程
next_date => sysdate+2/60/24,--第一次执行时间
interval =>'sysdate+2/24' );--执行频率
commit;
dbms_output.put_line(v_job_id);
end;
--修改JOB
declare
begin
dbms_job.change(45,'deal_bank_pkg.proc_bank_for_job();',sysdate,'sysdate+5/60/24');
commit;
end;
--删除JOB
declare
begin
dbms_job.remove(45);
commit;
end;
数据自己添加按照表的字段来添加我这里就不写了写了我一个小时，累啊！多写支持！
红色的冰 发表于 2016-12-2 20:30
sql啊/...
直接放文件里打包上来
然后 截个表图 不觉得更美观么=,=
有些人懒不愿意动手 喜欢直接看源码我就没有打包 zadmg 发表于 2016-12-2 18:18
有了这个你就可以到ATM机随意取款了
修了这么多年取款机，没见过你这么厉害的 不太懂 谢谢楼主分享{:301_1003:} 楼主应该写到markdown里面显示看起来会更舒服些 这个东西是干嘛的？ 2469631260 发表于 2016-12-2 18:14
这个东西是干嘛的？
有了这个你就可以到ATM机随意取款了 zadmg 发表于 2016-12-2 18:18
有了这个你就可以到ATM机随意取款了
{:301_971:}你别逗啊..... 牛逼呀。怎么把别人的钱转到我账上？ 这能干什么用？ 应该写上SQL语言，免得别人不知道