数据库课程设计-学生公寓管理系统的设计与实现（包含相关数据）

use ApartmentSystem go /* String dormid, String apartmentid, String department, String grade, String iclass */ -- 创建分配寝室修改寝室的存储过程 create procedure allocateDorm @iapartmentid varchar(50), @idepartment varchar(50), @igrade varchar(50), @iclass varchar(50), @idormid varchar(50) --定义输入参数 as UPDATE t_dorminfo SET dorm_apartmentID = @iapartmentid , dorm_department= @idepartment , dorm_grade= @igrade , dorm_class= @iclass WHERE id = @idormid go --根据学生id查询寝室信息的的存储过程 create procedure getDormInfoByStuID @stu_iid varchar(50) --定义输入参数 as select t_dorminfo.id, t_dorminfo.dorm_apartmentID, t_dorminfo.dorm_department, t_dorminfo.dorm_grade, t_dorminfo.dorm_class, t_dorminfo.dorm_restnum from t_stuinfo, t_dorminfo where t_dorminfo.id = t_stuinfo.stu_dorm and t_stuinfo.id = @stu_iid go ------------------------------------------------------------------------------------- --插入学生大物件出入信息记录 create procedure insertGoodsInfo @stu_iname varchar(50), --定义输入参数 @stu_iid varchar(50), @apartmentid varchar(50), @dormID varchar(50), @department varchar(50), @grade varchar(50), @iclass varchar(50), @goodsName varchar(50), @goodsReason varchar(50), @insertDate datetime as insert into t_GoodsRegisterInfo (gri_stuName, gri_stuid, gri_apartmentID, gri_dormID, gri_department, gri_grade, gri_class, gri_goodsName, gri_reasons, gri_date) values (@stu_iname, @stu_iid, @apartmentid, @dormID, @department, @grade, @iclass, @goodsName, @goodsReason, @insertDate) go --插入外来人员进出信息记录 create procedure insertOtherComeInfo @i_name varchar(50), --定义输入参数 @i_phone varchar(50), @i_apartmentID varchar(50), @i_dormid varchar(50), @i_reasons varchar(50), @i_data datetime as insert into t_OtherRegisterInfo (ori_name, ori_phone, ori_apartmentID, ori_dormid, ori_reasons, ori_data ) values (@i_name, @i_phone, @i_apartmentID, @i_dormid, @i_reasons, @i_data ) go --返回时间点的学生货物登记信息 create procedure queryGoodsInfoByTimePoint @goodsDate varchar(50) --定义输入参数 as select gri_apartmentID, gri_stuName, gri_stuid, gri_department, gri_grade, gri_class, gri_dormID, gri_goodsName, gri_reasons from t_GoodsRegisterInfo where gri_date = @goodsDate go --返回时间段的学生货物登记信息 create procedure queryGoodsInfoByTimePart @goodsStartDate varchar(50), --定义输入参数 @goodsEndDate varchar(50) as select gri_apartmentID, gri_stuName, gri_stuid, gri_department, gri_grade, gri_class, gri_dormID, gri_goodsName, gri_reasons from t_GoodsRegisterInfo where gri_date between @goodsStartDate and @goodsEndDate go --返回时间点的外来人员登记信息 create procedure queryOtherInfoByTimePoint @otherDate varchar(50) --定义输入参数 as select ori_name, ori_phone, ori_apartmentID, ori_dormid, ori_reasons from t_OtherRegisterInfo where ori_data = @otherDate go --返回时间段的外来人员登记信息 create procedure queryOtherInfoByTimePart @otherStartDate varchar(50), --定义输入参数 @otherEndDate varchar(50) as select ori_name, ori_phone, ori_apartmentID, ori_dormid, ori_reasons from t_OtherRegisterInfo where ori_data between @otherStartDate and @otherEndDate go --财产录入存储过程 create procedure addProperty @propertyName varchar(50), @propertyApartment varchar(50), @propertyBroken varchar(50), @propertyPlace varchar(50) as insert into t_PropertyInfo (property_name, property_apartmentID, property_brokenFlag, property_place ) values (@propertyName, @propertyApartment, @propertyBroken, @propertyPlace ) go --根据名字地点返回财产信息存储过程 create procedure queryPropertyByNameAndPlace @propertyName varchar(50), --定义输入参数 @propertyPlace varchar(50) as select property_name, property_apartmentID, property_brokenFlag, property_place from t_PropertyInfo where property_name= @propertyName and property_place= @propertyPlace go --调整学生宿舍的存储过程 create procedure adjustStudentDorm @apartmentiid varchar(50), --定义输入参数 @studormid varchar(50), @stuid varchar(50) as UPDATE t_stuinfo SET stu_apartmentID= @apartmentiid, stu_dorm= @studormid where id= @stuid go --根据剩余床位查询寝室信息的存储过程 create procedure queryDormByRestNum @dormRestNum varchar(50), --定义输入参数 @dormApartment varchar(50) as select * from t_dorminfo where dorm_restnum=@dormRestNum and t_dorminfo.dorm_apartmentID= @dormApartment go --根据分配条件查询寝室信息的存储过程 create procedure queryDormByCond @dormApartment varchar(50), @dormDepartment varchar(50), --定义输入参数 @dormGrade varchar(50), @dormClass varchar(50) as select * from t_dorminfo where dorm_apartmentID= @dormApartment and dorm_department= @dormDepartment and dorm_grade= @dormGrade and dorm_class= @dormClass go --根据宿管id获取宿管对象 create procedure getAdminById @adminId varchar(50) as select * from t_adminfo where id=@adminId go --根据寝室id获取寝室剩余床位 create procedure getDormRestNumByDormID @dormId varchar(50) as select * from t_dorminfo where id=@dormId go --根据学生寝室调整的信息 修改宿舍剩余床位 create procedure updateDormRestNumByStu @restNum varchar(50), @dormId varchar(50) as UPDATE t_dorminfo SET dorm_restnum= @restNum where id= @dormId go --根据学生id获取学生对象 create procedure getStuById @stuId varchar(50) as select * from t_stuinfo where id= @stuId go