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; --删除
