createdefiner= root@`%`function get_dept_child_ids_func(rootId varchar(1000))returnstextBEGINDECLARE sTemp text;DECLARE sTempChd text;SET sTemp ='';SET sTempChd =CAST(rootId ASCHAR);WHILE sTempChd ISNOTNULLDOSET sTemp = CONCAT(sTemp,',',sTempChd);SELECT GROUP_CONCAT(id)INTO sTempChd FROM department WHERE FIND_IN_SET(parenti_id,sTempChd);ENDWHILE;RETURN SUBSTRING(sTemp,2);END;
4. 存储过程实现循环插入
dropprocedureifexists test;DELIMITER//CREATEPROCEDURE test()BEGINDECLARE i INTDEFAULT0;DECLARE cnt INTDEFAULT0;selectcount(id)into cnt from department where id notin(select department_id from check_work_v2_rule_dept_link where check_work_rule_id ='8a8be68f73f0aec20173f1ae325e00a6')andstatus=0and id notlike'%gk%';WHILE i < cnt DO# 日常考勤insertinto check_work_v2_rule_dept_link (id,check_work_rule_id, department_id)selectREPLACE(UUID(),'-','') cid,'8a8be68f73f0aec20173f1ad13bd0053',id from department where id notin(select department_id from check_work_v2_rule_dept_link # where check_work_rule_id = '8a8be68f73f0aec20173f1ae325e00a6')andstatus=0and id notlike'%gk%'limit1;# 项目考勤insertinto check_work_v2_rule_dept_link (id,check_work_rule_id, department_id)selectREPLACE(UUID(),'-','') cid,'8a8be68f73f0aec20173f1ae325e00a6',id from department where id notin(select department_id from check_work_v2_rule_dept_link # where check_work_rule_id = '8a8be68f73f0aec20173f1ae325e00a6')andstatus=0and id notlike'%gk%'limit1;SET i=i+1;ENDWHILE;select@cnt;END//DELIMITER;