Oracle学习笔记之ATM实战

Oracle项目实战之ATM

• 项目简介

近年来，随着internet的迅速崛起，互联网已日益成为收集提供信息的最佳渠道并逐步进入传统的流通领域。如今，人们的交流已经越来越多，要求信息的传送速度更快，传送范围更广，“信息高速公路”也就应运而生了。其中，Internet已拥有了数千万用户，通过Internet我们就可以与全世界来联网，对于当中各种各样的信息，人们都可以通过通络来共享和使用。比如ATM银行取款系统，我们可以把它当做一个虚拟的市场销售中心，每个人都可以在这个网络上享受所提供的服务，这样既节约了时间，又满足了需求，尤其是人们的生活速度越来越快，没有大量的时间花费在银行等待长时间的排队。特别是当下传统的银行会受到时间、地点的限制，无法实现全天制营业，是的很多用户无法再非营业时间办理业务。而ATM银行取款系统的出现，使这一问题迎刃而解。

下面是ATM机的需求，注意本例主要是用来锻炼和强化oracle水平，强调的是一个在oracle中使用存储过程，触发器，函数设计和实现功能，而不是一个界面程序的开发。

步骤一：创建表空间,创建用户【用户默认操作新建的表空间】

--drop tablespace bankpro;
create tablespace bankpro
datafile 'D:\tablespace\bankpro'
size 1200m;

create user lyip identified by 123456 default tablespace bankpro;
grant dba to lyip;

–创建表，并为各表添加约束

/*
用户表：用户ID，姓名，身份证，联系电话，联系地址
要求：
1.用户ID为主键
2.身份证号码唯一，15或18位
3.为用户ID创建序列，用于用户ID自增
*/

	--drop table userInfo;
	create table userInfo(
	  user_id number(4) primary key,
	  user_name varchar2(20) not null,
	  PID varchar2(18) not null unique,
	  telephone number(13),
	  address varchar2(50)
	)tablespace bankPro;

	alter table userInfo add constraint user_pid check(regexp_like(PID,'^[0-9]{17}[0-9x]{1}$'));
	alter table userInfo add constraint user_tel check(regexp_like(telephone,'^[0-9]{11}$'));
	--alter table userInfo drop constraint user_pid;

–为用户ID创建序列

--drop sequence seq_uid;
create sequence seq_uid
start with 1001
increment by 1
maxvalue 9999
nocycle
cache 10;

–测试：添加两条用户表

insert into userInfo values(seq_uid.nextval,'darling','450721199666666666',13877777777,'加庄');
insert into userInfo values(seq_uid.nextval,'linling','45072119966666888x',13866666666,'加庄');

--delete from userInfo where user_id=1003;
select * from userInfo;

/*
银行卡信息表：卡号，币种（人民币），存储类型（定期，活期），开户时间，
开户金额，当前账目余额，密码，是否挂失，用户的ID
要求：
1.卡号主键，卡号必须是0-9的数字，格式xxxx xxxx xxxx xxxx以1010 3576开头
2.存储类型只能是下面三种之一：活期，定期，活定两便
3.开户金额必须大于1元
4.密码必须大于6位
5.是否挂失只能是下面两种之一：是，否
6.用户的ID是外键，关联用户表的主键。
7.币种默认”RMB”
8.开户时间默认当前时间
9.密码默认888888
10.是否挂失默认：否
11.根据卡号创建银行卡信息表的索引优化表查询。(卡号 主键 索引自动创建)
*/

	--drop table cardInfo;
	create table cardInfo(
	cardID varchar2(20) primary key,
	curType varchar(10) default'RMB' not null,
	savingType varchar(12) not null check(savingType in('定期','活期','活定两便')),
	openDate date default sysdate not null,
	openMoney number(10,2) not null,
	balance number(10,2) not null,
	pwd number(6) default'888888' not null,
	Isreportloss varchar2(3) default'否' not null,
	user_id number(4),

	constraint cardID_check check(regexp_like(cardID,'^(1010 3576) \d{4} \d{4}$')),
	constraint openMoney_check check(openMoney>1),
	constraint Isreportloss_check check(Isreportloss in('是','否')),
	constraint u_id foreign key(user_id) references userInfo(user_id)
	)tablespace bankPro;

–测试：添加两条银行卡信息表

insert into cardInfo values('1010 3576 1234 5678','RMB','定期',sysdate,1000,900,111111,'否'，1001);
insert into cardInfo values('1010 3576 1698 7452','RMB','定期',sysdate,1000,900,222222,'是'，1002);

select * from cardInfo;

/*
交易信息表：交易日期，卡号，交易类型，交易金额，备注
要求：
1.卡号为外键，关联银行卡信息表主键
2.交易类型只能是下面两种之一：存入，支取
3.交易金额必须大于0
4.交易时间默认为当前时间
*/

--drop table trans;
create table trans(
transdate date default sysdate,
cardID varchar2(20) not null,
transType varchar2(6) not null,
transMoney number(10,2) not null,
remark varchar2(50) not null,

constraint c_id foreign key(cardID) references cardInfo(cardID),
constraint transType_check check(transType in ('存入','支出')),
constraint transMoney_check check(transMoney>0)
)tablespace bankPro;

–注意：对于三张表都要创建公有同义词，便于以后的查询。

create  synonym us for userInfo;
create  synonym ca for cardInfo;
create  synonym tr for trans;

/*
步骤二：创建视图,要求如下：
1.创建 用户表表视图
2.创建银行卡信息表视图
3.创建交易信息表视图
4.创建查询挂失的客户信息视图
5.创建 查询本周开户的卡号 显示相关信息视图
6.创建 查询本月交易金额最高的卡号 的视图
*/

–1.创建 用户表表视图

create or replace view v_user
as
Select * from us;

–2.创建银行卡信息表视图

create or replace view v_card
as
Select * from ca;

–3.创建交易信息表视图

create or replace view v_trans
as
Select * from tr;

–4.创建查询挂失的客户信息视图

create or replace view v_loss
as
select * from ca where Isreportloss='是';

–5.创建 查询本周开户的卡号 显示相关信息视图

create or replace view v_open
as
select * from ca where (sysdate-openDate<=7);

–6.创建 查询本月交易金额最高的卡号 的视图

create or replace view v_bigmoney
as
select cardID from tr where transMoney in
(select max(transMoney) from tr);

–测试：分别测试上面个视图是否显示正常

select * from v_user;
select * from v_card;
select * from v_trans;
select * from v_loss;
select * from v_open;
select * from v_bigmoney;

/*
步骤三：创建触发器，要求如下：
1.不允许修改卡号：当更新的是银行卡信息表的卡号时提示“此列不允许修改”
2.当交易信息表有插入或者修改记录，银行卡信息表跟随改变：
如交易信息表新增一条 支出 ，那么对应的银行信息卡余额应该减少对应支取的金额。
同理如交易信息表新增一条 存入 ，那么对应的银行信息卡余额应该增加对应存入的金额。
注意：如果支取先判断余额，不足则提示。
如 交易成功 打印恭喜信息。
*/

–1.不允许修改卡号

create or replace trigger tri_bank
before
update of cardID on ca for each row
begin
  RAISE_APPLICATION_ERROR(-20000,'不允许修改卡号cardID！'); 
end;

–测试

select * from ca;
update ca set savingtype='活期' where user_id=1012;
update ca set cardID='1010 3576 6124 8900' where user_id=1012;

–2.当交易信息表有插入或者修改记录，银行卡信息表跟随改变

create or replace trigger tri_trans
after
insert  or  update on  tr for each row
declare
    my_balance number;
begin
    select balance into my_balance from Cardinfo where cardID=:new.cardID;
    if :new.transType = '支出' then
            if my_balance < :new.transMoney-1 then
                  RAISE_APPLICATION_ERROR(-20000,'抱歉，余额不足！'); 
                  return;
            else
                  update cardInfo set balance=my_balance - :new.transMoney where cardID = :new.cardID;
            end if;
    elsif :new.transType = '存入' then   
            update cardInfo set balance=my_balance + :new.transMoney where cardID = :new.cardID;
    end if;
    dbms_output.put_line('交易成功！');
end;

–测试：分别针对交易信息表做支取和存入的操作。

insert into tr values(sysdate,'1010 3576 1234 5678','存入',10000,'9月份工资');
insert into tr values(sysdate,'1010 3576 1234 5678','支出',10000,'买沙发');
insert into tr values(sysdate,'1010 3576 7207 1935','支出',1000,'还花呗');
select * from trans;
select * from cardInfo;

/*
步骤四：创建针对用户信息的程序包及程序包主体内容
1.创建函数：卡号随机生成，注意卡号的格式1010 3576 XXXX XXXX
2.创建开户的存储过程：注意卡号调用上面函数，开户过程包含插入信息到用户表及银行卡信息表
3.创建修改密码的存储过程
4.创建挂失账号的存储过程
*/

--包头
create or replace package  bankPack  
as
 function  fun_card  return  varchar2;
 procedure pro_openAccount(
--用户表
v_usname us.user_name%type,--姓名
v_PID us.pid%type,--身份证号码
v_tel us.telephone%type,--手机号码
v_address us.address%type,--家庭地址
--银行卡信息表
 v_curType ca.curtype%type,--币种
 v_savingType ca.savingtype%type,--存储方式
 v_openMoney ca.openmoney%type,--开户金额
 v_pwd ca.pwd%type--密码
 );
 procedure pro_pwd(p_cardID ca.cardid%type, p_pwd ca.pwd%type);
 procedure pro_loss(p_cardID ca.cardid%type);
end;

--包体
create or replace package body   bankPack 
as
  --随机卡号
  function  fun_card  return  varchar2
    as
        cardID varchar2(20);
        num1 varchar2(5);
        num2 varchar2(5);
    begin
        num1 := substr(to_char(dbms_random.value),2,4);
        num2 := lpad(trunc(dbms_random.value*10000),4,'0');
        cardID :=  '1010 3576 ' || num1 || ' ' || num2;
        return  cardID;
    end;  
    
    procedure pro_openAccount(
--用户表
v_usname us.user_name%type,--姓名
v_PID us.pid%type,--身份证号码
v_tel us.telephone%type,--手机号码
v_address us.address%type,--家庭地址
--银行卡信息表
 v_curType ca.curtype%type,--币种
 v_savingType ca.savingtype%type,--存储方式
 v_openMoney ca.openmoney%type,--开户金额
 v_pwd ca.pwd%type--密码
 )
as
  v_cardID varchar2(20);
  v_count number;
begin
  loop
    v_cardID := bankPack.fun_card;
    select count(*) into v_count from ca where cardID=v_cardID;
    if v_count=0 then
      insert into us values(seq_uid.nextval,v_usname,v_PID,v_tel,v_address);
      insert into ca values(v_cardID,v_curType,v_savingType,sysdate,v_openMoney,v_openMoney,v_pwd,'否',seq_uid.currval);
      dbms_output.put_line('开户成功！');
      dbms_output.put_line('您的卡号是：' || v_cardID);
      exit;
    --else  重新获取卡号，继续循环loop
    end if;
  end loop;
end; 
    
    --修改密码
    procedure pro_pwd(p_cardID ca.cardid%type, p_pwd ca.pwd%type)
      as
          p_count number;
      begin
         select count(*) into p_count from cardinfo where cardID=p_cardID;
         if p_count=1 then
            update  cardInfo set pwd=p_pwd where cardID=p_cardID;
            
         else
            RAISE_APPLICATION_ERROR(-20000,'该卡号不存在！');
         end if;
      end;
      
      --挂失账号
       procedure pro_loss(p_cardID ca.cardid%type)
       as
          p_count number;
          p_lo varchar2(3);
      begin
         select count(*) into p_count from cardinfo where cardID=p_cardID;
         if p_count=1 then
              select isreportloss into p_lo from ca where cardID=p_cardID;
              if p_lo='是' then
                  RAISE_APPLICATION_ERROR(-20000,'该卡号已挂失，无需重新挂失！');
              else
                  update  cardInfo set isreportloss='是' where cardID=p_cardID;
                  dbms_output.put_line('挂失成功！');
              end if;
         else
            RAISE_APPLICATION_ERROR(-20000,'该卡号不存在！');
         end if;
      end;
      
end;

–测试：卡号随机生成的函数

select bankPack.fun_card from dual;

–测试：执行开户的存储过程

declare
  --用户表
  v_usname us.user_name%type :='&姓名';
  v_PID us.pid%type  :='&身份证号码';
  v_tel us.telephone%type  :='&手机号码';
  v_address us.address%type  :='&家庭地址';
  --银行卡信息表
 v_curType ca.curtype%type  :='&币种';
 v_savingType ca.savingtype%type  :='&存储方式';
 v_openMoney ca.openmoney%type  :='&开户金额';
 v_pwd ca.pwd%type  :='&密码';
begin
  bankPack.pro_openAccount(
--用户表
v_usname,--姓名
v_PID,--身份证号码
v_tel,--手机号码
v_address,--家庭地址
--银行卡信息表
 v_curType,--币种
 v_savingType,--存储方式
 v_openMoney,--开户金额
 v_pwd
 );
end;

select * from us;
select * from ca;

–测试：执行修改密码的存储过程

call bankPack.pro_pwd('1010 3576 6124 8959',333333);
call bankPack.pro_pwd('1010 3576 6124 8977',333333);

–测试：执行挂失的存储过程

call bankPack.pro_loss('1010 3576 8836 0640');
call bankPack.pro_loss('1010 3576 1698 7452');

--select rowid, e.* from cardinfo e;

/*
步骤五：创建针对银行卡信息表的程序包及程序包主体内容
1.创建支取和存入的存储过程，注意只需要操作交易表，
而银行卡信息表的余额由上面步骤三中的触发器维护
2.创建查询余额存储过程
3.创建转账存储过程，注意：
判断要转账及要转入的账户是否都存在，转账金额是否充足，发生异常交易失败要回滚
4.创建汇总存储过程（对于银行来说流通余额=所有存入-所有取出）
（对于银行来说盈利结算=所有存入0.003-所有支取0.008）,
5.创建销户存储过程，注意销户要删除用户信息表，银行卡信息表，交易信息表中于之相关的所有记录
*/

--包头
create or replace package  cardPack  
as
 procedure pro_trans(p_cardID tr.cardid%type,p_type tr.transtype%type,p_money tr.transmoney%type,p_remark tr.remark%type);--存入支出
 procedure pro_balance(b_cardID ca.cardid%type);--查询余额
 procedure pro_transfer(acid tr.cardid%type,bcid ca.cardid%type,t_money tr.transmoney%type);--转账
 procedure pro_collect;--汇总
 procedure pro_deluser(p_uid us.user_id%type);--销户
end;

--包体
create or replace package body   cardPack 
as
     --存入支出
     procedure pro_trans(p_cardID tr.cardid%type,p_type tr.transtype%type,p_money tr.transmoney%type,p_remark tr.remark%type)
         as
           t_count number;
         begin
             select count(*) into t_count from cardinfo where cardID=p_cardID;
         if t_count=1 then
            insert into tr values(sysdate,p_cardID,p_type,p_money,p_remark);
         else
            RAISE_APPLICATION_ERROR(-20000,'该卡号不存在！');
         end if;
      end;
      
      --查询余额
       procedure pro_balance(b_cardID ca.cardid%type)
         as
           b_count number;
           b_ba ca.balance%type;
         begin
             select count(*) into b_count from cardinfo where cardID=b_cardID;
         if b_count=1 then
            select balance into b_ba from  cardinfo where cardID=b_cardID;
            dbms_output.put_line(b_ba);
         else
            RAISE_APPLICATION_ERROR(-20000,'该卡号不存在！');
         end if; 
       end;
       
       --转账
       procedure pro_transfer(acid tr.cardid%type,bcid ca.cardid%type,t_money tr.transmoney%type)
         as
         a_co number;
         b_co number;
         a_money ca.cardid%type;
         a_msg varchar2(50);
         b_msg varchar2(50);
         begin
           select count(*) into a_co from cardinfo where cardID=acid;
           if a_co=0 then
               RAISE_APPLICATION_ERROR(-20000,'该卡号不存在！');
           else
               select count(*) into b_co from cardinfo where cardID=bcid;
               if b_co=0 then
                   RAISE_APPLICATION_ERROR(-20000,'转入的卡号不存在！');
               else
                   select balance into a_money from ca where cardID=acid;
                   if a_money<t_money+1 then
                       RAISE_APPLICATION_ERROR(-20000,'抱歉，余额不足！');
                   else
                      a_msg :='转给'||bcid||'  '||t_money||'元';
                      b_msg :='收到'||acid||'  '||t_money||'元';
                      insert into tr values(sysdate,acid,'支出',t_money,a_msg);
                      insert into tr values(sysdate,bcid,'存入',t_money,b_msg);
                      dbms_output.put_line('转账成功！');
                   end if;
               end if;
           end if;
         end; 
        
    
        --汇总
          procedure pro_collect
            as
              income number(12,2);--所有存入
              disburse number(12,2);--所有支出
              circulate number(12,2);--流通余额
              profit number(12,2);--盈利
            begin
              select sum(transmoney) into income from tr where transtype='存入';
              select sum(transmoney) into disburse from tr where transtype='支出';
              circulate := income-disburse;
              profit := income*0.003+disburse*00008;
              dbms_output.put_line('流通余额：'||circulate);
              dbms_output.put_line('盈利结算：'||profit);
            end;
            
   --销户
   procedure pro_deluser(p_uid us.user_id%type)
     as
      us_num number;
      ca_num number;
     begin
       select count(*) into us_num from us where user_id=p_uid;
       if us_num=0 then
           RAISE_APPLICATION_ERROR(-20000,'用户不存在！');
       else
           select count(*) into ca_num from ca where user_id=p_uid;
           if ca_num=0 then
               delete us where user_id=p_uid;
           else
               delete tr where cardid=(select cardid from ca where user_id=p_uid);
               delete ca where  user_id=p_uid;
               delete us where user_id=p_uid;
           end if;
       end if;
     end;
     
end;

select * from us;
select * from ca;
select *from tr;

–测试：执行取钱和存钱的存储过程

    call cardPack.pro_trans('1010 3576 6124 8959','支出',500,'买鞋子');
    call cardPack.pro_trans('1010 3576 6124 8959','存入',500,'退款');
    call cardPack.pro_trans('1010 3576 6124 8900','支出',500,'买鞋子');

–测试：执行查询余额的存储过程

call cardPack.pro_balance('1010 3576 6124 8959');
call cardPack.pro_balance('1010 3576 6124 8900');

–测试：执行转账的存储过程，

call cardPack.pro_transfer('1010 3576 6124 8959','1010 3576 1698 7452',200);
call cardPack.pro_transfer('1010 3576 7207 1935','1010 3576 1698 7452',100);
select * from tr;
select * from ca;

–测试：执行汇总的存储过程

call cardPack.pro_collect();

–测试：执行销户的存储过程

call cardPack.pro_deluser(1021);