-----------------------------------------------------------

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