MySQL Common paging stored procedure
Process Parameters
Copy Code code as follows:
P_cloumns varchar, p_tables varchar (MB), P_where varchar (4000), P_order varchar (MB), 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 are not null,p_order, '), ' limit ', (P_PA geindex-1) *p_pagesize, ', ', p_pagesize);
#select V_sqlselect;leave $;
Set @sqlselect = V_sqlselect;
Prepare stmtselect from @sqlselect;
Execute stmtselect;
deallocate prepare stmtselect;
End $
Copy Code code as follows:
#拼接查询总记录的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;
Above I do in the stored procedure paging in dynamic SQL in the query to the count of the number of record bars through the variable @recordcount into the RecordCount inside.
MySQL's if else and the other database's judgment is somewhat different, the simple judgment statement is as follows.
Copy Code code as follows:
#根据总记录跳数计算出总页数
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;
#根据总记录跳数计算出总页数 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;
