写了个存储过程实现分页,但是返回结果得不到预期的值,为0....高手看了,帮忙指点一下,看哪里出现了问题~~在线给分!!CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_searchpage`(
IN page int,
IN HZPNum int,
IN HZPType int,
IN HZPStep int,
IN searchkey varchar(100),
In action varchar(100),
OUT pageNum int, /*总页数*/
OUT pageData int /*总记录数*/
)
COMMENT '分页存储过程'
BEGIN
/*定义变量 */
declare recordNum int default 0;
declare begin_row int default 0;
declare page_num int default 0;
/*构造语句*/
set begin_row = (page-1)*HZPNum;
set @count_string = concat('select count(*) into @row_tal from information where HZPType=', HZPType,' and HZPName like \'\%',searchkey,'\%\'');
set @msql = CONCAT('select * from information where HZPType=', HZPType,' and HZPName like \'\%',searchkey,'\%\'');
if HZPStep=0 then
set @msql = concat(@msql,' limit ', begin_row,' , ',HZPNum) ;
set @count_string = concat(@count_string,' limit ', begin_row,' , ',HZPNum);
else
set @msql = concat(@msql,' and HZPStep=',HZPStep,' limit ', begin_row,' , ',HZPNum) ;
set @msql = concat(@msql,' and HZPStep=',HZPStep,' limit ', begin_row,' , ',HZPNum) ;
end if;
set recordNum = @row_tal;
if recordNum mod HZPNum =0 then
set page_num=recordNum/HZPNum;
if page_num=0 then
set page_num=1;
end if;
else
set page_num=recordNum/HZPNum+1;
end if;
/*预处理*/
PREPARE count_stmt FROM @count_string;
execute count_stmt;
deallocate prepare count_stmt;
set pageNum = page_num;
PREPARE count_stmt FROM @count_string;
execute count_stmt;
deallocate prepare count_stmt;
set pageData = recordNum;
prepare main_stmt from @msql;
execute main_stmt;
deallocate prepare main_stmt;
END;
IN page int,
IN HZPNum int,
IN HZPType int,
IN HZPStep int,
IN searchkey varchar(100),
In action varchar(100),
OUT pageNum int, /*总页数*/
OUT pageData int /*总记录数*/
)
COMMENT '分页存储过程'
BEGIN
/*定义变量 */
declare recordNum int default 0;
declare begin_row int default 0;
declare page_num int default 0;
/*构造语句*/
set begin_row = (page-1)*HZPNum;
set @count_string = concat('select count(*) into @row_tal from information where HZPType=', HZPType,' and HZPName like \'\%',searchkey,'\%\'');
set @msql = CONCAT('select * from information where HZPType=', HZPType,' and HZPName like \'\%',searchkey,'\%\'');
if HZPStep=0 then
set @msql = concat(@msql,' limit ', begin_row,' , ',HZPNum) ;
set @count_string = concat(@count_string,' limit ', begin_row,' , ',HZPNum);
else
set @msql = concat(@msql,' and HZPStep=',HZPStep,' limit ', begin_row,' , ',HZPNum) ;
set @msql = concat(@msql,' and HZPStep=',HZPStep,' limit ', begin_row,' , ',HZPNum) ;
end if;
set recordNum = @row_tal;
if recordNum mod HZPNum =0 then
set page_num=recordNum/HZPNum;
if page_num=0 then
set page_num=1;
end if;
else
set page_num=recordNum/HZPNum+1;
end if;
/*预处理*/
PREPARE count_stmt FROM @count_string;
execute count_stmt;
deallocate prepare count_stmt;
set pageNum = page_num;
PREPARE count_stmt FROM @count_string;
execute count_stmt;
deallocate prepare count_stmt;
set pageData = recordNum;
prepare main_stmt from @msql;
execute main_stmt;
deallocate prepare main_stmt;
END;
问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)你的代码测试如下,创建过程没有问题mysql> delimiter //
mysql>
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_searchpage`(
-> IN page int,
-> IN HZPNum int,
-> IN HZPType int,
-> IN HZPStep int,
-> IN searchkey varchar(100),
-> In action varchar(100),
-> OUT pageNum int, /*总页数*/
-> OUT pageData int /*总记录数*/
-> )
-> COMMENT '分页存储过程'
-> BEGIN
-> /*定义变量 */
-> declare recordNum int default 0;
-> declare begin_row int default 0;
-> declare page_num int default 0;
-> /*构造语句*/
-> set begin_row = (page-1)*HZPNum;
-> set @count_string = concat('select count(*) into @row_tal from info
rmation where HZPType=', HZPType,' and HZPName like \'\%',searchkey,'\%\'');
-> set @msql = CONCAT('select * from information where HZPType=', HZPT
ype,' and HZPName like \'\%',searchkey,'\%\'');
-> if HZPStep=0 then
-> set @msql = concat(@msql,' limit ', begin_row,' , ',HZPNum) ;
-> set @count_string = concat(@count_string,' limit ', begin_row,' ,
',HZPNum);
-> else
-> set @msql = concat(@msql,' and HZPStep=',HZPStep,' limit ', begin
_row,' , ',HZPNum) ;
-> set @msql = concat(@msql,' and HZPStep=',HZPStep,' limit ', begin
_row,' , ',HZPNum) ;
-> end if;
->
-> set recordNum = @row_tal;
-> if recordNum mod HZPNum =0 then
-> set page_num=recordNum/HZPNum;
-> if page_num=0 then
-> set page_num=1;
-> end if;
-> else
-> set page_num=recordNum/HZPNum+1;
-> end if;
->
-> /*预处理*/
-> PREPARE count_stmt FROM @count_string;
-> execute count_stmt;
-> deallocate prepare count_stmt;
-> set pageNum = page_num;
->
-> PREPARE count_stmt FROM @count_string;
-> execute count_stmt;
-> deallocate prepare count_stmt;
-> set pageData = recordNum;
->
-> prepare main_stmt from @msql;
-> execute main_stmt;
-> deallocate prepare main_stmt;
-> END;
->
-> //
Query OK, 0 rows affected (0.39 sec)mysql>
检查一下结果