首页 > 数据库 >lightdb plorasql supports goto command

lightdb plorasql supports goto command

时间：2023-07-11 23:13:39浏览次数：41  
标签：DBMS command LOOP OUTPUT PUT plorasql LINE supports block

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.

 

 

 

标签：DBMS,command,LOOP,OUTPUT,PUT,plorasql,LINE,supports,block
From： https://www.cnblogs.com/lightdb/p/17546205.html

相关文章

  • bash: accelerate: command not found
    pyAIGCStableDiffusion文生图Lora模型微调实现虚拟上装详情  实验手册实验报告 3.安装Diffusers进入PAI-DSW开发环境。登录PAI控制台。在页面左上方，选择DSW实例所在的地域。在左侧导航栏单击工作空间列表，在工作空间列表页面......
  • OpenCV的CommandLineParser类
    C++带有参数的main函数我们可以使用命令行的方式给argv赋值：1#include<iostream>2#include<cstdlib>34//intmain(intargc,char*argv[])5intmain(intargc,char**argv)6{78intsum=0;9for(inti=1;i<argc;++i)10{......
  • Linux 错误： $'\r': command not found --九五小庞
    前段时间写脚本出现了$'\r':commandnotfound问题其实log报错已经非常明确了，是linux无法解析$'\r'。这其实是windows与linux系统的差异导致的。因为linux上的换行符为\n，而windows上的换行符为\r\n。所以脚本到linux上就无法解析了。通常的处理办法主要有2种:处理办法1setf......
  • vue项目报错：Node.js v18.16.1 error Command failed with exit code 1.
    原因：把node升级到了最新的长期支持版18.16.1，结果运行vue项目启动失败，报错如下：试了各种办法都解决不了，后面只能把node降级到16.20.1运行项目又可以启动了......
  • autosys job use unix command
    AutosysvariablenotbeingsetinprofileAskedby:BadHatHarryIhavecreatedanautosysjob(thisisatestjob,theoriginalonealsohasthesameproblem)whereifIsourcetheprofilemanuallythevariable$HDG_RunPergetssetproperly,whereasw......
  • PyCharm 运行时报错 allure: command not found
    安装Allure（Mac）方法一brewinstallallure方法二可能因为网络等，方式一会下载失败，可以通过以下方式安装1.https://repo.maven.apache.org/maven2/io/qameta/allure/allure-commandline/下载对应版本的压缩包2.配置环境变量-exportPATH=$PATH:/Users......
  • Apache启动报错：Invalid command ‘ProxyPass‘, perhaps misspelled or defined by a
    配置文件httpd，问题出在ProxyPass这个环节：ProxyPass/vict_servicehttp://localhost:8080/vict_service也就是要为/vict_service这个请求路径匹配一个实际可用的路径，既然提示ProxyPass命令无效，那么肯定是某个地方的设置问题。解决的方法就在httpd这个配置文件里，找到以下两条......
  • nginx error报错：nginx 8: Not enough storage is available to process this command
    在nginx.conf中http下添加如下代码：client_max_body_size2000m;client_body_buffer_size2000m;client_body_timeout500;client_header_buffer_size64k;client_header_timeout500;keepalive_requests300;large_client_header......
  • 使用 JCommander 解析命令行参数
    前言如果你想构建一个支持命令行参数的程序，那么jcommander非常适合你，jcommander是一个只有几十kb的Java命令行参数解析工具，可以通过注解的方式快速实现命令行参数解析。这篇教程会通过介绍jcommadner，快速的创建一个命令行程序，最后支持的命令参数功能如下图。这个命......
  • DVWA_Command_Injection(命令注入)-WP
    CommandInjection漏洞定义用户可以执行恶意代码语句，在实战中危害比较高，也称作命令执行，一般属于高危漏洞|命令管道符格式：第一条命令|第二条命令||第三条命令...]将第一条命令的结果作为第二条命令的参数来使用ps-aux|grep"ssh"&组合命令语法：第一条命令......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99