mysql存储过程实例

来源：互联网发布：买吉他去淘宝还是天猫编辑：程序博客网时间：2024/05/18 12:42

DELIMITER //create procedure pro_test()begindeclare i int;set i = 0;while i<5 doinsert 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()begindeclare i int;set i = 0;while i<8 doinsert 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.codeFROMcrm_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 THENset returnVar = '纯硬广'; end if ; if LOCATE('2',f_string)>0 THENset returnVar = CONCAT(returnVar,',','品牌栏目类'); end if; if LOCATE('3',f_string)>0 THENset returnVar = CONCAT(returnVar,',','热点事件类'); end if; if LOCATE('4',f_string)>0 THENset returnVar = CONCAT(returnVar,',','视频栏目类'); end if; if LOCATE('5',f_string)>0 THENset returnVar = CONCAT(returnVar,',','自主活动类'); end if; if LOCATE('6',f_string)>0 THENset returnVar = CONCAT(returnVar,',','客户定制'); end if; if LOCATE('7',f_string)>0 THENset returnVar = CONCAT(returnVar,',','大事件'); end if; return returnVar;  end if;  END$$    DELIMITER ; DELIMITER $$

0 0