mysql函数记录

mysql函数:

DELIMITER $$
--  SET GLOBAL log_bin_trust_function_creators = 1;
 -- V_SN SN号   V_UNITID 广告位
CREATE  FUNCTION  fetch_advert( V_SN VARCHAR(20),V_UNITID VARCHAR(20))
   RETURNS VARCHAR(255)
   
    BEGIN

	DECLARE v_result VARCHAR(255) DEFAULT '成功执行';
     
	DECLARE v_fetch_no VARCHAR(255) DEFAULT '';  -- sn投放区域
     
	DECLARE v_launch_area VARCHAR(255) DEFAULT '';  -- sn投放区域
	
     DECLARE v_materials_count INTEGER DEFAULT 1;   -- 当前位置需要物料数量
     DECLARE v_curarea_count INTEGER DEFAULT 0;    -- 当前区域匹配广告数量
     
      DECLARE v_limit INTEGER DEFAULT 1;   -- 当前limit值
      
      SELECT CONCAT(DATE_FORMAT(NOW(), '%Y-%m-%d-%H:%i:%s'),'-',(FLOOR(RAND()*100000))) INTO v_fetch_no FROM DUAL ;
     
      SELECT launch_area INTO v_launch_area FROM advert_media  WHERE media_outreach=V_SN;  -- 第一步查sn的投放区域
      SELECT materials_count INTO v_materials_count FROM advert_site  WHERE advert_place=V_UNITID;  -- 第一步查sn的投放区域
  
     
    SET v_result = CONCAT(v_result,'，物料数量位',v_materials_count);
    
     
      -- 查当投放当前区域的广告组
 INSERT INTO   advert_fetch_temp(fetch_no,sn,unitid,advert_group_no,advert_id,image,imgCheckCode,ad_link)   
SELECT v_fetch_no,V_SN,V_UNITID,a.advert_group_no,b.id,image,imgCheckcode,ad_link  FROM advert_setmeal a JOIN advert_manage b ON a.advert_group_no=b.advert_setmeal_id 
JOIN advert_site c ON b.site_no = c.advert_site_no
WHERE  a.setmeal_status IN (2,4) AND  a.takeeffect_areaids= v_launch_area  AND a.reserve_fields4>=b.advert_amount
AND b.image != "" AND b.image IS NOT NULL AND c.advert_place=V_UNITID ORDER BY b.advert_amount DESC LIMIT v_materials_count;
    
     SELECT COUNT(1) INTO v_curarea_count FROM advert_fetch_temp  WHERE fetch_no=v_fetch_no;
     
IF  
	v_curarea_count < v_materials_count  THEN 
	 
	SET v_limit = v_materials_count-v_curarea_count;  -- limit值
	-- 查当投放其他区域当前位置的广告
 INSERT INTO   advert_fetch_temp(fetch_no,sn,unitid,advert_group_no,advert_id,image,imgCheckCode,ad_link)   
SELECT v_fetch_no,V_SN,V_UNITID,a.advert_group_no,b.id,image,imgCheckcode,ad_link  FROM advert_setmeal a JOIN advert_manage b ON a.advert_group_no=b.advert_setmeal_id 
JOIN advert_site c ON b.site_no = c.advert_site_no
WHERE   a.setmeal_status IN (2,4)  AND  a.takeeffect_areaids != v_launch_area AND a.reserve_fields4>=b.advert_amount
AND b.image != "" AND b.image IS NOT NULL AND c.advert_place=V_UNITID ORDER BY b.advert_amount DESC LIMIT v_limit;

END IF;
    
    RETURN v_fetch_no;
    
    END $$
	
DELIMITER ;

执行函数之前需要运行命令:

SET GLOBAL log_bin_trust_function_creators = 1;