网吧管理系统数据库设计

摘  要：网吧管理系统是为解决大型网吧面临的复杂电脑管理业务流程和繁琐的客户服务、信息处理业务而开发的管理系统。它主要包含系统管理、会员管理、日常管理、查询管理、统计报表五个模块。数据库设计过程中主要进行了分析网吧电脑使用业务方面的需求，进行概念结构、逻辑结构和物理结构的设计，并通过建立视图、存储过程和触发器实现了满足不同用户的不同需求，如针对会员的上机和服务，针对管理员的账单查看和收费时间计划管理以及针对不同等级的会员的不同服务。实现了较为自动化、人性化的网吧管理、会员服务、信息管理的管理功能。

1、需求分析

本系统主要任务是实现网吧信息管理系统。系统分为以下五个功能模块：系统管理、会员管理、日常管理、查询管理、统计报表。具体功能可以从以下五个方面介绍：

1. 系统管理：

1. 设置管理员的基本信息，对管理员的基本信息进行操作。
2. 设置电脑的基本信息，对电脑的基本信息进行操作。
3. 对不同时间短的收费情况进行设置。
4. 不同计算机，不同用户的收费进行设置。

1. 会员管理：

1. 添加新注册的会员，并初始化会员的详细信息。
2. 注销以往注册的会员，并删除该会员的所有基本信息。
3. 修改会员的基本信息，用于修改用户的个人信息。
4. 会员充值，会员对自己的账户进行充值，方便后续使用电脑。

1. 日常管理：

1. 开机管理：用户上机后，将用户的信息和电脑的信息存入上机情况表中。
2. 限时管理：对用户的上机情况进行限制，根据会员的账户余额和网吧要关闭的时间进行限制。
3. 延时重启设置：用户根据自己的时间安排先占用计算机，但是不会收费，到需要的时间自动打开计算机。
4. 换机机制：用户根据自己的需求更换电脑。
5. 消息机制：会员可以使用该功能和管理员进行联系，提出自己想要管理员提供的服务。

1. 查询管理：

1. 账号查询：对会员的基本信息进行查询。
2. 账单查询：对会员的所有消费账单进行查询。
3. 充值明细：对会员的充值记录和消费记录进行查询。
4. 上机统计：统计用户的上机情况和所用电脑的使用记录。

1. 统计报表：

1. 管理员统计：按照不同条件对管理员的数据进行统计。
2. 会员统计：按照不同的条件对会员数据进行统计。
3. 收费报表：查询所有用户的充值情况和消费情况

数据流程图

数据字典 

1.数据项

表6-1 数据项

2.数据结构

表6-2 数据结构

3.数据流

表6-3 数据流

4.处理逻辑

表6-4 处理逻辑

5.数据存储

表6-5 数据存储

E-R图

 关系模式

会员（会员账号，会员身份证号，会员年龄，会员密码，会员余额，会员电话号，会员QQ，会员微信，会员职业，会员性别，会员地址，会员等级）

电脑（电脑编号，电脑显卡，电脑CPU，电脑显示器，电脑键鼠，电脑收费等级）

管理员（管理员账号，管理员身份证号，管理员年龄，管理员密码，管理员电话号，管理员QQ，管理员微信，管理员职业，管理员性别，管理员地址，管理员权限）

时段表（时段编号，收费时间段，收费金额）

费率表（费率编号，电脑编号，会员账号，管理员账号，电脑收费等级）

上机情况表（上机情况编号，电脑编号，会员账号，管理员账号，用户上机时间，会员下机时间，会员上机时长，电脑收费等级，收费时间段，电脑状态，延长时间，换机机号，重启，网络状态，延时时刻）

使用记录表（使用记录编号，电脑编号，会员账号，管理员账号，用户上机时间，会员下机时间，会员上机时长，电脑收费等级，电脑收费时间段，电脑状态）

管理员工作表（工作编号，管理员账号，管理员姓名，工作表编号，管理员电话，上班时间，下班时间，工作时长）

维修记录表（维修编号，电脑编号，管理员账号，维修费用，维修内容）

账单表（账单编号，会员上机时间，会员下机时间，会员上机时长，会员消费，会员其他消费，用户账号，管理员账号，电脑编号，会员姓名，管理员姓名）

充值表（充值编号，会员账户，管理员账户，充值金额，充值时间，会员姓名，管理员姓名）

消息表（消息编号，会员账户，管理员账户，用户姓名，管理员姓名，会员消息，管理员消息，会员等级）文章来源地址https://www.toymoban.com/news/detail-480977.html

数据库实施

1. 用户表

create table User0
 (  
UserAccount         varchar2(10) not null,  
UserID       varchar2(10) not null,  
UserAge     int,  
UserName      varchar2(40),  
UserPassword            varchar2(10),  
UserBalance          float,  
UserPhone             varchar2(10),  
UserQQ          varchar2(10),  
UserWechat        varchar2(10),  
UserProfession    varchar2(30),  
UserSex           varchar2(10),  
UserAddress       varchar2(40),  
UserGrade         varchar2(20),  

constraint PK_UserAccount  primary key (UserAccount),  
constraint CK_UserAge check (UserAge>=0 and UserAge <= 100),  
constraint CK_UserBalance check (UserBalance>=0),
constraint CK_UserGrade check (UserGrade = '钻石' or UserGrade = '金卡' or UserGrade = '银卡' or UserGrade = '普通'), 
constraint CK_UserSex check (UserSex = '男' or  UserSex = '女')
);

2. 管理员表

create table Administrator
 (  
AdministratorAccount         varchar2(10) not null,  
AdministratorID       varchar2(10) not null,  
AdministratorAge     int,  
AdministratorName      varchar2(40),  
AdministratorPassword            varchar2(10),  
AdministratorQQ          varchar2(10),  
AdministratorWechat             varchar2(10),  
AdministratorProfession          varchar2(10),  
AdministratorPhone        varchar2(10),  
AdministratorSex           varchar2(10),  
AdministratorAddress       varchar2(40),  
AdministratorRight         varchar2(20),  

constraint PK_AdministratorAccount  primary key (AdministratorAccount),  
constraint CK_AdministratorAge check (AdministratorAge>=0 and AdministratorAge <= 100),  
constraint CK_AdministratorRight check (AdministratorRight = '普通' or AdministratorRight = '高级'), 
constraint CK_AdministratorSex check (AdministratorSex = '男' or  AdministratorSex = '女')
);

3. 电脑表

create table Computer
 (  
PC_ID         varchar2(10) not null,  
PC_Gpu       varchar2(40) ,  
PC_CPU     varchar2(40),  
PC_Display      varchar2(40),  
PC_Keyboard            varchar2(40),  
PC_charge_grade          int,  

constraint PK_PC_ID  primary key (PC_ID),  
constraint CK_PC_charge_grade check (PC_charge_grade = 1 or  PC_charge_grade =  2  or  PC_charge_grade =  3)
);

4. 时段表

create table Time_Frame
 (  
Time_Frame_ID         varchar2(10) not null,  
T_start       date,  
T_end        date,  
Charge_amount       float,  

constraint PK_Time_Frame_ID  primary key (Time_Frame_ID),  
constraint CK_Charge_amount check (Charge_amount >0)
);

5. 费率表

create table Charge_List
 (  
Rate_ID         varchar2(10) not null,  
PC_ID       varchar2(10),  
UserAccount        varchar2(10),  
AdministratorAccount       varchar2(10),  
PC_charge_grade       int ,

constraint PK_Rate_ID  primary key (Rate_ID),  
constraint FK_PC_ID foreign key (PC_ID)   references Computer(PC_ID),
constraint FK_UserAccount foreign key (UserAccount)   references User0(UserAccount),
constraint FK_AdministratorAccount foreign key (AdministratorAccount)   references Administrator(AdministratorAccount),
constraint CK_PC_charge_grade1 check (PC_charge_grade = 1 or  PC_charge_grade =  2  or  PC_charge_grade =  3)
);

6. 上机情况表

create table Up_Computer_List
 (  
Up_computer_ID         varchar2(10) not null,  
PC_ID       varchar2(10),  
UserAccount        varchar2(10),  
AdministratorAccount       varchar2(10),  
UserUP_time       date ,
UserDown_time      date,  
UserDuration        float,  
PC_charge_grade      int,  
T_start              date,  
T_end                  date,  
PC_state                varchar2(10),  
Extend_time_length       float,  
Extend_PC_ID              varchar2(10),  
Restart                    varchar2(10),  
Network_State               varchar2(10),  
Extend_time                 date,  
Charge_amount                 float,  

primary key (Up_computer_ID),  
foreign key (PC_ID)   references Computer(PC_ID),
foreign key (UserAccount)   references User0(UserAccount),
foreign key (AdministratorAccount)   references Administrator(AdministratorAccount),
foreign key (Extend_PC_ID)   references Computer(PC_ID),
check (PC_charge_grade = 1 or  PC_charge_grade =  2  or  PC_charge_grade =  3),
check(UserDuration>=0),
check(Extend_time_length>=0),
check(Charge_amount>=0),
check(PC_state='正常'or PC_state= '维修'),
check(Restart='是'or Restart= '否'),
check(Network_State='是'or Network_State= '否')
);

7. 使用记录表

create table Use_Computer_List
 (  
Use_Computer_ID         varchar2(10) not null,  
PC_ID       varchar2(10),  
UserAccount        varchar2(10),  
AdministratorAccount       varchar2(10),  
UserUP_time       date ,
UserDown_time      date,  
UserDuration        float,  
PC_charge_grade      int,  
T_start              date,  
T_end                  date,  
PC_state                varchar2(10),  
Charge_amount                 float,  

primary key (Use_Computer_ID),  
foreign key (PC_ID)   references Computer(PC_ID),
foreign key (UserAccount)   references User0(UserAccount),
foreign key (AdministratorAccount)   references Administrator(AdministratorAccount),
check (PC_charge_grade = 1 or  PC_charge_grade =  2  or  PC_charge_grade =  3),
check(UserDuration>=0),
check(Charge_amount>=0),
check(PC_state='正常'or PC_state= '维修')
);

8. 管理员工作表

create table Administrator_Work_List
 (  
Administrator_work_ID         varchar2(10) not null,  
AdministratorAccount       varchar2(10),  
Administrator_Name     varchar2(40), 
AdministratorPhone      varchar2(10), 
AdministratorUp_time       date ,
AdministratorDown_time      date,  
AdministrationDuration        float,  

primary key (Administrator_work_ID),  
foreign key (AdministratorAccount)   references Administrator(AdministratorAccount),
check(AdministrationDuration>=0)
);

9. 维修记录表

create table Maintain_List
 (  
PC_maintain_ID         varchar2(10) not null,  
PC_ID       varchar2(10),  
AdministratorAccount     varchar2(10), 
Maintain_Content      varchar2(80), 
PC_maintain_Money        float,  

primary key (PC_maintain_ID),  
foreign key (AdministratorAccount)   references Administrator(AdministratorAccount),
foreign key (PC_ID)   references Computer(PC_ID),
check(PC_maintain_Money>=0)
);

10. 账单表

create table Bill_List
 (  
Bill_ID         varchar2(10) not null,  
PC_ID       varchar2(10),  
UserAccount       varchar2(10),  
AdministratorAccount     varchar2(10), 
User_name                varchar2(40), 
Administrator_Name       varchar2(40), 
UserUP_time              date,
UserDown_time            date,
UserDuration             float,
User_consume             float,
User_other_consume       float,

primary key (Bill_ID),  
foreign key (AdministratorAccount)   references Administrator(AdministratorAccount),
foreign key (UserAccount)   references User0(UserAccount),
foreign key (PC_ID)   references Computer(PC_ID),
check(UserDuration>=0),
check(User_consume>=0),
check(User_other_consume>=0)
);

11. 充值表

create table User_recharge_List
 (  
Recharge_ID         varchar2(10) not null,  
UserAccount       varchar2(10),  
AdministratorAccount     varchar2(10), 
User_name                varchar2(40), 
Administrator_Name       varchar2(40), 
User_Recharge_time              date,
User_recharge_amount             float,

primary key (Recharge_ID),  
foreign key (AdministratorAccount)   references Administrator(AdministratorAccount),
foreign key (UserAccount)   references User0(UserAccount),
check(User_recharge_amount>=0)
);

12. 信息表

create table Maeeage_List
(  
Message_ID         varchar2(10) not null,  
UserAccount       varchar2(10),  
AdministratorAccount     varchar2(10), 
User_name                varchar2(40), 
Administrator_Name       varchar2(40), 
User_massage             varchar2(50),
UserGrade       varchar2(20),  
Administrator_massage    varchar2(50),

primary key (Message_ID),  
foreign key (AdministratorAccount)   references Administrator(AdministratorAccount),
foreign key (UserAccount)   references User0(UserAccount),
check (UserGrade = '钻石' or UserGrade = '金卡' or UserGrade = '银卡' or UserGrade = '普通')
);

存储过程

1. 结算账单

create or replace procedure PRO_BILL(  
    UserAccount_n      in  bill_list.useraccount%type,  
    User_other_consume_n       in   bill_list.user_other_consume%type
    ) is  
      
    Bill_ID_n         varchar2(10);
    PC_ID_n       bill_list.pc_id%type;  
    AdministratorAccount_n      bill_list.administratoraccount%type; 
    User_name_n                 bill_list.user_name%type;
    Administrator_Name_n        bill_list.administrator_name%type; 
    UserUP_time_n               bill_list.userup_time%type;
    UserDown_time_n             bill_list.userdown_time%type;
    UserDuration_n              bill_list.userduration%type;
    User_consume_n             bill_list.user_consume%type;
    userbalance_n              user0.userbalance%type;
  
begin    
       --获取会员余额信息
       select User0.Userbalance 
       into userbalance_n
       from User0
       where User0.Useraccount = UserAccount_n;
       
       --获取账单信息
       select PC_ID,AdministratorAccount,User_name,Administrator_Name,UserUP_time,UserDown_time,UserDuration,User_consume
	   into PC_ID_n,AdministratorAccount_n,User_name_n,Administrator_Name_n,UserUP_time_n,UserDown_time_n,UserDuration_n,User_consume_n
	   from up_computer_list
	   where up_computer_list.useraccount = UserAccount_n;
	   
       --设置账单编号
	   Bill_ID_n :=  to_char(useraccount_n+UserUP_time_n);
 
        --添加账单信息  
        dbms_output.put_line(
             '这是您的账单：'||to_char(User_consume_n+User_other_consume_n));  
        insert into Bill_list  
        values(
            Bill_ID_n,
            PC_ID_n,
            UserAccount_n,
            AdministratorAccount_n,
            User_name_n,
            Administrator_Name_n,
            UserUP_time_n ,
            UserDown_time_n,
            UserDuration_n ,
            User_consume_n,
            User_other_consume_n
        );  
        commit;
          
        --修改会员的余额信息
        update user0 set user0.userbalance = userbalance_n - User_consume_n - User_other_consume_n
		where User0.Useraccount = UserAccount_n;
        dbms_output.put_line('账单已支付，欢迎下次光临！！！');  
    commit;  
end PRO_BILL;

2. 会员上机

create or replace procedure PRO_UP_PC(  
    PC_ID_n    in   up_computer_list.pc_id%type,  
    UserAccount_n    in    up_computer_list.useraccount%type,  
    AdministratorAccount_n    in   up_computer_list.administratoraccount%type,  
    UserUP_time_n    in   up_computer_list.userup_time%type ,
	Extend_time_length_n   in    up_computer_list.extend_time_length%type,  
    Extend_PC_ID_n          in   up_computer_list.extend_pc_id%type,  
    Restart_n       in         up_computer_list.restart%type,  
    Network_State_n       in       up_computer_list.network_state%type,  
    Extend_time_n        in         up_computer_list.extend_time%type, 
    ) is  
      
    Up_computer_ID_n         up_computer_list.pc_id%type;
	PC_state_n               up_computer_list.pc_id%type;
    User_consume_n                 up_computer_list.pc_id%type;
	UserGrade_n                    user0.usergrade%type;
	discount float;
	UserDown_time_n   date;
    UserDuration_n        float;
    PC_charge_grade_n      int;
    T_start_n              date;
    T_end_n                  date;
    Charge_amount_n      float;
  
begin    
       --获取电脑状态
	    select computer.pc_state
       into PC_state_n
       from computer
       where computer.pc_id = PC_ID_n;
	   --修改电脑使用状态
	   update computer set computer.pc_state_1 = 1
	   where computer.pc_id = PC_ID_n;
	    commit;
	   --设置上机编号
	   Up_computer_ID_n  :=  to_char(UserUP_time_n + useraccount_n);
       
       --获取上机信息
       select PC_ID,AdministratorAccount,User_name,Administrator_Name,UserUP_time,UserDown_time,UserDuration_n,User_consume_n
	   into PC_ID_n,AdministratorAccount_n,User_name_n,Administrator_Name_n,UserUP_time_n,UserDown_time_n,UserDuration_n,User_consume_n
	   from up_computer_list
	   where up_computer_list.useraccount = UserAccount_n;
	   
       --设置上机编号
	   Bill_ID_n :=  to_char(useraccount+UserUP_time_n);
 
        --添加上机信息  
        dbms_output.put_line(
             '这是您的上机情况');  
        insert into up_computer_list  
        values(
            Up_computer_ID_n,
            PC_ID_n,
            UserAccount_n,
            AdministratorAccount_n,
			UserUP_time_n,
            UserDown_time_n,
			UserDuration_n,
			PC_charge_grade_n,
			T_start_n,
			T_end_n,
			PC_state_n,
			Extend_time_length_n,
			Extend_PC_ID_n,
			Restart_n,
			Network_State_n,
			Extend_time_n,
			Charge_amount_n
			User_consume_n,
        );  
        commit;
end PRO_UP_PC;

3. 会员下机

create or replace procedure PRO_DOWN_PC(  
    PC_ID_n    in   up_computer_list.pc_id%type,  
    UserAccount_n    in    up_computer_list.useraccount%type,  
    AdministratorAccount_n    in   up_computer_list.administratoraccount%type,  
    UserDown_time_n    in   up_computer_list.userup_time%type ,
	UserDuration_n in up_computer_list.userduration%type
    ) is  
      
    User_consume_n                 up_computer_list.pc_id%type;
    UserGrade_n                    user0.usergrade%type;
    discount                        float;
    PC_charge_grade_n      int;
    T_start_n              date;
    T_end_n                  date;
    Charge_amount_n      float;
	UserUp_time_n        date;
  
begin    
       --修改电脑使用状态
       update computer set computer.pc_state_1 = 0
       where computer.pc_id = PC_ID_n;
        commit;
       --获取用户等级
       select UserGrade into UserGrade_n from user0 
         where user0.useraccount = UserAccount_n;
        if cuslevel='钻石' then discount:=0.9;  
            elsif cuslevel='金卡' then discount:=0.93;  
            elsif cuslevel='银卡' then discount:=0.96;  
            else discount:=1;  
        end if;  
       --获取上机时间
	   select UserUp_time
	   into UserUp_time_n
	   from up_computer_list
	   where up_computer_list.useraccount =  UserAccount_n  and  up_computer_list.administratoraccount = AdministratorAccount_n  and  up_computer_list.pc_id =  PC_ID_n;
      
        --获取收费等级 
       select PC_charge_grade
	   into PC_charge_grade_n
	   from computer
	   where computer.PC_ID = PC_ID_n;
	   
	   --获取收费时间段
	   select Charge_amount, t_start ,t_end
	   into Charge_amount_n, T_start_n, T_end_n
	   from time_frame
	   where time_frame.t_start  <= UserUp_time_n and time_frame.t_end >= UserDown_time_n;
	   
	   --计算消费金额
	   User_consume_n :=  (Charge_amount_n*PC_charge_grade_n*discount*UserDuration_n);
 
        --修改上机信息  
        dbms_output.put_line(
             '这是您的下机情况');  
        update up_computer_list  set 
		up_computer_list.userdown_time =  UserDown_time_n,
		up_computer_list.userduration =  UserDuration_n,
		up_computer_list.user_consume = User_consume_n,
		up_computer_list.pc_charge_grade = PC_charge_grade_n,
		up_computer_list.t_start =  T_start_n,
		up_computer_list.t_end = T_end_n,
		up_computer_list.charge_amount = Charge_amount_n;  
        commit;
end PRO_DOWN_PC;

4. 工作处理

create or replace procedure PRO_WORK(  
    AdministratorAccount_n    in   Administrator.AdministratorAccount%type,  
	AdministratorUp_time_n       in administrator_work_list.administratorup_time%type ,
    AdministratorDown_time_n      in administrator_work_list.administratordown_time%type,  
    AdministrationDuration_n        in administrator_work_list.administrationduration%type, 
    ) is  
	
    Administrator_work_ID_n         varchar2(10);  
    Administrator_Name_n     varchar2(40);
    AdministratorPhone_n      varchar2(10);

begin    
       --获取管理员信息
       select AdministratorName, AdministratorPhone
       into Administrator_Name_n, AdministratorPhone_n
       from Administrator
       where Administrator.AdministratorAccount = AdministratorAccount_n;

       --设置工作编号
       Administrator_work_ID_n  :=  to_char(AdministratorUp_time_n + AdministratorAccount_n);

        --添加上机信息  
        dbms_output.put_line( '这是您的工作情况');  
        insert into administrator_work_list  
        values(
            Administrator_work_ID_n,
			AdministratorAccount_n,
			Administrator_Name_n,
			AdministratorPhone_n,
			AdministratorUp_time_n,
			AdministratorDown_time_n,
			AdministrationDuration_n
        );  
        commit;
end PRO_WORK;

5. 会员充值

create or replace procedure PRO_RECHARGE(  
    AdministratorAccount_n    in   Administrator.AdministratorAccount%type,  
	UserAccount_n     in  user0.useraccount%type,
    User_Recharge_time_n        in      User_Recharge_List.User_Recharge_Time%type,
    User_recharge_amount_n       in      user_recharge_list.user_recharge_amount%type,
    ) is  
	
    Recharge_ID_n         varchar2(10);
    User_name_n                varchar2(40);
    Administrator_Name_n       varchar2(40);
    
begin    
       --获取管理员信息
       select AdministratorName
       into Administrator_Name_n
       from Administrator
       where Administrator.AdministratorAccount = AdministratorAccount_n;

       --获取会员信息
       select UserName
       into User_name_n
       from user0
       where user0.useraccount = UserAccount_n;
	   
       --设置充值编号
       Recharge_ID_n  :=  to_char(UserAccount_n+ 'cz' + AdministratorAccount_n);

        --添加上机信息  
        dbms_output.put_line( '这是您的充值情况');  
        insert into User_Recharge_List  
        values(
            Recharge_ID_n,
			UserAccount_n,
			AdministratorAccount_n,
			User_name_n,
			Administrator_Name_n,
			User_Recharge_time_n,
			User_recharge_amount_n
        );  
        commit;
		
		--修改会员的余额信息
        update user0 set user0.userbalance = user0.userbalance_n + User_recharge_amount_n
		where User0.Useraccount = UserAccount_n;
        dbms_output.put_line('充值成功，欢迎下次充值！！！');  
    commit;  
end PRO_RECHARGE;

6. 维修电脑

create or replace procedure PRO_MAINATION(  
    AdministratorAccount_n    in   Administrator.AdministratorAccount%type,  
	PC_ID_n     in  computer.pc_id%type ,
    Maintain_Content_n      in   maintain_list.maintain_content%type,  
    PC_maintain_Money_n        in   maintain_list.pc_maintain_money%type, 
	Maintain_time_n            in  maintain_list.maintain_time%type
    ) is  
	
    PC_maintain_ID_n         varchar2(10);
	
begin    
       --设置维修编号
       PC_maintain_ID_n  :=  to_char('wx' + AdministratorAccount_n + Maintain_time_n);

        --添加维修信息  
        dbms_output.put_line( '这是您的工作情况');  
        insert into administrator_work_list  
        values(
            PC_maintain_ID_n,
            PC_ID_n,
            AdministratorAccount_n,
            Maintain_Content_n,
            PC_maintain_Money_n,
            Maintain_time_n
        );  
        commit;
end PRO_MAINATION;

7. 更新管理员

create or replace procedure PRO_ADMINISTRATER(  
    AdministratorAccount_n    in  administrator.administratoraccount%type, 
    AdministratorID_n       in  administrator.administratorid%type, 
    AdministratorAge_n     in  administrator.administratorage%type, 
    AdministratorName_n      in  administrator.administratorname%type, 
    AdministratorPassword_n     in  administrator.administratorpassword%type, 
    AdministratorQQ_n     in  administrator.administratorqq%type, 
    AdministratorWechat_n        in  administrator.administratorwechat%type, 
    AdministratorProfession_n      in  administrator.administratorprofession%type, 
    AdministratorPhone_n     in  administrator.administratorphone%type, 
    AdministratorSex_n      in  administrator.administratorsex%type, 
    AdministratorAddress_n    in  administrator.administratoraddress%type, 
	AdministratorRight_n     in  administrator.administratorright%type,
	is_change in int 
    ) is     
begin    
      --1 表示为插入，0 表示为修改
      if is_change = 1 then
	  insert into administrator  
        values(
                AdministratorAccount_n,
				AdministratorID_n,
				AdministratorAge_n,
				AdministratorName_n,
				AdministratorPassword_n,
				AdministratorQQ_n,
				AdministratorWechat_n,
				AdministratorProfession_n,
				AdministratorPhone_n,
				AdministratorSex_n,
				AdministratorAddress_n,
				AdministratorRight_n,
        );  
	  else
	   update administrator set 
	           AdministratorAccount = AdministratorAccount_n,
				AdministratorID = AdministratorID_n,
				AdministratorAge = AdministratorAge_n,
				AdministratorName = AdministratorName_n,
				AdministratorPassword = AdministratorPassword_n,
				AdministratorQQ = AdministratorQQ_n,
				AdministratorWechat = AdministratorWechat_n,
				AdministratorProfession = AdministratorProfession_n,
				AdministratorPhone = AdministratorPhone_n,
				AdministratorSex = AdministratorSex_n,
				AdministratorAddress = AdministratorAddress_n,
				AdministratorRight = AdministratorRight_n,
       where administrator.AdministratorAccount = AdministratorAccount_n;
	  end if;
    commit;  
end PRO_ADMINISTRATER;

8. 更新电脑

create or replace procedure PRO_COMPUTER(  
    PC_ID_n        in computer.pc_id%type,
	PC_Gpu_n       in computer.pc_gpu%type,
	PC_CPU_n    in computer.pc_cpu%type,
	PC_Display_n    in computer.pc_display%type,
	PC_Keyboard_n      in computer.pc_keyboard%type,
	PC_charge_grade_n     in computer.pc_charge_grade%type,
	PC_STATE_n      in computer.pc_state%type,
	is_change in int
    ) is   
	
	PC_STATE_1_n   int;
	  
begin    
      PC_STATE_1_n := 0;
      --1 表示为插入，0 表示为修改
      if is_change = 1 then
	  insert into computer  
        values(
                PC_ID_n,
				PC_Gpu_n,
				PC_CPU_n,
				PC_Display_n,
				PC_Keyboard_n,
				PC_charge_grade_n,
				PC_STATE_1_n,
				PC_STATE_n
        );  
	  else
	   update computer set 
	            PC_ID = PC_ID_n,
				PC_Gpu = PC_Gpu_n,
				PC_CPU = PC_CPU_n,
				PC_Display = PC_Display_n,
				PC_Keyboard = PC_Keyboard_n,
				PC_charge_grade = PC_charge_grade_n,
				PC_STATE_1 = PC_STATE_1_n,
				PC_STATE = PC_STATE_n
       where computer.pc_id = PC_ID_n;
	  end if;
    commit;  
end PRO_COMPUTER;

9. 更新会员

create or replace procedure PRO_USERS(  
	UserAccount_n      in   user0.useraccount%type,  
	UserID_n      in   user0.userid%type,
	UserAge_n     in   user0.userage%type,
	UserName_n      in   user0.username%type,
	UserPassword_n      in   user0.userpassword%type,   
	UserBalance_n    in   user0.userbalance%type,
	UserPhone_n         in   user0.userphone%type,
	UserQQ_n     in   user0.userqq%type,
	UserWechat_n    in   user0.userwechat%type,
	UserProfession_n  in   user0.userprofession%type,
	UserSex_n    in   user0.usersex%type,
	UserAddress_n    in   user0.useraddress%type,
	UserGrade_n   in   user0.usergrade%type,
	is_change in int 
    ) is     
begin    
      --1 表示为插入，0 表示为修改
      if is_change = 1 then
      insert into user0  
        values(
                UserAccount_n,
				UserID_n,       
				UserAge_n,     
				UserName_n,      
				UserPassword_n,         
				UserBalance_n,       
				UserPhone_n,           
				UserQQ_n,        
				UserWechat_n,    
				UserProfession_n,  
				UserSex_n,    
				UserAddress_n,    
				UserGrade_n   
        );  
      else
       update user0 set 
                UserAccount = UserAccount_n,
				UserID = UserID_n,       
				UserAge =     UserAge_n,     
				UserName =   UserName_n,         
				UserPassword = UserPassword_n, 
				UserBalance =   UserBalance_n,   
				UserPhone =  UserPhone_n,        
				UserQQ =   UserQQ_n, 
				UserWechat = UserWechat_n,
				UserProfession =   UserProfession_n, 
				UserSex = UserSex_n, 
				UserAddress = UserAddress_n, 
				UserGrade = UserGrade_n
       where user0.UserAccount = UserAccount_n;
      end if;
    commit;  
end PRO_USERS;

触发器

会员等级变换

CREATE OR REPLACE TRIGGER TRI_POINTS  
  after insert  
  on Bill_List  
  for each row  
declare  
  pragma   autonomous_transaction;
  Grage_n user0.usergrade%type;  
  comsume_n bill_list.user_consume%type;  
begin  
  --获取会员等级  
  select user0.usergrade into Grage_n 
  from user0  
      where user0.useraccount=:new.useraccount;  
	  
   --获取会员消费金额 
  select sum(bill_list.user_consume + bill_list.user_other_consume) into comsume_n 
  from bill_list  
  where bill_list.bill_id=:new.bill_id;  
    
  --计算等级   
  if comsume_n>600 and comsume_n<1400 then Grage_n:='银卡';  
      elsif comsume_n>1400 and comsume_n<2200 then Grage_n:='金卡';  
      elsif comsume_n>2200 then Grage_n:='钻石';  
      else Grage_n:='普通';  
  end if;  
    
  --更新会员等级  
  update user0   
      set user0.usergrade=Grage_n
      where useraccount =:new.useraccount;  
      commit;
end TRI_POINTS; 

余量不足提醒

CREATE OR REPLACE TRIGGER TRI_computer  
  after update  
  on computer  
  for each row  
declare  
  shu int;
begin  
  select count(computer.pc_id)
  into shu
  from  computer
  where computer.pc_state_1 = 0;
  if shu <5 then  
      dbms_output.put_line('当前空闲电脑仅有 '||to_char(shu)  || ' 台！！！'); 
  end if;  
end TRI_computer;

到了这里，关于网吧管理系统数据库设计的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！