sql - 在 SQL 中遇到异常后如何继续运行我的程序？

Question

我编写了一个 PL/SQL 代码来仅打印大于 id=4 的记录。我在程序中使用了goto语句，在异常中未检测到该语句。请帮助我在遇到异常后继续程序。我的代码是

declare
    cursor cur is select * from student;
    c student%rowtype;
    lt3 exception;
    PRAGMA
    exception_init(lt3,-77);
begin
    open cur;
    loop
        <<backup>>
        fetch cur into c;
        if c.id < 4 then
            raise lt3;
        else
            dbms_output.put_line(c.name);
        end if;
    end loop;
    close cur;
exception
    when lt3 then
    dbms_output.put_line('Exception encountered');
    goto backup;
end;
/

我应该在哪里改变？

我在

处收到错误

ERROR at line 24:
ORA-06550: line 24, column 7:
PLS-00201: identifier 'BACKUP' must be declared
ORA-06550: line 24, column 2:
PL/SQL: Statement ignored

Answer 1

当您在cursor中使用goto时，光标将被关闭，因此您无法实现预期的行为。

来自Doc

If you use the GOTO statement to exit a cursor FOR loop prematurely, the cursor is closed automatically. The cursor is also closed automatically if an exception is raised inside the loop.

您可以做的一件事是使用 continue 、break、exit在循环中控制执行

open cur;
loop
    fetch cur into c;
    if c.id < 4 then
          continue;
    else
          dbms_output.put_line(c.name);
    end if;
end loop;
close cur;