Oracle PL/SQL/PL SQL/GOTO
Содержание
Example of a proper GOTO statement
<source lang="sql">
SQL> SQL> -- Example of a proper GOTO statement. SQL> DECLARE
2 s NUMBER := 1;
3 BEGIN
4 IF s = 1 THEN
5 GOTO mybranch;
6 ELSE
7 s := 1;
8 END IF;
9 <<mybranch>>
10 DBMS_OUTPUT.PUT_LINE("www.sqle.ru");
11 NULL;
12 END;
13 /
www.sqle.ru PL/SQL procedure successfully completed. SQL>
</source>
GOTO a Label
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 v_Status NUMBER := 1; 3 BEGIN 4 IF v_Status = 1 THEN 5 GOTO mybranch; 6 ELSE 7 v_Status := 1; 8 END IF; 9 <<mybranch>> 10 NULL; 11 END; 12 /
PL/SQL procedure successfully completed. SQL> --
</source>
GOTO statement.
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> DECLARE
2 v_Counter BINARY_INTEGER := 1;
3 BEGIN
4 LOOP
5 INSERT INTO MyTable
6 VALUES (v_Counter, "Loop count");
7 v_Counter := v_Counter + 1;
8 IF v_Counter >= 50 THEN
9 GOTO l_EndOfLoop;
10 END IF;
11 END LOOP;
12
13 <<l_EndOfLoop>>
14 INSERT INTO MyTable (char_col)
15 VALUES ("Done!");
16 END;
17 /
PL/SQL procedure successfully completed. SQL> drop table MyTable; Table dropped. SQL>
</source>
This script demonstrates GOTO
<source lang="sql">
SQL> SQL> SQL> PROMPT ** GOTO
- GOTO
SQL> SQL> SET SERVEROUTPUT ON SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("BEGINNING OF BLOCK");
3
4 GOTO l_Last_Line;
5
6 DBMS_OUTPUT.PUT_LINE("GOTO didn""t work!");
7 RETURN;
8 <<l_Last_Line>>
9 DBMS_OUTPUT.PUT_LINE("Last Line");
10 END;
11 /
BEGINNING OF BLOCK Last Line PL/SQL procedure successfully completed.
</source>
Use GoTO to jump out of a loop
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> DECLARE
2 v_Counter BINARY_INTEGER := 1;
3 BEGIN
4 LOOP
5 INSERT INTO MyTable
6 VALUES (v_Counter, "Loop count");
7 v_Counter := v_Counter + 1;
8 IF v_Counter > 50 THEN
9 GOTO l_EndOfLoop;
10 END IF;
11 END LOOP;
12
13 <<l_EndOfLoop>>
14 INSERT INTO MyTable (char_col)
15 VALUES ("Done!");
16 END;
17 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
1 Loop count
2 Loop count
3 Loop count
4 Loop count
5 Loop count
6 Loop count
7 Loop count
8 Loop count
9 Loop count
10 Loop count
11 Loop count
NUM_COL CHAR_COL
------------------------------------------------------------
12 Loop count
13 Loop count
14 Loop count
15 Loop count
16 Loop count
17 Loop count
18 Loop count
19 Loop count
20 Loop count
21 Loop count
22 Loop count
NUM_COL CHAR_COL
------------------------------------------------------------
23 Loop count
24 Loop count
25 Loop count
26 Loop count
27 Loop count
28 Loop count
29 Loop count
30 Loop count
31 Loop count
32 Loop count
33 Loop count
NUM_COL CHAR_COL
------------------------------------------------------------
34 Loop count
35 Loop count
36 Loop count
37 Loop count
38 Loop count
39 Loop count
40 Loop count
41 Loop count
42 Loop count
43 Loop count
44 Loop count
NUM_COL CHAR_COL
------------------------------------------------------------
45 Loop count
46 Loop count
47 Loop count
48 Loop count
49 Loop count
50 Loop count
Done!
51 rows selected. SQL> SQL> drop table MyTable; Table dropped.
</source>
Use sequential control with the GOTO statement and a block label
<source lang="sql">
SQL> SQL> DECLARE
2 counter NUMBER := 1;
3 BEGIN
4 WHILE (counter < 5) LOOP
5 IF counter = 2 THEN
6 GOTO loopindex;
7 ELSE
8 dbms_output.put_line("Index ["||counter||"].");
9 END IF;
10 << loopindex >>
11 IF counter >= 1 THEN
12 counter := counter + 1;
13 END IF;
14 END LOOP;
15 END;
16 /
Index [1]. Index [3]. Index [4]. PL/SQL procedure successfully completed.
</source>
