mysql 存储过程例子-备忘-逻辑查询并分页显示
delimiter //
CREATE PROCEDURE ClassList(
IN ID int,
fldName varchar(100),
pageSize int,
pageIndex int,
orderType int,
strWhere varchar(2000),
OUT cou int
)
begin
declare beginRow int;
declare sqlStr varchar(1000);
declare limitTemp varchar(1000);
declare orderTemp varchar(1000);
declare v_classp int;
declare oo int;
declare done int default 0;
declare sql1 varchar(500) default ” “;
declare sql2 varchar(200);
declare cur cursor for select shop_classid from shop_class where shop_parentclassid=id;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000′ SET done = 1;
set sql2=’select*from shop_class left join shop_main on shop_classid=shop_type_id where’;
select shop_parentclassid into v_classp from shop_class where shop_classid=id;
if(v_classp=0) then
open cur;
repeat
fetch cur into oo;
IF NOT done THEN
set sql1=concat(’ or shop_classid=’,oo,sql1);
END IF;
UNTIL done END REPEAT;
close cur;
set @sql=concat(sql2,substring(sql1,4,length(sql1)-3));
else
set @sql=concat(’select * from shop_main where shop_type_id=’,id);
end if;
set beginRow = (pageIndex-1)*pageSize;
set limitTemp = CONCAT(’ limit ‘,beginRow,’,',pageSize);
set orderTemp = CONCAT(’ order by ‘,fldName);
if orderType = 0 then
set orderTemp = CONCAT(orderTemp,’ ASC ‘);
else
set orderTemp = CONCAT(orderTemp,’ DESC ‘);
end if;
set @sqlString = CONCAT(@sql,’ ‘,strWhere,orderTemp,limitTemp);
prepare sqlstmt from @sqlString;
execute sqlstmt;
deallocate prepare sqlstmt;
end
