CREATE DEFINER=`root`@`%` PROCEDURE `phonesp`( starttime varchar(255), endtime varchar(255), str varchar(1000), startPage int, maxPerPage int ) BEGIN DECLARE cnt int; declare h int default 0; declare tbl_name varchar(20); declare tbl_name1 varchar(20); declare dt varchar(20); declare ho varchar(2); declare flag int default -1; declare readCount int default 0; declare cur_row_count int default 0; declare row_count int default 0; declare startRowNum int; declare findStartRow boolean default false;
set startRowNum = (startPage - 1) * maxPerPage + 1; set cnt=timestampdiff(hour,starttime,endtime); while h <= cnt do if readCount < maxPerPage then set dt = date_format(starttime,'%Y%m%d%H'); set tbl_name=concat('vq',dt,'0000'); set tbl_name1=concat('bicc',dt,'0000'); set @test = concat("select count(`TABLE_NAME`) into @flag from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='VQM' and `TABLE_NAME`='",tbl_name1,"';"); prepare s0 from @test; execute s0; set flag = @flag; deallocate prepare s0; if flag>0 then set @x = concat('SELECT count(*) into @cur_row_count FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str); prepare s1 from @x; execute s1; deallocate prepare s1; set cur_row_count = @cur_row_count; set row_count = row_count + cur_row_count; if row_count - startRowNum >= 0 then if !findStartRow then if row_count - startRowNum >= maxPerPage then set @x = concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str,' limit ',cur_row_count - (row_count - startRowNum) - 1,',',maxPerPage); prepare s1 from @x; execute s1; deallocate prepare s1; set readCount = maxPerPage; else set @x = concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str,' limit ',(cur_row_count - (row_count - startRowNum) - 1),',',row_count - startRowNum); end if; set findStartRow = true; else set @x = concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str,' limit ',0,',',maxPerPage - readCount); prepare s1 from @x; execute s1; deallocate prepare s1; set readCount = readCount + (cur_row_count - (maxPerPage - readCount - cur_row_count)); end if; end if; end if; end if; set h=h+1; set starttime=timestampadd(hour,1,starttime); end while; end;
concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' ....前面一大串.都一样的.就是后面的不一样..写了3次..不累呀.. 在前面..赋好值..然后在后面判断一下..再连接字符串.
在java程序中调用的,当传入的str的长度太长的时候(超过255)的时候程序报下面的错误: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'str' at row 1
CREATE DEFINER=`root`@`%` PROCEDURE `phonesp`(
starttime varchar(255),
endtime varchar(255),
str varchar(1000),
startPage int,
maxPerPage int
)
BEGIN
DECLARE cnt int;
declare h int default 0;
declare tbl_name varchar(20);
declare tbl_name1 varchar(20);
declare dt varchar(20);
declare ho varchar(2);
declare flag int default -1;
declare readCount int default 0;
declare cur_row_count int default 0;
declare row_count int default 0;
declare startRowNum int;
declare findStartRow boolean default false;
set startRowNum = (startPage - 1) * maxPerPage + 1;
set cnt=timestampdiff(hour,starttime,endtime);
while h <= cnt do
if readCount < maxPerPage then
set dt = date_format(starttime,'%Y%m%d%H');
set tbl_name=concat('vq',dt,'0000');
set tbl_name1=concat('bicc',dt,'0000');
set @test = concat("select count(`TABLE_NAME`) into @flag from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='VQM' and `TABLE_NAME`='",tbl_name1,"';");
prepare s0 from @test;
execute s0;
set flag = @flag;
deallocate prepare s0;
if flag>0 then
set @x = concat('SELECT count(*) into @cur_row_count FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str);
prepare s1 from @x;
execute s1;
deallocate prepare s1;
set cur_row_count = @cur_row_count;
set row_count = row_count + cur_row_count;
if row_count - startRowNum >= 0 then
if !findStartRow then
if row_count - startRowNum >= maxPerPage then
set @x = concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str,' limit ',cur_row_count - (row_count - startRowNum) - 1,',',maxPerPage);
prepare s1 from @x;
execute s1;
deallocate prepare s1;
set readCount = maxPerPage;
else
set @x = concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str,' limit ',(cur_row_count - (row_count - startRowNum) - 1),',',row_count - startRowNum);
end if;
set findStartRow = true;
else
set @x = concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str,' limit ',0,',',maxPerPage - readCount);
prepare s1 from @x;
execute s1;
deallocate prepare s1;
set readCount = readCount + (cur_row_count - (maxPerPage - readCount - cur_row_count));
end if;
end if;
end if;
end if;
set h=h+1;
set starttime=timestampadd(hour,1,starttime);
end while;
end;
在前面..赋好值..然后在后面判断一下..再连接字符串.
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'str' at row 1
但是这个varchar(n),n的大小问题还是没搞清.