1、實驗十一 GPS車輛管理系統(tǒng)中復雜存儲過程和觸發(fā)器的設計與應用1、實驗背景：GPS車輛管理系統(tǒng)隨著我國社會的快速發(fā)展，汽車等交通工具數(shù)量急劇上升，傳統(tǒng)的車輛管理服務已經(jīng)無法滿足現(xiàn)代交通的需求，車輛監(jiān)控管理系統(tǒng)成為國內(nèi)外研究的熱點。車輛監(jiān)控管理系統(tǒng)融合三項先進技術GIS，GPS和無線通信，不僅可以為移動終端的監(jiān)控、調(diào)度和安全管理提供現(xiàn)代化的手段，而且對于提高道路交通能力，緩解交通壓力等方面也有著重要的作用。GPRS的車輛監(jiān)控系統(tǒng)整體框架，該框架包括車載終端軟硬件設計、無線通信設計，服務器軟件和客戶端等四個部分。其中，服務器軟件部分的設計與實現(xiàn)是本系統(tǒng)的研究重點。GPS車輛管理系統(tǒng)軟件可劃分為數(shù)據(jù)

2、通信代理、GPS監(jiān)控平臺以及數(shù)據(jù)庫服務等三個主要單元。車輛監(jiān)控系統(tǒng)主要具有以下幾個作用；(1) 以車輛監(jiān)控系統(tǒng)為核心，組成人員或貨物運輸車隊的遠程調(diào)控系統(tǒng)。監(jiān)控中心根據(jù)每輛車的實時位置與人員貨物的起始點，對車輛的行駛路徑進行綜合規(guī)劃，從而提高車輛利用率，降低成本，最大限度地減少人員貨物的等待時間，提高效率。 (2) 以車輛監(jiān)控系統(tǒng)為核心，構造智能交通系統(tǒng)。監(jiān)控中心實時地將道路堵塞情況通知給移動車輛，使其能及時調(diào)整運行路線，自動調(diào)節(jié)城市內(nèi)各條道路的交通流量，從而達到提高道路通行能力的目的。 (3) 以車輛監(jiān)控系統(tǒng)為核心，構建智能監(jiān)控系統(tǒng)。監(jiān)控中心能夠提供車輛實時位置和實時狀態(tài)，并對車輛的運行狀態(tài)

3、進行控制；接收車輛報警信息，使發(fā)生事故的車輛及時得到搶救，并通知相關部門以最快的速度排除道路故障或車輛故障，為車輛的健康運行提供安全保證。綜上所述，車輛監(jiān)控管理系統(tǒng)不但能夠?qū)崿F(xiàn)個人和企業(yè)對車輛進行遠程監(jiān)視調(diào)度的功能，同時對于交通部門控制交通運輸，緩解道路壓力，保障交通運輸通暢等也有重大的作用。 實驗中的復雜存儲過程和觸發(fā)器的設計是GPS車輛管理系統(tǒng)的數(shù)據(jù)庫服務模塊的一部分。數(shù)據(jù)庫作為車輛監(jiān)控管理系統(tǒng)的數(shù)據(jù)存儲部分，信息種類繁多，數(shù)據(jù)增長率快，數(shù)據(jù)實體之間的關系復雜。數(shù)據(jù)庫設計的好壞直接影響整個系統(tǒng)的性能、穩(wěn)定性等各個方面。為此我們需要建設復雜的存儲過程和觸發(fā)器。遠程車輛終端傳回數(shù)據(jù)分析，如圖1

4、圖1 系統(tǒng)數(shù)據(jù)庫實體關系圖圖解從圖中可以看出車輛信息比較大，又本系統(tǒng)車載終端每10秒發(fā)送一次數(shù)據(jù)，數(shù)據(jù)庫存儲每條信息需要30個字節(jié)來計算，假設終端每天正常工作12小時，數(shù)據(jù)庫每天存放一臺終端的信息將需要30*6*60*12=129600字節(jié)，而且這個數(shù)據(jù)是隨著終端的數(shù)量而直線增長的。數(shù)據(jù)庫負載量非常大，需要我們對建立的數(shù)據(jù)庫進行優(yōu)化：健壯安全機制、盡可能減少數(shù)據(jù)冗余、良好的數(shù)據(jù)庫性能。數(shù)據(jù)庫業(yè)務要求終端每10秒發(fā)送一次數(shù)據(jù)，這樣我們服務器端才可以隨時監(jiān)控到該終端的具體信息，但由于網(wǎng)絡的原因，數(shù)據(jù)信息不一定都可以正確傳回到服務器。所有我們通過前一次傳回的數(shù)據(jù)與最近一次傳回的數(shù)據(jù)比較，查看是否有數(shù)

5、據(jù)丟失。如果丟失，就不里程。所以要求我們在數(shù)據(jù)庫服務器端創(chuàng)建添加里程以及補里程的存儲過程和添加里程的存儲過程。當業(yè)務要求增加單位時就要求在服務器端數(shù)據(jù)庫為該單位創(chuàng)建里程表、事件分析表以及結果分析表，這樣我們就需要創(chuàng)建觸發(fā)器來實現(xiàn)這一功能。當服務器增加一輛車（終端），服務器就要自動增加為該車輛創(chuàng)建車輛信息表，我們同樣需要寫觸發(fā)器來實現(xiàn)這一功能。2、實驗準備：1、 本配置：Intel PentiumIII以上級別的CPU，大于64MB的內(nèi)存。2、 軟件要求：Window 2000操作系統(tǒng)，Microsoft SQL Server 20053、 實驗學時：4學時。4、寫實驗報告。 5、準備安裝好SQ

6、L Server 2005及VS2005，完成數(shù)據(jù)庫的創(chuàng)建并完成基礎數(shù)據(jù)庫的數(shù)據(jù)錄入（見附錄一定額基礎數(shù)據(jù)庫）。GPS數(shù)據(jù)通信系統(tǒng)數(shù)據(jù)庫，數(shù)據(jù)庫結構見數(shù)據(jù)庫GPSDB，數(shù)據(jù)庫說明、要求細節(jié)和例子見提供的電子稿。3、實驗目標：學會設計具體項目中較為復雜的存儲過程和觸發(fā)器以及優(yōu)化數(shù)據(jù)庫等技術。4、實驗示例（1）車輛基本信息表 CarBaseInfo說明：這個表用于存放所有車輛的基本信息，通過字段GroupID與車隊信息表GroupCar相聯(lián)系。（1）車輛基本信息表 CarBaseInfo說明：這個表用于存放所有車輛的基本信息，通過字段GroupID與車隊信息表GroupCar相聯(lián)系。字段名稱字段類

7、型是否主鍵/關聯(lián)字段說明CarIDint是自增CarNumberVarchar(15)車牌號TarIDint外，TarBaseInfoUnitIDintUnit.UnitID單位IDGroupIDintCarGroup.GroupID車隊IDUserIDVarchar（16）erID用戶賬號：個人用戶時有效SpeedLimitFloat車速限制FactoryTypeVarchar（20）廠牌TypeVarcar（20）車型LicenceDateDateTime機動車行駛證日期RegisterDateDateTime系統(tǒng)登記日期CarGPSVarchar(15)GPS_CARID該車輛GPS信息

8、表的名字GPSBaskUpVarchar(15)BAKGPS_CarID該車輛歷史GPS信息表BackUpDateDataTimeGPS信息的備份截至時間（2）車隊所屬單位信息表 UnitInfo說明：這個表用來存放車隊的詳細信息，如：車隊的名稱，編號，車隊負責人姓名，性別，聯(lián)系電話，車隊地址等。字段名稱字段類型是否主鍵/關聯(lián)字段說明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）聯(lián)系電話AddressVarchar（50）單位地址QqVarchar（11）QQMSNVarchar(50)MSNEducationVarchar(10)教育程度BusTypeIntBusInfo. BusID行業(yè)類型MonitorPageVarchar(50)監(jiān)控界面ManagePage

10、Varchar(50)管理界面（3）車輛GPS信息表 CarGpsInfo說明：這個表主要用于接收的GPS信息，從而用于車輛現(xiàn)階段的定位或歷史記錄的定位。字段名稱字段類型是否主鍵/關聯(lián)字段說明IDInt是自增標識號CarNumberVarchar(15)車輛號Longitudefloat經(jīng)度Latitudefloat緯度Speedfloat速度Directionfloat方向ReceiveTimeDateTime接收GPS信息時間Interestbit是否為興趣點InterestPointbit興趣點名稱（4）單位里程表 MilTable 說明：用于存放一個單位的車輛的里程信息，包括泊車信息、

11、上下高速字段名稱字段類型是否主鍵/關聯(lián)字段說明IDInt是自增標識號CarNumberVarchar(15)車牌MilStartTimeDateTime開始時間MilEndTimeDateTime結束時間MilInt里程數(shù)（5）事件信息表 Event 說明：用于存放報警等事件信息，事件類型包括：0-報警、1-超速、2-離開泊車區(qū)域、3-開進泊車區(qū)域、4-上高速、5-下高速、6-超圍欄事件、7-進圍欄事件、8-泊車事件字段名稱字段類型是否主鍵/關聯(lián)字段說明IDInt是自增標識號CarNumberVarchar(15)車牌EventStartTimeDateTime接收事件時間handleBit默

12、認值0是否處理（0未處理，1處理）EventTypeInt事件類型EventNameVarchar（50）事件名稱（6）結果分析表 Result 說明：用于存放一些分析結果信息，包括泊車信息、上下高速字段名稱字段類型是否主鍵/關聯(lián)字段說明IDInt是自增標識號CarNumberVarchar(15)車牌EventStartTimeDateTime事件的開始時間EventEndTimeDateTime事件的結束時間EventTypeInt事件類型EventNameVarchar（50）事件名稱觸發(fā)器：創(chuàng)建車輛（生成GPS表）、刪除車輛（刪除GPS表）、創(chuàng)建單位（生成MILINFO表、事件信息表

13、Event和結果分析表 Result）、刪除單位（刪除MILINFO表、事件信息表 Event和結果分析表 Result）5、實驗內(nèi)容及實例上機題1 復雜的觸發(fā)器設計1AddUnit功能：當增加一個單位時，增加三個表（根據(jù)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(已經(jīng)存在該單位里程表) 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(已經(jīng)存在該單位事件表) 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(已經(jīng)存在該單位泊車表) 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表（根據(jù)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表還有數(shù)據(jù)，不能刪除車輛) return end set SqlStr=Drop Table +TableName exec(SqlStr)if (error 0) begin Rollback Tran print(刪除車輛失敗) return endEND上機題2 復雜的存儲過程設計1sp_AddGpsRecord功能：實現(xiàn)插入GPS信息記錄、判斷是否為興趣點，并把GPS信息與興趣點標志登記在數(shù)據(jù)庫中。興趣點的判斷依據(jù)是啟動點（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