Postgresql 从小白到高手 八：PL/pgSQL--SQL过程语言

Postgresql 从小白到高手 八 – PL/pgSQL 存储过程

第八章：PL/pgSQL 存储过程

---

`

---

一、过程语言

PostgreSQL允许使用除了 SQL 和 C 之外的其他语言编写用户定义的函数。这些其他的语言通常被称作过程语言（PL）。
四种过程语言可用： PL/pgSQL、 PL/Tcl、 PL/Perl 以及 PL/Python。
PL/pgsql 是内置安装的，其他过程语言需要扩展安装。

---

二 、PL/pgSQL 函数

1.PL/pgSQL语言结构

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

同级语句，缩进格数一致。执行顺序从上到下，从同级别到下级，从左到右。

2.创建

关键方法词：

2.1 DECLARE ： 申明变量

一个变量声明的一般语法是：

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

如果给定DEFAULT子句，它会指定进入该块时分 配给该变量的初始值。如果没有给出DEFAULT子句， 则该变量被初始化为SQL空值。 CONSTANT选项阻止该变量在初始化之后被赋值， 这样它的值在块的持续期内保持不变。COLLATE 选项指定用于该变量的一个排序规则。如果指 定了NOT NULL，对该变量赋值为空值会导致一个 运行时错误。所有被声明为NOT NULL的变量必须 被指定一个非空默认值。 等号（=）可以被用来代替 PL/SQL-兼容的 :=。

DECLARE   pdid integer := 30;	

申明函数变量：
传递给函数的参数被命名为标识符$1、$2等等。可选地，能够为$n参数名声明别名来增加可读性。不管是别名还是数字标识符都能用来引用参数值。

CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

2.2 RETURNS 输出

输出变量 :RETURNS text

RETURNS TABLE ：输出记录

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

调用带有 OUT 参数的函数， 在函数调用中省略输出参数：

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

调用存储过程出参可定义为null ：
CALL sum_n_product(2, 4, NULL, NULL);

多太函数：根据具体结果确定返回类型：

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

newname ALIAS FOR oldname; 可以为任何变量起别名 。

COLLATE ：排序规则

Function less_than
SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;

结果按c规则排序

DECLARE  local_a text COLLATE "en_US";

表达式 ：IF expression THEN …
expression 在语句运行是会自动被翻译成sql中的select 语句 。
因为一个 expression 可以被转换成 SELECT 命令，它可以包含与普通SELECT命令相同的子句，但是，它不能包含顶级UNION，INTERSECT， 或者 EXCEPT子句。
SELECT 必须生成一个单行列，不能超过一行。

赋值：variable { := | = } expression;
执行sql 结果处理：
返回一行或者只取一行 ： select xxx into from table ;加 into 语句 。
循环处理返回结果。

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

target是一个记录变量、行变量或者逗号分隔的标量变量列表。target被连续不断被赋予来自query的每一行，并且循环体将为每一行执行一次，如records。

CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Refreshing all materialized views...';
    FOR mviews IN
       SELECT n.nspname AS mv_schema,
              c.relname AS mv_name,
              pg_catalog.pg_get_userbyid(c.relowner) AS owner
         FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
        WHERE c.relkind = 'm'
     ORDER BY 1
    LOOP
        -- Now "mviews" has one record with information about the materialized view
        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
                     quote_ident(mviews.mv_schema),
                     quote_ident(mviews.mv_name),
                     quote_ident(mviews.owner);
        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
    END LOOP;
    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

另一种行数据处理

[ <<label>> ]
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
    statements
END LOOP [ label ];

2.3 一些方法函数

PERFORM :调用结果
调用一个有副作用但是没有有用的结果值的函数:

PERFORM query;

EXCEPTION： 错误捕捉

BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

SELECT INTO ：执行单一行结果sql，如增删改

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

EXECUTE ：执行动态sql
想要在PL/pgSQL函数中产生动态命令，也就是每次执行中会涉及到不同表或不同数据类型的命令,这个时候就需要EXECUTE 语句。

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

动态 SQL 语句也可以使用format函数来安全地构造。例如：

EXECUTE format('UPDATE tbl SET %I = %L '
   'WHERE key = %L', colname, newvalue, keyvalue); 
%I等效于quote_ident并且 %L等效于quote_nullable。 format函数可以和 USING子句一起使用：

EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)

请注意美元符号引用只对引用固定文本有用。尝试写出下面这个例子是一个非常糟糕的主意：

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = $$'
        || newvalue
        || '$$ WHERE key = '
        || quote_literal(keyvalue);

获得结果状态: GET ;
循环：
FETCH ：FETCH从游标中检索下一行到目标中
MOVE：重新定位而不检索
FOR或FOREACH

NULL:无状态

PL/pgSQL有三种形式的IF：

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

以及两种形式的CASE：

CASE ... WHEN ... THEN ... ELSE ... END CASE
CASE WHEN ... THEN ... ELSE ... END CASE

使用LOOP、EXIT、CONTINUE、WHILE、FOR和FOREACH语句，你可以安排PL/pgSQL重复一系列命令。

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

3.例子

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- 创建一个子块
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;
   RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50
   
   RETURN quantity;
END;
$$ LANGUAGE plpgsql;

select  somefunc(4 ,'xc') ;

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;
$$;

4.游标循环

4.1 申明游标

    name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

为了对Oracle的兼容性，可以用IS替代FOR,如果指定了SCROLL，那么游标可以反向滚动；如果指定了NO SCROLL，那么反向取的动作会被拒绝.

4.2 打开游标

OPEN FOR query:
在一个游标可以被用来检索行之前，它必需先被打开（这是和 SQL 命令DECLARE CURSOR等效的操作）。PL/pgSQL有三种形式的OPEN命令，其中两种用于未绑定游标变量，另外一种用于已绑定的游标变量。

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

OPEN FOR EXECUTE:

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string   [ USING expression [, ... ] ];

OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

打开一个已绑定的游标:

OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];

例如：

DECLARE
    key integer;
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
    key := 42;
    OPEN curs4;

4.3 使用游标

就像SELECT INTO一样，FETCH从游标中检索下一行到目标中;
FETCH [ direction { FROM | IN } ] cursor INTO target;

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

MOVE重新定位一个游标而不检索任何数据;
MOVE [ direction { FROM | IN } ] cursor;

MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

当一个游标被定位到一个表行上时，使用该游标标识该行就可以对它进行更新或删除;
UPDATE table SET … WHERE CURRENT OF cursor;
DELETE FROM table WHERE CURRENT OF cursor;

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

关闭游标、：
CLOSE cursor;

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- 需要在一个事务中使用游标。
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;

for 使用游标：

[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
    statements
END LOOP [ label ];