DELIMITER // create procedure pro_test() begin declare i int; set i = 0; while i<5 do insert into test select i+1,name from crm_contact where id=i+886; set i=i+1; end while; end; // DELIMITER ;
DELIMITER // create procedure pro_test() begin declare i int; set i = 0; while i<8 do insert into crm_right select i+824,crm_right.name, crm_right.rule, crm_right.action, crm_right.type, crm_right.parent_id, crm_right.scope, crm_right.code FROM crm_right where id = i+80; set i=i+1; end while; end; // DELIMITER ;
DELIMITER $$ DROP FUNCTION IF EXISTS `crm`.`func_get_split_string_total`$$ CREATE FUNCTION `func_get_split_string_total`( f_string varchar(1000),f_delimiter varchar(5) ) RETURNS int(11) BEGIN declare returnInt int(11); if length(f_delimiter)=2 then return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')))/2; else return 1+(length(f_string) - length(replace(f_string,f_delimiter,''))); end if; END$$ DELIMITER ; DELIMITER $$
DELIMITER $$ DROP FUNCTION IF EXISTS `crm`.`func_get_split_string_total`$$ CREATE FUNCTION `func_get_split_string_total`( f_string VARCHAR(1000),f_delimiter VARCHAR(5) ) RETURNS INT(11) BEGIN DECLARE returnInt INT(11); IF LENGTH(f_delimiter)=2 THEN RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,'')))/2; ELSE RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,''))); END IF; END$$ DELIMITER ;
DELIMITER $$ DROP FUNCTION IF EXISTS `crm`.`func_getParentList`$$ CREATE FUNCTION `func_getParentList`(rootId INT) RETURNS varchar(1000) BEGIN DECLARE sParentList varchar(1000); DECLARE sParentTemp varchar(1000); SET sParentTemp =cast(rootId as CHAR); WHILE sParentTemp is not null DO IF (sParentList is not null) THEN SET sParentList = concat(sParentTemp,',',sParentList); ELSE SET sParentList = concat(sParentTemp); END IF; SELECT group_concat(parent_id) INTO sParentTemp FROM crm_department where FIND_IN_SET(id,sParentTemp)>0; END WHILE; RETURN sParentList; END$$ DELIMITER ; select getParentList(6) id;
DELIMITER $$ DROP FUNCTION IF EXISTS `crm`.`func_getChildList`$$ CREATE FUNCTION `getChildList`(rootId INT) RETURNS varchar(1000) BEGIN DECLARE sChildList VARCHAR(1000); DECLARE sChildTemp VARCHAR(1000); SET sChildTemp =cast(rootId as CHAR); WHILE sChildTemp is not null DO IF (sChildList is not null) THEN SET sChildList = concat(sChildList,',',sChildTemp); ELSE SET sChildList = concat(sChildTemp); END IF; SELECT group_concat(id) INTO sChildTemp FROM crm_department where FIND_IN_SET(parent_id,sChildTemp)>0; END WHILE; RETURN sChildList; END$$ DELIMITER ; select getParentList(6) id; select * from crm_department where FIND_IN_SET(id, getChildList(1));
DELIMITER $$ DROP FUNCTION IF EXISTS `crm`.`func_get_split_name`$$ CREATE FUNCTION `func_get_split_name`( f_string varchar(20)) RETURNS varchar(200) BEGIN declare returnVar varchar(200); if length(f_string)=0 then return ''; else set returnVar = ''; if LOCATE('1',f_string)>0 THEN set returnVar = '纯硬广'; end if ; if LOCATE('2',f_string)>0 THEN set returnVar = CONCAT(returnVar,',','品牌栏目类'); end if; if LOCATE('3',f_string)>0 THEN set returnVar = CONCAT(returnVar,',','热点事件类'); end if; if LOCATE('4',f_string)>0 THEN set returnVar = CONCAT(returnVar,',','视频栏目类'); end if; if LOCATE('5',f_string)>0 THEN set returnVar = CONCAT(returnVar,',','自主活动类'); end if; if LOCATE('6',f_string)>0 THEN set returnVar = CONCAT(returnVar,',','客户定制'); end if; if LOCATE('7',f_string)>0 THEN set returnVar = CONCAT(returnVar,',','大事件'); end if; return returnVar; end if; END$$ DELIMITER ; DELIMITER $$