mysql存储过程实例

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