Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.