oracle goto语句介绍

以下内容来自oracle plsql user guide.

-------------------------------------------------------

一 定义：

     The Oracle PL/SQL GOTO statement is a sequential control structure available in Oracle. The GOTO statement immediately transfers program control (called "branching") unconditionally to a named statement label or block label. The statement or label name must be unique in the block. 

   属于plsql控制语句，用于程序控制非条件跳至指定标签<<???>>。不易控制和维护，慎用！

二 例子：

   1、简单GOTO 语句，判断数字是否为质数：

   

DECLARE
  p VARCHAR2(30);
  n PLS_INTEGER := 37; -- test any integer > 2 for prime
BEGIN
  FOR j IN 2 .. round(sqrt(n)) LOOP
    IF n MOD j = 0 THEN
      -- test for prime
      p := ' is not a prime number'; -- 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;
/

  2、使用null避免报错：

DECLARE
  done BOOLEAN;
BEGIN
  FOR i IN 1 .. 50 LOOP
    IF done THEN
      GOTO end_loop;
    END IF;
    <<end_loop>> -- not allowed unless an executable statement follows
    NULL; -- add NULL statement to avoid error
  END LOOP; -- raises an error without the previous NULL
END;
/

  3、使用goto分出一个环绕块：

-- example with GOTO statement
DECLARE
  v_last_name VARCHAR2(25);
  v_emp_id    NUMBER(6) := 120;
BEGIN
  <<get_name>>
  SELECT last_name
  INTO v_last_name
  FROM employees
  WHERE employee_id = v_emp_id;
  BEGIN
    dbms_output.put_line(v_last_name);
    v_emp_id := v_emp_id + 5;
    IF v_emp_id < 120 THEN
      GOTO get_name; -- branch to enclosing block
    END IF;
  END;
END;
/

----------------------

dylan presents.