mysql存储过程实例

落日映苍穹つ 2021-06-24 14:00 490阅读 0赞

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 $$