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;
----------------------------------------------