【Oracle】存储过程 cursor 循环中的 Exit、Continue、Return
前言
今天在看项目代码的时候发现 cursor 循环中用到了 Exit、Continue、Return 控制语句,同印象中的有点出入,做个小实验验证下。
示例代码
DECLARE
TYPE cursors IS REF CURSOR;
CUR_ONE CURSORS;
CUR_TWO CURSORS;
V_LEVEL_ONE NUMBER;
V_LEVEL_TWO NUMBER;
BEGIN
OPEN CUR_ONE FOR
SELECT LEVEL LEVEL_ONE FROM DUAL CONNECT BY LEVEL < 4;
LOOP
FETCH CUR_ONE
INTO V_LEVEL_ONE;
EXIT WHEN CUR_ONE%NOTFOUND;
OPEN CUR_TWO FOR
SELECT LEVEL LEVEL_TWO FROM DUAL CONNECT BY LEVEL < 4;
LOOP
FETCH CUR_TWO
INTO V_LEVEL_TWO;
EXIT WHEN CUR_TWO%NOTFOUND;
IF V_LEVEL_TWO = 2 THEN
EXIT;
--RETURN;
--CONTINUE;
END IF;
DBMS_OUTPUT.put_line(V_LEVEL_ONE || '-' || V_LEVEL_TWO);
END LOOP;
END LOOP;
CLOSE CUR_ONE;
CLOSE CUR_TWO;
END;
Return 结果
1-1
Continue 结果
1-1
1-3
2-1
2-3
3-1
3-3
Exit 结果
1-1
2-1
3-1
总结
Return:直接跳出存储过程
Exit:跳出本次循环,如果本循环外还有循环则会执行外层循环的下一次循环
Continue:本次循环后面的代码不再执行,继续执行本循环的下次循环