数据库实验报告触发器存储过程

1、实验十一 GPS车辆管理系统中复杂存储过程和触发器的设计与应用1、实验背景：GPS车辆管理系统随着我国社会的快速发展，汽车等交通工具数量急剧上升，传统的车辆管理服务已经无法满足现代交通的需求，车辆监控管理系统成为国内外研究的热点。车辆监控管理系统融合三项先进技术GIS，GPS和无线通信，不仅可以为移动终端的监控、调度和安全管理提供现代化的手段，而且对于提高道路交通能力，缓解交通压力等方面也有着重要的作用。GPRS的车辆监控系统整体框架，该框架包括车载终端软硬件设计、无线通信设计，服务器软件和客户端等四个部分。其中，服务器软件部分的设计与实现是本系统的研究重点。GPS车辆管理系统软件可划分为数据

2、通信代理、GPS监控平台以及数据库服务等三个主要单元。车辆监控系统主要具有以下几个作用；(1) 以车辆监控系统为核心，组成人员或货物运输车队的远程调控系统。监控中心根据每辆车的实时位置与人员货物的起始点，对车辆的行驶路径进行综合规划，从而提高车辆利用率，降低成本，最大限度地减少人员货物的等待时间，提高效率。 (2) 以车辆监控系统为核心，构造智能交通系统。监控中心实时地将道路堵塞情况通知给移动车辆，使其能及时调整运行路线，自动调节城市内各条道路的交通流量，从而达到提高道路通行能力的目的。 (3) 以车辆监控系统为核心，构建智能监控系统。监控中心能够提供车辆实时位置和实时状态，并对车辆的运行状态

3、进行控制；接收车辆报警信息，使发生事故的车辆及时得到抢救，并通知相关部门以最快的速度排除道路故障或车辆故障，为车辆的健康运行提供安全保证。综上所述，车辆监控管理系统不但能够实现个人和企业对车辆进行远程监视调度的功能，同时对于交通部门控制交通运输，缓解道路压力，保障交通运输通畅等也有重大的作用。 实验中的复杂存储过程和触发器的设计是GPS车辆管理系统的数据库服务模块的一部分。数据库作为车辆监控管理系统的数据存储部分，信息种类繁多，数据增长率快，数据实体之间的关系复杂。数据库设计的好坏直接影响整个系统的性能、稳定性等各个方面。为此我们需要建设复杂的存储过程和触发器。远程车辆终端传回数据分析，如图1

4、图1 系统数据库实体关系图图解从图中可以看出车辆信息比较大，又本系统车载终端每10秒发送一次数据，数据库存储每条信息需要30个字节来计算，假设终端每天正常工作12小时，数据库每天存放一台终端的信息将需要30*6*60*12=129600字节，而且这个数据是随着终端的数量而直线增长的。数据库负载量非常大，需要我们对建立的数据库进行优化：健壮安全机制、尽可能减少数据冗余、良好的数据库性能。数据库业务要求终端每10秒发送一次数据，这样我们服务器端才可以随时监控到该终端的具体信息，但由于网络的原因，数据信息不一定都可以正确传回到服务器。所有我们通过前一次传回的数据与最近一次传回的数据比较，查看是否有数

5、据丢失。如果丢失，就不里程。所以要求我们在数据库服务器端创建添加里程以及补里程的存储过程和添加里程的存储过程。当业务要求增加单位时就要求在服务器端数据库为该单位创建里程表、事件分析表以及结果分析表，这样我们就需要创建触发器来实现这一功能。当服务器增加一辆车（终端），服务器就要自动增加为该车辆创建车辆信息表，我们同样需要写触发器来实现这一功能。2、实验准备：1、 本配置：Intel PentiumIII以上级别的CPU，大于64MB的内存。2、 软件要求：Window 2000操作系统，Microsoft SQL Server 20053、 实验学时：4学时。4、写实验报告。 5、准备安装好SQ

6、L Server 2005及VS2005，完成数据库的创建并完成基础数据库的数据录入（见附录一定额基础数据库）。GPS数据通信系统数据库，数据库结构见数据库GPSDB，数据库说明、要求细节和例子见提供的电子稿。3、实验目标：学会设计具体项目中较为复杂的存储过程和触发器以及优化数据库等技术。4、实验示例（1）车辆基本信息表 CarBaseInfo说明：这个表用于存放所有车辆的基本信息，通过字段GroupID与车队信息表GroupCar相联系。（1）车辆基本信息表 CarBaseInfo说明：这个表用于存放所有车辆的基本信息，通过字段GroupID与车队信息表GroupCar相联系。字段名称字段类

7、型是否主键/关联字段说明CarIDint是自增CarNumberVarchar(15)车牌号TarIDint外，TarBaseInfoUnitIDintUnit.UnitID单位IDGroupIDintCarGroup.GroupID车队IDUserIDVarchar（16）erID用户账号：个人用户时有效SpeedLimitFloat车速限制FactoryTypeVarchar（20）厂牌TypeVarcar（20）车型LicenceDateDateTime机动车行驶证日期RegisterDateDateTime系统登记日期CarGPSVarchar(15)GPS_CARID该车辆GPS信息

8、表的名字GPSBaskUpVarchar(15)BAKGPS_CarID该车辆历史GPS信息表BackUpDateDataTimeGPS信息的备份截至时间（2）车队所属单位信息表 UnitInfo说明：这个表用来存放车队的详细信息，如：车队的名称，编号，车队负责人姓名，性别，联系电话，车队地址等。字段名称字段类型是否主键/关联字段说明UnitIDint是/ CarGroup r. UnitID单位IDUnitNameVarchar（50）单位名MilTableNameVarchar(15)MilInfo车队里程表EventTableNameVarchar(15)EventTableName事件

9、表ResultTableNameVarchar(15)ResultTableName结果表PrincipalNameVarchar（50）单位负责人Gender Char（2）性别EmailVarchar（50）用户EmailBirthDayDateTime出生日期ContactTelVarchar（15）联系电话AddressVarchar（50）单位地址QqVarchar（11）QQMSNVarchar(50)MSNEducationVarchar(10)教育程度BusTypeIntBusInfo. BusID行业类型MonitorPageVarchar(50)监控界面ManagePage

10、Varchar(50)管理界面（3）车辆GPS信息表 CarGpsInfo说明：这个表主要用于接收的GPS信息，从而用于车辆现阶段的定位或历史记录的定位。字段名称字段类型是否主键/关联字段说明IDInt是自增标识号CarNumberVarchar(15)车辆号Longitudefloat经度Latitudefloat纬度Speedfloat速度Directionfloat方向ReceiveTimeDateTime接收GPS信息时间Interestbit是否为兴趣点InterestPointbit兴趣点名称（4）单位里程表 MilTable 说明：用于存放一个单位的车辆的里程信息，包括泊车信息、

11、上下高速字段名称字段类型是否主键/关联字段说明IDInt是自增标识号CarNumberVarchar(15)车牌MilStartTimeDateTime开始时间MilEndTimeDateTime结束时间MilInt里程数（5）事件信息表 Event 说明：用于存放报警等事件信息，事件类型包括：0-报警、1-超速、2-离开泊车区域、3-开进泊车区域、4-上高速、5-下高速、6-超围栏事件、7-进围栏事件、8-泊车事件字段名称字段类型是否主键/关联字段说明IDInt是自增标识号CarNumberVarchar(15)车牌EventStartTimeDateTime接收事件时间handleBit默

12、认值0是否处理（0未处理，1处理）EventTypeInt事件类型EventNameVarchar（50）事件名称（6）结果分析表 Result 说明：用于存放一些分析结果信息，包括泊车信息、上下高速字段名称字段类型是否主键/关联字段说明IDInt是自增标识号CarNumberVarchar(15)车牌EventStartTimeDateTime事件的开始时间EventEndTimeDateTime事件的结束时间EventTypeInt事件类型EventNameVarchar（50）事件名称触发器：创建车辆（生成GPS表）、删除车辆（删除GPS表）、创建单位（生成MILINFO表、事件信息表

13、Event和结果分析表 Result）、删除单位（删除MILINFO表、事件信息表 Event和结果分析表 Result）5、实验内容及实例上机题1 复杂的触发器设计1AddUnit功能：当增加一个单位时，增加三个表（根据3个字段）（图3） - =- Author:- Create date: - Description:- =ALTER TRIGGER Insert_Unit ON dbo.UnitInfo AFTER INSERTAS declare MilTableName varchar(20) declare EventTableName varchar(20) declare Re

14、sultTableName varchar(20) declare UnitID intdeclare SqlStr varchar(300)BEGIN select MilTableName=MilTableName,EventTableName=EventTableName,ResultTableName=ResultTableName from inserted select MilTableName,EventTableName,ResultTableName IF(EXISTS(SELECT * FROM sysobjects WHERE NAME= MilTableName) be

15、gin rollback tran print(已经存在该单位里程表) return endset SqlStr=Create table +MilTableName+ (ID int IDENTITY(1, 1) NOT NULL Primary key,CarNumber varchar(15) NOT NULL ,DriveCode varchar(15) NULL , DriveBeginTime datetime NULL ,DriveEndTime datetime NULL , Mil float NULL) exec(SqlStr)if (error 0) begin Roll

16、back Tran print(添加单位失败) return end IF(EXISTS(SELECT * FROM sysobjects WHERE NAME= EventTableName) begin rollback tran print(已经存在该单位事件表) return endset SqlStr=Create table +EventTableName+ (ID int IDENTITY(1, 1) NOT NULL Primary key,CarNumber varchar(15) NOT NULL ,EventStartTime datetime NULL ,handle

17、int NULL , EventType int NULL, EventName varchar(50) NULL) exec(SqlStr)if (error 0) begin Rollback Tran print(添加单位失败) return end IF(EXISTS(SELECT * FROM sysobjects WHERE NAME= ResultTableName) begin rollback tran print(已经存在该单位泊车表) return endset SqlStr=Create table +ResultTableName+ (ID int IDENTITY(

18、1, 1) NOT NULL Primary key,CarNumber varchar(15) NOT NULL ,EventStartTime datetime NULL ,EventEndTime datetime NULL , EventType int NULL, EventName varchar(50) NULL,handle bit NULL DEFAULT 0 ) exec(SqlStr)if (error 0) begin Rollback Tran print(添加单位失败) return endEND2DelUnit功能：当删除一个单位时，删除三个表（图4）3AddCa

19、r功能：当插入一台车辆时，增加一个GPS表（根据1个字段）（图5）4DelCar功能：当删除一台车时，删除GPS表（图6）ALTER TRIGGER dbo.Delete_Car ON dbo.CarBaseInfo AFTER DELETEAS declare TableName varchar(20)declare SqlStr varchar(200)BEGIN select TableName=CarGPS from deleted set SqlStr=select * from +TableName exec(SqlStr) if (RowCount0) begin Rollbac

20、k Tran print(GPS表还有数据，不能删除车辆) return end set SqlStr=Drop Table +TableName exec(SqlStr)if (error 0) begin Rollback Tran print(删除车辆失败) return endEND上机题2 复杂的存储过程设计1sp_AddGpsRecord功能：实现插入GPS信息记录、判断是否为兴趣点，并把GPS信息与兴趣点标志登记在数据库中。兴趣点的判断依据是启动点（1）和拐弯点（2）。（图1）- =- Author:- Create date: - Description:- =CREATE P

21、ROCEDURE dbo.AddGpsRecord dip int,RecTime datetime,Latitude float,longitude float,Speed float,Direction floatASdeclare GpsTableName nvarchar(15)declare EventTableName nvarchar(15)declare ResultTableName nvarchar(15)declare CarNumber nvarchar(15)declare LastReceiveTime datetimedeclare SpeedLimit floa

22、tdeclare LastSpeed float declare LastDirection float declare LastLatitude floatdeclare Lastlongitude floatdeclare Interest intdeclare EventName varchar(50)declare SqlStr nvarchar(300)declare Fencename varchar(20)declare MaxStartTime datetimedeclare MaxEndTime datetimedeclare EventType intBEGINBEGIN

23、TRY Begin Tran SaveGps select GpsTableName=CarBaseInfo.CarGps,CarNumber=CarBaseInfo.CarNumber,SpeedLimit=SpeedLimit,EventTableName=UnitInfo.EventTableName,ResultTableName=ResultTableName from CarBaseInfo,UnitInfo where CarBaseInfo.TarID=dip and CarBaseInfo.UnitID=UnitInfo.UnitID set SqlStr=NSELECT L

24、astRecTime=MAX(ReceiveTime) FROM +GpsTableName+N WHERE ReceiveTime10.0 AND CarNumber=+CarNumber+ exec sp_executesql SqlStr, NLastRecTime datetime output,LastReceiveTime output set SqlStr=NSelect LSpeed=Speed,LDirection=Direction,LLatitude=Latitude,Llongitude=longitude FROM +GpsTableName+N WHERE ReceiveTime=+convert(varchar(40),LastReceiveTime,121)+ exec sp_executesql SqlStr, NLSpeed float output, LDirection float output,LLatitude float output,Llongitude float output,LastSpeed output , LastDirecti