按條件查詢數(shù)據(jù)
執(zhí)行語句：call pGiftGetList(2,2,7,'id','desc');
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `pGiftGetList`(pstatus int, pageno int, pagesize int,orderby varchar(20), orderrule varchar(4))
BEGIN
declare num int ;
declare gpagesize int ;
declare gpageno int ;
set @gstatus = pstatus;-- 條件
set gpagesize = pagesize;-- 第幾頁
set gpageno = pageno; -- 第幾項
set @gorderby = orderby; -- 排序
set @gorderrule= orderrule; -- 順序/倒敘
set @sql_text = ' select * from carcare.T_CARCARE_GIFT where 1=1 ';
-- 判斷條件是否為空
if @gstatus is not null then
set @sql_text := concat(@sql_text,' and status = ' ,@gstatus );
SELECT count(*) into @total from carcare.T_CARCARE_GIFT where status = @gstatus;
else
SELECT count(*) into @total from carcare.T_CARCARE_GIFT;
END if;
-- 拼接排序語句
set @sql_text := concat(@sql_text, ' order by ',@gorderby );
set @total = @total;
set num = (gpageno-1)*gpagesize;
-- 判斷傳過來的頁數(shù)是否超過數(shù)據(jù)總數(shù).
if @total >= gpageno*gpagesize then
set @sql_text := concat(@sql_text, ' limit ',num,',',gpagesize );
else
if @total%gpagesize = 0 then
set num = ((@total/gpagesize)-1)*gpagesize;
else
set num = floor(@total/gpagesize)*gpagesize;
end if;
set @sql_text := concat(@sql_text, ' limit ',num,',',gpagesize );
end if;
prepare stmt from @sql_text; -- 預處理需要執(zhí)行的動態(tài)SQL，其中stmt是一個變量
EXECUTE stmt; -- 執(zhí)行SQL語句
deallocate prepare stmt;
END
CREATE DEFINER=`root`@`%`
@%是自己有的 不要也可以， 可以改成這樣：CREATE PROCEDURE `pGiftGetList`(pstatus int)
set @gstatus = pstatus;-- 條件 @變量
不加@ 需要 declare gstatus varchar（）；這樣先聲明一下
SELECT count(*) into @total from 中into @total是，就是這個sql執(zhí)行的結果結果就是@total
添加數(shù)據(jù)
insert into carcare.T_CARCARE_GIFT (gategory,title ,imgpath,status , stock , giftintro , costscore, changecount,cdt ) values
(1,'提現(xiàn)到支付寶','TXnormal.png',2,100,'提現(xiàn)200',5000,1500,now());
insert into carcare.T_CARCARE_GIFT (gategory,title ,imgpath,status , stock , giftintro , costscore, changecount,cdt ) values
(2,'提現(xiàn)到支付寶','TXnormal.png',2,100,'提現(xiàn)200',4000,3800,now());
.....
