Mysql存储过程示例

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