(数据库课程设计)学生宿舍管理系统毕业设计论文(6)

来源：56doc.com 资料编号：5D4019 资料等级：★★★★★ %E8%B5%84%E6%96%99%E7%BC%96%E5%8F%B7%EF%BC%9A5D4019

资料以网页介绍的为准,下载后不会有水印.资料仅供学习参考之用. 密保惠帮助

资料介绍

ACDate datetime  否 Not null
SGName char(15)  是 Not null

附录2 存储过程定义
1.p1_Worker_Insert的定义:
CREATE PROCEDURE p1_Worker_Insert
@worker_no char(5),
@worker_name char(10)，
  @work_type char(8),
@work_wage int,
@worker_sex char(2),
@work_time char(30),
@dormitory_no smallint,
@dormitory_campus char(4),
@dormitory_location char(4)
as
insert into Worker
values(@worker_no, @worker_name, @work_type, @work_wage, @worker_sex,
@work_time, @work_time, @dormitory_no,@dormitory_campus,
@dormitory_location);
2.p2_Dormitory_Insert的定义:
create procedure p2_Dormitory_Insert
@dormitory_no smallint,
@dormitory_campus char(4),
@dormitory_location char(4),
@dormitory_phone char(12),
@dormitory_administer char(10)
as
insert into Dormitory
values(@dormitory_no, @dormitory_campus, @dormitory_location,
@dormitory_phone, @dormitory_administer);
3.p3_Room_Insert的定义:
create procedure p3_Room_Insert
@room_no char(6),
@room_header char(10),
@roomer_grade char(4),
@roomer_department char(20),
@roomer_perfection char(20),
@dormitory_no smallint,
@dormitory_campus char(4),
@dormitory_location char(4)
as
insert into Room
values(@room_no, @room_header, @roomer_grade, @roomer_department,
@roomer_perfection, @dormitory_no, @dormitory_campus,
     @dormitory_location);
4.p4_Fitment_Inser的定义:
create procedure p4_Fitment_Insert
@fitment_name char(16),
@fitment_price float,
@fitment_number int,
@dormitory_no smallint,
@dormitory_campus char(4),
@dormitory_location char(4)
as
insert to Fitment
values(@fitment_name, @fitment_price, @fitment_number, @dormitory_no,
   @dormitory_campus, @dormitory_location);
5.p5_Student_Insert的定义:
create procedure p5_Student_Insert
@student_no char(9),
@department_name char(20),
@student_name char(10),
@student_sex char(2),
@student_home char(10),
@student_borth datetime,
@student_enter_time datetime,
@student_perfection char(20),
@student_class int,
@room_no char(6),
@dormitory_no smallint,
@dormitory_campus char(4),
@dormitory_location char(4)
  as
  insert into Student
values(@student_no, @department_name, @student_name, @student_sex
  , @student_home, @student_borth, @student_enter_time, @student_perfection,
  @student_class, @room_no, @dormitory_no, @dormitory_campus,
  @dormitory_location);
6.p6_SafeGuard_Insert的定义:
create procedure p6_SafeGuard_Insert
@safeguard_name char(15),
@safeguard_worker_num int,
@safeguard_header char(10),
@safeguard_phone char(12)
as
insert into SGName
values(@safeguard_name, @safeguard_worker_num, @safeguard_header,
   @safeguard_phone);
7.p7_Artical_In_Out_Insert的定义:
create procedure p7_Artical_In_Out_InSert
@student_no char(9),
@aio_artical char(16),
@aio_principal char(9),
@aio_date datetime,
@aio_no int,
@dormitory_no smallint,
@dormitory_campus char(4),
@dormitory_location char(4)
as
insert into AIOArtical
values(@student_no, @aio_artical, @aio_principal, @aio_date,
   @dormitory_no, @dormitory_campus, @dormitory_location);
8.p8_FitmentDestruction_Insert: 的定义
create procedure p8_FitmentDestruction_Insert
@fitment_name char(16),
@student_no char(9),
@room_no char(6),
@fdf_num int,
@dormitory_no smallint,
@dormitory_campus char(4),
@dormitory_location char(4)
as
insert to FitmentDestruction
values(@fitment_name, @student_no, @room_no, @fdf_num, @dormitory_no,
   @dormitory_campus, @dormitory_location);
9.p9_FitmentCompensate的定义:
create procedure p9_FitmentCompensate
@fitment_name char(16),
@stu_no char(9),
@fc_principal char(10),
@fc_date datetime,
@fc_num int,
as
insert FitmentDestruction
values(@fitment_name, @stu_no, @fc_principal, @fc_date, @fc_num);
10.p11_Accident_Insert的定义:
create procedure p10_Accident_Insert
@ac_no int,
@ac_type char(10),
@stu_no char(30),
@ac_date datetime,
@ac_artical char(30),
@ac_verify bool,
@sg_name char(15),
@ac_ar_num int,
@ac_stu_phone char(12)
as
insert into Accident
values(@ac_no, @ac_type, @stu_no, @ac_date, @ac_artical, @ac_verify,
   @sg_name, @ac_ar_num, @ac_stu_phone);
11.p11_AccidentResearch_Insert的定义:
create procedure p11_AccidentResearch_Insert
@ac_no int,
@ar_name char(15),
@sg_name char(15),
@ar_result bool
as
insert into AccidentResearch
values(@ac_no, @ar_name, @sg_name, @ar_result);
12.p12_AccidentCompensate_Insert的定义:
create procedure p12_AccidentCompensate_Insert
@ac_no int,
@ac_stu char(10),
@ac_artical char(30),
@ac_date datetime,
@sg_name char(15)
as
insert into AccidentCompensate
values(@ac_no, @ac_stu, @ac_artical, @ac_date, @sg_name);
13.p13_Query_Worker的定义:
create procedure p13_Query_Worker
  @worker_no char(5)
  as
  select *
  from WorView
  where 编号 = ltrim(@worker_no);
14.p14_Query_Worker的定义:
create procedure p14_Query_Worker
  @worker_name char(10)
  as
  select *
  from WorView
  where 姓名 like ltrim(@worker_name)+’%’;
15.p15_Delete_Worker的定义：
create procedure p15_Delete_Worker
  @worker_no char(5)
  as
  delete
  from WorView
  where 编号 = rtrim(ltrim(@worker_no));
16.p16_Delete_Worker的定义：
create procedure p16_Delete_Worker
@worker_name char(10)
  as
  delete
  from WorView
  where 姓名 like rtrim(ltrim(@worker_name));

附录3 数据查看和存储过程功能的验证
1.基本表的数据查看(基于视图查询)：
1) 查看Room表中的数据：

2) 查看Fitment表中的数据：

3) 查看Student表中的数据：

4) 查看SafeGuard表中的数据：

5) 查看ArticalInOut表中的数据：

6) 查看FitmentDestruction表中的数据：

7) 查看FitmentCompensate表的数据：

8) 查看Accident表中的数据：

9) 查看AccidentResearch表中的数据：

10) 查看AccidentCompensate表中的数据：

2.存储过程功能的验证：
1) 存储过程p2_Dormitory_Insert功能的验证：

2) 存储过程p3_Room_Insert功能的验证：

3) 存储过程p4_Fitment_Insert功能的验证：

(注：由于篇幅限制，这里仅给出了其中几个存储过程功能的验证)

附录4 所有的SQL运行语句
create database Student_Dormitory_Management;

create table Dormitory(
  DorNo smallint   not null,
  DorCampus char(4) not null,
  DorLocation char(4) not null,
  DorPhNo char(12)      null,
  DorAdminist char(10)     not null,
  primary key(DorNo,DorCampus,DorLocation),
  check(DorNo>0 and DorNo<100));

create table Worker(
  WorNo char(5)      not null unique,
  WorName char(10) not null,
  WorType char(8)    not null,
  WorWage int         not null,
  WorSex char(2)     not null,
  WorPhNo char(12)    null,
  WorTime char(30)   null,
  DorNo smallint   not null,
  DorCampus char(4) not null,
  DorLocation char(4)   not null,
  primary key(WorNo),
  foreign key(DorNo,DorCampus,DorLocation) references
Dormitory(DorNo,DorCampus,DorLocation),
  check(WorWage >= 0),
  check(WorSex = '男' or WorSex = '女'));

create table Room(
  RNo char(6)    not null unique,
  RHeader char(10)   null,
  RGrade char(4)  not null,