Examples of the use of embedded cursors in Oracle and MySQL
It is relatively simple for Oracle cursors to use the For loop, and MySQL has only recently started to use. It is a little troublesome to feel that the following code is directly added:
-- Oracle
-- the embedded cursor is a cursor with parameters, and the parameter is the outer cursor value.
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-start the inner loop
--
--
END LOOP
END LOOP
Commit
END
-
-- Mysql
-- HANDLER can only declare one
-- done needs to be reset at the end of the inner loop.
-- it is found that mysql cannot directly execute begin..end. It needs to be called after the stored procedure is created.
-
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
-- define internal and external cursors
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);-- query conditions can directly use the value of outer cursor variables
OPEN cur_outer
Out_loop: LOOP
Fetch cur_outer into item_outer
IF done THEN-determine whether to continue the cycle
LEAVE out_loop
END IF
--
--
OPEN cur_process;-Open the embedded cursor
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;-- close the inner cursor and reset done
End loop
CLOSE cur_outer
Commit
END
Call PROC_CURSOR_TEST ();-- call the stored procedure
Drop procedure PROC_CURSOR_TEST;-Delete
