Oracle 游标用 For 循环比较简单, MySQL 也是最近才开始用, 感觉稍微麻烦一点, 下边直接上代码:
- -----------------------------------------------------------
- -- Oracle
-- 内嵌游标为带参游标, 参数为外游标值
- -----------------------------------------------------------
- DECLARE
- cursor cur_outer is select dept_id from tbl_test_dept;
- cursor cur_inner(deptid varchar2) is (SELECT user_id FROM tbl_test_user WHERE dept_id=deptid);
- BEGIN
- FOR DEPT_ITEM IN cur_outer LOOP
- --
- --
FOR KEY_ITEM IN cur_process(DEPT_ITEM.DEPT_ID) LOOP -- 开始内循环
- --
- --
- END LOOP;
- END LOOP;
- commit;
- END;
- ------------------------------
- -- Mysql
-- HANDLER 只能申明一个
-- 内循环结束后需要重置 done
-- 发现 mysql 不能直接执行 begin..end, 需要创建存储过程后调用执行;
- ------------------------------
- CREATE PROCEDURE `PROC_CURSOR_TEST`()
- BEGIN
- DECLARE done INT DEFAULT FALSE;
- DECLARE item_outer VARCHAR(50);outer
- DECLARE item_inner VARCHAR(50);
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 定义内外游标
DECLARE cur_outer cursor for select dept_id from tbl_test_dept;
DECLARE cur_inner cursor for (SELECT user_id FROM tbl_test_user WHERE dept_id=item_outer);-- 查询条件可直接用外游标变量值
- OPEN cur_outer;
- out_loop: LOOP
- fetch cur_outer into item_outer;
IF done THEN -- 判断是否继续循环
- LEAVE out_loop;
- END IF;
- --
- --
OPEN cur_process; -- 打开内嵌游标
- inner_loop: LOOP
- fetch cur_inner into item_inner;
- IF done THEN
- LEAVE inner_loop;
- END IF;
- --
- --
- end loop;
- CLOSE cur_inner;
SET done = 0; -- 关闭内游标, 重置 done
- end loop;
- CLOSE cur_outer;
- commit;
- END;
call PROC_CURSOR_TEST(); -- 调用存储过程
drop procedure PROC_CURSOR_TEST; -- 删除
来源: http://www.linuxidc.com/Linux/2018-04/151687.htm