oracle查询atm余额,ATM机oracle项目

create sequence new_rows start with 1 MAXVALUE 999999 MINVALUE 1 CYCLE CACHE 30 ---------------------------表创建----------------------------------------------------------- create table User_Account (   Aid      varchar2(16) not null primary key,   Username varchar2(20) not null,   Opendate date default sysdate,   Balance  number default 0 ,   password varchar2(11) not null,   stauts   Char(2) default 1 not null ) ; create table transinfo (   Tid       varchar2(20) not null primary key,   Aid       varchar2(16) not null,   transtype Number(2) not null,   tBalance  Number(8,2) not null,   Transdate date default sysdate not null,   note      Varchar2(500) not null ) ; alter table transinfo add constraint FK_RtID foreign key(Aid) references User_Account(Aid);  alter table User_Account add CONSTRAINT Balance_cap CHECK (Balance>=0); ----------------------------------程序包------------------------------------------------------------ CREATE OR REPLACE PACKAGE  User_Atm AS PROCEDURE CreateUserAccount(vUsername varchar2,vpassword varchar2);--开户 PROCEDURE insert_transinfo(vaid varchar2,vpassword varchar2,outNum out number);--登陆 PROCEDURE Change_password(vaid varchar2,oldPass varchar2,newPass varchar2);--修改密码 PROCEDURE give_money(vaid varchar2,giveID varchar2,money number);--转帐 PROCEDURE check_balance(vaid varchar2,vpassword varchar2,outNum out number); ----查询余额 PROCEDURE save_balance(vaid varchar2,inMoney number);------存款 PROCEDURE out_balance(vaid varchar2,ouMoney number,outNum out number);---取款 END User_Atm; ----------------------------------------------------------------------------- CREATE OR REPLACE PACKAGE BODY User_Atm AS --------------------------开户------------------------------------------------  PROCEDURE CreateUserAccount(vUsername varchar2,vpassword varchar2) AS num1 varchar2(16); countnum1 number; BEGIN countnum1:=1; while countnum1!=0 loop num1:=trunc(dbms_random.value(10000000,99999999))||'62270018'; select count(*) into countnum1  from User_Account where aid=num1; end loop; insert  into User_Account (Aid,Username,password) values(num1,vUsername,vpassword); dbms_output.put_line(num1||'帐号插入成功'); commit; END; ---------------------------登陆----------------------------------------- PROCEDURE insert_transinfo(vaid varchar2,vpassword varchar2,outNum out number) AS num1 varchar2(16); countnum number; countnum1 number; countnum2 varchar2(16); BEGIN select count(*) into countnum from user_account where aid=vaid; if countnum=0 then  Dbms_Output.put_line('帐户名不存在'); outNum:=-1; else select count(*) into countnum1 from user_account where aid=vaid and password=vpassword; if countnum1!=0 then select stauts into countnum2 from user_account where aid=vaid and password=vpassword; end if;  case  when countnum1=0 then  Dbms_Output.put_line('帐户名或密码错误'); outNum:=0;   when countnum2=-1 then outNum:=-1;   Dbms_Output.put_line('帐户已注销');   when countnum2=-2 then outNum:=-2;    Dbms_Output.put_line('帐户已冻结');    else    insert into transinfo (tid,aid,transtype,tbalance,note) values(new_rows.nextval,vaid,0,0,'登陆' ); commit;   end case; END if; END; --------------------------修改密码----------------------------------------------  PROCEDURE Change_password(vaid varchar2,oldPass varchar2,newPass varchar2) AS countnum number; BEGIN select count(*) into  countnum from user_account where aid=vaid and password=oldPass; if countnum!=0 then update  user_account set password=newPass where aid=vaid and password=oldPass; insert into transinfo values(new_rows.nextval,vaid,3,0,sysdate,'修改密码'); commit;  Dbms_Output.put_line('修改成功');  else   Dbms_Output.put_line('用户名或密码错误');   end if; end; ----------------------------转帐------------------------------------------  PROCEDURE give_money(vaid varchar2,giveID varchar2,money number) AS num1 number; num2 number; BEGIN select count(*) into num1 from user_account  where aid=vaid; select count(*) into num2  from user_account where aid=giveID; case when num1=0 then dbms_output.put_line('帐号未找到'); when num2=0 then dbms_output.put_line('目标帐号未找到'); else update user_account set balance=balance-money where aid=vaid; update user_account set balance=balance+money where aid=giveID; dbms_output.put_line('转帐成功'); commit; end case; exception when others then rollback; end; ----------------------------查询余额------------------------------------- PROCEDURE check_balance(vaid varchar2,vpassword varchar2,outNum out number) AS countnum number; BEGIN select count(*) into countnum from user_account where aid=vaid and password=vpassword; if countnum=0 then  Dbms_Output.put_line('帐户名不存在'); else select balance into outNum from user_account where aid=vaid and password=vpassword;  Dbms_Output.put_line('余额:'||outNum);  insert into transinfo (tid,aid,transtype,tbalance,note) values(new_rows.nextval,vaid,0,0,'查询' ); commit; end if; END; --------------------------存款------------------------------------------ PROCEDURE save_balance(vaid varchar2,inMoney number) AS countnum number; BEGIN if Mod(inMoney,100)=0 then select count(*) into countnum from user_account where aid=vaid; if countnum=0 then  Dbms_Output.put_line('帐户名不存在'); else update user_account set balance=balance+inMoney where aid=vaid; insert into transinfo (tid,aid,transtype,tbalance,note) values(new_rows.nextval,vaid,1,inMoney,'存款' ); commit;  Dbms_Output.put_line('存款成功'); end if; else  Dbms_Output.put_line('必需是100的倍数');  end if; exception when others then rollback; end; ----------------------------取款---------------------------------------- PROCEDURE out_balance(vaid varchar2,ouMoney number,outNum out number) AS countnum number; countnum2 number; countnum3 number; BEGIN outNum:=0; select balance into countnum from user_account where AID=vaid; select count(*) into countnum2 from transinfo where round(to_number(sysdate-transdate))=0 and  AID=vaid; select sum(tbalance) into countnum3 from transinfo where round(to_number(sysdate-transdate))=0 and  AID=vaid; case when Mod(ouMoney,50)!=0 then  Dbms_Output.put_line('必需是50的倍数');  when countnum-ouMoney<0 then  Dbms_Output.put_line('余额不足!');  when  countnum2>=3 then   Dbms_Output.put_line('当天取款超过三次了');   when  ouMoney>2000 then    Dbms_Output.put_line('每次最多2000');    when countnum3+ouMoney>5000 then     Dbms_Output.put_line('每天取款最高5000');   else  update user_account set balance=balance-ouMoney where AID=vaid; insert into transinfo (tid,aid,transtype,tbalance,note) values(new_rows.nextval,vaid,2,ouMoney,'取款' ); outNum:=1; commit; end case; exception when others then rollback; end; ------------------------------------------------------------------ end; ----------------------------------------------