PL/pgSQL控制结构

最新推荐文章于 2024-09-21 16:58:34 发布

wh62592855

最新推荐文章于 2024-09-21 16:58:34 发布

阅读量2.5k

点赞数

分类专栏： PostgreSQL 文章标签： exception insert query postgresql integer 数据库

PostgreSQL 专栏收录该内容

55 篇文章 1 订阅

订阅专栏

控制结构可能是 PL/pgSQL 中最有用的（以及最重要）的部分了。利用 PL/pgSQL 的控制结构，你可以以非常灵活而且强大的方法操纵 PostgreSQL 的数据。

从函数返回

有两个命令可以用来从函数中返回数据：RETURN 和 RETURN NEXT。

RETURN

RETURN expression;
带表达式的 RETURN 是用于终止函数, 然后 expression 的值返回给调用者。

如果返回标量类型，那么可以使用任何表达式。表达式的类型将被自动转换成函数的返回类型，就像我们在赋值中描述的那样。要返回一个复合（行）数值，你必须写一个记录或者行变量做 expression。

一个函数的返回值不能是未定义。如果控制到达了函数的最顶层的块而没有碰到一个 RETURN 语句，那么它就会发生一个错误。

请注意如果你声明了该函数返回 void，那么仍然必须声明 RETURN 语句；但是，跟在 RETURN 后面的表达式是可选的，并且在任何情况下都会被忽略。

RETURN NEXT

RETURN NEXT expression;
如果一个 PL/pgSQL 函数声明为返回 SETOF sometype，那么遵循的过程则略有不同。在这种情况下，要返回的独立的项是在 RETURN NEXT 命令里声明的，然后最后有一个不带参数的 RETURN 命令用于告诉我们这个函数已经完成执行了。 RETURN NEXT 可以用于标量和复合数据类型；对于后者，将返回一个完整的结果"表"。

使用 RETURN NEXT 的函数应该按照下面的风格调用：

SELECT * FROM some_func();
也就是说，这个函数是用做FROM子句里面的一个表数据源的。

RETURN NEXT 实际上并不从函数中返回；它只是简单地把表达式的值保存起来。然后执行继续执行 PL/pgSQL 函数里的下一条语句。随着后继的 RETURN NEXT 命令的执行，结果集就建立起来了。最后的一个不需要参数的 RETURN，导致控制退出该函数。

注意: 目前的 PL/pgSQL 的 RETURN NEXT 实现在从函数返回之前把整个结果集都保存起来，就象上面描述的那样。这意味着如果一个 PL/pgSQL 函数生成一个非常大的结果集，性能可能会很差：数据将被写到磁盘上以避免内存耗尽，但是函数在完成整个结果集的生成之前不会退出。将来的 PL/pgSQL 版本可能会允许用户定义没有这样限制的返回集合的函数。目前，数据开始向磁盘里写的时刻是由配置变量 work_mem 控制的。拥有足够内存的管理员如果想在内存里存储更大的结果集，则可以考虑把这个参数增大一些。

    条件

    IF 语句让你可以根据某种条件执行命令。 PL/pgSQL有五种形式的IF：

IF ... THEN

IF ... THEN ... ELSE

IF ... THEN ... ELSE IF

IF ... THEN ... ELSIF ... THEN ... ELSE

IF ... THEN ... ELSEIF ... THEN ... ELSE

IF-THEN

IF boolean-expression THEN
statements
END IF;
IF-THEN语句是IF的最简单形式。如果条件为真，在THEN和END IF之间的语句将被执行。否则，将忽略它们。

例子：

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

    IF-THEN-ELSE
IF boolean-expression THEN
    statements
ELSE
    statements
END IF;
    IF-THEN-ELSE语句增加了IF-THEN的分支，让你可以声明在条件计算结果为假的时候执行的语句。

例子：

IF parentid IS NULL OR parentid = ''
THEN
RETURN fullname;
ELSE
RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;

IF v_count > 0 THEN
    INSERT INTO users_count(count) VALUES(v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;

IF-THEN-ELSE IF

IF语句可以嵌套并且在下面的例子中：

IF demo_row.sex = 'm' THEN
pretty_sex := 'man';
ELSE
IF demo_row.sex = 'f' THEN
pretty_sex := 'woman';
END IF;
END IF;

如果你使用这种形式，那么你实际上就是在另外一个IF语句的ELSE 部分嵌套了一个IF语句．因此你需要一个END IF语句给每个嵌套的IF，另外还要一个给父IF-ELSE用．这么干是可以的，但是如果我们有太多候选项需要检查，那么就会变得很乏味．因此有下面的形式。

IF-THEN-ELSIF-ELSE

IF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
    ...]]
[ ELSE
    statements ]
END IF;
IF-THEN-ELSIF-ELSE提供了一种更方便的方法用于在一条语句中检查许多候选条件。形式上它和嵌套的IF-THEN-ELSE-IF-THEN命令相同，但是只需要一个END IF。

这里是一个例子：

IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    -- 另外一个唯一的可能是它是空值
    result := 'NULL';
END IF;

IF-THEN-ELSEIF-ELSE

ELSEIF 是 ELSIF 的别名。

   使用LOOP，WHILE，FOR 和 EXIT 语句，你可以控制你的 PL/pgSQL 函数重复一系列命令。

    LOOP
[<<label>>]
LOOP
    statements
END LOOP;

    LOOP 定义一个无条件的循环，无限循环，直到由EXIT或者RETURN语句终止。可选的标签可以由EXIT语句使用，用于在嵌套循环中声明应该结束哪一层循环。

EXIT

EXIT [ label ] [ WHEN expression ];

如果没有给出 label，那么退出最内层的循环，然后执行跟在END LOOP后面的语句。如果给出 label，那么它必须是当前或者更高层的嵌套循环块或者块的标签。然后该命名块或者循环就会终止，而控制落到对应循环/块的 END 语句后面的语句上。

如果出现了WHEN，循环退出只发生在声明的条件为真的时候，否则控制会落到EXIT后面的语句上。

EXIT 可以用于在所有的循环类型中提前退出；它并不仅限于在无条件循环中使用。

例子：

LOOP
    -- 一些计算
    IF count > 0 THEN
        EXIT; -- exit loop
    END IF;
END LOOP;

LOOP
-- 一些计算
EXIT WHEN count > 0;
END LOOP;

BEGIN
    -- 一些计算
    IF stocks > 100000 THEN
        EXIT; -- 导致从 BEGIN 块里退出
    END IF;
END;

WHILE

[<<label>>]
WHILE expression LOOP
statements
END LOOP;
只要条件表达式为真，WHILE语句就会不停在一系列语句上进行循环. 条件是在每次进入循环体的时候检查的.

比如:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- 可以在这里做些计算
END LOOP;

WHILE NOT BOOLEAN_expression LOOP
-- 可以在这里做些计算
END LOOP;

FOR （整数变种）
[<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
statements
END LOOP;

这种形式的FOR对一定范围的整数数值进行迭代的循环。变量name 会自动定义为integer类型并且只在循环里存在。给出范围上下界的两个表达式在进入循环的时候计算一次。迭代步进值总是为 1，但如果声明了REVERSE就是 -1。

一些整数FOR循环的例子∶

FOR i IN 1..10 LOOP
-- 这里可以放一些表达式
RAISE NOTICE 'i IS %', i;
END LOOP;

FOR i IN REVERSE 10..1 LOOP
-- 这里可以放一些表达式
END LOOP;

如果下界大于上界（或者是在 REVERSE 情况下是小于），那么循环体将完全不被执行。而且不会抛出任何错误。

使用不同类型的FOR循环，你可以遍历一个命令的结果并且相应的操作哪些数据。语法是：

[<<label>>]
FOR record_or_row IN query LOOP
statements
END LOOP;
这里的记录或者行变量将相继被赋予所有来自query（必须是一条 SELECT 命令）的行，并且循环体将为每行执行一次。下面是一个例子：

CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
mviews RECORD;

BEGIN
PERFORM cs_log('Refreshing materialized views...');

FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

-- 现在 "mviews" 里有了一条来自 cs_materialized_views 的记录

        PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
        EXECUTE 'INSERT INTO ' || quote_ident(mview.mv_name) || ' ' || mview.mv_query;
     END LOOP;

     PERFORM cs_log('Done refreshing materialized views.');
     RETURN 1;
END;
$$ LANGUAGE plpgsql;
    如果循环是用一个EXIT语句终止的，那么在循环之后你仍然可以访问最后赋值的行。

FOR-IN-EXECUTE语句是遍历所有行的另外一种方法：

[<<label>>]
FOR record_or_row IN EXECUTE text_expression LOOP
statements
END LOOP;
这个例子类似前面的形式，只不过源SELECT语句声明为了一个字串表达式，这样它在每次进入FOR循环的时候都会重新计算和生成执行计划。这样就允许程序员在一个预先规划好了的命令所获得的速度，和一个动态命令所获得的灵活性（就象一个简单的EXECUTE语句那样）之间进行选择。

注意: PL/pgSQL 分析器目前区分两种类型的FOR循环（整数或者返回记录的）：方法是检查是否有任何 .. 出现在 IN 和 LOOP 之间的圆括弧之外。如果没有看到 ..，那么这个循环就是在数据行上的循环。如果误敲了 .. 就很可能会导致像下面这样的错误信息： "loop variable of loop over rows must be a record or row variable"，而不是我们以为会看到的简单的语法错误。

捕获错误

缺省时，一个在 PL/pgSQL 函数里发生的错误退出函数的执行，并且实际上是其周围的事务也会退出。你可以使用一个带有 EXCEPTION 子句的 BEGIN 块捕获错误并且从中恢复。其语法是正常的 BEGIN 块语法的一个扩展：

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

如果没有发生错误，这种形式的块只是简单地执行所有 statements，但是如果在 statements 里发生了一个错误，则对 statements 的进一步处理将废弃，控制传递到了 EXCEPTION 列表。系统搜索这个列表，寻找匹配发生的错误的第一个元素。如果找到匹配，则执行对应的 handler_statements，然后控制传递到 END 之后的下一个语句。如果没有找到匹配，该错误就会广播出去，就好像根本没有 EXCEPTION 子句一样：该错误可以被一个包围块用 EXCEPTION 捕获，如果没有包围块，则退出函数的处理。

condition 名字可以是 Appendix A 里显示的任何名字。一个范畴名匹配任意该范畴里的错误。特殊的条件名 OTHERS 匹配除了 QUERY_CANCELED 之外的所有错误类型。（我们可以用名字捕获 QUERY_CANCELED，不过通常是不明智的。）条件名是大小写无关的。

如果在选中的 handler_statements 里发生了新错误，那么它不能被这个 EXCEPTION 子句捕获，而是传播出去。一个外层的 EXCEPTION 子句可以捕获它。

如果一个错误被 EXCEPTION 捕获，PL/pgSQL 函数的局部变量保持错误发生的时候的原值，但是所有该块中想固化在数据库中的状态都回滚。作为一个例子，让我们看看下面片断：

    INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
    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;

当控制到达给 y 赋值的地方的时候，它会带着一个 division_by_zero 错误失败。这个错误将被 EXCEPTION 子句波获。而在 RETURN 语句里返回的数值将是 x 的增量值。但是，在该块之前的 INSERT 将不会回滚，因此最终的结果是数据库包含 Tom Jones 而不是 Joe Jones。

提示: 进入和退出一个包含 EXCEPTION 子句的块要比不包含的块开销大的多。因此，不必要的时候不要使用 EXCEPTION。