Three mysql paging Stored Procedure instances (1/3)

If you are in mysql version 5 or later, I want to tell you that there are three mysql paging Stored Procedure instances. The stored procedure is supported only after mysql 5.0, now let's take a look at this stored procedure and look at a simple stored procedure.

If you are in mysql tutorial version 5 or later, I want to tell you that there are three mysql paging Stored Procedure instances. The stored procedure is supported only after mysql 5.0, now let's take a look at this stored procedure and look at a simple stored procedure.

* Mssql Stored Procedure

*/

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 stored procedure shared by experts.

Create procedure 'mysqltestuser _ select_pageable '(
_ Whereclause varchar (2000), -- search criteria
_ Orderby varchar (2000), -- sorting Condition
_ Pagesize int, -- number of records per page
_ Pageindex int, -- current page number
_ Docount bit -- flag: Statistical data/output data
)
Not deterministic
SQL security definer
Comment''
Begin
-- Define a temporary key field table
Drop table if exists _ temptable_keyid; -- delete a temporary table.
Create temporary table _ temptable_keyid
(
Userid int
) Type = heap;

 

-- Construct dynamic SQL statements and output the id set of key keywords
-- Search Condition
 

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 = concat (@ SQL, 'ORDER BY', _ orderby );
End if;

-- Prepare to insert the id record to the temporary table
 

Set @ SQL = concat ('insert into _ temptable_keyid (userid) ', @ SQL );
Prepare stmt from @ SQL;
Execute stmt;
Deallocate prepare stmt;

-- Id set of key [end]

-- Below is the output

If (_ docount = 1) then -- statistics
Begin
Select count (*) as recordcount from _ temptable_keyid;
End;
Else -- output record set
Begin
-- Calculate the start position of the record
Set @ startpoint = ifnull (_ pageindex-1) * _ pagesize, 0 );
Set @ SQL = 'select .*
From mysqltestuser
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 ddl of the mysqltestuser table is as follows:

Create table 'mysqltestuser '(
'Userid' int (11) not null auto_increment,
'Name' varchar (50) default null,
'Chinesename' varchar (50) 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') values
(1, 'xu1', 'www. bKjia. c0m', '2017-03-29 12:54:41 ', 2007, 'scription1 '),

1 2 3