oracle实战atm

-- 步骤一 -- 1.创建用户表t_userscreate table t_users(uuid number primary key,uname varchar2(20),idcard varchar2(20) constraint c1 check(lengthb(idcard) in(15,18)),-- lengthb(string)计算string所占的字节长度：返回字符串的长度，单位是字节   length(string)计算string所占的字符长度：返回字符串的长度，单位是字符utelephone varchar2(20),uaddress varchar2(20))-- 创建散列分区partition by hash(idcard)(partition p1,partition p2,partition p3);-- 身份证号创建唯一索引create unique index idcard_index on t_users(idcard);-- 用户ID 创建序列create sequence uuid_seqincrement by 1start with 1drop sequence uuid_seq-- 用户ID 自增触发器create or replace trigger uuid_auto_incr_tri before insert on t_users for each rowdeclare v_newuuid number;v_num number;v_now number;begin  if(:new.uuid is null)then -- 自动增长  loop    select uuid_seq.nextval into v_newuuid from dual;    select count(1) into v_num from t_users where uuid=v_newuuid;    if(v_num=0)then    :new.uuid:=v_newuuid;    exit;    end if;  end loop;  else -- 手动插入    select count(1) into v_num from t_users where uuid=:new.uuid;    if(v_num=0) then    select uuid_seq.currval into v_now from dual;    if(v_now<:new.uuid) then         loop          select uuid_seq.nextval into v_newuuid from dual;            if(:new.uuid=v_newuuid)then            exit;            end if;      end loop;      end if;    end if;  end if;  end;  -- 添加两条用户信息  insert into t_users(uname,idcard,utelephone,uaddress) values('ysg','123425334991407323','18339655356','闽南');  insert into t_users values(1,'wwk','111111111111111','12345678910','闽南');  -- 创建公有同义词synt_users  create public synonym synt_users for t_users;  drop synonym synt_users;    select * from t_users;  delete  from t_users;    -- 2.创建银行卡信息表t_cards  create table t_cards(  cid varchar2(20) primary key constraint c2 check (translate(cid,'0123456789','xxxxxxxxxx')='xxxx xxxx xxxx xxxx' and instr(cid,'1010 3576')=1),  -- cid varchar2(20) primary key constraint c2 check(regexp_like(cid,'1010\s3576\s\d{4}\s\d{4}')), 用正则 （常用的：regexp_like、regexp_substr、regexp_instr、regexp_replace）  ctype varchar2(20) default 'RMB',  cstore varchar2(20) constraint c3 check (cstore in('活期','定期','活定两便')),  cstarttime date default sysdate,-- oracle常见数据类型：char varchar2 number  number(m,n) date blob clob  cmoney number constraint c4 check (cmoney>1),  cbalance number,  cpassword varchar2(20) default '888888' constraint c5 check(lengthb(cpassword)=6),  creport varchar2(20) default '否' constraint c6 check(creport in('是','否')),  cuuid number,  foreign key(cuuid) references t_users(uuid)  )  partition by range(cstarttime)(  partition threeyearago values less than(to_date('2014-11-21','yyyy-mm-dd')),  partition oneyearage values less than(to_date('2016-11-21','yyyy-mm-dd')),  partition thismonth values less than(maxvalue)  );    drop table t_cards;  -- 添加两条银行卡信息  insert into t_cards(cid) values('1010 3576 1541 1111');  insert into t_cards(cid,cstore,cmoney,cbalance,cuuid) values('1010 3576 1114 1111','活期','1000','888',1);  insert into t_cards(cid,cstore,cmoney,cbalance,creport,cuuid) values('1010 3576 1114 19014','活期','1000','888','是',1);insert into t_cards(cid,cstore,cstarttime,cmoney,cbalance,creport,cuuid) values('1010 3576 0014 1154','活期',to_date('2017-05-23','yyyy-mm-dd'),'1000','888','是',1); -- oracle插入日期时常需要to_date函数格式化待插入的日期。TO_CHAR(<date>,'<format>')要求指定date的格式    select * from t_cards;  delete from t_cards;  -- 创建公有同义词synt_cards  create public synonym synt_cards for t_cards;    -- 3.创建交易信息表t_trades  create table t_trades(  tdate date default sysdate,  tcid varchar2(20),  ttype varchar2(20) constraints c7 check(ttype in ('存入','支出')),  tmoney number constraints c8 check(tmoney>0),  tnote varchar2(100),  foreign key(tcid) references t_cards(cid)  on delete cascade  on update cascade  )  partition by range(tdate)(  partition halfyearago values less than(to_date('2017-5-21','yyyy-mm-dd')),  partition onemonthage values less than(to_date('2017-10-21','yyyy-mm-dd')),  partition inthisweek values less than(maxvalue)  );    insert into t_trades(tcid,tmoney) values('1010 3576 1541 1111',2000);  insert into t_trades(tcid,tmoney) values('1010 3576 1114 1111',220);  drop table t_trades;  select * from t_trades;  delete from t_trades;  -- 根据卡号创建交易信息表的索引   create index tcid_index on t_trades(tcid);  -- 创建公有同义词synt_trades  create public synonym synt_trades for t_trades;    -- 步骤二   创建视图： create [or replace] view 视图名称 as 1条select语句  -- 1.创建并测试用户表表视图  create or replace view users_view  as  select * from t_users;    select * from users_view;  -- 2.创建并测试银行卡信息表视图  create or replace view cards_view  as  select * from t_cards;    select * from cards_view;  --3.创建并测试交易信息表视图  create or replace view trades_view   as  select * from t_trades;    select * from trades_view; --4.创建并测试查询挂失的客户信息视图 create or replace view creport_user_view as select * from t_users where uuid in (select cuuid from t_cards where creport='是')  select * from creport_user_view;   --5.创建并测试查询本周开户的卡号 显示相关信息视图 create or replace view thisweek_cid_view as select * from t_cards where cstarttime between trunc(sysdate,'IW') and trunc(sysdate,'IW')+6  select * from thisweek_cid_view;   -- TRUNC函数用于对值进行截断:  -- TRUNC（NUMBER）表示截断数字，TRUNC（n1,n2） ，n1表示被截断的数字，n2表示要截断到那一位。n2可以是负数，表示截断小数点前。注意，TRUNC截断不是四舍五入。  -- TRUNC（date）表示截断日期        -- select trunc(sysdate) from dual  结果是 截止到当日不设置，默认是截止到”日“        -- select trunc(sysdate,'year') from dual; --获取到本年 第一天        -- select trunc(sysdate,'month') from dual; --获取到 本月 第一天         -- select trunc(sysdate,'q') from dual; --获取到本季度 第一天        -- select to_char(trunc(sysdate),'yyyy-mm-dd hh24:mi:ss') from dual; --默认获取到日（当日的零点零分零秒）        -- select trunc(sysdate,'iw') from dual; --本周一        -- select trunc(sysdate,'ww') from dual;  -- 获取离当前时间最近的周四，若当天为周四则返回当天，否则返回上周四        -- select trunc(sysdate,'day') from dual; --获取到周（本周第一天，即上周日）        -- select trunc(sysdate,'hh24') from dual;  --截取到小时（当前小时，零分零秒）        -- select trunc(sysdate,'mi') from dual; --截取到分（当前分，零秒）  --6.创建并测试查询本月交易金额最高的卡号的视图 create or replace view max_tmoney_view as select cid from t_cards where cid in(select tcid from t_trades where tmoney=(select max(tmoney) from t_trades))   select * from max_tmoney_view;  -- 步骤三 创建触发器 -- 1.创建并测试卡号修改触发器 create or replace trigger test_update_cid_tri before update on t_cards for each row declare begin   if(:new.cid!=:old.cid)then   raise_application_error(-20000,'此列不允许修改！！！');   end if;   end;      update t_cards set cid='1010 3576 1114 1000' where cid='1010 3576 1114 1111'; -- 2.创建并测试交易信息表的触发器 create or replace trigger test_ins_upd_tra_tri before insert or update on t_trades for each row declare  oldcbalance t_cards.cbalance%type; begin   if(:new.ttype='存入')then     select cbalance into oldcbalance from t_cards where cid=:new.tcid;     update t_cards set cbalance=(oldcbalance+:new.tmoney) where :new.tcid=t_cards.cid and :new.tmoney!=0;       dbms_output.put_line('恭喜您存款啦！！！');   elsif(:new.ttype='支出')then     select cbalance into oldcbalance from t_cards where cid=:new.tcid;     if(oldcbalance>=:new.tmoney)then     update t_cards set cbalance=(oldcbalance-:new.tmoney)where :new.tcid=t_cards.cid and :new.tmoney!=0;       dbms_output.put_line('恭喜您取款啦！！！');     else       raise_application_error(-20001,'余额不足，支出失败！!!');     end if;   end if;   end;      insert into t_trades(tcid,ttype,tmoney) values('1010 3576 1114 1111','支出','100');   insert into t_trades(tcid,ttype,tmoney) values('1010 3576 1114 1111','存入','10000');   insert into t_trades(tcid,ttype,tmoney) values('1010 3576 1114 1111','支出','20000');   select * from t_trades;   select * from t_cards;     -- 步骤四：创建针对用户信息的程序包及程序包主体内容  -- 创建包头  create or replace package users_package  is  function create_cid_fun return varchar2; -- 声明公有的生成卡号的函数  procedure open_account_pro(v_uname t_users.uname%type,v_idcard t_users.idcard%type,v_utelephone t_users.utelephone%type,v_uaddress t_users.uaddress%type,v_ctype t_cards.ctype%type,v_cstore t_cards.cstore%type,v_cmoney t_cards.cmoney%type); --声明公有的开户的存储过程  procedure modify_password_pro(v_cid t_cards.cid%type,v_cpassword t_cards.cpassword%type,vnew_cpassword t_cards.cpassword%type); --声明公有的修改密码的存储过程  procedure report_card_pro(v_cid t_cards.cid%type,v_cpassword t_cards.cpassword%type,v_cuuid t_cards.cuuid%type); -- 声明公有的挂失账户的存储过程  end;  -- 创建包体  create or replace package body users_package  is  --实现公有的生成卡号的函数  function create_cid_fun   -- CONCAT 只能连接两个字符串,|| 可以连接多个字符串    return varchar2   as   v_cid t_cards.cid%type;   begin  -- dbms_output.put_line(trunc(dbms_random.value(1000,9999),-1));   v_cid:='1010 3576 '||trunc(dbms_random.value(1000,9999),-1)||' '||trunc(dbms_random.value(1000,9999),-1);   return v_cid;   end;    -- 实现公有的开户的存储过程   procedure open_account_pro(v_uname t_users.uname%type,v_idcard t_users.idcard%type,v_utelephone t_users.utelephone%type,v_uaddress t_users.uaddress%type,v_ctype t_cards.ctype%type,v_cstore t_cards.cstore%type,v_cmoney t_cards.cmoney%type)    as   v_cid t_cards.cid%type;   c number;   begin     v_cid:=ysg.users_package.create_cid_fun();     select count(1) into c from t_cards where v_cid=cid;      if(c=0)then     insert into t_users(uname,idcard,utelephone,uaddress) values(v_uname,v_idcard,v_utelephone,v_uaddress);     insert into t_cards(cid,ctype,cstore,cmoney,cbalance) values(v_cid,v_ctype,v_cstore,v_cmoney,v_cmoney);     else       raise_application_error(-20006,'此卡号已存在，开户失败！！！');     end if;     end;    --实现公有的修改密码的存储过程   procedure modify_password_pro(v_cid t_cards.cid%type,v_cpassword t_cards.cpassword%type,vnew_cpassword t_cards.cpassword%type )   as   begin     update t_cards set cpassword=vnew_cpassword where cid=v_cid and cpassword=v_cpassword and cpassword!=vnew_cpassword;   end;  -- 实现公有的挂失账户的存储过程  procedure report_card_pro(v_cid t_cards.cid%type,v_cpassword t_cards.cpassword%type,v_cuuid t_cards.cuuid%type)   as   c number;  old_cpassword t_cards.cpassword%type;  old_cuuid t_cards.cuuid%type;  begin    select count(1) into c from t_cards where v_cid=cid and creport='否';    select cpassword into old_cpassword from t_cards where v_cid=cid;    select cuuid into old_cuuid from t_cards where v_cid=cid;     if(c=1)then  --  if(v_cid=t_cards.cid)then    if(v_cpassword=old_cpassword)then    if(v_cuuid=old_cuuid)then     update t_cards set creport='是' where v_cid=cid;     dbms_output.put_line('挂失成功！！！');    end if;   -- end if;    else      dbms_output.put_line('挂失失败！！！');    end if;    else      dbms_output.put_line('此卡已挂失！！！');    end if;  end;    end;      -- 调用包  begin  ysg.users_package.modify_password_pro('1010 3576 1114 19014','666666','666660');  dbms_output.put_line(ysg.users_package.create_cid_fun());  ysg.users_package.open_account_pro('zh','123456789090909','12345612345','shida','RMB','活期','890');  ysg.users_package.report_card_pro('1010 3576 9650 6100','666660',1);  end;      -- 步骤五：创建针对银行卡信息表的程序包及程序包主体内容  -- 创建包头  create or replace package cards_package  is  --声明支取和存入的存储过程  procedure draw_and_store_pro(v_tcid t_trades.tcid%type,v_ttype t_trades.ttype%type,v_tmoney t_trades.tmoney%type,v_cpassword t_cards.cpassword%type);  -- 声明查询余额存储过程   procedure check_balance_pro(v_cid t_cards.cid%type,v_cpassword t_cards.cpassword%type,v_ctype out t_cards.ctype%type,v_cbalance out t_cards.cbalance%type);   -- 声明转账存储过程  procedure transfer_account_pro(from_cid t_cards.cid%type,from_cpassword t_cards.cpassword%type,to_cid t_cards.cid%type,from_cbalance t_cards.cbalance%type);  -- 声明汇总存储过程  procedure gather_pro(v_circulation out t_trades.tmoney%type,v_profit out t_trades.tmoney%type);   -- 声明销户存储过程  procedure close_account_pro(v_cid t_cards.cid%type,v_cpassword t_cards.cpassword%type);   end;    -- 创建包体  create or replace package body cards_package  is -- 实现支取和存入的存储过程   procedure draw_and_store_pro(v_tcid t_trades.tcid%type,v_ttype t_trades.ttype%type,v_tmoney t_trades.tmoney%type,v_cpassword t_cards.cpassword%type)     as     old_cpassword t_cards.cpassword%type;     begin       select cpassword into old_cpassword from t_cards where cid=v_tcid;       if(v_cpassword=old_cpassword)then       if(v_ttype='存入')then       insert into t_trades(tcid,ttype,tmoney) values(v_tcid,v_ttype,v_tmoney);       elsif(v_ttype='支出')then       insert into t_trades(tcid,ttype,tmoney) values(v_tcid,v_ttype,v_tmoney);       end if;       end if;       end;      -- 实现查询余额存储过程    procedure check_balance_pro(v_cid t_cards.cid%type,v_cpassword t_cards.cpassword%type,v_ctype out t_cards.ctype%type,v_cbalance out t_cards.cbalance%type)     as     c number;     begin       select count(1) into c from t_cards where cid=v_cid and cpassword=v_cpassword;       if(c=1)then       select ctype,cbalance into v_ctype,v_cbalance from t_cards where cid=v_cid and cpassword=v_cpassword;       dbms_output.put_line('您的账户余额是：'||v_cbalance||v_ctype);       else         dbms_output.put_line('卡号或密码错误，查询失败！！！');       end if;       end;   -- 实现转账存储过程  procedure transfer_account_pro(from_cid t_cards.cid%type,from_cpassword t_cards.cpassword%type,to_cid t_cards.cid%type,from_cbalance t_cards.cbalance%type)    as    from_user number;    to_user number;    old_cbalance t_cards.cbalance%type;    begin     select count(1) into from_user from t_cards where cid=from_cid and cpassword=from_cpassword;     select count(1) into to_user from t_cards where cid=to_cid;     if(from_user=1)then     if(to_user=1)then     select cbalance into old_cbalance from t_cards where cid=from_cid and cpassword=from_cpassword;     if(old_cbalance>from_cbalance)then     update t_cards set cbalance=cbalance+from_cbalance where cid=to_cid;     update t_cards set cbalance=cbalance-from_cbalance where cid=from_cid;      else        dbms_output.put_line('账户余额不足！！！');       rollback;     end if;     end if;     end if;   end;   -- 实现汇总存储过程  procedure gather_pro(v_circulation out t_trades.tmoney%type,v_profit out t_trades.tmoney%type)  as  expense_money t_trades.tmoney%type;  store_money t_trades.tmoney%type;  begin  select count(tmoney) into store_money from t_trades where ttype='存入';  select count(tmoney) into expense_money from t_trades where ttype='支出';  v_circulation:=store_money-expense_money;  v_profit:=store_money*0.003-expense_money*0.008;  dbms_output.put_line('流通余额:'||v_circulation||'   '||'盈利结算:'||v_profit);  end;  -- 实现销户存储过程  procedure close_account_pro(v_cid t_cards.cid%type,v_cpassword t_cards.cpassword%type)  as  v_cuuid t_cards.cuuid%type;  v_cbalance t_cards.cbalance%type;  c number;  u number;  begin  select count(1) into c from t_cards where cid=v_cid and cpassword=v_cpassword;  if(c=1)then  select cuuid into v_cuuid from t_cards where cid=v_cid and cpassword=v_cpassword;  select cbalance into v_cbalance from t_cards where cid=v_cid and cpassword=v_cpassword;  select count(1) into u from t_cards where cuuid=v_cuuid;  if(v_cbalance>0)then  dbms_output.put_line('你卡上的余额：'||v_cbalance||'将全部取出！！！');  delete from t_trades where tcid=v_cid;  delete from t_cards where cid=v_cid;  if(u=1)then  delete from t_users where uuid=v_cuuid;  end if;  else  dbms_output.put_line('你卡上的余额为0，直接销户！！！');  delete from t_trades where tcid=v_cid;  delete from t_cards where cid=v_cid;  if(u=1)then  delete from t_users where uuid=v_cuuid;  end if;  end if;  end if;  end;    end;    -- 调用包  declare   v_cbalance t_cards.cbalance%type;  v_ctype t_cards.ctype%type;  v_circulation t_trades.tmoney%type;  v_profit t_trades.tmoney%type;  begin   ysg.cards_package.draw_and_store_pro('1010 3576 0014 1154','存入',2000,'888888');   ysg.cards_package.check_balance_pro('1010 3576 1114 1114','888888',v_ctype,v_cbalance);    ysg.cards_package.transfer_account_pro('1010 3576 1114 1111','888888','1010 3576 1114 1114',6000);   ysg.cards_package.gather_pro(v_circulation,v_profit);   ysg.cards_package.close_account_pro('1010 3576 1114 1111','888888');  end;  select * from t_trades;select * from t_cards;select * from t_users;