• mysql分页存储过程及事务处理


    MYSQL5.5版本

    /*
    --名称:MYSQL版查询分页存储过程 by peace 2013-8-14
    --输入参数:@fields        -- 要查询的字段用逗号隔开
    --输入参数:@tables        -- 要查询的表
    --输入参数:@where        -- 查询条件
    --输入参数:@orderby    -- 排序字段
    --输出参数:@page        -- 当前页计数从1开始
    --输出参数:@pagesize    -- 每页大小
    --输出参数:@totalcount -- 总记录数
    --输出参数:@pagecount  -- 总页数 
    */
    DROP PROCEDURE IF EXISTS Query_Pagination; 
    CREATE PROCEDURE Query_Pagination
    (
        in _fields varchar(2000),   
        in _tables text, 
        in _where varchar(2000),  
        in _orderby varchar(200),
        in _pageindex int,
        in _pagesize int,
        in _sumfields  varchar(200),/*增加统计字段2013-5-8 peaceli*/
        out _totalcount int ,
        out _pagecount int 
    )
    begin
       set @startRow = _pageSize*(_pageIndex -1);
       set @pageSize = _pageSize;  set @rowindex = 0;
         set @strsql = CONCAT('select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,',_fields,' from ',_tables,case ifnull(_where,'') when '' then '' else concat(' where ',_where) end,' order by ',_orderby,' limit ',@startRow,',',@pageSize);
         prepare strsql from @strsql;
         execute strsql;
       deallocate prepare strsql;
       set _totalcount = found_rows(); 
    
       if (_totalcount <= _pageSize) then
                        set _pagecount = 1;
                        else if (_totalcount % _pageSize > 0) then
                        set _pagecount = _totalcount / _pageSize + 1;
                        else
                         set _pagecount = _totalcount / _pageSize;
                end if;
            end if;
    if(ifnull(_sumfields,'') <> '') then set @sumsql = contact('select ',_sumfields,' from ',_tables,case ifnull(_where,'') when '' then '' else concat(' where ',_where) end); prepare sumsql from @sumsql; execute sumsql; deallocate prepare sumsql; end if; end
    

      

    CREATE PROCEDURE TransTest(in p1 VARCHAR(20),in p2 VARCHAR(50))
    BEGIN
    declare err int default 0;  
     /*如果出现sql异常,则将err设置为1后继续执行后面的操作 */  
    declare continue handler for sqlexception set err=1; -- 出错处理  
    set autocommit = 0;
    insert into sy_queryconfig(syq_id) values(p1); 
    insert into sy_queryconfig(syq_id) values(p2); 
    if err=1 then  
     ROLLBACK;
    ELSE
     COMMIT;
    end if;
    END
    

      

    CREATE PROCEDURE TransTest2(in p1 VARCHAR(20),in p2 VARCHAR(50))
    BEGIN
    /*只要发生异常就回滚*/ 
    declare exit handler for sqlexception 
    BEGIN
        ROLLBACK;
      /*返回异常处理结果等其它操作*/  
    END; 
    START TRANSACTION;
    insert into sy_queryconfig(syq_id) values(p1); 
    insert into sy_queryconfig(syq_id) values(p2); 
    COMMIT;
    END
    

      

  • 相关阅读:
    解决ubuntu中firefox浏览器总是提示找不到server的问题
    Android学习笔记(14):相对布局RelativeLayout
    浅析java(多方面解读)
    做自己
    SGU 261. Discrete Roots (N次剩余)
    hdu1115 Lifting the Stone(几何,求多边形重心模板题)
    Android编码规范
    hdu 3790 最短路径问题
    怎样在gluster的源代码中加入自己的xlator
    处理空列表
  • 原文地址:https://www.cnblogs.com/tongxinyuan/p/4435489.html
Copyright © 2020-2023  润新知