MySQL存储过程 在动态SQL内获取返回值的方法分析

这篇文章主要为大家详细介绍了MySQL存储过程 在动态SQL内获取返回值的方法分析，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

p_cloumns varchar(500),p_tables varchar(100),p_where varchar(4000),p_order varchar(100),p_pageindex int,p_pagesize int,out p_recordcount int,out p_pagecount int

$:begin
 declare v_sqlcounts varchar(4000);
 declare v_sqlselect varchar(4000);
#拼接查询总记录的SQL语句
 set v_sqlcounts = concat('select count(*) into @recordcount from ',p_tables,p_where);
 #select v_sqlcounts;leave $;
 set @sqlcounts = v_sqlcounts;
 prepare stmt from @sqlcounts;
 execute stmt;
 deallocate prepare stmt;
 #获取动态SQL语句返回值
 set p_recordcount = @recordcount;
 #根据总记录跳数计算出总页数
 set p_pagecount = ceiling((p_recordcount+0.0)/p_pagesize);
 if p_pageindex <1 then
 set p_pageindex = 1;
 elseif p_pageindex > p_pagecount and p_pagecount <> 0 then
 set p_pageindex = p_pagecount;
 end if;
 #拼接分页查询记录的动态SQL语句
 set v_sqlselect = concat('select ',p_cloumns,' from ',p_tables,p_where,if(p_order is not null,p_order,''),' limit ',(p_pageindex-1)*p_pagesize,' , ',p_pagesize);
 #select v_sqlselect;leave $;
 set @sqlselect = v_sqlselect;
 prepare stmtselect from @sqlselect;
 execute stmtselect;
 deallocate prepare stmtselect;
 end $

代码如下:

#拼接查询总记录的SQL语句 
set v_sqlcounts = concat('select count(*) into @recordcount from ',v_tables,v_where); 
set @sqlcounts := v_sqlcounts; 
#预处理动态SQL 
prepare stmt from @sqlcounts; 
#传递动态SQL内参数 
set @s1= categoryid; 
execute stmt using @s1; 
deallocate prepare stmt; 
#获取动态SQL语句返回值 
set recordcount = @recordcount; 

代码如下:

#根据总记录跳数计算出总页数 
set pagecount = ceiling((recordcount+0.0)/pagesize); 
if pageindex <1 then 
 set pageindex = 1; 
elseif pageindex > pagecount then 
 set pageindex = pagecount; 
else 
 select pageindex,pagecount; 
end if; 

注：关于MySQL存储过程 在动态SQL内获取返回值的方法分析的内容就先介绍到这里，更多相关文章的可以留意