數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告觸發(fā)器存儲過程

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

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

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

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

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

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

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

8、表的名字GPSBaskUpVarchar(15)BAKGPS_CarID該車輛歷史GPS信息表BackUpDateDataTimeGPS信息的備份截至?xí)r間（2）車隊(duì)所屬單位信息表 UnitInfo說明：這個(gè)表用來存放車隊(duì)的詳細(xì)信息，如：車隊(duì)的名稱，編號，車隊(duì)負(fù)責(zé)人姓名，性別，聯(lián)系電話，車隊(duì)地址等。字段名稱字段類型是否主鍵/關(guān)聯(lián)字段說明UnitIDint是/ CarGroup r. UnitID單位IDUnitNameVarchar（50）單位名MilTableNameVarchar(15)MilInfo車隊(duì)里程表EventTableNameVarchar(15)EventTableName事件

9、表ResultTableNameVarchar(15)ResultTableName結(jié)果表PrincipalNameVarchar（50）單位負(fù)責(zé)人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說明：這個(gè)表主要用于接收的GPS信息，從而用于車輛現(xiàn)階段的定位或歷史記錄的定位。字段名稱字段類型是否主鍵/關(guān)聯(lián)字段說明IDInt是自增標(biāo)識號CarNumberVarchar(15)車輛號Longitudefloat經(jīng)度Latitudefloat緯度Speedfloat速度Directionfloat方向ReceiveTimeDateTime接收GPS信息時(shí)間Interestbit是否為興趣點(diǎn)InterestPointbit興趣點(diǎn)名稱（4）單位里程表 MilTable 說明：用于存放一個(gè)單位的車輛的里程信息，包括泊車信息、

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

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

13、Event和結(jié)果分析表 Result）、刪除單位（刪除MILINFO表、事件信息表 Event和結(jié)果分析表 Result）5、實(shí)驗(yàn)內(nèi)容及實(shí)例上機(jī)題1 復(fù)雜的觸發(fā)器設(shè)計(jì)1AddUnit功能：當(dāng)增加一個(gè)單位時(shí)，增加三個(gè)表（根據(jù)3個(gè)字段）（圖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功能：當(dāng)刪除一個(gè)單位時(shí)，刪除三個(gè)表（圖4）3AddCa

19、r功能：當(dāng)插入一臺車輛時(shí)，增加一個(gè)GPS表（根據(jù)1個(gè)字段）（圖5）4DelCar功能：當(dāng)刪除一臺車時(shí)，刪除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上機(jī)題2 復(fù)雜的存儲過程設(shè)計(jì)1sp_AddGpsRecord功能：實(shí)現(xiàn)插入GPS信息記錄、判斷是否為興趣點(diǎn)，并把GPS信息與興趣點(diǎn)標(biāo)志登記在數(shù)據(jù)庫中。興趣點(diǎn)的判斷依據(jù)是啟動(dòng)點(diǎn)（1）和拐彎點(diǎn)（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