mysql存储过程 cast_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 $$