mysql 教程 存储过程
程序员文章站
2023-11-24 08:13:40
不过在网上找了一些,发现都有一个特点——就是不能传出总记录数,干脆自己研究吧。终于,算是搞出来了,效率可能不是很好,但是我也觉得不错了。贴代码吧直接:也算是对自己学习mys...
不过在网上找了一些,发现都有一个特点——就是不能传出总记录数,干脆自己研究吧。终于,算是搞出来了,效率可能不是很好,但是我也觉得不错了。贴代码吧直接:也算是对自己学习mysql的一个记录。
create procedure p_pagelist
(
m_pageno int ,
m_perpagecnt int ,
m_column varchar(1000) ,
m_table varchar(1000) ,
m_condition varchar(1000),
m_orderby varchar(200) ,
out m_totalpagecnt int
)
begin
set @pagecnt = 1; -- 总记录数
set @limitstart = (m_pageno - 1)*m_perpagecnt;
set @limitend = m_perpagecnt;
set @sqlcnt = concat('select count(1) into @pagecnt from ',m_table); -- 这条语句很关键,用来得到总数值
set @sql = concat('select ',m_column,' from ',m_table);
if m_condition is not null and m_condition <> '' then
set @sql = concat(@sql,' where ',m_condition);
set @sqlcnt = concat(@sqlcnt,' where ',m_condition);
end if;
if m_orderby is not null and m_orderby <> '' then
set @sql = concat(@sql,' order by ',m_orderby);
end if;
set @sql = concat(@sql, ' limit ', @limitstart, ',', @limitend);
prepare s_cnt from @sqlcnt;
execute s_cnt;
deallocate prepare s_cnt;
set m_totalpagecnt = @pagecnt;
prepare record from @sql;
execute record;
deallocate prepare record;
end
复制代码 代码如下:
create procedure p_pagelist
(
m_pageno int ,
m_perpagecnt int ,
m_column varchar(1000) ,
m_table varchar(1000) ,
m_condition varchar(1000),
m_orderby varchar(200) ,
out m_totalpagecnt int
)
begin
set @pagecnt = 1; -- 总记录数
set @limitstart = (m_pageno - 1)*m_perpagecnt;
set @limitend = m_perpagecnt;
set @sqlcnt = concat('select count(1) into @pagecnt from ',m_table); -- 这条语句很关键,用来得到总数值
set @sql = concat('select ',m_column,' from ',m_table);
if m_condition is not null and m_condition <> '' then
set @sql = concat(@sql,' where ',m_condition);
set @sqlcnt = concat(@sqlcnt,' where ',m_condition);
end if;
if m_orderby is not null and m_orderby <> '' then
set @sql = concat(@sql,' order by ',m_orderby);
end if;
set @sql = concat(@sql, ' limit ', @limitstart, ',', @limitend);
prepare s_cnt from @sqlcnt;
execute s_cnt;
deallocate prepare s_cnt;
set m_totalpagecnt = @pagecnt;
prepare record from @sql;
execute record;
deallocate prepare record;
end