Mysql分页查询通用存储过程
来源:互联网 发布:算法面试题 编辑:程序博客网 时间:2024/06/06 12:13
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);
set sOrder = concat(` order by `, order_field);
if asc_field = 1 then
set sTemp = `<(select min`;
else
set sTemp = `>(select max`;
end if;
if currpage = 0 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, `( private_key )`, ` from (select `);
set sSql = concat(sSql, ` `, primary_field, ` as private_key from `, tablename,` where `,sCondition, sOrder);
set sSql = concat(sSql, ` limit `, (currpage-1)*pagesize,`,`,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, ` as private_key from `, tablename, sOrder);
set sSql = concat(sSql, ` limit `, (currpage-1)*pagesize,`,`,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;
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);
set sOrder = concat(` order by `, order_field);
if asc_field = 1 then
set sTemp = `<(select min`;
else
set sTemp = `>(select max`;
end if;
if currpage = 0 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, `( private_key )`, ` from (select `);
set sSql = concat(sSql, ` `, primary_field, ` as private_key from `, tablename,` where `,sCondition, sOrder);
set sSql = concat(sSql, ` limit `, (currpage-1)*pagesize,`,`,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, ` as private_key from `, tablename, sOrder);
set sSql = concat(sSql, ` limit `, (currpage-1)*pagesize,`,`,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 -u root -p < pageResult.sql;
调用:call prc_page_result(1, "*", "Tablename", "", "columnname", 1, "PKID", 25);
- Mysql分页查询通用存储过程
- Mysql分页查询通用存储过程
- mysql分页查询通用存储过程
- Mysql分页查询通用存储过程
- Mysql分页查询通用存储过程
- 通用分页查询存储过程
- 分页查询存储过程[通用]
- 通用分页查询存储过程
- 通用分页查询存储过程
- MySql 通用分页存储过程
- MYSQL通用分页存储过程
- mysql分页通用存储过程
- mysql通用存储过程分页
- SQLServer分页查询通用存储过程
- 通用分页显示查询存储过程
- SQLServer分页查询通用存储过程
- SQLServer分页查询通用存储过程
- SQL之通用查询分页存储过程
- 必须掌握的八个DOS命令
- 验证与业务逻辑
- CSDN真棒
- 一只蜻蜓
- [SharePoint]异常来自 HRESULT:0x80040E14
- Mysql分页查询通用存储过程
- DateRange.java
- 写程序10大习惯和如何提高编程能力(不知道被转了几百次了找不到原出处了) 【转载】_beyond yourself--思路决定出路
- I come back
- Spring AOP事务配置
- yum的说明文档
- 使用Vs.Net 的一些小技巧
- asp 应用ADODb.Command使用adLongVarChar参数类型保存Access备注字段
- linux基础:配置GRUB的五个参数