【www.gdgbn.com--Mssql】

如果你正在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.111cn.net", "2007-03-29   12:54:41 ",1.5, "description1 "),
  

 
存储过程调用测试:

--   方法原型     `mysqltestuser_select_pageable`(条件,排列顺序,每页记录数,第几页,是否统计数据)
--   call   `mysqltestuser_select_pageable`(_whereclause   ,_orderby   ,_pagesize   ,_pageindex   ,   _docount)

--   统计数据
call   `mysqltestuser_select_pageable`(null,   null,   null,   null,   1)
--   输出数据,没条件限制,10条记录/页,第一页
call   `mysqltestuser_select_pageable`(null,   null,   10,   1,0)
--   输出数据,条件限制,排列,   10条记录/页,第一页
call   `mysqltestuser_select_pageable`( "chinesename   like   " "%飞3% " " ",   "userid   asc ",   10,   1,   0)

mysql存储过程二

mysql测试版本：5.0.41-community-nt
/*****************************************************
mysql分页存储过程
吴剑 2009-07-02
*****************************************************/

drop procedure if exists pr_pager;
create procedure pr_pager(

    in     p_table_name        varchar(1024),        /*表名*/
    in     p_fields            varchar(1024),        /*查询字段*/
    in     p_page_size            int,                /*每页记录数*/
    in     p_page_now            int,                /*当前页*/
    in     p_order_string        varchar(128),        /*排序条件(包含order关键字,可为空)*/
    in     p_where_string        varchar(1024),        /*where条件(包含where关键字,可为空)*/
     out     p_out_rows            int                    /*输出记录总数*/
   
)
    not deterministic
     sql security definer
     comment "分页存储过程"
   
begin

    /*定义变量*/
    declare m_begin_row int default 0;
    declare m_limit_string char(64);

    /*构造语句*/   
    set m_begin_row = (p_page_now - 1) * p_page_size;
    set m_limit_string = concat(" limit ", m_begin_row, ", ", p_page_size);
   
    set @count_string = concat("select count(*) into @rows_total from ", p_table_name, " ", p_where_string);
    set @main_string = concat("select ", p_fields, " from ", p_table_name, " ", p_where_string, " ", p_order_string, m_limit_string);

    /*预处理*/
    prepare count_stmt from @count_string;
    execute count_stmt;
    deallocate prepare count_stmt;
    set p_out_rows = @rows_total;

    prepare main_stmt from @main_string;
    execute main_stmt;
    deallocate prepare main_stmt;
   
end;

第2个例子

php教程
$server="localhost";
$ua="root";
$pwd="123456";
$conn=mysql_connect($server,$ua,$pwd,1,131072); //后面比平时多了1,131072表示本地化,这样才能返回记录集
mysql_select_db("guestbook");
mysql_query("set names "gbk"");
//$re=mysql_query("select * from guestbook order by id desc");
$re=mysql_query("call prc_page_result(1, "name,time", "guestbook", "id<>0", "id", 1, "id", 2);");

while($row=mysql_fetch_assoc($re)){
   echo $row["name"]."
";
   echo $row["time"]."
";

}
mysql_close();
?>

 

use guestbook;
delimiter $$
drop procedure if exists prc_page_result $$
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 primary_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 ", primary_field, stemp);
            set ssql = concat(ssql, "(", primary_field, ")", " from (select ");
            set ssql = concat(ssql, " ", primary_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 ", primary_field, stemp);
            set ssql = concat(ssql, "(", primary_field, ")", " from (select ");
            set ssql = concat(ssql, " ", primary_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;

//调用

call prc_page_result(1, "字段列表", "表名", "条件", "排序字段", 1, "id", 25);
参数说明:

 

本文来源：http://www.gdgbn.com/shujuku/27559/