第十八章-Oracle实战案例.ppt

单击此处编辑母版标题样式,第一级,第二级,第三级,第四级,第五级,*,Click to edit the title text format,2012,Inspur Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,Inspur,Education,2012,Inspur Group,Click to edit the title text format,Inspur,Education,版权所有：,浪潮优派教育科技有限公司,版权所有：,浪潮优派教育科技有限公司,谢谢大家,!,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,2012,Inspur Group,Click to edit the title text format,Inspur Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,Inspur,Education,版权所有：,浪潮优派教育科技有限公司,版权所有：,浪潮优派教育科技有限公司,谢谢大家,!,Click to edit the title text format,Inspur Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,2012,Inspur Group,Click to edit the title text format,Inspur Group,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,*,/23,Inspur,Education,Click to edit the title text format,Inspur,Education,Inspur Group,Click to edit the title text format,Click to edit the outline text format,Second Outline Level,Third Outline Level,Fourth Outline Level,Fifth Outline Level,Sixth Outline Level,Seventh Outline Level,Eighth Outline Level,Ninth Outline Level,Inspur,Education,*,第,18,章,Oracle,实战案例,本章目标,运用前面的章节所涉及的知识进行,Oracle,项目功能的实现,2,需求说明与概要设计,ATM,基本功能,3,需求说明与概要设计,ATM,用户使用的基本流程,4,欢迎界面,密码验证,选择操作界面,存款,取款,查询余额,查询明细,转账,修改密码,继续交易,退出,打印凭条,打印,是,是,否,否,是,否,需求说明与概要设计,取款流程,5,取款,100,元,200,元,300,元,400,元,自定义,500,元,输入金额,100,的倍数,取走钞票,继续交易,主界面,退出,其他服务,是,是,金额选择,否,否,需求说明与概要设计,存款流程,6,存款,放入钞票,等待处理,取走未识别的钞票,添加,确认金额,继续交易,退出,主界面,其他服务,是,是,否,否,需求说明与概要设计,查询流程,7,查询余额,币种选择,港币,日元,欧元,美元,人民币,显示余额,继续交易,主界面,其他服务,退出,是,否,需求说明与概要设计,转账流程,8,转账,输入转入卡号,卡号正确,输入转出金额,继续,操作成功,继续交易,主界面,其他服务,退出,是,否,是,否,否,是,详细,设计,取款,9,详细,设计,存,款,10,详细,设计,查询,11,详细,设计,现金转账,12,详细,设计,电子转账,13,详细,设计,密码修改,14,数据库,设计,15,数据库设计一般分为六个阶段，本书着重介绍与系统设计有关的前四个阶段：,需求分析,概念模型设计,逻辑结构设计,物理结构设计,数据库实施,数据库,设计,数据库,需求分析,16,用户可以在,ATM,取得现金。,用户可以在,ATM,上进行现金存储。,用户可以在,ATM,上进行现金转账。,用户可以修改账户的密码。,查看账户的基本信息。,查看账户的交易信息。,修改账户的密码,对于异常账户的停用和启用。,数据库,设计,数据库,需求分析,17,数据库,设计,概念,模型设计,18,数据库,设计,概念,模型设计,19,数据库,设计,概念,模型设计,20,数据库,设计,概念,模型设计,21,数据库,设计,概念,模型设计,22,数据库,设计,逻辑,结构设计,23,数据库,设计,物理,结构设计,24,系统实现,建表,25,-,创建用户表,CREATE TABLE t_user(,user_number NUMBER(10)NOT NULL,user_ic VARCHAR2(18)NOT NULL,user_name VARCHAR2(32)NOT NULL,user_contact VARCHAR2(13)NOT NULL,user_type VARCHAR2(20)NOT NULL,CONSTRAINT pk_t_user PRIMARY KEY(user_number),);,COMMENT ON TABLE t_user IS,用户表,;,COMMENT ON COLUMN t_user.user_number IS,用户编号,;,COMMENT ON COLUMN t_user.user_ic IS,用户身份证号,;,COMMENT ON COLUMN t_user.user_name IS,用户姓名,;,COMMENT ON COLUMN t_user.user_type IS,用户类型,;,COMMENT ON COLUMN t_user.user_contact IS,用户联系方式,;,-,创建账户表,CREATE TABLE t_account(,acc_cardsid NUMBER(19)NOT NULL,acc_usernumber NUMBER(10)NOT NULL,acc_cardspassword NUMBER(6)NOT NULL,acc_cardsdate DATE NOT NULL,acc_cardsmoney DECIMAL(10,2)NOT NULL,acc_balance DECIMAL(10,2)NOT NULL,acc_cardsstate VARCHAR2(40),CONSTRAINT pk_t_account PRIMARY KEY(acc_cardsid),);,COMMENT ON TABLE t_account IS,账户表,;,COMMENT ON COLUMN t_account.acc_usernumber IS,用户编号,;,COMMENT ON COLUMN t_account.acc_cardsid IS,卡号,;,COMMENT ON COLUMN t_account.acc_cardspassword IS,密码,;,COMMENT ON COLUMN t_account.acc_cardsdate IS,开户日期,;,COMMENT ON COLUMN t_account.acc_cardsmoney IS,开户金额,;,COMMENT ON COLUMN t_account.acc_balance IS,余额,;,-,创建,ATM,表,CREATE TABLE t_atm(,atm_number NUMBER(10)NOT NULL,atm_state CHAR(1)NOT NULL,atm_balance NUMBER(10,2)DEFAULT 0.00,atm_address VARCHAR2(100),CONSTRAINT pk_t_atm PRIMARY KEY(atm_number),);,COMMENT ON TABLE t_atm IS ATM,表,;,COMMENT ON COLUMN t_atm.atm_number IS ATM,编号,;,COMMENT ON COLUMN t_atm.atm_state IS ATM,运行状态,;,COMMENT ON COLUMN t_atm.atm_balance IS ATM,余额,;,COMMENT ON COLUMN t_atm.atm_address IS ATM,地址,;,-,创建交易流水表,CREATE TABLE t_transaction(,transactions_number NUMBER(20)NOT NULL,transactions_transoutacc NUMBER(19)NOT NULL,transactions_transinacc NUMBER(19)NOT NULL,transactions_atmnumber NUMBER(10)NOT NULL,transactions_type VARCHAR2(8)NOT NULL,transactions_money DECIMAL(10,2)DEFAULT 0.00,CONSTRAINT pk_t_transaction PRIMARY KEY(transactions_number),);,COMMENT ON TABLE t_transaction IS,交易流水表,;,COMMENT ON COLUMN t_transaction.transactions_number IS,交易流水号,;,COMMENT ON COLUMN t_transaction.transactions_transoutacc IS,转出账户,;,COMMENT ON COLUMN t_transaction.transactions_transinacc IS,转入账户,;,COMMENT ON COLUMN t_transaction.transactions_atmnumber IS ATM,编号,;,COMMENT ON COLUMN t_transaction.transactions_type IS,交易类型,;,COMMENT ON COLUMN t_transaction.transactions_money IS,交易金额,;,系统实现,存储过程,26,CREATE OR REPLACE PROCEDURE qukuan(i_cardsid t_account.acc_cardsid%TYPE,i_currpass t_account.acc_cardspassword%TYPE,i_money t_account.acc_cardsmoney%TYPE,i_type t_transaction.transactions_type%TYPE,i_atmnumber t_atm.atm_number%TYPE),-,定义,4,个输入参数：卡号，密码，金额，交易类型和,atm,编号,IS,-,定义,5,个变量,v_password t_account.acc_cardspassword%TYPE;,v_cardsstate t_account.acc_cardsstate%TYPE;,v_balance t_account.acc_cardsmoney%TYPE;,v_atmbalance t_atm.atm_balance%TYPE;,v_accumulate NUMBER(20);,BEGIN,SELECT acc_cardspassword INTO v_password FROM t_account WHERE acc_cardsid=i_cardsid;-,获取账户密码,IF v_password=i_currpass THEN-,密码校验,SELECT acc_cardsstate INTO v_cardsstate FROM t_account WHERE acc_cardsid=i_cardsid;-,获取账户状态,IF v_cardsstate ,冻结,THEN-,判断是否已冻结,SELECT sum(transactions_money)INTO v_accumulate FROM t_transaction WHERE transactions_transoutacc=i_cardsid AND transactions_type=,取款,;-,计算取款总额,IF(v_accumulate IS NOT null AND v_accumulate+i_money 20000 AND i_money 5000 AND mod(i_money,100)=0)OR(v_accumulate IS null AND i_money i_money THEN-,判断,ATM,的余额是否大于取款金额,SELECT acc_balance INTO v_balance FROM t_account WHERE acc_cardsid=i_cardsid;-,获取账户的余额,SELECT atm_balance INTO v_atmbalance FROM t_atm WHERE i_atmnumber=atm_number;,INSERT INTO t_transaction(transactions_number,transactions_transoutacc,transactions_atmnumber,transactions_type,transactions_money)VALUES(seq_atm.nextval,i_cardsid,i_atmnumber,i_type,i_money);-,生成交易流水,UPDATE t_account SET acc_balance=v_balance-i_money WHERE i_cardsid=acc_cardsid;-,更新账户余额,UPDATE t_atm SET atm_balance=v_atmbalance-i_money WHERE i_atmnumber=atm_number;-,更新,ATM,机余额,dbms_output.put_line(,取款成功！,);,commit;,ELSE,dbms_output.put_line(ATM,机内余额不足！,);,END IF;,ELSE,dbms_output.put_line(,金额不满足条件！,);,END IF;,ELSE,dbms_output.put_line(,此卡被冻结！,);,END IF;,ELSE,dbms_output.put_line(,密码不正确,);,END IF;,EXCEPTION,WHEN no_data_found THEN,ROLLBACK;,dbms_output.put_line(,没有数据！,);,WHEN OTHERS THEN,ROLLBACK;,dbms_output.put_line(,取款失败！,);,END;,/,本章小结,27,谢谢,!,28,