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