Oracle校园卡消费系统（文档+代码）

程序员文章站 2024-01-17 11:54:58

...

1 系统分析

2 数据库设计与实现

1 系统分析

1. 题目要求

校园卡可以用于食堂就餐和商铺消费结算。每个食堂消费终端内置有唯一的终端编号，结算时先输入金额，顾客再刷卡，系统自动从卡中扣除相应金额。每个商铺销售终端都有唯一编号，消费时需要输入各商品编号、数量，系统从数据库中读出单价并计算消费总额。每周由财务处进行一次集中结算，统计每个商铺和食堂窗口的销售额。每个食堂窗口和商铺均有多个消费结算终端，均连接到数据库服务器。客户需要先到财务处预存款才可消费。

1. 题目分析

通过对上述题目要求的分析，总结出一个完整的校园卡消费系统需要具备：存现、转账、退款、商铺消费、窗口消费、挂失、学生信息查询、学生所有订单查询、终端消费统计等功能。

在对现实生活中校园卡消费终端的研究发现，每个终端都内置一个终端编号，每张校园卡也内置一个校园卡卡号（不是学号），比如在消费、存现的过程中，们只需要输入金额，在卡片接触终端机器时，系统会自动识别两端的编号。但在我们模拟过程中，做不到自动识别，所以需要手动的输入终端编号和校园卡卡号，然后通过卡号去查询所属学号，产生一个与学号相关的订单记录并插入到总数据库中。这样即便是更换新的校园卡时，仍然可以查询到消费记录。

但挂失就不一样了，挂失是在我们不知道卡号的情况下，需要用学号到办理中心进行卡号变更，所以输入的是学号。

2 数据库设计与实现

2.1 概念结构设计

表结构：

校园卡信息表card：

Oracle校园卡消费系统（文档+代码）

终端信息表terminal：

Oracle校园卡消费系统（文档+代码）

商品信息表goods：

Oracle校园卡消费系统（文档+代码）

消费记录表consume：

Oracle校园卡消费系统（文档+代码）

变更记录表change：

Oracle校园卡消费系统（文档+代码）

E-R图：

Oracle校园卡消费系统（文档+代码）

2.3 物理结构设计

数据库的物理设计就是根据所选用的DBMS和处理需求，进行物理存储安排，建立索引，形成数据库的内模式，为逻辑数据模型选取一个最适合应用要求的物理结构的过程，在这个阶段中要完成两大任务：

1）确定数据库的物理结构，在关系数据库中主要是存取方法和存储结构；

2）对物理结构进行评价，评价的重点是时间和空间效率。

为数据库中各基本表建立的索引如下：

由于基本表 card的属性user_id经常在查询条件和连接操作的连接条件中出现，考虑在这个属性上建立索引。

3 数据库实施

建立数据表、索引、序列，视图

建立5个数据表，索引为校园卡信息表中的学号属性，并分别在校园卡卡号，消费订单编号，业务办理编号上建立序列。当办理挂失业务和产生订单时，自动生成新的唯一编号。视图为财务部统计的各终端消费汇总情况，按消费金额排名。

插入初始数据

录入终端信息，校园卡信息，以及商品信息，订单信息由存储过程自动生成。

三、设计pl存储过程

1.商铺消费 EXECUTE sp_sql(卡号,终端编号,商品编号,商品数量);

输入校园卡编号，终端编号，商品编号，商品数量，订单号由序列自动创建、时间为当前系统时间。首先判断是否挂失，如果挂失则输出“已冻结”，如图

Oracle校园卡消费系统（文档+代码）

否则根据商品编号查找商品表goods获取商品价格，乘以数量计算订单金额，查询用户余额，如果余额不足则输出

Oracle校园卡消费系统（文档+代码）

满足即可创建订单，查寻校园卡信息表获取用户学号，插入一条新的消费记录，更改校园卡余额，获取用户姓名。根据终端编号查询终端名，最后由程序输出本次的记录，类似于小票

Oracle校园卡消费系统（文档+代码）

整个过程将4个表联系在了一起，需要注意的是创建的订单里面是学号，不是卡号便于后期查询个人消费记录。

2. 食堂消费 EXECUTE st_sql(卡号,终端编号,金额);

同上，只是没有商品数量字段，产生的订单格式一样。

Oracle校园卡消费系统（文档+代码）

3. 存现程序 EXECUTE cx_sql(卡号,金额);

输入卡号、存现金额。首先查询校园卡信息表判断是否挂失，挂失则输出校园卡已冻结，否则更改余额，并产生业务办理记录，如（交易单号、根据卡号查询的学号、默认的业务类型“存现”、存现金额、自动生成的交易时间），交易单号由序列生成。然后输出业务办理的小票

Oracle校园卡消费系统（文档+代码）

4. 退款程序 EXECUTE tk_sql(卡号);

输入卡号、首先判断是否挂失，如果未挂失则将校园卡余额更新为0，产生并插入一条业务办理记录，如（交易单号、根据卡号查询的学号、默认的业务类型“退款”、退款金额、自动生成的交易时间）。最后输出业务业务办理的小票

Oracle校园卡消费系统（文档+代码）

5.转账程序 EXECUTE zz_sql(卡号1,卡号2,金额);

输入卡号1，卡号2，转账金额。首先判断两个卡号是否挂失，然后判断转出账户金额是否充足，若条件都满足则扣减卡号1账户余额，增添卡号2账户余额，并产生业务办理记录（交易单号、根据卡号1查询的学号、默认的业务类型“转账”、转账金额、自动生成的交易时间）。最后打印小票

Oracle校园卡消费系统（文档+代码）

6.挂失程序 EXECUTE gs_sql(学号);

输入学号，查询到当前使用的校园卡卡号、为其添加“挂失”字段，然后创建新的校园卡表记录，将学号、名字、金额转移（原金额设为0），挂失字段为空。

Oracle校园卡消费系统（文档+代码）

7.查询某个账户信息 EXECUTE user_sql(学号);

输入学号，查询校园卡信息表，获得当前正在使用的卡号、姓名、余额，并打印输出

Oracle校园卡消费系统（文档+代码）

8.查询某个用户所有订单信息 EXECUTE user_order(学号,:data); PRINT data;

输入学号，查询消费记录表，获取金额信息，将类型设置为“消费”，根据存储的终端编号查找终端信息表获得终端名，获得订单创建的时间戳。在根据学号查询变更记录表，获取金额信息，将类型设置为存储的类型{“存现”，“转账”，“退款”}，地点字段为空，获得记录创建时间。然后将两个查询的结果表纵向连接，按时间先后排序，输出的一张表，这里用到了游标的知识，表的属性为（金额、类型（存现、转账、退款、消费）、地点（只有消费显示地点）、具体时间），结果如下

Oracle校园卡消费系统（文档+代码）

9.财务部查看各终端消费视图 select * from v_rank;

按消费金额降序排序，统计各个终端的消费情况，比如输出的字段（终端名、所在区域、累计消费金额）。

Oracle校园卡消费系统（文档+代码）

4 其它说明

遇到的问题

1.PL程序中序列的引用问题

最开始采用p_change_id:=change_seq.nextval这种形式引用，在oracle11版本上测试成功，但在oracle10版本上出错，最后使用了课本上规范的写法。

SELECT change_seq.nextval

INTO p_change_id

FROM dual;

2.PL程序中多条件查询要使用括号

要根据学号查询校园卡信息表中正在使用的校园卡，一开始没有在and后面的条件外加括号，使得每次查询都出错，但过程却创建成功，网上找了很多资料也不知道什么原因，最后无意中尝试用括号括起来，成功了。WHERE user_id=p_user_id AND (card_lost is null);

3.如何输入一个参数，经查询返回一个表

一开始考虑到两个方案，一是实现能传入参数的视图，因为视图能输出表，但不能引入参数，没有找到这样应用的实例；二使用存储过程，可以传参但只能一行一行的输出，虽然使用for loop循环，但破坏了表的输出形式，经查询，网上都是使用包和游标，太复杂了就没有采用。后来随意尝试发现不用包只用游标也可以，用游标记录一个结果集，通过out传参。存储过程内定义为data out sys_refcursor；过程外定义变量为VARIABLE data refcursor;打印输出print data;

代码：


drop table card;
drop table terminal;
drop table goods;
drop table consume;
drop table change;
DROP SEQUENCE card_seq;
DROP SEQUENCE consume_seq;
DROP SEQUENCE change_seq;
drop procedure gs_sql;
drop procedure cx_sql;
drop procedure tk_sql;
drop procedure zz_sql;
drop procedure st_sql;
drop procedure sp_sql;
drop procedure user_sql;
drop procedure user_order;
drop view v_rank;

--建表
create table card(
card_id varchar2(10) primary key,
user_id varchar2(10) not null,
user_name varchar2(10) not null,
card_account float not null,
card_lost varchar2(10)check(card_lost='挂失') 
);

create table terminal(
terminal_id varchar2(10) primary key,
terminal_name varchar2(15) not null,
terminal_area varchar2(10)check(terminal_area='西区'or terminal_area='南区') not null
);

create table goods(
goods_id varchar2(10) primary key,
goods_name varchar2(10) not null,
goods_money float not null
);

create table consume(
consume_id varchar2(10) primary key,
terminal_id varchar2(10) not null,
user_id varchar2(10) not null,
consume_money float not null,
consume_time date not null
);

create table change(
change_id varchar2(10) primary key,
user_id varchar2(10) not null,
change_type varchar2(10)check(change_type='存现'or change_type='退款'or change_type='转账') not null,
change_money float not null,
change_time date not null
);

--索引
CREATE INDEX i_user_id ON card(user_id);

--序列
CREATE SEQUENCE card_seq
INCREMENT BY 1
START WITH 10000
CACHE 500
NOCYCLE;

CREATE SEQUENCE consume_seq
INCREMENT BY 1
START WITH 20000000
CACHE 500
NOCYCLE;

CREATE SEQUENCE change_seq
INCREMENT BY 1
START WITH 30000000
CACHE 500
NOCYCLE;

--插入数据
INSERT INTO card VALUES(card_seq.nextval,'1704011011','张三',100,null);
INSERT INTO card VALUES(card_seq.nextval,'1704011012','李四',1000,null);
INSERT INTO card VALUES(card_seq.nextval,'1704011021','王五',0,'挂失');
INSERT INTO card VALUES(card_seq.nextval,'1704011021','王五',100,null);

INSERT INTO goods VALUES('1','矿泉水',2);
INSERT INTO goods VALUES('2','面包',6);
INSERT INTO goods VALUES('3','饼干',4);
INSERT INTO goods VALUES('4','方便面',5);
INSERT INTO goods VALUES('5','卫生纸',3);
INSERT INTO goods VALUES('6','烤肠',1);
INSERT INTO goods VALUES('7','水壶',55);

INSERT INTO terminal VALUES('100','1食堂商店','西区');
INSERT INTO terminal VALUES('101','1食堂1-1班组','西区');
INSERT INTO terminal VALUES('102','1食堂1-2班组','西区');
INSERT INTO terminal VALUES('103','2食堂商店','西区');
INSERT INTO terminal VALUES('104','2食堂2-1班组','南区');

--存现pl
SET serveroutput ON
SET verify OFF
CREATE OR REPLACE PROCEDURE cx_sql
  (p_card_id IN card.card_id%TYPE,
    p_money IN card.card_account%TYPE)
IS
    p_user_id card.user_id%TYPE;
    p_user_name card.user_name%TYPE;
    p_card_lost card.card_lost%TYPE;
    p_change_id change.change_id%TYPE;
  BEGIN
  SELECT user_id,user_name,card_lost
  INTO p_user_id,p_user_name,p_card_lost
  FROM card
  WHERE card_id=p_card_id;
  IF p_card_lost='挂失' THEN
     DBMS_OUTPUT.PUT_LINE('存现失败，校园卡已冻结！');
  ELSE
    BEGIN
      UPDATE card SET card_account=card_account+p_money
      WHERE card_id=p_card_id;
      SELECT change_seq.nextval
      INTO p_change_id
      FROM dual;
      INSERT INTO change VALUES(p_change_id,p_user_id,'存现',p_money,SYSDATE);
      DBMS_OUTPUT.PUT_LINE('账户：'||p_user_id||p_user_name||' 存现成功，金额'||p_money||'元，交易编号：'||p_change_id);
    END;
  END IF;
END;
/

--退款pl
CREATE OR REPLACE PROCEDURE tk_sql
  (p_card_id IN card.card_id%TYPE)
IS
    p_user_id card.user_id%TYPE;
    p_user_name card.user_name%TYPE;
    p_card_account card.card_account%TYPE;
    p_card_lost card.card_lost%TYPE;
    p_change_id change.change_id%TYPE;
  BEGIN
  SELECT user_id,user_name,card_account,card_lost
  INTO p_user_id,p_user_name,p_card_account,p_card_lost
  FROM card
  WHERE card_id=p_card_id;
  IF p_card_lost='挂失' THEN
     DBMS_OUTPUT.PUT_LINE('退款失败，校园卡已冻结！');
  ELSE
    BEGIN
      UPDATE card SET card_account=0
      WHERE card_id=p_card_id;
      SELECT change_seq.nextval
      INTO p_change_id
      FROM dual;
      INSERT INTO change VALUES(p_change_id,p_user_id,'退款',p_card_account,SYSDATE);
      DBMS_OUTPUT.PUT_LINE('账户：'||p_user_id||p_user_name||' 退款成功，金额'||p_card_account||'元，交易编号：'||p_change_id);
    END;
  END IF;
END;
/

--转账pl
CREATE OR REPLACE PROCEDURE zz_sql
  ( p_card_id1 IN card.card_id%TYPE,
    p_card_id2 IN card.card_id%TYPE,
    p_money IN card.card_account%TYPE)
IS
    p_user_id1 card.user_id%TYPE;
    p_user_name1 card.user_name%TYPE;
    p_card_lost1 card.card_lost%TYPE;
    p_user_id2 card.user_id%TYPE;
    p_user_name2 card.user_name%TYPE;
    p_card_lost2 card.card_lost%TYPE;
    p_change_id change.change_id%TYPE;
    p_card_account card.card_account%TYPE;
  BEGIN
  SELECT user_id,user_name,card_account,card_lost
  INTO p_user_id1,p_user_name1,p_card_account,p_card_lost1
  FROM card
  WHERE card_id=p_card_id1;
  SELECT user_id,user_name,card_lost
  INTO p_user_id2,p_user_name2,p_card_lost2
  FROM card
  WHERE card_id=p_card_id2;
  IF p_card_lost1='挂失' THEN
    DBMS_OUTPUT.PUT_LINE('转账失败，'||p_card_id1||'已冻结！');
  ELSIF p_card_lost2='挂失' THEN
    DBMS_OUTPUT.PUT_LINE('转账失败，'||p_card_id2||'已冻结！');
  ELSE
    BEGIN
    IF p_card_account<p_money THEN
      DBMS_OUTPUT.PUT_LINE('转账失败，校园卡余额不足！');
      ELSE
      BEGIN
      UPDATE card SET card_account=card_account-p_money
      WHERE card_id=p_card_id1;
      UPDATE card SET card_account=card_account+p_money
      WHERE card_id=p_card_id2;
      SELECT change_seq.nextval
      INTO p_change_id
      FROM dual;
      INSERT INTO change VALUES(p_change_id,p_user_id1,'转账',p_money,SYSDATE);
      DBMS_OUTPUT.PUT_LINE('账户：'||p_user_id1||p_user_name1||' 向账户：'||p_user_id2||p_user_name2||' 转账金额'||p_money||'元，交易编号：'||p_change_id);
      END;
    END IF;
  END;
END IF;
END;
/

--挂失pl
CREATE OR REPLACE PROCEDURE gs_sql
  (p_user_id IN card.user_id%TYPE)
IS
    p_card_id card.card_id%TYPE;
    p_user_name card.user_name%TYPE;
    p_card_account card.card_account%TYPE;
  BEGIN
  SELECT card_id,user_name,card_account
  INTO p_card_id,p_user_name,p_card_account
  FROM card
  --出现的问题，and后面的条件要用括号括起来
  WHERE user_id=p_user_id AND (card_lost is null);
  UPDATE card SET card_lost='挂失',card_account=0
  WHERE card_id=p_card_id;
  SELECT card_seq.nextval
  INTO p_card_id
  FROM dual;
  INSERT INTO card VALUES(p_card_id,p_user_id,p_user_name,p_card_account,null);
  DBMS_OUTPUT.PUT_LINE(p_user_id||'已成功办理挂失业务，您的新卡号：'||p_card_id);
END;
/

--商铺消费pl
CREATE OR REPLACE PROCEDURE sp_sql
  ( p_card_id IN card.card_id%TYPE,
  	p_terminal_id IN terminal.terminal_id%TYPE,
  	p_goods_id IN goods.goods_id%TYPE,
    p_num IN INT
    )
IS
    p_user_id card.user_id%TYPE;
    p_user_name card.user_name%TYPE;
    p_card_account card.card_account%TYPE;
    p_card_lost card.card_lost%TYPE;
    p_goods_name goods.goods_name%TYPE;
    p_goods_money goods.goods_money%TYPE;
    p_remain FLOAT;
    p_money FLOAT;
    p_terminal_name terminal.terminal_name%TYPE;
    p_terminal_area terminal.terminal_area%TYPE;
    p_consume_id consume.consume_id%TYPE;
  BEGIN
  SELECT user_id,user_name,card_account,card_lost
  INTO p_user_id,p_user_name,p_card_account,p_card_lost
  FROM card
  WHERE card_id=p_card_id;
  IF p_card_lost='挂失' THEN
     DBMS_OUTPUT.PUT_LINE('无法消费，校园卡已冻结！');
  ELSE
    BEGIN
    SELECT goods_name,goods_money
    INTO p_goods_name,p_goods_money
    FROM goods
    WHERE goods_id=p_goods_id;
    p_money:=p_goods_money*p_num;
    IF p_card_account<p_money THEN
      DBMS_OUTPUT.PUT_LINE('校园卡余额不足！');
    ELSE
    BEGIN
      p_remain:=p_card_account-p_money;
      UPDATE card SET card_account=p_remain
      WHERE card_id=p_card_id;
      SELECT consume_seq.nextval
      INTO p_consume_id
      FROM dual;
      INSERT INTO consume VALUES(p_consume_id,p_terminal_id,p_user_id,p_money,SYSDATE);
      SELECT terminal_name,terminal_area
      INTO p_terminal_name,p_terminal_area
      FROM terminal
      WHERE terminal_id=p_terminal_id;
      DBMS_OUTPUT.PUT_LINE(p_user_name||' 学号：'||p_user_id||' 在'||p_terminal_area||p_terminal_name||' 购买 '||p_goods_name||p_num||'个，消费'||p_money||'元，余额'||p_remain||'元，时间'||SYSDATE||'订单编号'||p_consume_id);
      END;
      END IF;
    END;
  END IF;
END;
/

--食堂消费pl
CREATE OR REPLACE PROCEDURE st_sql
  ( p_card_id IN card.card_id%TYPE,
  	p_terminal_id IN terminal.terminal_id%TYPE,
  	p_money IN FLOAT
    )
IS
    p_user_id card.user_id%TYPE;
    p_user_name card.user_name%TYPE;
    p_card_account card.card_account%TYPE;
    p_card_lost card.card_lost%TYPE;
    p_remain FLOAT;
    p_terminal_name terminal.terminal_name%TYPE;
    p_terminal_area terminal.terminal_area%TYPE;
    p_consume_id consume.consume_id%TYPE;
  BEGIN
  SELECT user_id,user_name,card_account,card_lost
  INTO p_user_id,p_user_name,p_card_account,p_card_lost
  FROM card
  WHERE card_id=p_card_id;
  IF p_card_lost='挂失' THEN
     DBMS_OUTPUT.PUT_LINE('无法消费，校园卡已冻结！');
  ELSE
    BEGIN
    IF p_card_account<p_money THEN
      DBMS_OUTPUT.PUT_LINE('校园卡余额不足！');
    ELSE
    BEGIN
      p_remain:=p_card_account-p_money;
      UPDATE card SET card_account=p_remain
      WHERE card_id=p_card_id;
      SELECT consume_seq.nextval
      INTO p_consume_id
      FROM dual;
      INSERT INTO consume VALUES(p_consume_id,p_terminal_id,p_user_id,p_money,SYSDATE);
      SELECT terminal_name,terminal_area
      INTO p_terminal_name,p_terminal_area
      FROM terminal
      WHERE terminal_id=p_terminal_id;
      DBMS_OUTPUT.PUT_LINE(p_user_name||' 学号：'||p_user_id||' 在'||p_terminal_area||p_terminal_name||' 消费'||p_money||'元，余额'||p_remain||'元，时间'||SYSDATE||'订单编号'||p_consume_id);
      END;
      END IF;
    END;
  END IF;
END;
/

--查询某一学生信息
CREATE OR REPLACE PROCEDURE user_sql
  (p_user_id IN card.user_id%TYPE) 
  IS
    p_card_id card.card_id%TYPE;
    p_user_name card.user_name%TYPE;
    p_card_account card.card_account%TYPE;
BEGIN
  SELECT card_id,user_name,card_account
  INTO p_card_id,p_user_name,p_card_account
  FROM card
  WHERE user_id=p_user_id AND (card_lost is null);
  dbms_output.put_line(p_card_id||' '||p_user_id||' '||p_user_name||' '||p_card_account);
exception
  WHEN no_data_found THEN
    dbms_output.put_line('then user no exists');
END;
/

--查询某个学生的所有账单
create or replace procedure user_order
  (p_user_id IN card.user_id%TYPE,
    data out sys_refcursor) 
  is
begin
  open data for select * from(
  select consume_money as 金额,
  '消费' as 类型,
  (select terminal_name from terminal where terminal_id=c.terminal_id) as 地点,
  to_char(consume_time,'YYYY-MM-DD HH24:MI:SS')时间 
  from consume c 
  where user_id=p_user_id 
union 
select change_money as 金额,
change_type as 类型,
null as 地点,
to_char(change_time,'YYYY-MM-DD HH24:MI:SS')时间 
from change 
where user_id=p_user_id) order by 时间;
end;
/
VARIABLE data refcursor;

--创建视图
create or replace view v_rank as
select t.terminal_name as 消费终端,t.terminal_area as 所在校区,sum(consume_money) as 累计消费
from consume c inner join terminal t on c.terminal_id = t.terminal_id 
group by t.terminal_name,t.terminal_area order by 累计消费 desc;

--操作pl程序
EXECUTE cx_sql('10001',200);
EXECUTE sp_sql('10004','100','1',2);
EXECUTE st_sql('10001','101',13.8);
EXECUTE sp_sql('10001','100','4',1);
EXECUTE zz_sql('10004','10001',20);
EXECUTE st_sql('10002','104',34);
EXECUTE cx_sql('10004',250);
EXECUTE st_sql('10001','102',6);
EXECUTE sp_sql('10001','100','6',4);
EXECUTE sp_sql('10002','103','7',1);
EXECUTE gs_sql('1704011011');
EXECUTE sp_sql('10004','103','2',1);
EXECUTE sp_sql('10001','103','4',2);
EXECUTE cx_sql('10005',50);
EXECUTE st_sql('10002','102',16);
EXECUTE tk_sql('10005');
EXECUTE st_sql('10005','101',11);
EXECUTE user_sql('1704011011');
EXECUTE user_order('1704011011',:data)
PRINT data;

select * from card;
select * from terminal;
select * from goods;
select * from consume;
select * from change;
select * from v_rank;

Oracle校园卡消费系统（文档+代码）

1 系统分析

2 数据库设计与实现

2.1 概念结构设计

2.3 物理结构设计

3 数据库实施

4 其它说明

Oracle校园卡消费系统（文档+代码）

Linux系统中安装Oracle 11g的安装文档

Oracle VM VirtualBox 在linux系统下安装增强插件实现访问主机的共享文档方法

输入的word时间日期代码是从系统中调用的,在打开该Word文档时自动更新时间

公司加密系统之源代码加密防拷贝方案、文档透明加密与环境加密的区别

Oracle VM VirtualBox 在linux系统下安装增强插件实现访问主机的共享文档方法

Linux系统中安装Oracle 11g的安装文档

公司加密系统之源代码加密防拷贝方案、文档透明加密与环境加密的区别

直接用php创建word文档代码(系统无需安装word软件)

输入的word时间日期代码是从系统中调用的,在打开该Word文档时自动更新时间