Mysql stored procedure statement _ MySQL

Mysql stored procedure statement bitsCN.com 001 set @ Prov_Code: = 32; 002 set @ Prov_Name = "provincial/municipal departments"; 003 set @ Area_Code: = 3201; 004 set @ Area_Name = "municipal Bureau "; 005 006 007 SET FOREIGN_KEY_CHECKS = 0; 008 -- ---------------------------- 009 -- Table structure for 'alertinfo '010 -- -------------------------- 011 drop table if exists 'alertinfo '; 012 create table 'alertinfo' (013 'alertid' bigint (20) not null auto_increment COMMENT 'unique, non-empty, auto-increment, primary key', 014 'deviceid' bigint (20) default null comment 'unit No. of the alert device installation unit ', 015 'lineidondevice' bigint (20) default NULL comment' line No. of the line on the recording device ', 016 'alertdate' datetime default NULL comment' alert date, valid only for date, time: 00:00:00, 017 'allcallcount' int (10) default null comment 'total calls of the day ', 018 'timeoutcount' int (10) default NULL comment' number of calls timed out on the current day ', 019 'linelostcount' int (10) default NULL comment' current disconnection count ', 020 'comfirmalert 'tinyint (4) default '0' comment' whether the alarm has been confirmed', 021 primary key ('alertid') 022) ENGINE = InnoDB default charset = utf8 COMMENT = 'alert info table '; 023 024 -- primary 025 -- Records of AlertInfo026 -- -------------------------- 027 028 -- Partition 029 -- Table structure for 'Partition descinfo '030 -- Partition 031 drop table if exists' limit descinfo '; 032 create table 'Your descinfo' (033 'Your class' bigint (20) not null comment' unit level ', 034 'Your desc' varchar (50) default NULL comment' unit level description ', 035 primary key ('primary class') 036) ENGINE = InnoDB default charset = utf8 COMMENT = 'Department-level name description information '; 037 038 -- 2017039 -- Records of includescinfo040 -- -------------------------- 041 insert into includescinfo VALUES ('1', 'registry '); 042 insert into includescinfo VALUES ('2 ', 'area subauthorization'); 043 insert into includescinfo VALUES ('3', 'Police Station '); 044 045 -- limit 046 -- Table structure for 'desttinfo '047 -- limit 048 drop table if exists' DepartInfo '; 049 create table 'deptinfo' (050 'destentid' bigint (20) not null comment 'unit number', 051 'Organization name' varchar (50) default NULL comment' unit name', 052 primary key ('departid') 053) ENGINE = InnoDB default charset = utf8 COMMENT = 'Department info table '; 054 055 -- 2017056 -- Records of DepartInfo057 -- ------------------------ 058 insert into DepartInfo VALUES (concat (@ Prov_Code, '20140901'), @ Prov_Name); 059 insert into DepartInfo VALUES (concat (@ Area_Code, '20140901'), @ Area_Name ); 060 061 -- partitions 062 -- Table structure for 'deviceconnect '063 -- -------------------------- 064 drop table if exists 'deviceconnect'; 065 create table 'deviceconnect '(066 'connectid' bigint (20) not null auto_increment COMMENT 'unique, non-empty, auto-incrementing, primary key', 067 'deviceid' bigint (20) default NULL comment' device ID ', 068 'date date' datetime default null comment 'alert date', 069 'connectstatus' int (20) default null comment 'alert status', 070 'comfirmalert 'tinyint (4) whether the default '0' comment' has been confirmed. the alarm is not confirmed. 0, 1, 071 'comfirmtime' datetime default null comment' confirm alert date ', 072 'comfirmuserid' bigint (20) default null comment' confirm alert Contact ', 073 primary key ('d d') 074) ENGINE = InnoDB default charset = utf8 COMMENT = 'device alert info table '; 075 076 -- limit 077 -- Records of DeviceConnect078 -- -------------------------- 079 080 -- limit 081 -- Table structure for 'deviceinfo' 082 -- limit 083 drop table if exists 'deviceinfo '; 084 create table 'deviceinfo' (085 'deviceid' bigint (20) not null auto_increment COMMENT 'device, server number', 086 'demotid' bigint (20) default null comment 'device installation Unit Number', 087 'devicetype 'int (11) default null comment 'device type', 088 'devicename' varchar (100) default null comment 'device name', 089 'linecount' int (11) default null comment 'line number', 090 'ipadd' varchar (50) default null comment 'IP Address of the device ', 091 'httpport' int (11) default NULL comment' port number used by the device to query the voice ', 092 'manageport' int (11) default null comment 'device management Port number', 093 'username' varchar (50) default null comment 'manage username', 094 'userpwd' varchar (50) default null comment 'administrative password', 095 'uploadpolicy' int (20) default null comment 'upload policy', 096 'uploadtime' varchar (50) default NULL comment' Upload time, time, if you need to upload at any time, it is 2400 ', 097 'lastcleandate' datetime default NULL, 098 'statusmid' int (20) default null comment 'recording server and recording terminal device, which matrix server collects data? ', 099 primary key ('deviceid') 100) ENGINE = InnoDB default charset = utf8 COMMENT = 'device infotable '; 101 102 -- reset 103 -- Records of DeviceInfo104 -- ---------------------------- 105 106 -- reset 107 -- Table structure for 'importantnote' 108 -- limit 109 drop table if exists' ImportantNote '; 110 create table 'importantnote' (111 'noteid' bigint (20) not null auto_increment COMMENT 'number', 112 'note' varchar (8000) default NULL comment' information content ', 113 'indate' datetime default NULL comment' input date ', 114 'userid' bigint (20) default NULL comment' add user ID for this information ', 115 primary key ('noteid') 116) ENGINE = InnoDB default charset = utf8 COMMENT = 'important prompt information '; 117 118 -- lifecycle 119 -- Records of ImportantNote120 -- ------------------------------ 121 122 -- -------------------------- 123 -- Table structure for 'linelost' 124 -- limit 125 drop table if exists 'linelost '; 126 create table 'linelost' (127 'lostid' bigint (20) not null auto_increment COMMENT 'number', 128 'deviceid' bigint (20) default null comment 'device number ', 129 'lineidondevice' bigint (20) default NULL comment' line number on the device ', 130 'starttime' datetime default null comment 'Disconnection start time ', 131 'endtime' datetime default NULL comment' disconnection end time', 132 primary key ('lostid') 133) ENGINE = InnoDB default charset = utf8 COMMENT = 'Disconnection info table '; 134 135 -- lifecycle 136 -- Records of LineLost137 -- ---------------------------- 138 139 -- ---------------------------- 140 -- Table structure for 'Python' 141 -- limit 142 drop table if exists' 'Python '; 143 create table 'Python' (144 'lineid' bigint (20) not null auto_increment COMMENT 'phone number line number', 145 'demotid' bigint (20) default null comment 'unit of The number', 146 'phoneno' varchar (50) default null comment 'phone number', 147 'Your phoneno' varchar (50) default null comment 'phone number', 148 'linedesc' varchar (50) default NULL comment' description information', 149 primary key ('lineid') 150) ENGINE = InnoDB default charset = utf8 COMMENT = 'phone number Docker '; 151 152 -- lifecycle 153 -- Records of PhoneLine154 -- ------------------------------ 155 156 -- ---------------------------- 157 -- Table structure for 'queryrecord' 158 -- limit 159 drop table if exists' QueryRecord '; 160 create table 'queryrecord' (161 'queryid' bigint (20) not null auto_increment comment' no. ', 162 'userid' bigint (20) not null comment 'user ID of the query', 163 'querytime' datetime default null comment 'query time', 164 'fileid' bigint (20) default null comment 'number of the queried file ', 165 primary key ('queryid') 166) ENGINE = InnoDB default charset = utf8 COMMENT = 'User query record table '; 167 168 -- lifecycle 169 -- Records of QueryRecord170 -- ------------------------------ 171 172 -- -------------------------- 173 -- Table structure for 'realmonitor '174 -- limit 175 drop table if exists 'realmonitor '; 176 create table 'realmonitor '(177 'realmonitorid' bigint (20) not null auto_increment COMMENT 'number', 178 'matrixserverid' bigint (20) default null comment 'number of The Matrix Server provided by data', 179 'deviceid' bigint (20) default NULL comment' device number of the line ', 180 'lineidondevice' bigint (20) default NULL comment' number of the line on the device ', 181 'linetype' int (11) default '0' comment' line type ', 182 'callstatus' int (20) default null comment 'Call result', 183 'calldirection' int (20) default null comment 'Call direction', 184 'myphoneno' varchar (50) default null comment 'local number', 185 'myusers' phoneno 'varchar (50) default NULL comment' local short number ', 186 'remotephoneno' varchar (50) default null comment 'peer number', 187 'refleshtime' datetime default NULL Comment' The information refresh time', 188 primary key ('realmonitorid') 189) ENGINE = InnoDB default charset = utf8 COMMENT = 'real-time monitoring information '; 190 191 -- lifecycle 192 -- Records of RealMonitor193 -- -------------------------------- 194 195 -- -------------------------- 196 -- Table structure for 'uploadfile' 197 -- limit 198 drop table if exists' uploadfile '; 199 create table 'uploadfile' (200 'fileid' bigint (20) not null auto_increment comment' no. ', 201 'filedesc' varchar (1500) default NULL comment' file description ', 202 'filename' varchar (100) default NULL comment' file name', 203 'indate' datetime default null comment 'upload time', 204 'userid' bigint (20) not null comment 'user ID for uploading this file', 205 primary key ('fileid') 206) ENGINE = InnoDB default charset = utf8 COMMENT = 'File download information '; 207 208 -- lifecycle 209 -- Records of UploadFile210 -- ------------------------------ 211 212 -- ---------------------------- 213 -- Table structure for 'userlist' 214 -- limit 215 drop table if exists 'userlist '; 216 create table 'userlist' (217 'userid' bigint (20) not null auto_increment COMMENT 'user number', 218 'demotid' bigint (20) default null comment 'device number ', 219 'username' varchar (20) default null comment 'username', 220 'userpwd' varchar (200) default null comment 'user password', 221 'fullname' varchar (100) default null comment 'user name', 222 'userdesc' varchar (50) default NULL comment' user description information', 223 'idcard 'varchar (40) default null comment 'ID card no ', 224 'userpopedom 'int (20) default '0' COMMENT 'user permission value', 225 primary key ('userid') 226) ENGINE = InnoDB default charset = utf8 COMMENT = 'User info table'; 227 228 -- ---------------------------- 229 -- Records of UserList230 -- -------------------------- 231 232 insert into UserList VALUES ('1 ', concat (@ Prov_Code, '20140901'), 'styh', '698d51a19d8a121ce581499d7b701668 ', 'styh', @ Prov_Name, '20170901', '63 '); 233 insert into UserList VALUES ('2', concat (@ Area_Code, '000000'), 'admin', '698d51a19d8a121ce581499d7b701668 ', 'sjyh', @ Area_Name, '2016 ', '63'); 234 235 -- -------------------------- 236 -- Table structure for 'userauthenticate' 237 -- ---------------------------- 238 drop table if exist' UserAuthenticate '; 239 create table 'userauthenticate' (240 'authid' bigint (20) not null auto_increment COMMENT 'authorization number', 241 'userid' bigint (20) default null comment 'user number ', 242 'managedepartid' bigint (20) default NULL comment' number of accessible units authorized to the user ', 243 primary key ('authid') 244) ENGINE = InnoDB default charset = utf8 COMMENT = 'User permission information'; 245 246 -- privileges 247 -- Records of UserAuthenticate248 -- ------------------------ 249 insert into UserAuthenticate VALUES ('1', '1 ', concat (@ Prov_Code, '20140901'); 000000 insert into UserAuthenticate VALUES ('2', '2', concat (@ Area_Code, '20140901 ')); 251 252 -- ---------------------------- 253 -- Table structure for 'userlog' 254 -- -------------------------- 255 drop table if exists 'userlog'; 256 create table 'userlog' (257 'logid' bigint (16) not null auto_increment COMMENT 'logon number', 258 'userid' bigint (16) not null comment 'user number', 259 'ipadd' varchar (50) default null comment 'IP Address used for user logon ', 260 'logintime' datetime default NULL comment' logon time', 261 'logouttime' datetime default null comment 'logout time ', 262 primary key ('logid') 263) ENGINE = InnoDB default charset = latin1 COMMENT = 'User logon Records '; 264 265 -- lifecycle 266 -- Records of UserLog267 -- ---------------------------- 268 269 -- lifecycle 270 -- Table structure for 'voicefilelist' 271 -- limit 272 drop table if exists 'voicefilelist '; 273 create table 'voicefilelist' (274 'fileid' bigint (20) not null auto_increment COMMENT 'file number', 275 'deviceid' bigint (20) default null comment 'device number of the current file', 276 'lineid' bigint (20) default NULL comment' line number corresponding to the call Board Number ', 277 'Python' varchar (50) default NULL comment' other party's phone number ', 278 'rectime' datetime default null comment 'Call start time', 279 'ringlength' int (20) default null comment 'alert time length', 280 'reclength' int (20) default NULL comment' call time length', 281 'filename' varchar (100) default null comment 'file name', 282 'iscallout' int (20) default NULL comment' incoming call output', 283 'istate' int (20) default null comment 'Call result ', 284 'ifilelevel' int (20) default NULL comment' file importance level', 285 'filememo' varchar (50) default NULL comment' file description ', 286 'isdeled' int (20) default NULL comment' whether it has been deleted ', 287 'orgdeviceid' bigint (20) default NULL Comment' The file is on the original recording device number ', 288 'orglineidondevic' bigint (20) default NULL comment' line number of the file on the original recording device ', 289 'orgstoragetypeondevic' bigint (20) default null comment 'Storage media type of the file on the original recording device ', 290 'orgfileidondevice' bigint (20) default null comment 'number of the file on the original recording device ', 291 primary key ('fileid') 292) ENGINE = InnoDB default charset = utf8 COMMENT = 'Call record list '; 293 294 -- lifecycle 295 -- Records of VoiceFileList296 -- -------------------------- 297 298 299 -- lifecycle 300 -- Table structure for 'longcallinfo '301 -- limit drop table if exists 'longcallinfo '; 302 create table 'longcallinfo' (303 'longcallid' int (11) not null auto_increment comment' no. ', 304 'deviceid' bigint (11) not null comment' device no ', 305 'lineidondevic' bigint (11) not null comment 'line number', 306 'iscallout' int (1) not null comment 'incoming call out', 307 'pythoneno' varchar (64) not null comment 'local number', 308 'remotephoneno 'varchar (64) not null comment 'peer number', 309 'Your phoneno' varchar (64) not null comment 'start time', 310 'starttime' datetime not null comment' start time', 311 'endtime' datetime not null comment' end time ', 312 primary key ('longcallid') 313) ENGINE = InnoDB default charset = utf8 COMMENT = 'long call information '; 314 315 -- lifecycle 316 -- Records of LongCallInfo317 -- -------------------------- 01 -- objective 02 -- Function structure for 'getplevelbyid' 03 -- comment 04 drop function if exists 'getplevelbyid'; 05 DELIMITER ;; 06 create definer = 'root' @ '% 'function' getdeplevelbyid' (depID bigint) RETURNS int (11) 07 COMMENT 'find the department level '08inin09 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; 20 DELIMITER; 01 -- -------------------------- 02 -- Function structure for 'getlistnums' 03 -- define 04 drop function if exists 'getlistnums'; 05 DELIMITER ;; 06 create 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; 12 DELIMITER; 01 -- comment 02 -- Function structure for 'getpardepidbylevel' 03 -- -------------------------- 04 drop function if exists 'getpardepidbylevel'; 05 DELIMITER ;; 06 create definer = 'root' @ '% 'function' GetParDepIDByLevel' (depID bigint, 07 depLevel int) RETURNS bigint (20) 08 COMMENT: Find the department ID corresponding to the level/r/n such as 32010101,1 ---> 32000000/r/n'09inin10 declare parDepID bigint default depID based on the department ID and the level to be searched; 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; 29 DELIMITER; author bug brother bitsCN.com