文档中心MogDBMogDB StackUqbar
v5.0

文档:v5.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;

LABEL_LOOP语句

语法格式

[label_begin:] LOOP
    statements
END LOOP [label_end]

img 说明:

在简单loop语句的基础上增加了label标签的用法,标签规则如下:

  • label_begin可以单独出现(不加label_end),但是使用label_end,就必须有与之相同的label_begin。
  • 标签可以被continue或exit语句引用,特别的是,在数据库模式为'B'时,也可用iterate或leave语句。

img 须知: 该循环只在数据库兼容模式为'B'时使用,其他模式下报错。必须要结合EXIT使用(’B‘模式下可使用’LEAVE‘,与EXIT效果相同;’B‘模式下可使用'ITERATE',与CONTINUE效果相同),否则将陷入死循环。

示例

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

CALL proc_loop(10,5);

WHILE_DO语句

语法格式

[label_begin:] WHILE condition DO
statements
END WHILE [label_end]

img 说明:

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

标签规则如下:

  • label_begin可以单独出现(不加label_end),但是使用label_end,就必须有与之相同的label_begin。
  • 标签可以被continue或exit语句引用,特别的是,在数据库模式为'B'时,也可用iterate或leave语句。

img 须知: 该循环只在数据库兼容模式为'B'时使用,其他模式下报错。

示例

create or replace procedure while_test()
as 
declare _i integer = 0;
BEGIN
the_while:
  while _i < 10 do
    _i := _i + 1;
    continue the_while when _i % 2 = 0;
    raise notice '%', _i;
  end while the_while;
end; 
/

select while_test();

REPEAT语句

语法格式

[label_begin:] REPEAT
statements
UNTIL condition
END REPEAT [label_end]

img 说明:

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

标签规则如下:

  • label_begin可以单独出现(不加label_end),但是使用label_end,就必须有与之相同的label_begin。
  • 标签可以被continue或exit语句引用,特别的是,在数据库模式为'B'时,也可用iterate或leave语句。

img 须知: 该循环只在数据库兼容模式为'B'时使用,其他模式下报错。

示例

CREATE or replace PROCEDURE dorepeat(p1 INT)
as 
declare
i int =0;
BEGIN
label:
repeat
i = i + 1;
until i >p1 end repeat label;
raise notice '%',i;
end;
/

分支语句

语法

分支语句的语法请参见图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;

B模式下新增declare声明的错误捕获语法。

功能描述

与exception语法相同都是在执行语句内部发生错误时触发异常处理寻找匹配错误的第一个条件。若找到匹配,则执行对应的statement,否则抛出异常。与exception语法不同的是declare有两种行为,一种是exit,在声明exit异常处理语句并匹配到错误条件时会将已经正确执行的语句提交,在执行完声明的异常处理statement后退出;另一种行为是continue,与exit行为不同在执行语句发生错误触发异常处理语句并匹配到错误条件时会跳过错误语句,继续执行剩余语句。

语法格式

BEGIN
    DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
    statements
END;

参数说明

  • handler_action

    触发异常处理条件后的行为。

    • continue:触发声明的异常条件时执行statement,跳过执行异常SQL并继续执行。
    • exit:触发声明的异常条件时执行statement,提交已执行SQL并退出。
    • undo:暂不支持。

img说明:

由于MogDB异常处理机制与MySQL数据库不同,只在SQL执行发生异常时才进行异常处理,产生warning等不进行异常处理。

  • condition_value
    • mysql_error_code:除0和1之外的整型数字。
    • SQLSTATE [VALUE] sqlstate_value:sqlstate错误码。
    • condition_name:条件名。
    • SQLWARNING:'01'开头的sqlstate错误码。
    • NOT FOUND:'02'开头的sqlstate错误码。
    • SQLEXCEPTION:除'01','02'开头的其他错误码。

示例

create table declare_handler_t_continue (i INT PRIMARY KEY, j INT);
create table declare_handler_t_exit (i INT PRIMARY KEY, j INT);

CREATE OR REPLACE PROCEDURE proc_ex()  IS
BEGIN
    DECLARE EXIT HANDLER FOR unique_violation
        RAISE NOTICE 'unique_violation HANDLER: SQLSTATE = %, SQLERRM = %', SQLSTATE, SQLERRM;

    INSERT INTO declare_handler_t_exit VALUES (1, 1);
    INSERT INTO declare_handler_t_exit VALUES (2, 1);
    INSERT INTO declare_handler_t_exit VALUES (1, 1); /* duplicate key */
    INSERT INTO declare_handler_t_exit VALUES (3, 1);
END;
/
call proc_ex();
NOTICE:  unique_violation HANDLER: SQLSTATE = 23505, SQLERRM = duplicate key value violates unique constraint "declare_handler_t_exit_pkey"
 proc_ex 
---------
 
(1 row)

SELECT * FROM declare_handler_t_exit ORDER BY i;
 i | j 
---+---
 1 | 1
 2 | 1
(2 rows)

CREATE OR REPLACE PROCEDURE proc_continue_sqlexception()  IS
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        RAISE NOTICE 'SQLEXCEPTION HANDLER: SQLSTATE = %, SQLERRM = %', SQLSTATE, SQLERRM;

    INSERT INTO declare_handler_t_continue VALUES (1, 1);
    INSERT INTO declare_handler_t_continue VALUES (2, 1);
    RAISE division_by_zero;
    INSERT INTO declare_handler_t_continue VALUES (1, 1);
    INSERT INTO declare_handler_t_continue VALUES (3, 1);
END;
/
call proc_continue_sqlexception();
NOTICE:  SQLEXCEPTION HANDLER: SQLSTATE = 22012, SQLERRM = division_by_zero
NOTICE:  SQLEXCEPTION HANDLER: SQLSTATE = 23505, SQLERRM = duplicate key value violates unique constraint "declare_handler_t_continue_pkey"
 proc_continue_sqlexception 
----------------------------
 
(1 row)

SELECT * FROM declare_handler_t_continue ORDER BY i;
 i | j 
---+---
 1 | 1
 2 | 1
 3 | 1
(3 rows)

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 © 2011-2024 www.enmotech.com All rights reserved.