MySQL存储过程创建示例

DELIMITER //
CREATE PROCEDURE 
BEGIN 
declare count int;
SELECT count(*) into count 
FROM goods gd 
inner join recommend rec on rec.object_id = gd.goods_id 
where 1=1 and info_state='1' and is_del='1' and is_up='0' and rec.type_code = '33';

if count = 0 then
	select tb2.* from (SELECT goods_id,gd.goods_name,goods_no,list_img as img,list_img 
	FROM goods gd 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and gd.goods_id not in (
	SELECT goods_id 
	FROM goods gd 
	inner join recommend rec on rec.object_id = gd.goods_id 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and rec.type_code = '33' )
	order by gd.in_date desc limit 4) as tb2;
elseif count = 1 then 
	select tb1.* from (SELECT goods_id,rec.title as goods_name,goods_no,rec.img,list_img 
	FROM goods gd 
	inner join recommend rec on rec.object_id = gd.goods_id 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and rec.type_code = '33' 
	order by rec.sort) as tb1 
	union 
	select tb2.* from (SELECT goods_id,gd.goods_name,goods_no,list_img as img,list_img 
	FROM goods gd 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and gd.goods_id not in (
	SELECT goods_id 
	FROM goods gd 
	inner join recommend rec on rec.object_id = gd.goods_id 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and rec.type_code = '33' )
	order by gd.in_date desc limit 3) as tb2;
elseif count = 2 then 
	select tb1.* from (SELECT goods_id,rec.title as goods_name,goods_no,rec.img,list_img 
	FROM goods gd 
	inner join recommend rec on rec.object_id = gd.goods_id 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and rec.type_code = '33' 
	order by rec.sort) as tb1 
	union 
	select tb2.* from (SELECT goods_id,gd.goods_name,goods_no,list_img as img,list_img 
	FROM goods gd 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and gd.goods_id not in (
	SELECT goods_id 
	FROM goods gd 
	inner join recommend rec on rec.object_id = gd.goods_id 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and rec.type_code = '33' )
	order by gd.in_date desc limit 2) as tb2;
elseif count = 3 then 
	select tb1.* from (SELECT goods_id,rec.title as goods_name,goods_no,rec.img,list_img 
	FROM goods gd 
	inner join recommend rec on rec.object_id = gd.goods_id 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and rec.type_code = '33' 
	order by rec.sort) as tb1 
	union 
	select tb2.* from (SELECT goods_id,gd.goods_name,goods_no,list_img as img,list_img 
	FROM goods gd 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and gd.goods_id not in (
	SELECT goods_id 
	FROM goods gd 
	inner join recommend rec on rec.object_id = gd.goods_id 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and rec.type_code = '33' )
	order by gd.in_date desc limit 1) as tb2;
else 
	SELECT goods_id,rec.title as goods_name,goods_no,rec.img,list_img 
	FROM goods gd 
	inner join recommend rec on rec.object_id = gd.goods_id 
	where 1=1 and info_state='1' and is_del='1' and is_up='0' and rec.type_code = '33' 
	order by rec.sort;
end if;
END

创建结束后，进行call存储过程进行调用。

call getNewGoods();