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 ;

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)
);