文档中心
MogDBMogDB StackUqbar
生态工具
v3.0
文档目录
文档:v3.0

控制语句

返回语句

MogDB提供两种方式返回数据:RETURN或RETURN NEXT及RETURN QUERY。其中,RETURN NEXT和RETURN QUERY只适用于函数,不适用存储过程。

RETURN

语法

返回语句的语法请参见图1

图 1 return_clause::=

return_clause

对以上语法的解释如下:

用于将控制从存储过程或函数返回给调用者。

示例

请参见调用语句的示例

RETURN NEXT及RETURN QUERY

语法

创建函数时需要指定返回值SETOF datatype。

return_next_clause::=

img

return_query_clause::=

img

对以上语法的解释如下:

当需要函数返回一个集合时,使用RETURN NEXT或者RETURN QUERY向结果集追加结果,然后继续执行函数的下一条语句。随着后续的RETURN NEXT或RETURN QUERY命令的执行,结果集中会有多个结果。函数执行完成后会一起返回所有结果。

RETURN NEXT可用于标量和复合数据类型。

RETURN QUERY有一种变体RETURN QUERY EXECUTE,后面还可以增加动态查询,通过USING向查询插入参数。

示例

MogDB=# CREATE TABLE t1(a int);
MogDB=# INSERT INTO t1 VALUES(1),(10);

--RETURN NEXT
MogDB=# CREATE OR REPLACE FUNCTION fun_for_return_next() RETURNS SETOF t1 AS $$
DECLARE
   r t1%ROWTYPE;
BEGIN
   FOR r IN select * from t1
   LOOP
      RETURN NEXT r;
   END LOOP;
   RETURN;
END;
$$ LANGUAGE PLPGSQL;
MogDB=# call fun_for_return_next();
 a
---
 1
 10
(2 rows)

-- RETURN QUERY
MogDB=# CREATE OR REPLACE FUNCTION fun_for_return_query() RETURNS SETOF t1 AS $$
DECLARE
   r t1%ROWTYPE;
BEGIN
   RETURN QUERY select * from t1;
END;
$$
language plpgsql;
MogDB=# call fun_for_return_query();
 a
---
 1
 10
(2 rows)

条件语句

条件语句的主要作用判断参数或者语句是否满足已给定的条件,根据判定结果执行相应的操作。

MogDB有五种形式的IF:

  • IF_THEN

    图 2 IF_THEN::=

    IF_THEN

    IF_THEN语句是IF的最简单形式。如果条件为真,statements将被执行。否则,将忽略它们的结果使该IF_THEN语句执行结束。

    示例

    MogDB=# IF v_user_id <> 0 THEN
        UPDATE users SET email = v_email WHERE user_id = v_user_id;
    END IF;
  • IF_THEN_ELSE

    图 3 IF_THEN_ELSE::=

    IF_THEN_ELSE

    IF_THEN_ELSE语句增加了ELSE的分支,可以声明在条件为假的时候执行的语句。

    示例

    MogDB=# IF parentid IS NULL OR parentid = ''
    THEN
        RETURN;
    ELSE
        hp_true_filename(parentid);--表示调用存储过程
    END IF;
  • IF_THEN_ELSE IF

    I F语句可以嵌套,嵌套方式如下:

    MogDB=# IF sex = 'm' THEN
        pretty_sex := 'man';
    ELSE
        IF sex = 'f' THEN
            pretty_sex := 'woman';
        END IF;
    END IF;

    这种形式实际上就是在一个IF语句的ELSE部分嵌套了另一个IF语句。因此需要一个END IF语句给每个嵌套的IF,另外还需要一个END IF语句结束父IF-ELSE。如果有多个选项,可使用下面的形式。

  • IF_THEN_ELSIF_ELSE

    图 4 IF_THEN_ELSIF_ELSE::=

    IF_THEN_ELSIF_ELSE

    示例

    IF number_tmp = 0 THEN
        result := 'zero';
    ELSIF number_tmp > 0 THEN
        result := 'positive';
    ELSIF number_tmp < 0 THEN
        result := 'negative';
    ELSE
        result := 'NULL';
    END IF;
  • IF_THEN_ELSEIF_ELSE

    ELSEIF是ELSIF的别名。

    综合示例

    CREATE OR REPLACE PROCEDURE proc_control_structure(i in integer)
    AS
        BEGIN
            IF i > 0 THEN
                raise info 'i:% is greater than 0. ',i;
            ELSIF i < 0 THEN
                raise info 'i:% is smaller than 0. ',i;
            ELSE
                raise info 'i:% is equal to 0. ',i;
            END IF;
            RETURN;
        END;
    /
    
    CALL proc_control_structure(3);
    
    --删除存储过程
    DROP PROCEDURE proc_control_structure;

循环语句

简单LOOP语句

语法图

图 5 loop::=

loop

示例

CREATE OR REPLACE PROCEDURE proc_loop(i in integer, count out integer)
AS
    BEGIN
        count:=0;
        LOOP
        IF count > i THEN
            raise info 'count is %. ', count;
            EXIT;
        ELSE
            count:=count+1;
        END IF;
        END LOOP;
    END;
/

CALL proc_loop(10,5);

img须知:

该循环必须要结合EXIT使用,否则将陷入死循环。

WHILE_LOOP语句

语法图

图 6 while_loop::=

while_loop

只要条件表达式为真,WHILE语句就会不停的在一系列语句上进行循环,在每次进入循环体的时候进行条件判断。

示例

CREATE TABLE integertable(c1 integer) ;
CREATE OR REPLACE PROCEDURE proc_while_loop(maxval in integer)
AS
    DECLARE
    i int :=1;
    BEGIN
        WHILE i < maxval LOOP
            INSERT INTO integertable VALUES(i);
            i:=i+1;
        END LOOP;
    END;
/

--调用函数
CALL proc_while_loop(10);

--删除存储过程和表
DROP PROCEDURE proc_while_loop;
DROP TABLE integertable;

FOR_LOOP(integer变量)语句

语法图

图 7 for_loop::=

for_loop

img说明:

  • 变量name会自动定义为integer类型并且只在此循环里存在。变量name介于lower_bound和upper_bound之间。
  • 当使用REVERSE关键字时,lower_bound必须大于等于upper_bound,否则循环体不会被执行。

FOR_LOOP查询语句

语法图

图 8 for_loop_query::=

for_loop_query

img说明: 变量target会自动定义,类型和query的查询结果的类型一致,并且只在此循环中有效。target的取值就是query的查询结果。

FORALL批量查询语句

语法图

图 9 forall::=

forall

img说明:

  • 变量index会自动定义为integer类型并且只在此循环里存在。index的取值介于low_bound和upper_bound之间。
  • 如果声明了SAVE EXCEPTIONS,则会将循环体DML执行过程中每次遇到的异常保存在SQL&BULK_EXCEPTIONS中,并在执行结束后统一抛出一个异常,循环过程中没有异常的执行的结果在当前子事务内不会回滚。

示例

CREATE TABLE hdfs_t1 (
  title NUMBER(6),
  did VARCHAR2(20),
  data_peroid VARCHAR2(25),
  kind VARCHAR2(25),
  interval VARCHAR2(20),
  time DATE,
  isModified VARCHAR2(10)
);

INSERT INTO hdfs_t1 VALUES( 8, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK' );

CREATE OR REPLACE PROCEDURE proc_forall()
AS
BEGIN
    FORALL i IN 100..120
        update hdfs_t1 set title = title + 100*i;
END;
/

--调用函数
CALL proc_forall();

--查询存储过程调用结果
SELECT * FROM hdfs_t1 WHERE title BETWEEN 100 AND 120;

--删除存储过程和表
DROP PROCEDURE proc_forall;
DROP TABLE hdfs_t1;

分支语句

语法

分支语句的语法请参见图10

图 10 case_when::=

case_when

when_clause子句的语法图参见图11

图 11 when_clause::=

when_clause

参数说明:

  • case_expression:变量或表达式。
  • when_expression:常量或者条件表达式。
  • statement:执行语句。

示例

CREATE OR REPLACE PROCEDURE proc_case_branch(pi_result in integer, pi_return out integer)
AS
    BEGIN
        CASE pi_result
            WHEN 1 THEN
                pi_return := 111;
            WHEN 2 THEN
                pi_return := 222;
            WHEN 3 THEN
                pi_return := 333;
            WHEN 6 THEN
                pi_return := 444;
            WHEN 7 THEN
                pi_return := 555;
            WHEN 8 THEN
                pi_return := 666;
            WHEN 9 THEN
                pi_return := 777;
            WHEN 10 THEN
                pi_return := 888;
            ELSE
                pi_return := 999;
        END CASE;
        raise info 'pi_return : %',pi_return ;
END;
/

CALL proc_case_branch(3,0);

--删除存储过程
DROP PROCEDURE proc_case_branch;

空语句

在PL/SQL程序中,可以用NULL语句来说明“不用做任何事情”,相当于一个占位符,可以使某些语句变得有意义,提高程序的可读性。

语法

空语句的用法如下:

DECLAREBEGINIF v_num IS NULL THEN
        NULL; -- 不需要处理任何数据。
    END IF;
END;
/

错误捕获语句

缺省时,当PL/SQL函数执行过程中发生错误时退出函数执行,并且周围的事务也会回滚。可以用一个带有EXCEPTION子句的BEGIN块捕获错误并且从中恢复。其语法是正常的BEGIN块语法的一个扩展:

[<<label>>]
[DECLARE
    declarations]
BEGIN
    statements
EXCEPTION
    WHEN condition [OR condition ...] THEN
        handler_statements
    [WHEN condition [OR condition ...] THEN
        handler_statements
    ...]
END;

如果没有发生错误,这种形式的块儿只是简单地执行所有语句,然后转到END之后的下一个语句。但是如果在执行的语句内部发生了一个错误,则这个语句将会回滚,然后转到EXCEPTION列表。寻找匹配错误的第一个条件。若找到匹配,则执行对应的handler_statements,然后转到END之后的下一个语句。如果没有找到匹配,则会向事务的外层报告错误,和没有EXCEPTION子句一样。错误码可以捕获同一类的其他错误码。

也就是说该错误可以被一个包围块用EXCEPTION捕获,如果没有包围块,则进行退出函数处理。

condition的名称可以是SQL标准错误码编号说明的任意值。特殊的条件名OTHERS匹配除了QUERY_CANCELED之外的所有错误类型。

如果在选中的handler_statements里发生了新错误,则不能被这个EXCEPTION子句捕获,而是向事务的外层报告错误。一个外层的EXCEPTION子句可以捕获它。

如果一个错误被EXCEPTION捕获,PL/SQL函数的局部变量保持错误发生时的原值,但是所有该块中想写入数据库中的状态都回滚。

示例:

CREATE TABLE mytab(id INT,firstname VARCHAR(20),lastname VARCHAR(20)) ;

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');

CREATE FUNCTION fun_exp() RETURNS INT
AS $$
DECLARE
    x INT :=0;
    y INT;
BEGIN
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
        RETURN x;
END;$$
LANGUAGE plpgsql;

call fun_exp();
NOTICE:  caught division_by_zero
 fun_exp
---------
       1
(1 row)

select * from mytab;
 id | firstname | lastname
----+-----------+----------
    | Tom       | Jones
(1 row)

DROP FUNCTION fun_exp();
DROP TABLE mytab;

当控制到达给y赋值的地方时,会有一个division_by_zero错误失败。这个错误将被EXCEPTION子句捕获。而在RETURN语句里返回的数值将是x的增量值。

img说明:

进入和退出一个包含EXCEPTION子句的块要比不包含的块开销大的多。因此,不必要的时候不要使用EXCEPTION。 在下列场景中,无法捕获处理异常,整个存储过程回滚:节点故障、网络故障引起的存储过程参与节点线程退出以及COPY FROM操作中源数据与目标表的表结构不一致造成的异常。

示例:UPDATE/INSERT异常

这个例子根据使用异常处理器执行恰当的UPDATE或INSERT 。

CREATE TABLE db (a INT, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP

--第一次尝试更新key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
--不存在,所以尝试插入key,如果其他人同时插入相同的key,我们可能得到唯一key失败。
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
        --什么也不做,并且循环尝试再次更新。
        END;
     END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

--删除FUNCTION和TABLE
DROP FUNCTION merge_db;
DROP TABLE db;

GOTO语句

GOTO语句可以实现从GOTO位置到目标语句的无条件跳转。GOTO语句会改变原本的执行逻辑,因此应该慎重使用,或者也可以使用EXCEPTION处理特殊场景。当执行GOTO语句时,目标Label必须是唯一的。

语法

label declaration ::=

img

goto statement ::=

img

示例

MogDB=# CREATE OR REPLACE PROCEDURE GOTO_test()
AS
DECLARE
    v1  int;
BEGIN
    v1  := 0;
        LOOP
        EXIT WHEN v1 > 100;
                v1 := v1 + 2;
                if v1 > 25 THEN
                        GOTO pos1;
                END IF;
        END LOOP;
<<pos1>>
v1 := v1 + 10;
raise info 'v1 is %. ', v1;
END;
/

call GOTO_test();

限制场景

GOTO使用有以下限制场景

  • 不支持有多个相同的GOTO labels目标场景,无论是否在同一个block中。

    BEGIN
      GOTO pos1;
      <<pos1>>
      SELECT * FROM ...
      <<pos1>>
      UPDATE t1 SET ...
    END;
  • 不支持GOTO跳转到IF语句,CASE语句,LOOP语句中。

    BEGIN
       GOTO pos1;
       IF valid THEN
         <<pos1>>
         SELECT * FROM ...
       END IF;
     END;
  • 不支持GOTO语句从一个IF子句跳转到另一个IF子句,或从一个CASE语句的WHEN子句跳转到另一个WHEN子句。

    BEGIN
       IF valid THEN
         GOTO pos1;
         SELECT * FROM ...
       ELSE
         <<pos1>>
         UPDATE t1 SET ...
       END IF;
     END;
  • 不支持从外部块跳转到内部的BEGIN-END块。

    BEGIN
       GOTO pos1;
       BEGIN
         <<pos1>>
         UPDATE t1 SET ...
       END;
     END;
  • 不支持从异常处理部分跳转到当前的BEGIN-END块。但可以跳转到上层BEGIN-END块。

    BEGIN
       <<pos1>>
       UPDATE t1 SET ...
       EXCEPTION
         WHEN condition THEN
            GOTO pos1;
     END;
  • 如果从GOTO到一个不包含执行语句的位置,需要添加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;
    /
Copyright © 2009-2022 www.enmotech.com All rights reserved.