快捷搜索:  as  2018  FtCWSyGV  С˵  test  xxx  Ψһ  w3viyKQx

和记APP_机器人论坛



前段光阴没有给出SQLServer转到和记APPMysql的通用存储历程,本着共享的精神,为大年夜家奉献这段Mysql分页查询通用存储历程,假设所用数据库为guestbook:

use guestbook;

delimiter $$

drop procedure if exists prc_page_result $$

create procedure prc_page_result (

in currpageint,

in columnsvarchar(500),

in tablenamevarchar(500),

in sConditionvarchar(500),

in order_fieldvarchar(100),

in asc_fieldint,

in primary_field var和记APPchar(100),

in pagesizeint

)

begin

declare sTempvarchar(10和记APP00);

declare sSqlvarchar(4000);

declare sOrder varchar(1000);

if asc_field = 1 then

set sOrder = concat(' order by ', order_field, ' desc ');

set sTemp= '(select max';

end if;

if currpage = 1 then

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 sS和记APPql = concat('select ', columns, ' from ', tablename);

set sSql = concat(sS和记APPql, ' 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;

可以存储为数据库脚本,然后用敕令导入:

mysql -u root -p

调用:call prc_page_result(1, "*", "Tablename", "", "columnname", 1, "PKID", 25);

您可能还会对下面的文章感兴趣: