Three MySQL paging stored procedure example (1/3)

If you are in the MySQL tutorial version 5 above, I want to tell you there are three MySQL paging stored process instances Oh, the stored procedure is MySQL 5.0 after the support, now look at this stored procedure, look at a simple stored procedure

*mssql Stored Procedures

*/

Create definer= ' root ' @ ' localhost ' procedure ' getrecordasp Tutorial age ' (
In Tbname varchar (100),
Fldname varchar (100),
pagesize int,
pageindex int,
OrderType int,
Strwhere varchar (2000)
)
Begin
declare beginrow int;
DECLARE sqlstr varchar (1000);
DECLARE limittemp varchar (1000);
DECLARE ordertemp varchar (1000);
Set Beginrow = (pageindex-1) *pagesize;
Set sqlstr = concat (' select * from ', tbname);
Set limittemp = concat (' Limit ', Beginrow, ', ', pagesize);
Set ordertemp = Concat (' ORDER by ', fldname);
If OrderType = 0 Then
Set ordertemp = Concat (ordertemp, ' ASC ');
Else
Set ordertemp = Concat (ordertemp, ' desc ');
End If;

Set @sqlstring = Concat (Sqlstr, ', strwhere,ordertemp,limittemp);

Prepare sqlstmt from @sqlstring;
Execute sqlstmt;
deallocate prepare sqlstmt;

End

Create definer= ' root ' @ ' localhost ' procedure ' GetRecordCount ' (
In Tbname varchar (20),
In strwhere varchar (20)
)
Begin
If strwhere!= "" Then
Set @strsql =concat (' Select COUNT (*) from ', Tbname, ' where ', strwhere);
Else
Set @strsql =concat (' Select COUNT (*) from ', tbname);
End If;
Prepare sqlstmt from @strsql;
Execute sqlstmt;
deallocate prepare sqlstmt;
End

This is a master share of his stored procedures

CREATE PROCEDURE ' Mysqltestuser_select_pageable ' (
_whereclause varchar (2000),--Find conditions
_orderby varchar (2000),--Sorting criteria
_pagesize int,--Number of records per page
_pageindex int,--current page number
_docount bit--flag: Statistic data/output data
)
Not deterministic
SQL Security Definer
Comment '
Begin
--Define key field temp table
drop table if exists _temptable_keyid; --Delete temporary tables, if present
Create temporary table _temptable_keyid
(
UserID int
) Type=heap;

--Build dynamic SQL and output the ID collection of key keys
--Find conditions

set   @sql    =   ' select     userid   from   Mysqltestuser ';
  if   (_whereclause   is   not   null)      and    (_whereclause   <>   ')    then
  set   @sql =    concat (@sql,   '    where   '   , _whereclause);
  end   if;

  if   (_orderby   is   not   null)      and      (_orderby   <> ')    then
  set   @sql = & nbsp Concat (   @sql   ,   '    order   by   '   ,    _orderby);
  end   if;

--Prepares ID records for insertion into temporary tables

set @sql =concat (' INSERT INTO _temptable_keyid (userid) ', @sql);
Prepare stmt from @sql;
Execute stmt;
deallocate prepare stmt;

--The ID set of key [end]

--Here is the output

if (_docount=1) then--statistics
Begin
Select COUNT (*) as RecordCount from _temptable_keyid;
End
Else--output recordset
Begin
--Calculates the starting point of a record
Set @startpoint = Ifnull ((_pageindex-1) *_pagesize,0);
Set @sql = ' Select A.*
From Mysqltestuser A
INNER JOIN _temptable_keyid b
On A.userid =b.userid ';

Set @sql =concat (@sql, "limit", @startpoint, ",", _pagesize);
Prepare stmt from @sql;
Execute stmt;
deallocate prepare stmt;
End
End If;

drop table _temptable_keyid;
End

The following are the DDL for the Mysqltestuser table:

CREATE TABLE ' Mysqltestuser ' (
' userid ' int (one) not NULL auto_increment,
' name ' varchar default NULL,
' chinesename ' varchar default NULL,
' registerdatetime ' datetime default NULL,
' JF ' decimal (20,2) default NULL,
' Description ' Longtext,
Primary KEY (' UserID ')
) Engine=innodb default charset=gb2312;

Insert some data:

Insert INTO ' mysqltestuser ' (' userid ', ' name ', ' Chinesename ', ' registerdatetime ', ' JF ', ' description ') v Alues
(1, ' xuu1 ', ' www.111cn.net ', ' 2007-03-29 12:54:41 ', 1.5, ' description1 '),

Home 1 2 3 last