公司项目的业务系统中,没有使用公司的工作流,使用标志位来玩。所有就牵涉到删除代办。不说了,直接看代码。
1、在删除的时候我想使用mysql的split函数分割,后来发现没有这个函数,只好自己写一个了。
/**********返回数量***************/
create function getSplitCount(splitString varchar(4000),splitChar varchar(20))
returns int
begin
return (1+(length(splitString) - length(replace(splitString,splitChar,''))));
end;
/**********获取值******************/
create function getSplitValue(splitString varchar(4000),splitChar varchar(20),saplit_count int)
returns varchar(400)
begin
return reverse(substring_index(reverse(substring_index(splitString,splitChar,saplit_count)),splitChar,1));
end;
2、删除代办
/********* 删除代办的存储过程 **********/
drop procedure IF EXISTS delete_process_task_proc;
create procedure delete_process_task_proc (in parm_taskid varchar(32))
begin
declare parm_task_id varchar(4000);
set parm_task_id = parm_taskid;
call delete_process_task_proc(parm_task_id);
call delete_down_process_task_proc(parm_task_id);
end;
/******* 删除上级代办代办的存储过程 *******/
drop procedure IF EXISTS delete_up_process_task_proc;
create procedure delete_up_process_task_proc (parm_taskid varchar(32))
begin
declare parm_up_taskid varchar(4000);
declare parm_branch_count int(11);
/**** 查询上级代办****/
select up_taskid,branch_count into parm_up_taskid,parm_branch_count from zjxf_process_task where taskid = (
select task_id from zjxf_process_task where taskid = parm_taskid
);
if(parm_branch_count = 1) then /** 说明没有分支 **/
delete from zjxf_process_task where taskid = parm_taskid;
call delete_up_process_task_proc(parm_up_taskid);
else /** 说明有分支 **/
delete from zjxf_process_task where taskid = parm_taskid;
update zjxf_process_task set branch_count = (parm_branch_count - 1) where taskid = parm_next_taskid;
end if;
end;
/************删除下级代办的存储过程*********************/
drop procedure IF EXISTS delete_down_process_task_proc;
create procedure delete_down_process_task_proc (parm_taskid varchar(32))
begin
declare parm_down_taskid varchar(4000);
declare split_value varchar(4000);
declare split_count int;
declare i int default 0;
select down_taskid into parm_down_taskid from zjxf_process_task where taskid = parm_taskid;
if(parm_down_taskid <> '' || parm_down_taskid <> null) then
set split_count = (1+(length(parm_down_taskid) - length(replace(parm_down_taskid,',',''))));
while i < split_count do
set split_value = reverse(substring_index(reverse(substring_index(parm_down_taskid,',',i)),',',1));
delete from zjxf_process_task where taskid = split_value;
call delete_down_process_task_proc(split_value);
set i = i+1;
end while;
end if;
end;