BEGIN
DECLARE _isexists1 INT;
DECLARE _depid INT;
DECLARE _userid INT;
DECLARE _done int DEFAULT 0;
DECLARE cur_SaleDepas CURSOR FOR
SELECT Department_ID FROM Department WHERE CORPRATION_ID=1 OR CORPRATION_ID=5 AND Department_Validity=1 AND SALE_FLAG=1;
DECLARE cur_preSalSpecUsers CURSOR FOR
SELECT User_ID FROM UserT WHERE User_Name IN ('1','2');
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
OPEN cur_SaleDepas;
out_loop:LOOP
#循环销售部门
FETCH cur_SaleDepas INTO _depid;
IF _done = 1 THEN
LEAVE out_loop;
END IF;
OPEN cur_preSalSpecUsers;
inner_loop:LOOP
FETCH cur_preSalSpecUsers INTO _userid;
#循环特殊人员列表
IF _done = 1 THEN
LEAVE inner_loop;
END IF;
SELECT ID INTO _isexists1 FROM T_PRESALE_PERMISSION WHERE USER_ID=_userid AND DEPT_ID=_depid LIMIT 1;
IF(_isexists1 IS NULL) THEN
INSERT INTO T_PRESALE_PERMISSION (USER_ID,DEPT_ID) VALUES (_userid,_depid);
END IF;
END LOOP inner_loop;
CLOSE cur_preSalSpecUsers;
SET _done=0;
END LOOP out_loop;
CLOSE cur_SaleDepas;
END
mysql 存储过程 - 循环嵌套
最新推荐文章于 2023-11-24 16:29:59 发布