shell 获取mysql存储过程返回值_mysql存储过程 在动态SQL内获取返回值

很简单，不需要使用游标循环来读取，直接select * into @变量就OK了，还可以传递参数来执行。

1

MySql通用分页存储过程23过程参数4p_cloumnsvarchar(500),p_tablesvarchar(100),p_wherevarchar(4000),p_ordervarchar(100),p_pageindexint,p_pagesizeint,out p_recordcountint,out p_pagecountint56$$:begin7declarev_sqlcountsvarchar(4000);8declarev_sqlselectvarchar(4000);9 #拼接查询总记录的SQL语句10setv_sqlcounts=concat('select count(*) into @recordcount from',p_tables,p_where);11 #selectv_sqlcounts;leave $$;12set@sqlcounts=v_sqlcounts;13preparestmtfrom@sqlcounts;14executestmt;15deallocatepreparestmt;16 #获取动态SQL语句返回值17setp_recordcount=@recordcount;18 #根据总记录跳数计算出总页数19setp_pagecount=ceiling((p_recordcount+0.0)/p_pagesize);20ifp_pageindex<1then21setp_pageindex=1;22 elseif p_pageindex>p_pagecountandp_pagecount<>0then23setp_pageindex=p_pagecount;24endif;25 #拼接分页查询记录的动态SQL语句26setv_sqlselect=concat('select',p_cloumns,'from',p_tables,p_where,if(p_orderisnotnull,p_order,''),'limit',(p_pageindex-1)*p_pagesize,',',p_pagesize);27 #selectv_sqlselect;leave $$;28set@sqlselect=v_sqlselect;29preparestmtselectfrom@sqlselect;30executestmtselect;31deallocatepreparestmtselect;32end$$

Sql代码

#拼接查询总记录的SQL语句

setv_sqlcounts = concat('select count(*) into @recordcount from ',v_tables,v_where);

set@sqlcounts := v_sqlcounts;

#预处理动态SQL

preparestmtfrom@sqlcounts;

#传递动态SQL内参数

set@s1= categoryid;

executestmt using @s1;

deallocatepreparestmt;

#获取动态SQL语句返回值

setrecordcount = @recordcount;

#拼接查询总记录的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;

以上我上再做存储过程分页里用到动态SQL里将查询到的count记录条数通过变量@recordcount放到recordcount里面了。

mysql的IF ELSE和其他数据库的判断有点不一样，简单的判断语句如下。

Sql代码

#根据总记录跳数计算出总页数

setpagecount = ceiling((recordcount+0.0)/pagesize);

if pageindex <1then

setpageindex = 1;

elseif pageindex > pagecountthen

setpageindex = pagecount;

else

selectpageindex,pagecount;

endif;

#根据总记录跳数计算出总页数

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;