如果你正在mysql 5以上版本，我要告訴你這裡有三款 mysql 分頁存儲過程實例哦，存儲過程是mysql 5.0以後才支持的，現在看看這款存儲過程吧，看一款簡單存儲過程
如果你正在mysql教程 5以上版本，我要告訴你這裡有三款 mysql 分頁存儲過程實例哦，存儲過程是mysql 5.0以後才支持的，現在看看這款存儲過程吧，看一款簡單存儲過程
*mssql存儲過程
*/
create definer=`root`@`localhost` procedure `getrecordasp教程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
這是一款高手分享的他的存儲過程
create procedure `mysqltestuser_select_pageable`(
_whereclause varchar(2000), -- 查找條件
_orderby varchar(2000), -- 排序條件
_pagesize int , -- 每頁記錄數
_pageindex int , -- 當前頁碼
_docount bit -- 標志:統計數據/輸出數據
)
not deterministic
sql security definer
comment ' '
begin
-- 定義key字段臨時表
drop table if exists _temptable_keyid; -- 刪除臨時表,如果存在
create temporary table _temptable_keyid
(
userid int
)type=heap;
-- 構建動態的sql，輸出關鍵字key的id集合
-- 查找條件
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;
-- 准備id記錄插入到臨時表
set @sql=concat( 'insert into _temptable_keyid(userid) ', @sql);
prepare stmt from @sql;
execute stmt ;
deallocate prepare stmt;
-- key的id集合 [end]
-- 下面是輸出
if (_docount=1) then -- 統計
begin
select count(*) as recordcount from _temptable_keyid;
end;
else -- 輸出記錄集
begin
-- 計算記錄的起點位置
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;
下面是mysqltestuser表的ddl:
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 into `mysqltestuser` (`userid`, `name`, `chinesename`, `registerdatetime`, `jf`, `description`) values
(1, 'xuu1 ', 'www.bKjia.c0m', '2007-03-29 12:54:41 ',1.5, 'description1 '),
1 2 3
