Mysql分页查询通用存储过程
create procedure prc_page_result (
in currpage int,
in columns varchar(500),
in tablename varchar(500),
in sCondition varchar(500),
in order_field varchar(100),
in asc_field int,
in Prima(最完善的虚拟主机管理系统) ry_field varchar(100),
in pagesize int
)
begin
declare sTemp varchar(1000);
declare sSql varchar(4000);
declare sOrder varchar(1000);
if asc_field = 1 then
set sOrder = concat(' order by ', order_field, ' desc ');
set sTemp = '<(select min';
else
set sOrder = concat(' order by ', order_field, ' asc ');
set sTemp = '>(select max';
end if;
if currpage = 1 then
if sCondition <> '' then
set sSql = concat('select ', columns, ' from ', tablename, ' where ');
set sSql = concat(sSql, sCondition, sOrder, ' limit ?');
else
set sSql = concat('select ', columns, ' from ', tablename,万劫合击版本, sOrder, ' limit ?');
end if;
else
if sCondition <> '' then
set sSql = concat('select ', columns, ' from ', tablename);
set sSql = concat(sSql, ' where ', sCondition, ' and ', Prima(最完善的虚拟主机管理系统) ry_field, sTemp);
set sSql = concat(sSql, '(', Prima(最完善的虚拟主机管理系统) ry_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', Prima(最完善的虚拟主机管理系统) ry_field, ' from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
else
set sSql = concat('select ', columns, ' from ', tablename);
set sSql = concat(sSql, ' where ', Prima(最完善的虚拟主机管理系统) ry_field, sTemp);
set sSql = concat(sSql, '(', Prima(最完美的虚构主机治理体系) ry_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', Prima(最完善的虚拟主机管理系统) ry_field, ' from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
end if;
end if;
set @iPageSize = pagesize;
set @sQuery = sSql;
prepare stmt from @sQuery;
execute stmt using @iPageSize;
end;
$
delimiter;
能够存储为数据库脚本，而后用命令导入：MySQL(跟PHP搭配之最佳组合) -u root -p < pageResult.sql;
调用：call prc_page_result(1, "*", "Tablename", "", "columnname", 1, "PKID", 25);
SQLServer分页查询通用存储过程
*procedure name : prcPageResult
* author : FuChun
* create date : 2006-10-04
*/
CREATE PROCEDURE prcPageResult
-- 取得某一页的数据 --
@currPage int = 1, --当前页页码 (即Top currPage)
@showColumn varchar(2000) = '*', --需要得到的字段 (即 column1,column2,......)
@tabName varchar(2000), --需要查看的表名 (即 from table_name)
@strCondition varchar(2000) = '', --查询前提 (即 where condition......) 不必加where要害字
@ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '', --主键名称
@pageSize int = 20 --分页大小
AS
BEGIN -- 存储过程开始
-- 该存储过程需要用到的多少个变量 --
DECLARE @strTemp varchar(1000)
DECLARE @strSql varchar(4000) --该存储过程最后执行的语句
DECLARE @strOrderType varchar(1000) --排序类型语句 (order by column asc或者order by column desc)
BEGIN
IF @bitOrderType = 1 -- bitOrderType=1即执行降序
BEGIN
SET @strOrderType = ' ORDER BY [email='+@ascColumn+']'+@ascColumn+'[/email] DESC'
SET @strTemp = '<(SELECT min'
END
ELSE
BEGIN
SET @strOrderType = ' ORDER BY [email='+@ascColumn+']'+@ascColumn+'[/email] ASC'
SET @strTemp = '>(SELECT max'
END
IF @currPage = 1 -- 假如是第一页
BEGIN
IF @strCondition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' [email='+@showColumn+']'+@showColumn+'[/email] FROM [email='+@tabName]'+@tabName[/email]+
' WHERE [email='+@strCondition+@strOrderType]'+@strCondition+@strOrderType[/email]
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' [email='+@showColumn+']'+@showColumn+'[/email] FROM [email='+@tabName+@strOrderType]'+@tabName+@strOrderType[/email]
END
ELSE -- 其余页
BEGIN
IF @strCondition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' [email='+@showColumn+']'+@showColumn+'[/email] FROM [email='+@tabName]'+@tabName[/email]+
' WHERE [email='+@strCondition+']'+@strCondition+'[/email] AND [email='+@pkColumn+@strTemp+'('+@pkColumn+')'+']'+@pkColumn+@strTemp+'('+@pkColumn+')'+'[/email] FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+
' [email='+@pkColumn+']'+@pkColumn+'[/email] FROM [email='+@tabName+@strOrderType+']'+@tabName+@strOrderType+'[/email]) AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' [email='+@showColumn+']'+@showColumn+'[/email] FROM [email='+@tabName]'+@tabName[/email]+
' WHERE [email='+@pkColumn+@strTemp+'('+@pkColumn+')'+']'+@pkColumn+@strTemp+'('+@pkColumn+')'+'[/email] FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+' [email='+@pkColumn]'+@pkColumn[/email]+
' FROM [email='+@tabName+@strOrderType+']'+@tabName+@strOrderType+'[/email]) AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)
END -- 存储过程停止
------------------------------------------------
GO
调用方式：
prcPageResult 1,'*','TableName','','CreateDate',1,'PkID',25
上面表现，查询表TableName的所有字段,去角质，前25条记录，由于是第一页，排序字段为CreateDate，降序排列，主键是PkID。这个存储过程的功效比拟强盛，用在名目中十分的实用。不信你可以尝尝看，尤其是在百万级数据上，他的上风就露出无疑了，当然，这段代码是可以转换成MySql中的存储过程的，不外，在这里就不给大家了，你们可以本人试着转换看看。
下面的存储进程查询表的记载数：
/**//*----------------------------------------------
*procedure name : prcRowsCount
* author : FuChun
* create date : 2006-09-22
*/
CREATE PROC prcRowsCount
@tabName varchar(200), --须要查问的表名
@colName varchar(200)='*', --需要查询的列名
@condition varchar(200)='' --查询条件
AS
BEGIN
DECLARE @strSql varchar(255)
IF @condition = ''
SET @strSql='select count([email='+@colName+']'+@colName+'[/email]) from [email='+@tabName]'+@tabName[/email]
ELSE
SET @strSql='select count([email='+@colName+']'+@colName+'[/email]) from [email='+@tabName+']'+@tabName+'[/email] where [email='+@condition]'+@condition[/email]
EXEC (@strSql)
END
------------------------------------------------
GO
还有删除记录的通用存储过程和查询单条记录的通用存储过程在这里就不奉送了，感激供给原通用分页查询存储过程的哥们，谢谢。
Oracle分页存储过程
create or replace package PKG_Tools
is
type ResultData is ref cursor;
procedure sp_Page(p_PageSize int, --每页记载数
p_PageNo int, --当前页码,从 1 开始
p_SqlSelect varchar2, --查询语句,含排序局部
p_SqlCount varchar2, --获取记录总数的查询语句
p_OutRecordCount out int,--返回总记录数
p_OutCursor out ResultData);
end PKG_Tools;
/
create or replace package body PKG_Tools
is
procedure sp_Page(p_PageSize int, --每页记录数
p_PageNo int, --当前页码,从 1 开端
p_SqlSelect varchar2, --查询语句,含排序部门
p_SqlCount varchar2, --获取记录总数的查询语句
p_OutRecordCount out int,--返回总记录数
p_OutCursor out ResultData)
as
v_sql varchar2(3000);
v_count int;
v_heiRownum int;
v_lowRownum int;
begin
----取记录总数
execute immediate p_SqlCount into v_count;
p_OutRecordCount := v_count;
----履行分页查询
v_heiRownum := p_PageNo * p_PageSize;
v_lowRownum := v_heiRownum - p_PageSize +1;
v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| p_SqlSelect ||') A
WHERE rownum <= '|| to_char(v_heiRownum) || '
) B
WHERE rn >= ' || to_char(v_lowRownum) ;
--留神对rownum别号的应用,第一次直接用rownum,第二次必定要用别名rn
OPEN p_OutCursor FOR v_sql;
end sp_Page;
end PKG_Tools;
