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 primeBEGIN  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 NULLEND;/

  3、使用goto分出一個環繞塊：

-- example with GOTO statementDECLARE  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.