ATM取款机(Oracle)
- 格式：doc
- 大小：39.00 KB
- 文档页数：6
ATM取款机(Oracle实现)一.功能描述:1.创建用户表和账户表，完成建表相关操作，录入测试数据（40分）2.建立程序包，完成如下功能:开户（10分）,登陆（10分）,修改密码（10分）,转帐（20分）,查询余额（10分）(总计60分)二.参考代码:建表:程序包定义:参考答案：（把下面的答案直接复制到SQL执行控制台可以直接执行）--账户表create table User_Account(Aid varchar2(19) not null primary key, --卡号Username varchar2(20) not null, --户主Opendate date default sysdate, --开户时间Balance number default 0 , --余额userpassword varchar2(8) DEFAULT '888888' not null, --密码stauts Char(2) default 0 not NULL --卡状态:0:正常，1：冻结2:注销)--交易表create table transinfo(Tid INTEGER not null primary key, --交易流水号Aid varchar2(19) not null, --卡号transtype VARCHAR2(10) not null, --交易类型:存入，支取tBalance Number(8,2) not null, --交易金额Transdate date default sysdate not null --交易时间)-- 创建交易流水号序列create sequence seq_transinfostart with 000000001increment by 1nomaxvalue;--查询数据select * from User_Account where aid='9558000076903450474'select * from transinfodelete from transinfodelete from User_Account--录入测试数据insert into User_Account values(substr(to_char(( dbms_random.value(100000000000,0)+95580000*100000000000)),1,19), '张三',default,0,'111111','0');insert into transinfo values(seq_transinfo.nextval,'9558000076903450474','存入',20,default); insert into transinfo values(seq_transinfo.nextval,'9558000076903450474','存入',20,sysdate);--建立包头create or replace package pack_User_Atmis--存储过程proc_CreateUserAccount(vUsername varchar2);--开户，输入用户姓名，产生卡号为19位，前8位为95580000，录入到账户表并输出生成的卡号。
procedure proc_CreateUserAccount(vUsername varchar2);--存储过程proc_insert_transinfo(vaid varchar2,vpassword varchar2);--登陆，输入账号和密码，登录成功打印ok，失败打印errorprocedure proc_insert_transinfo(vaid varchar2,vpassword varchar2);--存储过程proc_Change_password(vaid varchar2,oldPass varchar2,newPass varchar2);--修改密码，输入账号、原始密码、新密码，判断修改流程，成功打印ok，失败打印error procedure proc_Change_password(vaid varchar2,oldPass varchar2,newPass varchar2);--存储过程proc_give_money(vaid varchar2,giveID varchar2,money number);--转帐，输入转出账号，转入账号，以及金额，执行相关流程，成功打印ok，失败打印errorprocedure proc_give_money(vaid varchar2,giveID varchar2,money number);--函数fun_check_balance(vaid varchar2,vpassword varchar2); ----查询余额，输入账号和密码，返回余额function fun_check_balance(vaid varchar2,vpassword varchar2) return number;end pack_User_Atm;--建立包体create or replace package body pack_User_Atmis--注册procedure proc_CreateUserAccount(vUsername varchar2)isvaid User_Account.Aid%type;begin--开头是95580000，后面数字随即生成select substr(to_char(( dbms_random.value(100000000000,0)+95580000*100000000000)),1,19) into vaid from dual;--默认账户已经激活insert into User_Account values(vaid,vUsername,sysdate,0,'111111','0');commit;dbms_output.put_line(vaid);end;--登录procedure proc_insert_transinfo(vaid varchar2,vpassword varchar2)isdl_count int;beginselect count(*) into dl_count from User_Account where Aid=vaid and userpassword=vpassword and stauts='0';if(dl_count=1) thendbms_output.put_line('OK');elsedbms_output.put_line('error');end if;end;--修改密码procedure proc_Change_password(vaid varchar2,oldPass varchar2,newPass varchar2)isdl_count int;begin--查询账号是否存在和状态是否正常select count(*) into dl_count from User_Account where Aid=vaid and userpassword=oldPass and stauts='0';--如果存在if(dl_count=1) then--更新密码update User_Account set userpassword=newPass where Aid=vaid;--提交commit;dbms_output.put_line('OK');elsedbms_output.put_line('error');end if;exception--捕获异常when others thendbms_output.put_line('error');rollback ;end;--转账procedure proc_give_money(vaid varchar2,giveID varchar2,money number)isvBalance User_Account.Balance%type;stautsF User_Account.Stauts%type;stautsT User_Account.Stauts%type;begin--设置保存记录点savepoint save_myitems;select Balance into vBalance from User_Account where aid=giveID;--判断账户是否正常select Stauts into stautsF from User_Account where aid=vaid;select Stauts into stautsT from User_Account where aid=giveID;if(stautsF='0' and stautsT='0') then--判断余额是否足够if(vBalance<money) thendbms_output.put_line('error');else--vaid增加钱update User_Account set Balance=Balance+money where aid=vaid;--giveID扣钱update User_Account set Balance=Balance-money where aid=giveID;--写入交易流水账号insert into transinfo values(seq_transinfo.nextval,vaid,'存入',money,sysdate);insert into transinfo values(seq_transinfo.nextval,giveID,'支取',money,sysdate);--提交commit;dbms_output.put_line('OK');end if;elsedbms_output.put_line('error');end if;exception--捕获异常when dup_val_on_index thenrollback to savepoint save_myitems;commit;end;--查询余额function fun_check_balance(vaid varchar2,vpassword varchar2) return numberisyue User_Account.Balance%type;sta User_Account.Stauts%type;begin--判断账户状态是否可用select Stauts into sta from User_Account where aid=vaid;if(sta = '0') then--用户可用查询余额select Balance into yue from User_Account where aid=vaid and userpassword=vpassword; else--否则输出错误信息dbms_output.put_line('error');end if;return yue;end;end pack_User_Atm;--测试数据--注册declarebeginpack_User_Atm.proc_CreateUserAccount('李四');end;--登录declarebeginpack_User_Atm.proc_insert_transinfo('9558000076903450474','111111');end;--修改密码declarebeginpack_User_Atm.proc_Change_password('9558000076903450474','111111','222222'); end;--转账declarebeginpack_User_Atm.proc_give_money('9558000043465243545','9558000076903450474',10); end;--查询余额select pack_User_Atm.fun_check_balance('9558000076903450474','111111') from dual;--查看错误信息select * from user_errors;。
