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,河北物业http://www.hbwye.roboo.com/,
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,郴州房地产http://www.czfdc.roboo.com, ')', ' 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", "",中国义齿网http://www.cnycw.roboo.com/, "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中的存储过程的，不外，在这里就不给大家了，你们可以本人试着转换看看。
下面的存储过程查询表的记录数：

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;