补充今天早上发的ATM

彬帅帅2655407 · 发表于 2016-12-2 17:36

下午就直接发写的代码了  我整合了一下和之前可能有点小小的差别不过还是可以实现的。---------------多多支持一下，谢谢！

--实现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_type  IN VARCHAR2);

  FUNCTION func_check_deal(p_acc_no    IN VARCHAR2,
                        p_deal_money IN NUMBER,
                        p_deal_type  IN 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_type  IN 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_bank  VARCHAR2(1); --转出账户的银行
v_tgt_bank  VARCHAR2(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_type  IN 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;

数据自己添加  按照表的字段来添加  我这里就不写了  写了我一个小时，累啊！  多写支持！

彬帅帅2655407 · 发表于 2016-12-3 08:21

红色的冰发表于 2016-12-2 20:30
sql啊 /...
直接放文件里打包上来
然后截个表图不觉得更美观么=,=

有些人懒不愿意动手喜欢直接看源码我就没有打包

KM920201 · 发表于 2016-12-2 18:39

zadmg 发表于 2016-12-2 18:18
有了这个你就可以到ATM机随意取款了

修了这么多年取款机，没见过你这么厉害的

萌萌龖 · 发表于 2016-12-2 17:49

不太懂谢谢楼主分享

唯丶歌 · 发表于 2016-12-2 18:01

楼主应该写到markdown里面显示看起来会更舒服些

我见青山多妩媚 · 发表于 2016-12-2 18:14

这个东西是干嘛的？

zadmg · 发表于 2016-12-2 18:18

2469631260 发表于 2016-12-2 18:14
这个东西是干嘛的？

有了这个你就可以到ATM机随意取款了

a425869651 · 发表于 2016-12-2 18:29

zadmg 发表于 2016-12-2 18:18
有了这个你就可以到ATM机随意取款了

你别逗啊.....

liyongji2016 · 发表于 2016-12-2 18:40

牛逼呀。怎么把别人的钱转到我账上？

ghshr521 · 发表于 2016-12-2 18:46

这能干什么用？

Deteriorator · 发表于 2016-12-2 18:53

应该写上SQL语言，免得别人不知道

帐号		自动登录	找回密码
密码			注册[Register]

[其他原创] 补充今天早上发的ATM

免费评分