oracle -实战项目ATM

本篇博客主要是将ATM的项目进行复现，在复现过程中，主要使用的就是表的创建，表约束条件，外键，检查条件等的设置，另外还根据表进行了视图的创建，通过创建的视图来完成一些比价简单地功能，接触到的新的知识就是对对日期进行了筛选，使用‘iw’将本周的日期数据筛选出来。主要参考的博客：

Oracle项目实战之ATM - 站在西瓜上的猪 - 博客园 (cnblogs.com)

--ATM 项目实战

/*
* ATM：需要包含的表有用户表，交易信息表，银行卡信息表
* 用户表与银行卡的关系1-n，交易信息表与银行卡信息表为n-n,
* 用户表跟交易信息表的关系1-n

**/

--用户表的创建
/*------------------------------------
    *要求：
    *1.    用户ID为主键
    *2.    身份证号码唯一，15或18位 
    *3.    为用户ID创建序列，用于用户ID自增
  -------------------------------------*/
create table ATM_user(
user_id  number(10) primary key,
user_name varchar2(20) not null,
user_card number(18) unique,
user_phone number(30) not null,
user_loca varchar2(50)
)

--银行卡信息表的创建
 /*要求：
 
  1.    卡号主键，卡号必须是0-9的数字，格式xxxx xxxx xxxx xxxx以1010 3576开头
  2.    存储类型只能是下面三种之一：活期，定期，活定两便
  3.    开户金额必须大于1元
  4.    密码必须大于6位
  5.    是否挂失只能是下面两种之一：是，否
  6.    用户的ID是外键，关联用户表的主键。
  7.    币种默认”RMB”  
  8.    开户时间默认当前时间
  9.    密码默认888888
  10.    是否挂失默认：否
  11.    根据卡号创建银行卡信息表的索引优化表查询。
  */
create table ATM_cardinfo(
card_id varchar2(20) primary key,
style varchar2(30) check(style in('活期','定期','活定两便')),
atm_account number(30) check(atm_account >= 1),
atm_password varchar(30) default '888888',
status varchar2(4) default '否',
card_userid number(10),
BZ varchar2(10) default 'RMB',
atm_time date default sysdate,
--卡号必须是0-9的数字，格式xxxx xxxx xxxx xxxx以1010 3576开头
constraint card_id_check check (regexp_like(card_id,'^(1010 3576) \d{4} \d{4}$')),
constraint len_password check(length(atm_password)>=6),
constraint atm_status check(status in ('是','否')),
--外键进行关联时需要在相应的字段上加小括号,关联用户的身份证号
foreign key (card_userid) references ATM_user(user_card)
)

--交易信息表的创建
/*要求：
  1.    卡号为外键，关联银行卡信息表主键
  2.    交易类型只能是下面两种之一：存入，支取
  3.    交易金额必须大于0
  4.    交易时间默认为当前时间*/
create table ATM_transinfo(
atm_cardid varchar2(20),
foreign key (atm_cardid) references ATM_cardinfo(card_id) on delete cascade,
atm_style varchar2(10) check(atm_style in ('存入','支取')) not null,
trans_account number(30) check(trans_account>0) not null,
trans_time date default sysdate,
trans_remark varchar2(100)
)

select * from atm_user
select * from atm_cardinfo
select * from atm_transinfo

drop table atm_transinfo purge
drop table atm_cardinfo purge

--银行卡信息表中插入新的字段
alter table atm_cardinfo add(atm_cur_account number(20))

--修改字段的类型 银行卡信息表
alter table atm_cardinfo modify card_id char(19)

alter table atm_transinfo modify atm_cardid char(19)

select * from atm_user
--插入数据
--在用户表中，user_id为自增序列，所以在插入时，可以创建一个自增的序列
create sequence U_ID_SEQ;
INSERT INTO atm_user
  VALUES
    (U_ID_SEQ.NEXTVAL, 'cidy', '450921199601042491', '18877572921', '广西玉林');
    commit;
    
  INSERT INTO atm_cardinfo(card_id,BZ,style,atm_time,atm_account,atm_cur_account,atm_password,status,card_userid)
  VALUES
    ('1010 3576 4000 0000', 'RMB', '活期', SYSDATE, 200, 200, '12345678', '否', 1);
    
  INSERT INTO atm_cardinfo(card_id,BZ,style,atm_time,atm_account,atm_cur_account,atm_password,status,card_userid)
   VALUES
    ('1010 3576 4000 5856', 'RMB', '活期', SYSDATE, 200, 200, '12345dashg678', '否', 3);
  
  INSERT INTO atm_cardinfo
    (CARD_ID, card_userid)
 VALUES
   ('1010 3576 4000 5890', 4);
     
 INSERT INTO atm_transinfo(trans_time,atm_cardid,atm_style,trans_account,trans_remark)
 VALUES   (SYSDATE,'1010 3576 4000 5890','支取',300,'test');
 
 
 
 /*
 * --创建视图
 **/ 
 
 -- 1.创建用户表视图
 create or replace view view_user as select * from atm_user
 
 -- 2.创建银行卡信息表视图
 create or replace view view_cardinfo as select * from atm_cardinfo
 
 --3.创建交易信息表视图
 create or replace view view_transinfo as select * from atm_transinfo
 
 select * from view_user
 select * from view_cardinfo
 select * from view_transinfo
 
 --4. 创建查询挂失的客户信息视图
 create or replace view  view_loss as select * from atm_cardinfo where status = '是'
 
 select * from view_loss
 
 --5.创建 查询本周开户的卡号 显示相关信息视图
 --'rw'可以返回当前周内的记录
 --select * from atm_cardinfo
 create or replace view view_cardid as 
   select *
      from atm_cardinfo 
       where to_char(atm_time,'iw') = to_char(sysdate,'iw')
       
 select * from view_cardid
 
 --6.创建 查询本月交易金额最高的卡号的视图
 create or replace view view_max_amount as 
   select  atm_cardid max_cardid from
   --求和，找出最大值
   (select atm_cardid from 
     (select atm_cardid,sum(trans_account) sum_amount from atm_transinfo 
       where to_char(trans_time,'mm') =  to_char(sysdate,'mm')
        group by atm_cardid order by sum_amount desc) A 
           where rownum=1) B,atm_cardinfo 
        where atm_cardid = card_id
        
 select * from view_max_amount
 
 --创建同义词