看代码drop procedure if exists query_news;
delimiter $$
create procedure query_news(IN page_size int,IN page_num int,IN order_key varchar(30),IN order_flag int,OUT num int)
begin
DECLARE start_pos int;
DECLARE seq varchar(5);
DECLARE ps int;
DECLARE ok varchar(30);
set @seq=order_flag;
set @ps=page_size;
set @ok=order_key;
set num=(select count(id) from news);
set @start_pos=page_size*(page_num-1);
if @seq=0 then
PREPARE STMT FROM 'select * from news order by ? desc limit ?,?;';
end if;
if @seq=1 then
PREPARE STMT FROM 'select* from news order by ? asc limit ?,?;';
end if;
EXECUTE STMT USING @ok,@start_pos,@ps;
end
$$
delimiter ;1.这是一个分页的查询存储过程
2.用变量标识排序列和分页参数
3.磁存储过程的int类型的参数1表示升序,0表示降序
4.调用 call query_news(10,1,'id',1,@out);
5.问题是无论第四个int类型的参数怎么变都只能升序排列
6.sql语句经过测试可以显示降序
7.news的建表语句如下create table news
(
id bigint not null auto_increment,
catagory char(3) not null,
pub_time datetime not null,
publisher_id tinyint not null,
title char(50) not null,
content text,
primary key (id)
);
delimiter $$
create procedure query_news(IN page_size int,IN page_num int,IN order_key varchar(30),IN order_flag int,OUT num int)
begin
DECLARE start_pos int;
DECLARE seq varchar(5);
DECLARE ps int;
DECLARE ok varchar(30);
set @seq=order_flag;
set @ps=page_size;
set @ok=order_key;
set num=(select count(id) from news);
set @start_pos=page_size*(page_num-1);
if @seq=0 then
PREPARE STMT FROM 'select * from news order by ? desc limit ?,?;';
end if;
if @seq=1 then
PREPARE STMT FROM 'select* from news order by ? asc limit ?,?;';
end if;
EXECUTE STMT USING @ok,@start_pos,@ps;
end
$$
delimiter ;1.这是一个分页的查询存储过程
2.用变量标识排序列和分页参数
3.磁存储过程的int类型的参数1表示升序,0表示降序
4.调用 call query_news(10,1,'id',1,@out);
5.问题是无论第四个int类型的参数怎么变都只能升序排列
6.sql语句经过测试可以显示降序
7.news的建表语句如下create table news
(
id bigint not null auto_increment,
catagory char(3) not null,
pub_time datetime not null,
publisher_id tinyint not null,
title char(50) not null,
content text,
primary key (id)
);
insert into news(catagory,pub_time,publisher_id,title,content)values('201',now(),1,'开张啦!!!','公司今天开张了,走过路过不要错过');自己顶一下,这是插入语句,希望哪位大神可以帮帮忙啊~~~~
PREPARE STMT FROM @asql;
EXECUTE STMT;