526互联

lightdb plorasql supports goto command

发布时间 2023-07-11 23:07:23作者: lightdb

Article directory

  • background
  • Scenes
  • Case presentation
    • nested blocks
    • LOOP
    • WHILE
    • FOR
    • COMMIT
    • ROLL BACK
    • IF
    • CASE
    • EXIT
    • RETURN
    • GOTO
    • EXCEPTION
    • NULL
  • in conclusion

Background

The GOTO statement is an unconditional jump statement, which can jump the execution flow of the program to a position specified by a label. Tags must be unique within the scope of their execution. In version 23.2 of LightDB Database, plorasql supports GOTO statement.

 

 

Scene

GOTO statements are supported in anonymous blocks, FUNCTION, and PROCEDURE in plorasql.
However, the use of GOTO has the following limitations

  • Labels specifying jump locations must be defined before an executable statement or PL/oraSQL block.
  • You can jump from a nested block to an outer block, but not from an outer block to a nested block.
  • You cannot jump from outside the IF statement to inside the IF statement.
  • It is not possible to jump from the outside of the loop to the inside of the loop.
  • It is not possible to jump from outside the subroutine to inside the subroutine.
  • You cannot jump from outside the CASE statement to inside the CASE statement.
  • You cannot jump from an exception-handling section to an executable section, or from an executable section to an exception-handling section.

Case presentation

The following cases can be executed by using the ltsql command.

Create oracle environment

create database test_oracle lightdb_syntax_compatible_type oracle;

Connect to test_oracle database

\c test_oracle

Set the output output

select dbms_output. serveroutput(true);

Nested BLOCK

goto does not support GOTO from an external block to an internal block, as in the following cases:

 

 

BEGIN
  DBMS_OUTPUT.PUT_LINE('out block 1');
  goto testlabel;
  DBMS_OUTPUT.PUT_LINE('out block 2');
  BEGIN
    DBMS_OUTPUT.PUT_LINE('in block 1');
    <<testlabel>>
    DBMS_OUTPUT.PUT_LINE('in block 2');
  END;
  DBMS_OUTPUT.PUT_LINE('out block 3');
END;
/

The output is as follows:

out block 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/oraSQL function inline_code_block

goto supports GOTO from internal block to external block, as in the following cases:

BEGIN
  DBMS_OUTPUT.PUT_LINE('out block 1');
  goto testlabel;
  DBMS_OUTPUT.PUT_LINE('out block 2');
  <<testlabel>>
  BEGIN
    DBMS_OUTPUT.PUT_LINE('in block 1');
    goto testlabel2;
    DBMS_OUTPUT.PUT_LINE('in block 2');
  END;

  DBMS_OUTPUT.PUT_LINE('out block 3');

  <<testlabel2>>
  DBMS_OUTPUT.PUT_LINE('out block 4');
END;
/

The output is as follows:

out block 1
in block 1
out block 4
do

LOOP

goto does not support external GOTO to LOOP, as in the following cases:

BEGIN
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 1');
    goto testlabel;
    LOOP
        DBMS_OUTPUT.PUT_LINE('IN LOOP 1');
        <<testlabel>>
        DBMS_OUTPUT.PUT_LINE('IN LOOP 2');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 2');
END;
/

The output is as follows:

 

 

OUT LOOP 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/oraSQL function inline_code_block

goto supports GOTO from inside LOOP to outside, as in the following cases:

BEGIN
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 1');
    goto testlabel;
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 2');
    <<testlabel>>
    LOOP
        DBMS_OUTPUT.PUT_LINE('IN LOOP 1');
        goto testlabel2;
        DBMS_OUTPUT.PUT_LINE('IN LOOP 2');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 3');
    <<testlabel2>>
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 4');
END;
/

The output is as follows:

OUT LOOP 1
IN LOOP 1
OUT LOOP 4
do

WHILE

goto does not support external GOTO to WHILE internal, as in the following cases:

BEGIN
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 1');
    goto testlabel;
    WHILE true LOOP
        DBMS_OUTPUT.PUT_LINE('IN LOOP 1');
        <<testlabel>>
        DBMS_OUTPUT.PUT_LINE('IN LOOP 2');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 2');
END;
/

The output is as follows:

OUT LOOP 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/oraSQL function inline_code_block

goto supports GOTO from inside WHILE to outside, as in the following cases:

BEGIN
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 1');
    goto testlabel;
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 2');

    <<testlabel>>
    WHILE true LOOP
        DBMS_OUTPUT.PUT_LINE('IN LOOP 1');
        goto testlabel2;
        DBMS_OUTPUT.PUT_LINE('IN LOOP 2');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 3');
    <<testlabel2>>
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 4');
END;
/

The output is as follows:

OUT LOOP 1
IN LOOP 1
OUT LOOP 4
do

FOR

goto does not support GOTO from the outer block to the inside of FOR, as in the following cases:

DECLARE
    i INTEGER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 1');
    goto testlabel;
    FOR i IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('IN LOOP 1');
        <<testlabel>>
        DBMS_OUTPUT.PUT_LINE('IN LOOP 2');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 2');
END;
/

The output is as follows:

 

 

OUT LOOP 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/oraSQL function inline_code_block

goto supports from internal FOR GOTO to external block, as in the following cases:

DECLARE
    i INTEGER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 1');
    goto testlabel;
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 2');

    <<testlabel>>
    FOR i IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('IN LOOP 1');
        goto testlabel2;
        DBMS_OUTPUT.PUT_LINE('IN LOOP 2');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 3');
    <<testlabel2>>
    DBMS_OUTPUT.PUT_LINE('OUT LOOP 4');
END;
/

The output is as follows:

OUT LOOP 1
IN LOOP 1
OUT LOOP 4
do

COMMIT

goto supports GOTO to COMMIT, the following cases:

CREATE TABLE test1(a int);

BEGIN
    INSERT INTO test1 (a) VALUES (1);
    INSERT INTO test1 (a) VALUES (2);
    goto testlabel;
    INSERT INTO test1 (a) VALUES (3);
    <<testlabel>>
    COMMIT;
END;
/
select * from test1;
DROP TABLE test1;

The output is as follows:

do
lightdb@postgres=# select * from test1;
 a
---
 1
 2
(2 rows)

ROLLBACK

goto supports GOTO to rollback, the following cases:

CREATE TABLE test1(a int);

BEGIN
    INSERT INTO test1 (a) VALUES (1);
    INSERT INTO test1 (a) VALUES (2);
    goto testlabel;
    INSERT INTO test1 (a) VALUES (3);
    <<testlabel>>
    ROLLBACK;
END;
/

select * from test1;
DROP TABLE test1;

The output is as follows:

a
---
(0 rows)
DROP TABLE test1;

IF

goto does not support GOTO from an external block to an IF internal block, as in the following cases:

BEGIN
  DBMS_OUTPUT.PUT_LINE('out block 1');
  goto testlabel;
  DBMS_OUTPUT.PUT_LINE('out block 2');
  IF true THEN
    DBMS_OUTPUT.PUT_LINE('in block 1');
    <<testlabel>>
    DBMS_OUTPUT.PUT_LINE('in block 2');
  END IF;

  DBMS_OUTPUT.PUT_LINE('out block 3');
END;
/

The output is as follows:

out block 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/oraSQL function inline_code_block

goto supports GOTO from the IF internal block to the external block, as in the following cases:

BEGIN
  DBMS_OUTPUT.PUT_LINE('out block 1');
  goto testlabel;
  DBMS_OUTPUT.PUT_LINE('out block 2');
  <<testlabel>>
  IF true THEN
    DBMS_OUTPUT.PUT_LINE('in block 1');
    goto testlabel2;
    DBMS_OUTPUT.PUT_LINE('in block 2');
  END IF;

  DBMS_OUTPUT.PUT_LINE('out block 3');

  <<testlabel2>>
  DBMS_OUTPUT.PUT_LINE('out block 4');
END;
/

The output is as follows:

out block 1
in block 1
out block 4
do

CASE

goto does not support GOTO from an external block to a CASE internal block, as in the following cases:

BEGIN
  DBMS_OUTPUT.PUT_LINE('out block 1');
  goto testlabel;
  DBMS_OUTPUT.PUT_LINE('out block 2');
  
  CASE 2
    WHEN 1 THEN
      <<testlabel>>
      DBMS_OUTPUT.PUT_LINE('in block 1');
    ELSE
      DBMS_OUTPUT.PUT_LINE('in block 2');
  END CASE;
  DBMS_OUTPUT.PUT_LINE('out block 3');
END;
/

The output is as follows:

out block 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/oraSQL function inline_code_block

goto supports GOTO from CASE internal block to external block, as in the following cases:

BEGIN
  DBMS_OUTPUT.PUT_LINE('out block 1');
  goto testlabel;
  DBMS_OUTPUT.PUT_LINE('out block 2');
  <<testlabel>>
  
  CASE 2
    WHEN 1 THEN
      DBMS_OUTPUT.PUT_LINE('in block 1');
    ELSE
      DBMS_OUTPUT.PUT_LINE('in block 2');
      goto testlabel2;
      DBMS_OUTPUT.PUT_LINE('in block 3');
  END CASE;

  DBMS_OUTPUT.PUT_LINE('out block 3');

  <<testlabel2>>
  DBMS_OUTPUT.PUT_LINE('out block 4');
END;
/

The output is as follows:

out block 1
in block 2
out block 4
do

EXIT

goto supports GOTO to EXIT, the following cases:

BEGIN
  DBMS_OUTPUT.PUT_LINE('out block 1');
  LOOP
    goto testlabel;
    DBMS_OUTPUT.PUT_LINE('out block 2');
    <<testlabel>>
    EXIT;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('out block 3');
END;
/

The output is as follows:

out block 1
out block 3
do

RETURN

goto supports GOTO to RETURN, the following cases:

BEGIN
  DBMS_OUTPUT.PUT_LINE('out block 1');
  LOOP
    goto testlabel;
    DBMS_OUTPUT.PUT_LINE('in block 1');
    <<testlabel>>
    RETURN;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('out block 2');
END;
/

The output is as follows:

out block 1
do

GOTO

goto supports GOTO to GOTO, the following cases:

BEGIN
  goto testlabel;
  DBMS_OUTPUT.PUT_LINE('out block 1');
  <<testlabel>>
  goto testlabel3;
  <<testlabel2>>
  DBMS_OUTPUT.PUT_LINE('out block 2');
  <<testlabel3>>
  DBMS_OUTPUT.PUT_LINE('out block 3');
  <<testlabel4>>
  DBMS_OUTPUT.PUT_LINE('out block 4');
END;
/

The output is as follows:

out block 3
out block 4
do

EXCEPTION

goto does not support mutual GOTO from normal blocks and exception blocks, as in the following cases:

GOTO from exception block to normal block is not supported.

DECLARE
  i INTEGER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('block 1');
  i := 1/0;
<<testlabel>>
  DBMS_OUTPUT.PUT_LINE('block 2');
EXCEPTION
    WHEN others THEN
      DBMS_OUTPUT.PUT_LINE('exception block 1');
      goto testlabel;
      DBMS_OUTPUT.PUT_LINE('exception block 2');
END;
/

The output is as follows:

block 1
exception block 1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/oraSQL function inline_code_block

GOTO from normal block to exception block is also not supported

DECLARE
  i INTEGER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('1');
  goto testlabel;
EXCEPTION
  WHEN others THEN
      <<testlabel>>
      DBMS_OUTPUT.PUT_LINE('2');
      return;
END;
/

The output is as follows:

1
ERROR: illegal GOTO statement; this GOTO cannot branch to label "testlabel"
CONTEXT: PL/oraSQL function inline_code_block

NULL

GOTO to NULL is supported, as shown in the following example:

BEGIN
    DBMS_OUTPUT.PUT_LINE('STEP 1');
    GOTO testlabel;
    DBMS_OUTPUT.PUT_LINE('STEP 2');
    <<testlabel>>
    NULL;
    DBMS_OUTPUT.PUT_LINE('STEP 3');
END;
/

The output is as follows:

STEP 1
STEP 3
do

Conclusion

The above example demonstrates the GOTO in the anonymous block, and the plorasql function (FUNCTION) and plpgsql stored procedure (PROCEDURE) are also applicable. It is worth noting that plpgsql version 23.2 does not support transactions in FUNCTION, so GOTO to COMMMIT in FUNCTION cannot be used. /ROLLBACK.