mysql存储过程语句_MySQL

bitsCN.com 001set @Prov_Code:=32;002set @Prov_Name="省厅";003set @Area_Code:=3201;004set @Area_Name="市局";005 006 007SET FOREIGN_KEY_CHECKS=0;008-- ----------------------------009-- Table structure for `AlertInfo`010-- ----------------------------011DROP TABLE IF EXISTS `AlertInfo`;012CREATE TABLE `AlertInfo` (013 `AlertID` bigint(20) NOT NULL auto_increment COMMENT '唯一、非空、自增、主键',014 `DeviceID` bigint(20) default NULL COMMENT '告警设备的安装单位的单位编号',015 `LineIDOnDevice` bigint(20) default NULL COMMENT '该线路在录音设备上的线路编号',016 `AlertDate` datetime default NULL COMMENT '告警日期，仅日期有效，时间为00:00:00',017 `AllCallCount` int(10) default NULL COMMENT '当天总通话次数',018 `TimeOutCount` int(10) default NULL COMMENT '当天通话超时次数',019 `LineLostCount` int(10) default NULL COMMENT '当前断线次数',020 `ComfirmAlert` tinyint(4) default '0' COMMENT '告警是否已经确认',021 PRIMARY KEY (`AlertID`)022) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='告警信息表';023 024-- ----------------------------025-- Records of AlertInfo026-- ----------------------------027 028-- ----------------------------029-- Table structure for `DepartDescInfo`030-- ----------------------------031DROP TABLE IF EXISTS `DepartDescInfo`;032CREATE TABLE `DepartDescInfo` (033 `DepartClass` bigint(20) NOT NULL COMMENT '单位级别',034 `DepartDesc` varchar(50) default NULL COMMENT '单位级别描述',035 PRIMARY KEY (`DepartClass`)036) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门级别名称描述信息表';037 038-- ----------------------------039-- Records of DepartDescInfo040-- ----------------------------041 INSERT INTO DepartDescInfo VALUES ('1', '市局');042INSERT INTO DepartDescInfo VALUES ('2', '区分局');043INSERT INTO DepartDescInfo VALUES ('3', '派出所');044 045-- ----------------------------046-- Table structure for `DepartInfo`047-- ----------------------------048DROP TABLE IF EXISTS `DepartInfo`;049CREATE TABLE `DepartInfo` (050 `DepartID` bigint(20) NOT NULL COMMENT '单位编号',051 `DepartName` varchar(50) default NULL COMMENT '单位名称',052 PRIMARY KEY (`DepartID`)053) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门信息表';054 055-- ----------------------------056-- Records of DepartInfo057-- ----------------------------058INSERT INTO DepartInfo VALUES (concat(@Prov_Code,'000000'),@Prov_Name);059INSERT INTO DepartInfo VALUES (concat(@Area_Code,'0000'),@Area_Name);060 061-- ----------------------------062-- Table structure for `DeviceConnect`063 -- ----------------------------064DROP TABLE IF EXISTS `DeviceConnect`;065CREATE TABLE `DeviceConnect` (066 `ConnectID` bigint(20) NOT NULL auto_increment COMMENT '唯一、非空、自增、主键',067 `DeviceID` bigint(20) default NULL COMMENT '设备ID',068 `ConnectDate` datetime default NULL COMMENT '告警日期',069 `ConnectStatus` int(20) default NULL COMMENT '告警状态',070 `ComfirmAlert` tinyint(4) default '0' COMMENT '是否已确认告警 未确认0,已确认1',071 `ComfirmTime` datetime DEFAULT NULL COMMENT '确认告警日期',072 `ComfirmUserID` bigint(20) DEFAULT NULL COMMENT '确认告警人',073 PRIMARY KEY (`ConnectID`)074) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='设备告警信息表';075 076-- ----------------------------077-- Records of DeviceConnect078-- ----------------------------079 080-- ----------------------------081-- Table structure for `DeviceInfo`082-- ----------------------------083DROP TABLE IF EXISTS `DeviceInfo`;084CREATE TABLE `DeviceInfo` (085 `DeviceID` bigint(20) NOT NULL auto_increment COMMENT '设备、服务器编号',086 `DepartID` bigint(20) default NULL COMMENT '设备安装的单位编号',087 `DeviceType` int(11) default NULL COMMENT '设备类型',088 `DeviceName` varchar(100) default NULL COMMENT '设备名称',089 `LineCount` int(11) default NULL COMMENT '线路数量',090 `IPAddr` varchar(50) default NULL COMMENT '设备的IP地址',091 `HttpPort` int(11) default NULL COMMENT '设备查询语音时使用的端口号',092 `ManagePort` int(11) default NULL COMMENT '设备管理端口号',093 `UserName` varchar(50) default NULL COMMENT '管理用户名',094 `UserPWD` varchar(50) default NULL COMMENT '管理密码',095 `UploadPolicy` int(20) default NULL COMMENT '上传策略',096 `UploadTime` varchar(50) default NULL COMMENT '上传时间，时分，如需要随时上传，则为2400',097 `LastCleanDate` datetime default NULL,098 `StatusMID` int(20) default NULL COMMENT '录音服务器和录音终端设备，数据由哪个矩阵服务器采集',099 PRIMARY KEY (`DeviceID`)100) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='设备信息表';101 102-- ----------------------------103-- Records of DeviceInfo104-- ----------------------------105 106-- ----------------------------107-- Table structure for `ImportantNote`108-- ----------------------------109DROP TABLE IF EXISTS `ImportantNote`;110CREATE TABLE `ImportantNote` (111 `NoteID` bigint(20) NOT NULL auto_increment COMMENT '编号',112 `Note` varchar(8000) default NULL COMMENT '信息内容',113 `InDate` datetime default NULL COMMENT '输入日期',114 `UserID` bigint(20) default NULL COMMENT '添加该信息的用户ID',115 PRIMARY KEY (`NoteID`)116) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='重要提示信息表';117 118-- ----------------------------119-- Records of ImportantNote120-- ----------------------------121 122-- ----------------------------123-- Table structure for `LineLost`124-- ----------------------------125DROP TABLE IF EXISTS `LineLost`;126CREATE TABLE `LineLost` (127 `LostID` bigint(20) NOT NULL auto_increment COMMENT '编号',128 `DeviceID` bigint(20) default NULL COMMENT '设备编号',129 `LineIDOnDevice` bigint(20) default NULL COMMENT '设备上的线路号',130 `StartTime` datetime default NULL COMMENT '断线开始时间',131 `EndTime` datetime default NULL COMMENT '断线结束时间',132 PRIMARY KEY (`LostID`)133) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='断线信息表';134 135-- ----------------------------136-- Records of LineLost137-- ----------------------------138 139-- ----------------------------140-- Table structure for `PhoneLine`141-- ----------------------------142DROP TABLE IF EXISTS `PhoneLine`;143CREATE TABLE `PhoneLine` (144 `LineID` bigint(20) NOT NULL auto_increment COMMENT '电话号码线路编号',145 `DepartID` bigint(20) default NULL COMMENT '号码所在单位',146 `PhoneNO` varchar(50) default NULL COMMENT '电话号码',147 `ShortPhoneNO` varchar(50) default NULL COMMENT '电话号码',148 `LineDesc` varchar(50) default NULL COMMENT '描述信息',149 PRIMARY KEY (`LineID`)150) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='电话号码表';151 152-- ----------------------------153-- Records of PhoneLine154 -- ----------------------------155 156-- ----------------------------157-- Table structure for `QueryRecord`158-- ----------------------------159DROP TABLE IF EXISTS `QueryRecord`;160CREATE TABLE `QueryRecord` (161 `QueryID` bigint(20) NOT NULL auto_increment COMMENT '编号',162 `UserID` bigint(20) NOT NULL COMMENT '该查询的用户编号',163 `QueryTime` datetime default NULL COMMENT '查询时间',164 `FileID` bigint(20) default NULL COMMENT '查询的文件的编号',165 PRIMARY KEY (`QueryID`)166) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户查询记录表';167 168-- ----------------------------169-- Records of QueryRecord170-- ----------------------------171 172-- ----------------------------173-- Table structure for `RealMonitor`174-- ----------------------------175DROP TABLE IF EXISTS `RealMonitor`;176CREATE TABLE `RealMonitor` (177 `RealMonitorID` bigint(20) NOT NULL auto_increment COMMENT '编号',178 `MatrixServerID` bigint(20) default NULL COMMENT '数据提供的矩阵服务器的编号',179 `DeviceID` bigint(20) default NULL COMMENT '该线路的设备的编号',180 `LineIDOnDevice` bigint(20) default NULL COMMENT '该线路在设备上的编号',181 `LineType` int(11) default '0' COMMENT '线路类型',182 `CallStatus` int(20) default NULL COMMENT '呼叫结果',183 `CallDirection` int(20) default NULL COMMENT '呼叫方向',184 `MyPhoneNO` varchar(50) default NULL COMMENT '本方号码',185 `MyShortPhoneNO` varchar(50) default NULL COMMENT '本方短号',186 `RemotePhoneNO` varchar(50) default NULL COMMENT '对方号码',187 `RefleshTime` datetime default NULL COMMENT '该信息刷新的时间',188 PRIMARY KEY (`RealMonitorID`)189) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='实时监控信息表';190 191-- ----------------------------192 -- Records of RealMonitor193-- ----------------------------194 195-- ----------------------------196-- Table structure for `UploadFile`197-- ----------------------------198DROP TABLE IF EXISTS `UploadFile`;199CREATE TABLE `UploadFile` (200 `FileID` bigint(20) NOT NULL auto_increment COMMENT '编号',201 `FileDesc` varchar(1500) default NULL COMMENT '文件描述',202 `FileName` varchar(100) default NULL COMMENT '文件名称',203 `InDate` datetime default NULL COMMENT '上传时间',204 `UserID` bigint(20) NOT NULL COMMENT '上传该文件的用户的编号',205 PRIMARY KEY (`FileID`)206) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='文件下载信息表';207 208-- ----------------------------209-- Records of UploadFile210-- ----------------------------211 212-- ----------------------------213 -- Table structure for `UserList`214-- ----------------------------215DROP TABLE IF EXISTS `UserList`;216CREATE TABLE `UserList` (217 `UserID` bigint(20) NOT NULL auto_increment COMMENT '用户编号',218 `DepartID` bigint(20) default NULL COMMENT '设备编号',219 `UserName` varchar(20) default NULL COMMENT '用户名',220 `UserPWD` varchar(200) default NULL COMMENT '用户密码',221 `FullName` varchar(100) default NULL COMMENT '用户姓名',222 `UserDesc` varchar(50) default NULL COMMENT '用户描述信息',223 `IDCard` varchar(40) default NULL COMMENT '身份证号',224 `UserPopedom` int(20) default '0' COMMENT '用户权限值',225 PRIMARY KEY (`UserID`)226) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';227 228-- ----------------------------229-- Records of UserList230-- ----------------------------231 232INSERT INTO UserList VALUES ('1', concat(@Prov_Code,'000000') , 'styh', '698d51a19d8a121ce581499d7b701668', 'styh', @Prov_Name, '123456789987654', '63');233INSERT INTO UserList VALUES ('2', concat(@Area_Code,'0000') , 'admin', '698d51a19d8a121ce581499d7b701668', 'sjyh', @Area_Name, '123456789987654', '63');234 235-- ----------------------------236-- Table structure for `UserAuthenticate`237-- ----------------------------238DROP TABLE IF EXISTS `UserAuthenticate`;239CREATE TABLE `UserAuthenticate` (240 `AuthID` bigint(20) NOT NULL auto_increment COMMENT '授权编号',241 `UserID` bigint(20) default NULL COMMENT '用户编号',242 `ManageDepartID` bigint(20) default NULL COMMENT '授权给该用户的可以访问的单位的编号',243 PRIMARY KEY (`AuthID`) 244) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户权限信息表';245 246-- ----------------------------247-- Records of UserAuthenticate248-- ----------------------------249INSERT INTO UserAuthenticate VALUES ('1', '1', concat(@Prov_Code,'000000'));250INSERT INTO UserAuthenticate VALUES ('2', '2', concat(@Area_Code,'0000'));251 252-- ----------------------------253-- Table structure for `UserLog`254-- ----------------------------255DROP TABLE IF EXISTS `UserLog`;256CREATE TABLE `UserLog` (257 `LogID` bigint(16) NOT NULL auto_increment COMMENT '登录编号',258 `UserID` bigint(16) NOT NULL COMMENT '用户编号',259 `IPAddr` varchar(50) default NULL COMMENT '用户登录时使用的IP地址',260 `LogINTime` datetime default NULL COMMENT '登录时间',261 `LogOUTTime` datetime default NULL COMMENT '注销时间',262 PRIMARY KEY (`LogID`) 263) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='用户登录记录表';264 265-- ----------------------------266-- Records of UserLog267-- ----------------------------268 269-- ----------------------------270-- Table structure for `VoiceFileList`271-- ----------------------------272DROP TABLE IF EXISTS `VoiceFileList`;273CREATE TABLE `VoiceFileList` (274 `FileID` bigint(20) NOT NULL auto_increment COMMENT '文件编号',275 `DeviceID` bigint(20) default NULL COMMENT '当前文件所在设备的编号',276 `LineID` bigint(20) default NULL COMMENT '该通话局内号码对应的线路编号',277 `PhoneNO` varchar(50) default NULL COMMENT '对方电话号码',278 `RecTime` datetime default NULL COMMENT '通话开始时间',279 `RingLength` int(20) default NULL COMMENT '响铃时间长度',280 `RecLength` int(20) default NULL COMMENT '通话时间长度',281 `FileName` varchar(100) default NULL COMMENT '文件名称',282 `isCallOUT` int(20) default NULL COMMENT '呼入呼出',283 `iState` int(20) default NULL COMMENT '呼叫结果',284 `iFileLevel` int(20) default NULL COMMENT '文件重要等级',285 `FileMemo` varchar(50) default NULL COMMENT '文件描述',286 `isDELed` int(20) default NULL COMMENT '是否已被删除',287 `OrgDeviceID` bigint(20) default NULL COMMENT '该文件在原始录音设备编号',288 `OrgLineIDOnDevice` bigint(20) default NULL COMMENT '该文件在原始录音设备上的线路编号',289 `OrgStorageTypeOnDevice` bigint(20) default NULL COMMENT '该文件在原始录音设备上的存储介质类型',290 `OrgFileIDOnDevice` bigint(20) default NULL COMMENT '该文件在原始录音设备上的文件编号',291 PRIMARY KEY (`FileID`)292) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='通话记录清单';293 294-- ----------------------------295-- Records of VoiceFileList296-- ----------------------------297 298-- ----------------------------299-- Table structure for `LongCallInfo`300-- ----------------------------301DROP TABLE IF EXISTS `LongCallInfo`;302CREATE TABLE `LongCallInfo` (303 `LongCallID` int(11) NOT NULL auto_increment COMMENT '编号',304 `DeviceID` bigint(11) NOT NULL COMMENT '设备编号',305 `lineIDOnDevice` bigint(11) NOT NULL COMMENT '线路编号',306 `isCallOUT` int(1) NOT NULL COMMENT '呼入呼出',307 `PhoneNO` varchar(64) NOT NULL COMMENT '本方号码',308 `RemotePhoneNO` varchar(64) NOT NULL COMMENT '对方号码',309 `ShortPhoneNO` varchar(64) NOT NULL COMMENT '本方短号',310 `StartTime` datetime NOT NULL COMMENT '开始时间',311 `EndTime` datetime NOT NULL COMMENT '结束时间',312 PRIMARY KEY (`LongCallID`)313) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='长时间通话信息表';314 315-- ----------------------------316-- Records of LongCallInfo317-- ----------------------------01-- ----------------------------02-- Function structure for `GetDepLevelByID`03-- ----------------------------04DROP FUNCTION IF EXISTS `GetDepLevelByID`;05DELIMITER ;;06CREATE DEFINER=`root`@`%` FUNCTION `GetDepLevelByID`(depID bigint) RETURNS int(11)07 COMMENT '根据部门的编号id来查找部门的级别'08begin09 declare tmpDepID bigint default depID; 10 declare depLevel bigint default 1; 11 WHILE tmpDepID>100 DO 12 if (tmpDepID mod 100)<>0 then13 set depLevel=depLevel+1;14 end if;15 set tmpDepID=tmpDepID/100;16 END WHILE; 17 return depLevel;18 end19;;20DELIMITER ;01-- ----------------------------02-- Function structure for `GetListNums`03-- ----------------------------04DROP FUNCTION IF EXISTS `GetListNums`;05DELIMITER ;; 06CREATE DEFINER=`root`@`%` FUNCTION `GetListNums`(CurLogID bigint) RETURNS bigint(20)07begin08RETURN (Select Count(*) as ListenNums from QueryRecord, UserLog Where UserLog.LogID=CurLogID and QueryRecord.UserID = UserLog.UserID09 and QueryRecord.QueryTime >= UserLog.LogINTime and QueryRecord.QueryTime <= UserLog.LogOUTTime);10end11;;12DELIMITER ; 01-- ----------------------------02-- Function structure for `GetParDepIDByLevel`03-- ----------------------------04DROP FUNCTION IF EXISTS `GetParDepIDByLevel`;05DELIMITER ;;06CREATE DEFINER=`root`@`%` FUNCTION `GetParDepIDByLevel`(depID bigint,07 depLevel int) RETURNS bigint(20)08 COMMENT '根据部门编号和所要查找的级别找到该级别相对应的部门编号/r/n如 32010101,1 --->32000000/r/n'09begin10 declare parDepID bigint default depID;11 declare levelPar bigint default 0;12 declare i int default 0; 13 14 if GetDepLevelByID(depID)<>depLevel then15 WHILE parDepID>power(100,depLevel) DO16 set levelPar=power(100,i);17 set parDepID=depID p levelPar; 18 set i=i+1;19 END WHILE;20 set levelPar=power(100,i-1); 21 set parDepID=parDepID*levelPar;22 return parDepID;23 24 else25 return depID;26 end if;27end28;;29DELIMITER ; 作者 bug哥 bitsCN.com