Mysql存储过程示例

最新推荐文章于 2024-04-15 10:15:00 发布

wenxuechaozhe

最新推荐文章于 2024-04-15 10:15:00 发布

阅读量1.6w

点赞数 1

分类专栏： MySQL 文章标签： mysql 存储过程创建 mysql存储过程示例

本文链接：https://blog.csdn.net/wenxuechaozhe/article/details/51815364

版权

MySQL 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

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存储过程进行调用。