1.Exit、return、continue
将exit 或是 return 用在loop 。。。。end loop;中都可以中断循环,但是它们有一个很明显的不同
exit只是单纯的结束循环,但还会继续执行程序包中其他的内容,而return则是直接中断整个程序。
continue作用是终止本次循环,开始下一次循环,它不是跳出循环。
而oracle数据库之前却一直没有支持continue语法,直到oracle11g加入了continue语法。
DECLARE
V_STR VARCHAR2(1) :='b';
X_CHAR_SET VARCHAR2(100) := 'abcdefg';
BEGIN
FOR I IN 1..LENGTH(V_STR) LOOP
IF(INSTR(X_CHAR_SET, SUBSTR(V_STR, I, 1)) = 0) THEN
dbms_output.put_line('NO');
CONTINUE;
END IF;
dbms_output.put_line('1、循环:'||I);
END LOOP;
dbms_output.put_line('CONTINUE-------------------------------------------------------------');
FOR I IN 1..LENGTH(V_STR) LOOP
IF(INSTR(X_CHAR_SET, SUBSTR(V_STR, I, 1)) = 0) THEN
dbms_output.put_line('NO');
EXIT;
END IF;
dbms_output.put_line('2、循环:'||I);
END LOOP;
dbms_output.put_line('EXIT-------------------------------------------------------------');
FOR I IN 1..LENGTH(V_STR) LOOP
IF(INSTR(X_CHAR_SET, SUBSTR(V_STR, I, 1)) = 0) THEN
dbms_output.put_line('OK');
RETURN;
END IF;
dbms_output.put_line('3、循环:'||I);
END LOOP;
dbms_output.put_line('RETURN-------------------------------------------------------------');
END;
DECLARE
IN_DECLARE_DT VARCHAR2(10);
D_DECLAREDATE DATE;
TYPE C_TYPE IS REF CURSOR;
CUR_DT C_TYPE;
BEGIN
OPEN CUR_DT FOR (SELECT V_DATE FROM (SELECT '20201010' FROM DUAL UNION ALL SELECT '20211010' FROM DUAL))
LOOP
FETCH CUR_DT INTO IN_DECLARE_DT;
EXIT WHEN CUR_DT%NOTFOUND;
D_DECLAREDATE := TO_DATE(IN_DECLARE_DT, 'YYYYMMDD');
IF D_DECLAREDATE > SYSDATE THEN
dbms_output.put_line(D_DECLAREDATE);
END IF;
END LOOP;
CLOSE CUR_DT;
dbms_output.put_line('OVER');
END;
DECLARE
V_STR VARCHAR2(1) :='b';
X_CHAR_SET VARCHAR2(100) := 'abcdefg';
BEGIN
FOR I IN 1..LENGTH(V_STR) LOOP
IF(INSTR(X_CHAR_SET, SUBSTR(V_STR, I, 1)) = 0) THEN
dbms_output.put_line('NO');
CONTINUE;
END IF;
dbms_output.put_line('1、循环:'||I);
END LOOP;
dbms_output.put_line('CONTINUE-------------------------------------------------------------');
FOR I IN 1..LENGTH(V_STR) LOOP
IF(INSTR(X_CHAR_SET, SUBSTR(V_STR, I, 1)) = 0) THEN
dbms_output.put_line('NO');
EXIT;
END IF;
dbms_output.put_line('2、循环:'||I);
END LOOP;
dbms_output.put_line('EXIT-------------------------------------------------------------');
FOR I IN 1..LENGTH(V_STR) LOOP
IF(INSTR(X_CHAR_SET, SUBSTR(V_STR, I, 1)) = 0) THEN
dbms_output.put_line('OK');
RETURN;
END IF;
dbms_output.put_line('3、循环:'||I);
END LOOP;
dbms_output.put_line('RETURN-------------------------------------------------------------');
END;
DECLARE
IN_DECLARE_DT VARCHAR2(10);
D_DECLAREDATE DATE;
TYPE C_TYPE IS REF CURSOR;
CUR_DT C_TYPE;
BEGIN
OPEN CUR_DT FOR (SELECT V_DATE FROM (SELECT '20201010' FROM DUAL UNION ALL SELECT '20211010' FROM DUAL))
LOOP
FETCH CUR_DT INTO IN_DECLARE_DT;
EXIT WHEN CUR_DT%NOTFOUND;
D_DECLAREDATE := TO_DATE(IN_DECLARE_DT, 'YYYYMMDD');
IF D_DECLAREDATE > SYSDATE THEN
dbms_output.put_line(D_DECLAREDATE);
END IF;
END LOOP;
CLOSE CUR_DT;
dbms_output.put_line('OVER');
END;
2.goto
属于plsql控制语句,用于程序控制非条件跳至指定标签<<???>>。不易控制和维护,慎用!
简单GOTO 语句,判断数字是否为质数,如下:
DECLARE
p VARCHAR2(30);
n PLS_INTEGER := 37;
BEGIN
FOR j IN 2 .. round(sqrt(n)) LOOP
IF n MOD j = 0 THEN
p := ' is not a prime number';
GOTO print_now;
END IF;
END LOOP;
p := ' is a prime number';
<<print_now>>
dbms_output.put_line(to_char(n) || p);
END;
原文链接:https://blog.csdn.net/Mavey__/article/details/118601208