Home > Database > Mysql Tutorial > mysql存储过程 在动态SQL内获取返回值的方法详解_MySQL

mysql存储过程 在动态SQL内获取返回值的方法详解_MySQL

WBOY
Release: 2016-06-01 13:24:51
Original
1037 people have browsed it

bitsCN.com MySql通用分页存储过程
过程参数

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        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; 

#拼接查询总记录的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和其他数据库的判断有点不一样,简单的判断语句如下。

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

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

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template