mysql函数记录

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;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。