MySQL function创建

MySQL创建function示例

DELIMITER //
CREATE FUNCTION `getNewGoods` ()
RETURNS INTEGER
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
	CREATE TEMPORARY TABLE temp_newgoods SELECT * FROM (
	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 4) as tb2) as goodstable;
elseif count = 1 then 
	CREATE TEMPORARY TABLE temp_newgoods SELECT * FROM (
	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) as goodstable;
elseif count = 2 then 
	CREATE TEMPORARY TABLE temp_newgoods SELECT * FROM (
	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) as goodstable;
elseif count = 3 then 
	CREATE TEMPORARY TABLE temp_newgoods SELECT * FROM (
	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) as goodstable;
else 
	CREATE TEMPORARY TABLE temp_newgoods SELECT * 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 goodstable;
end if;
RETURN temp_newgoods;
END

创建结束后，直接select进行调用即可。

查看存储过程创建的示例：http://blog.csdn.net/wenxuechaozhe/article/details/51815364