Oracle测试--ATM系统开发

系统概述

某银行需要开发ATM取款机实现如下功能：

https://download.csdn.net/download/qq_48033003/18176566?spm=1001.2014.3001.5501

• 开户（到银行填写开户申请单，卡号自动生成）
• 取款
• 存款
• 挂失
• 修改密码
• 催款提醒
• 统计查询银行业务信息
• 转账

系统角色

• 数据库设计员
• 数据库程序员
• 数据库维护员

1.明确项目需求

• 银行业务处理
  • 每个用户可以开设多个银行卡账户
  • 每个银行卡账户可以有多笔交易

2.设计数据库

• 思路分析

  • 按照数据库设计步骤确定实体及实体关系
  • 使用Power Designer绘制E-R图
  • 使用Power Designer绘制数据库模型图
  • 使用第三范式规范数据库设计

• 难点提示

  • 寻找、确定ATM取款机系统的实体及实体间关系
  • 根据业务确定实体 确定实体之间关系
  • 使用三大范式规范数据库设计
  • 表内的每一个值都只能被表达一次
  • 表内的每一行都应该被唯一的标识（主键）
  • 表内不应该存储依赖于其他键的非键信息

• 需求说明

• 使用SQL语言创建表空间Tablespace_Bank

• 文件保存在E:\bank目录下

• 使用SQL语言创建表

  • 存款类型表
  • 客户信息表
  • 银行卡信息表
  • 交易信息表

• 使用SQL语言在每个表上添加约束

  • 主键约束、外键约束、CHECK约束、非空约束

3.编码实现需求
4.代码测试

开始创建

1.使用PowerDesigner设计表

• 不懂得可以看

https://blog.csdn.net/qq_48033003/article/details/116208196

• 或者直接文件（可以得到sql语句）

2.oracle创建用户及表

--创建ATM银行系统表空间
create tablespace bank_tablespace
    datafile 'D:\oracleSpace\app\db\\bank.dbf' size 10M AUTOEXTEND on next 2M maxsize unlimited;
--创建ATM系统用户
create user c##bank IDENTIFIED by www123 default tablespace bank_tablespace;
--为c##bank用户授权
grant connect,resource,UNLIMITED TABLESPACE ,create any view to c##bank;
--解锁c##bank用户
alter user c##bank account unlock;

• 然后在oracle中执行生成表

/*==============================================================*/
/* DBMS name:      ORACLE Version 11g                           */
/* Created on:     2021/4/28 16:01:28                           */
/*==============================================================*/


alter table cardinfo
   drop constraint FK_CARDINFO_REFERENCE_DEPOSIT;

alter table cardinfo
   drop constraint FK_CARDINFO_REFERENCE_USERINFO;

alter table tradeinfo
   drop constraint FK_TRADEINF_REFERENCE_CARDINFO;

drop table Deposit cascade constraints;

drop table cardinfo cascade constraints;

drop table tradeinfo cascade constraints;

drop table userinfo cascade constraints;

/*==============================================================*/
/* Table: Deposit                                               */
/*==============================================================*/
create table Deposit 
(
   savingID             VARCHAR(20)          not null,
   savingName           VARCHAR(20)          not null,
   descrip              VARCHAR(200),
   constraint PK_DEPOSIT primary key (savingID)
);

comment on table Deposit is
'存款类型表';

/*==============================================================*/
/* Table: cardinfo                                              */
/*==============================================================*/
create table cardinfo 
(
   cardID               number               not null,
   Dep_savingID         VARCHAR(20),
   use_customerID       VARCHAR(20),
   curid                VARCHAR(10),
   savingID             number               not null,
   openDate             DATE,
   openMoney            NUMBER               not null,
   balance              NUMBER               not null,
   pass                 CHAR(6 CHAR),
   isreportloss         NUMBER(1),
   customerID           NUMBER               not null,
   constraint PK_CARDINFO primary key (cardID)
);

comment on table cardinfo is
'银行卡信息表';

/*==============================================================*/
/* Table: tradeinfo                                             */
/*==============================================================*/
create table tradeinfo 
(
   tradeID              VARCHAR(20)          not null,
   tradeDate            DATE,
   cardID               NUMBER,
   tradeType            CHAR(10 BYTE)        not null,
   tradeMoney           NUMBER(6,2)          not null,
   remark               LONG,
   constraint PK_TRADEINFO primary key (tradeID)
);

comment on table tradeinfo is
'交易信息表';

/*==============================================================*/
/* Table: userinfo                                              */
/*==============================================================*/
create table userinfo 
(
   customerID           VARCHAR(20)          not null,
   customerName         VARCHAR(20)          not null,
   PID                  VARCHAR(18)          not null,
   telephone            VARCHAR(20)          not null,
   address              VARCHAR(200),
   constraint PK_USERINFO primary key (customerID)
);

comment on table userinfo is
'顾客信息表备注）';

alter table cardinfo
   add constraint FK_CARDINFO_REFERENCE_DEPOSIT foreign key (Dep_savingID)
      references Deposit (savingID);

alter table cardinfo
   add constraint FK_CARDINFO_REFERENCE_USERINFO foreign key (use_customerID)
      references userinfo (customerID);

alter table tradeinfo
   add constraint FK_TRADEINF_REFERENCE_CARDINFO foreign key (cardID)
      references cardinfo (cardID);

• 插入语句测试

--测试插入数据
--存款类型
--存款类型
INSERT INTO deposit (savingID,savingName,descrip) VALUES (1,'活期','按存款日结算利息');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (2,'定期一年','存款期是1年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (3,'定期二年','存款期是2年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (4,'定期三年','存款期是3年');
INSERT INTO deposit (savingID,savingName) VALUES (5,'定活两便');
INSERT INTO deposit (savingID,savingName) VALUES (6,'通知');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (7,'零存整取一年','存款期是1年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (8,'零存整取二年','存款期是2年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (9,'零存整取三年','存款期是3年');
INSERT INTO deposit (savingID,savingName,descrip) VALUES (10,'存本取息五年','按月支取利息');
SELECT * FROM DEPOSIT;

INSERT INTO userInfo(customerID,customerName,PID,telephone,address )
     VALUES(1,'张三','123456789012345','010-67898978','北京海淀');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612345678',1,1000,1000,1);

INSERT INTO userInfo(customerID,customerName,PID,telephone)
     VALUES(2,'李四','321245678912345678','0478-44443333');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612121134',2,1,1,2);

INSERT INTO userInfo(customerID,customerName,PID,telephone)
     VALUES(3,'王五','567891234532124670','010-44443333');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612121130',2,1601,1601,3);

INSERT INTO userInfo(customerID,customerName,PID,telephone)
     VALUES(4,'丁六','567891321242345618','0752-43345543');
INSERT INTO cardInfo(cardID,savingID,openMoney,balance,customerID)
     VALUES('1010357612121004',2,1,1,4);


/*
张三的卡号（1010 3576 1234 5678）取款900元，李四的卡号（1010 3576 1212 1134）存款5000元，要求保存交易记录，以便客户查询和银行业务统计。
说明：当存钱或取钱（如300元）时候，会往交易信息表（tradeInfo）中添加一条交易记录，
      同时应更新银行卡信息表（cardInfo）中的现有余额（如增加或减少500元）
*/
/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeid,tradeType,cardID,tradeMoney) 
      VALUES(seq_tradeinfo_id.nextval,'支取','1010357612345678',900)  ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance-900 WHERE cardID='1010357612345678';

/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeid,tradeType,cardID,tradeMoney) 
      VALUES(seq_tradeinfo_id.nextval,'存入','1010357612121130',300)  ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+300 WHERE cardID='1010357612121130';

/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeid,tradeType,cardID,tradeMoney) 
      VALUES(seq_tradeinfo_id.nextval,'存入','1010357612121004',1000)  ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+1000 WHERE cardID='1010357612121004';

/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeid,tradeType,cardID,tradeMoney) 
      VALUES(seq_tradeinfo_id.nextval,'支取','1010357612121130',1900)  ;
/*-------------更新银行卡信息表中的现有余额--报错-----------------*/
UPDATE cardInfo SET balance=balance-1900 WHERE cardID='1010357612121130';

/*--------------交易信息表插入交易记录--------------------------*/
INSERT INTO tradeInfo(tradeid,tradeType,cardID,tradeMoney) 
      VALUES(seq_tradeinfo_id.nextval,'存入','1010357612121134',5000)   ;
/*-------------更新银行卡信息表中的现有余额-------------------*/
UPDATE cardInfo SET balance=balance+5000 WHERE cardID='1010357612121134';

commit;
/*--------检查测试数据是否正确---------*/
SELECT * FROM cardInfo;
SELECT * FROM tradeInfo;
SELECT * FROM userInfo;

--delete from tradeInfo;
--delete from cardInfo;
--delete from userInfo;

3.实现需求

• 1.修改客户密码

update cardinfo set pass='123456' where 1=1;

• 2.办理银行卡挂失 1表示正常使用 0表示挂失

update cardinfo set isreportloss='0' where cardid=1010357612345678;

• 3.统计银行资金流通余额和盈利结算

select  (select sum(trademoney) from tradeinfo where tradetype='存入' )总存入,(select sum(trademoney) from tradeinfo where tradetype='支取' ) 总支取,((select sum(trademoney) from tradeinfo where tradetype='存入')-(select sum(trademoney) from tradeinfo where tradetype='支取')) 银行资金流通余额 from dual;
select ((select sum(trademoney) from tradeinfo where tradetype='支取')*0.008-(select sum(trademoney) from tradeinfo where tradetype='存入')*0.003) 盈利结算 from dual;

• 4.查询本周开户的卡号，显示该卡相关信息

select trunc(sysdate,'day')+1 from dual;--取得本周第一天（由于美国将星期日当做一周的第一天，所以加上1）
select * from cardinfo where opendate>=trunc(sysdate,'day')+1;

• 5.查询本月交易金额最高的卡号

select max(trademoney) from tradeinfo where to_char(tradedate,'mm')=to_char(sysdate,'mm');--查询出当月最高交易金额
select cardid,tradetype,trademoney from tradeinfo where trademoney in (select max(trademoney) from tradeinfo where to_char(tradedate,'mm')=to_char(sysdate,'mm')) and  to_char(tradedate,'mm')=to_char(sysdate,'mm');

注意外面还要加上月份的约束条件 因为上面的那个查出来当月的最高交易金额
已经变成了	数字，如果其他月也有相同金额的数据则也会被查询出来
最后同一个卡号去重

select DISTINCT cardid,tradetype,trademoney from tradeinfo where trademoney in (select max(trademoney) from tradeinfo where to_char(tradedate,'mm')=to_char(sysdate,'mm')) and  to_char(tradedate,'mm')=to_char(sysdate,'mm');

• 6.查询挂失账号的客户信息

select u.* from cardinfo c,userinfo u where c.customerid=u.CUSTOMERID and isreportloss=0;

• 7.创建银行卡信息视图

create or replace view view_cardinfo
as
select c.*,u.customername,u.telephone,u.address,d.savingname,d.descrip from cardinfo c,userinfo u,deposit d where c.customerid=u.customerid and c.savingid=d.savingid;
--执行视图
select * from view_cardinfo;

• 8.创建银行卡交易信息视图

create or replace view view_tradeinfo
as
select t.*,u.* from tradeinfo t,cardinfo c,userinfo u where t.cardid=c.cardid and c.customerid=u.customerid;
--执行视图
select *  from view_tradeinfo;

• 9.转账交易存储过程

创建一个tradeinfo自增的序列
create sequence seq_tradeinfo_id start with 20 minvalue 20 maxvalue 99999999 increment by 1 CACHE 20;

–存储过程的创建

create or replace procedure proc_baseTrade(
    v_cardid in number, --交易银行卡号
    v_money in number, --交易金额
    v_password in varchar2, --银行卡密码 （取款时使用，存款时不用）
    v_type in varchar2, --交易类型
    v_result out number, --交易状态（200成功，500错误）
    v_message out varchar --提示信息
)
as
 v_cardpassword varchar2(20);
 begin
 
    if v_type='存入' then
    
        insert into tradeinfo(tradeid, TRADEDATE, CARDID, TRADETYPE, TRADEMONEY) values(seq_tradeinfo_id.nextval,
        sysdate,v_cardid,v_type,v_money
        );
        update cardinfo set balance=balance+v_money where cardid=v_cardid;
        commit; --提交事务
        v_result:=200;
        v_message:='存入成功';
    elsif v_type='支取' then
        select pass into v_cardpassword from cardinfo where cardid=v_cardid;--将密码存储进v_password变量
        if v_password=v_cardpassword then
        
            insert into tradeinfo(tradeid, TRADEDATE, CARDID, TRADETYPE, TRADEMONEY) values(seq_tradeinfo_id.nextval,
            sysdate,v_cardid,v_type,v_money
            );
             update cardinfo set balance=balance-v_money where cardid=v_cardid;
             commit;
            v_result:=200;
            v_message:='支取成功';
        else
            v_result:=201;
            v_message:='支取时密码错误';
        end if;
    else
        v_result:=202;
        v_message:='无效的交易指令';
    end if;
    
    
    Exception
        when others then
            rollback;
            v_result:=203;
            v_message:='交易出现异常';
            RAISE_APPLICATION_ERROR(-20001,'交易失败');
 
 end;

• 调用存储过程测试
• 10.开户功能存储过程的实现

create or replace procedure proc_userinfo_create(
    --开户涉及到 userinfo表 cardinfo表 tradeinfo表 deposit表
    --输入参数
    --userinfo表
    v_customerName in varchar, --用户名
    v_pid in varchar, --身份证号
    v_telephone in varchar, --手机号
    v_address in varchar, --地址
    --cardinfo表
    v_curid in varchar, --货币 
    v_savingid in number, --存款种类
    v_openMoney in number, --开户金额 最低一元
    v_pass in varchar, --银行卡密码
    --tradeinfo交易信息表

    --输出参数
    --返回开户结果
    v_result out varchar
)is
v_newcustomerid VARCHAR2(20);--保存新创建的客户id
v_randomcardid number;
v_ISREPORTLOSS number;
begin
    --对userinfo表进行操作
    v_newcustomerid:=SEQ_USERINFO_CUSTOMERID.nextval;
    insert into userinfo(CUSTOMERID, CUSTOMERNAME, PID, TELEPHONE, ADDRESS) values(
    v_newcustomerid,v_customerName,v_pid,v_telephone,v_address);
    --对cardinfo表进行操作
    --获得随机的银行卡号并赋值给v_randomcardid变量
    proc_random_cardid(v_randomcardid);
     v_ISREPORTLOSS:=1;
    
    INSERT INTO CARDINFO (CARDID, DEP_SAVINGID, USE_CUSTOMERID, CURID, SAVINGID, OPENDATE, OPENMONEY, BALANCE, PASS, ISREPORTLOSS, CUSTOMERID) 
    VALUES (v_randomcardid, v_savingid, v_newcustomerid, v_curid, v_savingid, sysdate, v_openMoney, v_openMoney, v_pass, v_ISREPORTLOSS, v_newcustomerid);
    
    --对tradeinfo交易信息表进行操作
    insert into tradeinfo(TRADEID, TRADEDATE, CARDID, TRADETYPE, TRADEMONEY, REMARK) values(
    SEQ_TRADEINFO_ID.nextval,sysdate,v_randomcardid,'开户',v_openMoney,'开户记录'
    );

    commit;
    v_result:='开户成功';

    --异常处理
    exception WHEN others then
        --事务回滚
        rollback;
        --输出参数
        v_result:='开户失败，请检查你要输入的参数';
        RAISE_APPLICATION_ERROR(-20001,'开户失败');
end;

• 测试没毛病