PL/pgSQL 流程控制

最新推荐文章于 2023-07-09 08:52:04 发布

肯尼思布赖恩埃德蒙

最新推荐文章于 2023-07-09 08:52:04 发布

阅读量867

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/weixin_43638238/article/details/118661525

版权

SQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1.返回

1.1 RETURN用于不返回非集合的结果

RETURN expression;

-- 返回一个标量类型的函数
RETURN 1 + 2;
RETURN scalar_var;

-- 返回一个组合类型的函数
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- 必须把列造型成正确的类型

1.2 RETURN NEXT以及RETURN QUERY 返回结果集

RETURN NEXT和RETURN QUERY实际上不会从函数中返回 — 它们简单地向函数的结果集中追加零或多行。然后会继续执行PL/pgSQL函数中的下一条语句。随着后继的RETURN NEXT和RETURN QUERY命令的执行，结果集就建立起来了。最后一个RETURN（应该没有参数）会导致控制退出该函数（或者你可以让控制到达函数的结尾）。

RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

--return next例子：
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM foo WHERE fooid > 0
    LOOP
        -- 这里可以做一些处理
        RETURN NEXT r; -- 返回 SELECT 的当前行
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();

--return query例子：
CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY SELECT flightid
                   FROM flight
                  WHERE flightdate >= $1
                    AND flightdate < ($1 + 1);

    -- 因为执行还未结束，我们可以检查是否有行被返回
    -- 如果没有就抛出异常。
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No flight at %.', $1;
    END IF;

    RETURN;
 END
$BODY$
LANGUAGE plpgsql;

-- 返回可用的航班或者在没有可用航班时抛出异常。
SELECT * FROM get_available_flightid(CURRENT_DATE);

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

1.3 RETURN的其他用途

如果你声明带输出参数的函数，那么就只需要写不带表达式的RETURN。输出参数变量的当前值将被返回。
如果你声明函数返回void，一个RETURN语句可以被用来提前退出函数；但是不要在RETURN后面写一个表达式。

1.4 注意

一个函数的返回值不能是未定义。如果控制到达了函数最顶层的块而没有碰到一个RETURN语句，那么会发生一个运行时错误。不过，这个限制不适用于带输出参数的函数以及返回void的函数。在这些情况中，如果顶层的块结束，将自动执行一个RETURN语句。

2.调用存储过程

PL/pgSQL函数，存储过程或DO块可以使用 CALL调用存储过程。输出参数的处理方式与纯SQL中CALL的工作方式不同。存储过程的每个INOUT参数必须和CALL语句中的变量对应，并且无论存储过程返回什么，都会在返回后赋值给该变量。

CREATE PROCEDURE triple(INOUT x int)
LANGUAGE plpgsql
AS $$
BEGIN
    x := x * 3;
END;
$$;

DO $$
DECLARE myvar int := 5;
BEGIN
  CALL triple(myvar);
  RAISE NOTICE 'myvar = %', myvar;  -- prints 15
END
$$;

3.IF-THEN-ELSE

else允许指定一组在条件不为真时应该被执行的语句（注意这也包括条件为 NULL 的情况）。
关键词ELSIF也可以被拼写成ELSEIF。

IF boolean-expression THEN
statements
ELSE
statements
END IF;

4.CASE

如果没有找到匹配，ELSE 语句会被执行。但是如果ELSE不存在，将会抛出一个CASE_NOT_FOUND异常。

CASE x
WHEN 1, 2 THEN
msg := 'one or two';
ELSE
msg := 'other value than one or two';
END CASE;

CASE
WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
... ]
[ ELSE
statements ]
END CASE;

5.FOR-LOOP

LOOP定义一个无条件的循环，它会无限重复直到被EXIT或RETURN语句终止。可选的label可以被EXIT和CONTINUE语句用在嵌套循环中指定这些语句引用的是哪一层循环。

--从查询结果集中取值
[ <<label>> ]
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
statements
END LOOP [ label ];

-- 按顺序取值（reverse 倒序取值）
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
    statements
END LOOP [ label ];

第一个例子中：也可以使用游标来遍历

第二个例子中：如果下界大于上界（或者在REVERSE情况下是小于），循环体根本不会被执行。而且不会抛出任何错误。

6. FOREACH

--通过数组取值
CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
  s int8 := 0;
  x int;
BEGIN
  FOREACH x IN ARRAY $1
  LOOP
    s := s + x;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql;

--通过切面取值
CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
x int[];
BEGIN
FOREACH x SLICE 1 IN ARRAY $1
LOOP
RAISE NOTICE 'row = %', x;
END LOOP;
END;
$$ LANGUAGE plpgsql;

第二种方式的输出结果：
NOTICE: row = {1,2,3}
NOTICE: row = {4,5,6}
NOTICE: row = {7,8,9}
NOTICE: row = {10,11,12}

7. LOOP、WHILE等简单循环

--loop循环
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- 这里是一些计算
END LOOP;
WHILE NOT done LOOP
    -- 这里是一些计算
END LOOP;

--while
[ <<label>> ]
WHILE boolean-expression LOOP
statements
END LOOP [ label ];

8.CONTINUE

CONTINUE [ label ] [ WHEN boolean-expression ];

如果没有给出label，最内层循环的下一次迭代会开始。也就是，循环体中剩余的所有语句将被跳过，并且控制会返回到循环控制表达式（如果有）来决定是否需要另一次循环迭代。如果label存在，它指定应该继续执行的循环的标签。

9.EXIT

EXIT [ label ] [ WHEN boolean-expression ];

在和BEGIN块一起使用时，EXIT会把控制交给块结束后的下一个语句。需要注意的是，一个标签必须被用于这个目的；一个没有被标记的EXIT永远无法被认为与一个BEGIN块匹配（这种状况从PostgreSQL 8.4 之前的发布就已经开始改变。这可能允许一个未被标记的EXIT匹配一个BEGIN块）。

10.EXCEPTION

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

condition的名字可以是《PostgreSQL错误代码》中显示的任何名字。一个分类名匹配其中所有的错误。特殊的条件名OTHERS匹配除了QUERY_CANCELED和ASSERT_FAILURE之外的所有错误类型（虽然通常并不明智，还是可以用名字捕获这两种错误类型）。条件名是大小写无关的。一个错误条件也可以通过SQLSTATE代码指定。

-- 以下是等价的
WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...

异常下，是否会滚：
当前块中所有对持久数据库状态的改变都会被回滚。（不在当前块下，则不会回滚）
性能：
进入和退出一个包含EXCEPTION子句的块要比不包含EXCEPTION的块开销大的多。因此，只在必要的时候使用EXCEPTION。

11.一个流程控制的例子

这个例子使用异常处理来酌情执行UPDATE或 INSERT。我们推荐应用使用带有 ON CONFLICT DO UPDATE的INSERT 而不是真正使用这种模式。下面的例子主要是为了展示 PL/pgSQL如何控制流程：

-- 表声明
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

--函数声明
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- 首先尝试更新见
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
-- 不在这里，那么尝试插入该键
-- 如果其他某人并发地插入同一个键，
-- 我们可能得到一个唯一键失败
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- 什么也不做，并且循环再次尝试 UPDATE
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;

-- 调用函数，并观察结果
SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');
得到有关一个错误的信息
GET STACKED DIAGNOSTICS variable { = | := } item [ , ... ];

item标识一个被赋予给指定变量（应该具有接收该值的正确数据类型）的状态值，参考《错误诊断项》

12. 一个异常抛出的例子

GET DIAGNOSTICS命令检索有关当前执行状态的信息（反之上文讨论的GET STACKED DIAGNOSTICS命令会把有关执行状态的信息报告成一个以前的错误）。它的PG_CONTEXT状态项可用于标识当前执行位置。状态项PG_CONTEXT将返回一个文本字符串，其中有描述该调用栈的多行文本。第一行会指向当前函数以及当前正在执行GET DIAGNOSTICS的命令。第二行及其后的行表示调用栈中更上层的调用函数。

CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
BEGIN
RETURN inner_func();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
DECLARE
stack text;
BEGIN
GET DIAGNOSTICS stack = PG_CONTEXT;
RAISE NOTICE E'--- Call Stack ---\n%', stack;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT outer_func();

NOTICE: --- Call Stack ---
PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
PL/pgSQL function outer_func() line 3 at RETURN
CONTEXT: PL/pgSQL function outer_func() line 3 at RETURN
outer_func
------------
1
(1 row)

GET STACKED DIAGNOSTICS … PG_EXCEPTION_CONTEXT返回同类的栈跟踪，但是它描述检测到错误的位置而不是当前位置。

肯尼思布赖恩埃德蒙

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
PL/pgSQL 流程控制

1.返回1.1 RETURN用于不返回非集合的结果RETURN expression;-- 返回一个标量类型的函数RETURN 1 + 2;RETURN scalar_var;-- 返回一个组合类型的函数RETURN composite_type_var;RETURN (1, 2, 'three'::text); -- 必须把列造型成正确的类型1.2 RETURN NEXT以及RETURN QUERY 返回结果集RETURN NEXT expression;RETURN QUE
复制链接

扫一扫