基于开发者空间OpenGauss数据库的PLPGSQL实践二

OpenGauss PL/pgSQL实践详解

最新推荐文章于 2025-12-04 15:25:15 发布

原创最新推荐文章于 2025-12-04 15:25:15 发布 · 1k 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle

1 概述

1.1 案例介绍

PLPGSQL是一种程序语言，叫做过程化SQL语言(Procedural Language/Postgres SQL)，pl/pgsql是PostgreSQL数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编译语言的特点，所以pl/pgsql就是把数据操作和查询语句组织在pl/pgsql代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

本案例选择OpenGauss数据库作为示例，并借助开发者空间云主机提供的免费OpenEuler系统环境和OpenGaussDB实例，直观地展示 PL/PGSQL在商业应用开发与过程化编程能力和实际应用开发中为开发者带来的便利。

通过实际操作，让大家深入了解如何利用 PLPGSQL 开发并部署一个函数功能模块。在这个过程中，大家将学习到从函数创建、数据批量读取到SQL程序编写以及与触发器集成等一系列关键步骤，从而掌握 PLPGSQL 的基本使用方法，体验其在应用开发中的优势。

1.2 适用对象

企业
个人开发者
高校学生

1.3 案例时间

本案例总时长预计60分钟。

1.4 案例流程

基于开发者空间OpenGauss数据库的PLPGSQL实践二_openGauss

说明：

领取空间开发桌面；
在空间开发桌面终端进入OpenGaussDB；
进行数据库的开发者空间进行OpenGaussDB之PL/pgSQL的操作；

1.5 资源总览

资源名称	规格	单价(元)	时长(分钟)
开发者空间-云主机	鲲鹏通用计算增强型 kC2 \| 4vCPUs \| 8G \| 实际操作系统Euler	免费	60

最新案例动态，请查阅 《基于开发者空间OpenGauss数据库的PLPGSQL实践二》。小伙伴快来领取华为开发者空间进行实操体验吧！

2 OpenGauss数据库PL/pgSQL实践

2.1 开发者空间配置

面向广大开发者群体，华为开发者空间提供一个随时访问的“开发桌面云主机”、丰富的“预配置工具集合”和灵活使用的“场景化资源池”，开发者开箱即用，快速体验华为根技术和资源。

如何还没有领取开发者空间云主机，可以参考免费领取云主机文档领取。

领取云主机后可以直接进入华为开发者空间工作台界面，点击打开云主机 \> 进入桌面连接云主机。

基于开发者空间OpenGauss数据库的PLPGSQL实践二_openGauss_02

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_03

2.2 启动OpenGaussDB实例并登录

本案例中，使用OpenGaussDB开发平台，完成SQL的编程和自定义函数等多种功能。

基于之前案例《基于开发者空间部署OpenGauss主备集中式数据库系统》。在云主机部署OpenGaussDB实例。并启动数据库服务。

进入OpenGaussDB的安装目录的bin文件，该案例云主机环境中安装目录在环境变量\$GAUSSHOME中，读者根据自己云主机安装目录进行操作修改。

初始化数据库实例，初始化数据库目录在当前目录下data，设置节点名称和初始化用户密码。如下所示

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_04

以单节点模式启动数据库实例，并在当前目录下输出日志文件logfile

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_05

用gsql客户端工具，进入OpenGaussDB数据库。参数 -a表示追加、-r表示使用readline

基于开发者空间OpenGauss数据库的PLPGSQL实践二_数据库_06

3 PL/PGSQL实践

PLPGSQL是数据库的编程语言。相当于在数据库中用SQL语言进行逻辑处理与代码开发。可以把业务系统中封装的功能模块下沉到数据库端实现，以达到减轻业务系统的逻辑压力、降低架构复杂度和简化业务系统实现难度。

PLPGSQL是一种块结构型语言，例如匿名块，存储过程和函数体的完整文本必须是块。块定义如下：

PLPGSQL定义的功能模块(存储过程和函数)可以互相嵌套。例如SQL块中嵌套子SQL块，存储过程引用PLPGSQL定义的其他函数和模块功能。

3.1 游标之数据批量处理

游标用于对 SQL 查询的结果集做批处理读取场景。当SQL查询的结果集数据量过大，一次性读取会导致内存不够缓存。所以出现了游标的功能，用COUSOR对大数据量的结果集中分批处理(例如一次取1000条数据)，直到循环多次，批量把结果集里的数据读取完毕。

3.1.1 声明游标变量

PL/pgSQL语法中，所有游标都必须通过游标变量去访问，游标变量是特殊的数据类型 refcursor。游标变量的声明语法如下：

当关键字 SCROLL 被指定，则游标可以向后滚动处理数据，如果指定了NO SCROLL，则游标向后读取数据将被拒绝。如果没有指定 SCROLL，游标是否自动向后读取数据取决了SQL查询。arguments 如果被指定，则其以逗号分隔以对应 query 查询数据类型的列表，arguments的字段名称将替换掉 query查询中对应的字段名。示例如下：

DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

这3个游标变量都属于 refcursor 数据类型，但curs1能被用于任何Query，curs2已经被绑定了一个完整的query查询，最后的curs3游标绑定了一个参数化的Query(当curs3被打开时，key用一个整形数值替代)。curs1由于没有绑定于任何指定的Query，所以可称之为末绑定的游标变量curs1。

当游标Query使用FOR UPDATE / SHARE(互斥锁 / 共享锁)时，则不能指定关键字SCROLL。对于Query涉及volatile函数时，则推荐使用NO SCROLL。SCROLL的实现场景是游标对Query输出结果集多次读取以保证读取结果的一致性(多次读取的数据内容保持不变)，而Query里的volatile函数无法保证这一点。

3.1.2 打开游标

语法1：

该语法操作是打开游标去执行游标绑定的Query查询。游标必须已经声明的refcursor变量，Query查询必须是SELECT，或返回元组的其他内容(例如EXPLAIN)。

示例如下：

语法2：

该游标变量被打开并指定的Query查询去执行。

此用例中，通过Query中函数format()写入表名，col1的值是通过USING插入的，因此这不需要引用。

语法3：

这种方法是游标在声明时，已经绑定了动态SQL，而在打开游标时，需要对该游标适配具体的参数值，使其在动态SQL中被替换掉。这种方法下，SQL计划一直保存在缓存中，直到游标执行结束。这种方法不能指定关键字SCROLL和NO SCROLL，因为游标的滚动行为已经确定。该语法3与语法2的EXECUTE关键字并不等价。

游标的参数值用两种方法传递：位置和命名。用参数位置表示时，所有参数都是按顺序指定的。在参数命名法中，使用 := 指定每个参数的名称，将其与参数表达式分开。

如下示例：

下面示例中的curs4，效果与上面curs3一样。

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

3.1.3 使用游标

通过上面语法打开游标后，用 FETCH 语句来操作它。当PL/pgSQL函数要批量返回数据时，可以让函数返回refcursor值，调用函数对游标进行操作。refcursor变量值只能引用打开的游标，直到事务结束时被隐匿的关闭。

3.1.3.1 FETCH

语法如下：

FETCH从游标中检索下一行(在指示的方向上)到目标中，该目标可能是行变量、记录变量或逗号分隔的简单变量列表，就像SELECT INTO一样。如果没有合适的行，则将目标设置为NULL(s)。与SELECT INTO一样，可以检查特殊变量，以查看是否获取了行。如果没有获得行，则光标定位在最后一行之后或第一行之前，具体取决于移动方向。

方向子句可以是SQL FETCH命令中允许的任何变体，但可以读取多行的变体除外；例如，它可以是NEXT、PRIOR、First、Last、ABSOLUTE count、RELATION count、Forward或backward。省略方向与指定NEXT相同。在使用计数的表单中，计数可以是任何整数值表达式(与SQL FETCH命令不同，SQL FETCH命令只允许使用整数常量)。需要向后移动的方向值很可能会失败，除非游标是用SCROLL选项声明或打开的。

游标必须是引用打开的游标入口的refcursor变量的名称。示例如下：

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

3.1.3.2 MOVE

语法如下：

关键字MOVE在不检索任何数据的情况下重新定位游标。MOVE的原理与FETCH类似，其不同在于MOVE只重新定位光标，而不返回移动到的行。而SELECT INTO 通过指定变量FOUND能够检测其移动的位置是否在数据集中，避免发生错误。如果没有指定的drection，则游标会移到数据集最后一行的下一行位置，或者第一行的前面，其取决于指定的drection。

3.1.3.3 UPDATE/DELETE WHERE CURRENT OF

语法如下：

当游标位于表的某一元组时，则使用该语法，游标指定的元组可以被修改更新或者删除元组。如果要限制游标查询，则应该使用FOR UPDATE。

3.1.3.4 关闭游标

语法如下：

对打开的游标执行关闭操作，可以理解为游标资源的释放。但该操作应该在事务结束之前，对游标进行释放。如果在事务结束时没有关闭游标，则结束事务时也会自动对游标进行关闭操作。

示例如下：

3.1.4 通过游标批量处理结果集

PL/pgSQL函数返回一个游标时，在处理大数据集时，返回多行或者多列比较高效。

下面示例中，游标名称被调用者指定的用法：

DORP TABLE IF EXISTS test;
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

基于开发者空间OpenGauss数据库的PLPGSQL实践二_数据库_07

基于开发者空间OpenGauss数据库的PLPGSQL实践二_openGauss_08

基于开发者空间OpenGauss数据库的PLPGSQL实践二_数据库_09

游标名称自动生成的用例：

-- 此处自行创建用例表test
DROP TABLE IF EXISTS test;
CREATE TABLE test(col int);
CREATE FUNCTION reffunc2() RETURNS refcursor AS $$
DECLARE
    ref_temp refcursor;
BEGIN
    OPEN ref_temp FOR SELECT col FROM test;
    RETURN ref_temp;
END;
$$ LANGUAGE plpgsql;

基于开发者空间OpenGauss数据库的PLPGSQL实践二_数据库_10

-- need to be in a transaction to use cursors.
BEGIN;
SELECT reffunc2();

      reffunc2
--------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;

基于开发者空间OpenGauss数据库的PLPGSQL实践二_openGauss_11

红框中根据实际内容而改变。

下面用例展示，从函数中返回多个游标的用法：(注：根据Function实际，创建前需要建表table_1和table_2)

-- 此处简单创建用例表table_1, table_2
create table table_1(id int);
create table table_2(id int); 
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;

基于开发者空间OpenGauss数据库的PLPGSQL实践二_openGauss_12

-- need to be in a transaction to use cursors.
BEGIN;

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

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

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_13

注：首先要确保上面自定义函数中调用的表table_1和table_2存在，此处该两张表都是text字段类型。如下所示

基于开发者空间OpenGauss数据库的PLPGSQL实践二_数据库_14

3.1.4.1 使用LOOP循环体从游标中获取结果集

使用FOR关键字的LOOP循环体，用迭代变量获取游标返回的元组数据，循环处理游标中的数据结果。语法如下：

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

这里使用的游标必须已经绑定到Query，并且不能是已经被打开的游标变量。FOR语句会自动打开游标，并在循环退出时自动关闭游标。当FORLOOP使用带参数的游标时，则必须使用表达式的实际参数值。其方式与OPEN方式相同。

3.2 自定义函数实现

PL/pgSQL中自定义函数非常常见。通常用CREATE FUNCTIONA表示该操作。函数必须要有返回值RETURNS。其它与存储过程内容一致。但当函数没有要返回的结果时，可以只写

RETURN;表示返回NULL。

自定义函数其他章节已经有过多介绍和示例，此处不再过多说明。

示例：(函数get_score_point根据输入的vscore成绩分数返回对应的绩点)

CREATE or REPLACE FUNCTION get_score_point(vscore INT)
RETURNS DECIMAL
as $$
DECLARE
BEGIN
  RETURN (
  CASE
    WHEN vscore <= 59 THEN
      0
    WHEN vscore <= 69 THEN
      0.1
    WHEN vscore <= 79 THEN
      0.2
    WHEN vscore <= 89 THEN
      0.3
    ELSE
      0.4
  END);
END; $$ LANGUAGE PLPGSQL;

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_15

示例：(函数add_mask根据SQL查询对部分字段脱敏，调用dbe_output.print_line输出元组数据)

CREATE OR REPLACE FUNCTION add_mask(id1 TEXT, id2 TEXT) RETURNS TEXT AS $$
DECLARE
    var1 TEXT;
    var2 int;
    cursor c1 is select case when id::text = id2 then '***' else id::text end as id, ranking from (select id, dense_rank() over(order by sum_point desc) as ranking from 
(select id,get_score_point(math)+get_score_point(art)+get_score_point(phy) as sum_point from stu)) where id::text = id1;
BEGIN
    open c1;
    loop fetch c1 into var1, var2;
    exit when c1%notfound;
        return dbe_output.print_line('学号是：'||var1||', 排名是：'||var2);
    end loop;
    close c1;
END;
$$ LANGUAGE PLPGSQL;

该用例中使用了dbe_output.print_line函数包，是GaussDB的兼容Oracle的商业特性，此处opengauss会报该函数不存在。故可忽略。

3.3 触发器实现

当数据库中表数据被改变或者发生数据库事件(events)时，可定义触发器函数，触发对应的操作。触发器函数与自定义函数类似，不同的是触发器函数返回的是触发器return trigger。

3.3.1 数据变更触发器

一般触发器函数是没有参数且返回触发器类型，但是触发器函数有自己的默认参数。其默认参数有12个，这里主要介绍常用的两个：NEW和OLD，其都是record数据类型。NEW参数主要用于行级操作的INSERT/UPDATE触发器。OLD参数主要用于行级操作的UPDATE/DELETE触发器。

下面示例，当表的一个元组插入或更新时，当前用户名和时间则会被写入元组last_user和last_date字段。并检查employee表的name字段是否被给定和salary的数据是否正确。

CREATE TABLE emp (
    empname           text,
    salary            integer,
    last_date         timestamp,
    last_user         text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when they must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE FUNCTION emp_stamp();

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_16

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_17

下面示例表示，当表emp元组的任何insert,update或者delete时，其记录都将被写到emp_audit表中。当时的时间和用户名也一并被写入。

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary            integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- making use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE FUNCTION process_emp_audit();

基于开发者空间OpenGauss数据库的PLPGSQL实践二_openGauss_18

基于开发者空间OpenGauss数据库的PLPGSQL实践二_openGauss_19

示例：

CREATE TABLE emp (
    empname           text PRIMARY KEY,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary            integer,
    stamp             timestamp NOT NULL
);

CREATE VIEW emp_view AS
    SELECT e.empname,
           e.salary,
           max(ea.stamp) AS last_updated
      FROM emp e
      LEFT JOIN emp_audit ea ON ea.empname = e.empname
     GROUP BY 1, 2;

CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
    BEGIN
        --
        -- Perform the required operation on emp, and create a row in emp_audit
        -- to reflect the change made to emp.
        --
        IF (TG_OP = 'DELETE') THEN
            DELETE FROM emp WHERE empname = OLD.empname;
            IF NOT FOUND THEN RETURN NULL; END IF;

            OLD.last_updated = now();
            INSERT INTO emp_audit VALUES('D', current_user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
            IF NOT FOUND THEN RETURN NULL; END IF;

            NEW.last_updated = now();
            INSERT INTO emp_audit VALUES('U', current_user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp VALUES(NEW.empname, NEW.salary);

            NEW.last_updated = now();
            INSERT INTO emp_audit VALUES('I', current_user, NEW.*);
            RETURN NEW;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
    FOR EACH ROW EXECUTE FUNCTION update_emp_view();

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_20

基于开发者空间OpenGauss数据库的PLPGSQL实践二_openGauss_21

示例：

--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
    time_key                    integer NOT NULL,
    day_of_week                 integer NOT NULL,
    day_of_month                integer NOT NULL,
    month                       integer NOT NULL,
    quarter                     integer NOT NULL,
    year                        integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key                    integer NOT NULL,
    product_key                 integer NOT NULL,
    store_key                   integer NOT NULL,
    amount_sold                 numeric(12,2) NOT NULL,
    units_sold                  integer NOT NULL,
    amount_cost                 numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
    time_key                    integer NOT NULL,
    amount_sold                 numeric(15,2) NOT NULL,
    units_sold                  numeric(12) NOT NULL,
    amount_cost                 numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEGIN

        -- Work out the increment/decrement amount(s).
        IF (TG_OP = 'DELETE') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') THEN

            -- forbid updates that change the time_key -
            -- (probably not too onerous, as DELETE + INSERT is how most
            -- changes will be made).
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
                                                      OLD.time_key, NEW.time_key;
            END IF;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        END IF;


        -- Insert or update the summary row with the new values.
        <<insert_update>>
        LOOP
            UPDATE sales_summary_bytime
                SET amount_sold = amount_sold + delta_amount_sold,
                    units_sold = units_sold + delta_units_sold,
                    amount_cost = amount_cost + delta_amount_cost
                WHERE time_key = delta_time_key;

            EXIT insert_update WHEN found;

            BEGIN
                INSERT INTO sales_summary_bytime (
                            time_key,
                            amount_sold,
                            units_sold,
                            amount_cost)
                    VALUES (
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );

                EXIT insert_update;

            EXCEPTION
                WHEN UNIQUE_VIOLATION THEN
                    -- do nothing
            END;
        END LOOP insert_update;

        RETURN NULL;

    END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
    FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_22

基于开发者空间OpenGauss数据库的PLPGSQL实践二_openGauss_23

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_24

示例：(下面示例中new_table和old_table需要替换)

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary            integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create rows in emp_audit to reflect the operations performed on emp,
        -- making use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit
                SELECT 'D', now(), current_user, o.* FROM old_table o;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit
                SELECT 'U', now(), current_user, n.* FROM new_table n;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit
                SELECT 'I', now(), current_user, n.* FROM new_table n;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit_ins
    AFTER INSERT ON emp
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_upd
    AFTER UPDATE ON emp
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
    AFTER DELETE ON emp
    REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

3.3.2 事件触发器

事件触发器即当有event发生时，触发对应的操作，该类触发器函数的默认参数有两个：TG_EVENT text和TG_TAG text。示例如下：

CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_25

3.4 打印输出说明

3.4.1 存储过程打印输出结果集

存储过程没有Return变量，因此存储过程结束时也没有Return语句。此场景下，如果要提前结束其运行过程，则使用没有表达式的Return语句。如果该存储过程有Output输出参数，则其会返回最终结果。

PL/pgSQL的函数，存储过程，Do匿名块都可以内部调用存储过程。Output参数与Call调用的处理方式不同。存储过程的每个Out 或者 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;
$$;

存储过程中输出参数对应的变量可以是简单数值，也可以是复合数值，但目前Output 参数不能是数组元素。

注意 raise 多用于调试 :

RAISE LEVEL ... LEVEL 有6种错误级别: debug, log, info, notice, warning, exception(默认)

RETURN

return 字段 \|\| ‘,’ \|\| 字段;

语法如下：

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;

例如：

变量v_job_id将会替换掉%。

示例：

-- 编写add_mask(id1,id2)函数，当id1是当前查询用户时，显示正常ID，如果不是则显示为-- id2
    create or replace function add_mask(id1 text, id2 text)
    returns text as $$
    declare
        var1 text;
    begin
        select current_user into var1;
        if var1 = id1 then
            return 'current user is ' || id1;
        else
            return 'current user is ' || id2;
        end if;
    end;
    $$ language plpgsql;

基于开发者空间OpenGauss数据库的PLPGSQL实践二_openGauss_26

3.4.2 函数输出结果集

3.4.2.1 RETURN语句

自定义函数可以声明返回任意数据集，可通过RETURN NEXT，RETURN QUERY，RETURNS TABLE，RETURNS SETOF。

语法如下：

函数用RETURN语句终止运行并返回结果给调用者。这种形式用于不返回结果集合的PL/pgSQL函数。

在返回标量类型的函数中，表达式的结果将自动转换为函数的返回类型。但要返回复合(行)值，则必须编写一个表达式精确地返回所请求的列集，该方法需要显式构造。

如果函数声明了参数输出值，那么函数 return 后不跟表达式，将返回当前值给输出参数变量。如果将函数声明为返回 void 类型，则可以使用 return 语句提前退出函数，return后面不能写表达式。

函数的返回值不能为undefined。如果PL/pgSQL控制块执行到函数的顶层块的末尾而未命中return语句，则将发生运行报错。但是，此限制不适用于带有输出参数的函数和返回void的函数。在这些情况下，如果顶级块完成，则自动执行return语句。

例如：

-- functions returning a scalar type
RETURN 1 + 2;
RETURN scalar_var;

-- functions returning a composite type
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- must cast columns to correct types

3.4.2.2 RETURN NEXT和RETURN QUERY

语法如下：

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

当PL/pgSQL函数被声明返回SETOF sometype时，则存储过程的实现略有不同。

在该场景下，RETURN NEXT 或 RETURN QUERY 返回的一系列结果集，用 RETURN 不带参数的语句来声明函数执行结束。RETURN NEXT 既可以表示标题数据类型，也可以表示复合数据类型。而对于复合数据类型，RETURN NEXT 将返回结果整个表的结果集。RETURN QUERY 是将QUERY的执行结果集返回给函数结果集。在函数的单个返回结果集中，RETURN NEXT 和 RETURN QUERY 可以混合使用，该场景下函数的结果集将以串联方式展现。而一个无参数的 RETURN语句则会控制函数的执行结束以达到控制函数执行流程的结果。

RETURN QUERY 有一个 RETURN QUERY EXECUTE 的变量，它用于指向SQL的动态执行结果。参数表达式可通过 WITH 插入到 QERUY 字符串里，其方法与 EXECUTE 方法相同。

如果声明函数时没给Output参数，则在使用Reutrn Next时不应该跟表达式。当有多个Output参数时，函数则应该声明返回SETOF。如下面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
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END;
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();

基于开发者空间OpenGauss数据库的PLPGSQL实践二_数据库_27

RETURN QUERY用例：(需要提前创建flight表，字段至少包含flightid和flightdate)

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

    -- Since execution is not finished, we can check whether rows were returned
    -- and raise exception if not.
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No flight at %.', $1;
    END IF;

    RETURN;
 END;
$BODY$
LANGUAGE plpgsql;

-- Returns available flights or raises exception if there are no
-- available flights.
SELECT * FROM get_available_flightid(CURRENT_DATE);

3.5 PL/PGSQL之事务管理

调用存储过程使用CALL关键字，如同调用匿名块使用DO一样。在PL/pgSQL语法中，可以使用COMMIT和ROLLBACK结束事务。使用COMMIT或者ROLLBACK结束事务后，另一个新事务会自动启动。所以没有单独的START TRANSACTION。这些与PL/pgSQL中的BEGIN和END是不同的。

示例如下：(需要创建表test1(a int)，其中a字段至少有奇数和偶数值)

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
$$;

CALL transaction_test1();

事务的控制仅受顶层的CALL或DO调用，或者嵌套的CALL或DO调用中可用。如果调用栈为CALL proc1() -\> CALL proc2() -\> CALL proc3()，那么第二个和第三个过程可以执行事务控制动作。但如果调用栈是CALL proc1() -\> SELECT func2() -\> CALL proc3()，由于中间有SELECT，那么最后一个过程不能做事务控制。

PL/pgSQL不支持保存点(保存点/回滚到保存点/释放保存点命令)。

在Cursor LOOP中有些特殊注意点：参数如下示例：

CREATE PROCEDURE transaction_test2()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM test2 ORDER BY x LOOP
        INSERT INTO test1 (a) VALUES (r.x);
        COMMIT;
    END LOOP;
END;
$$;

CALL transaction_test2();

在非只读的游标循环中不允许使用事务操作(例如UPDATE...Returning)。

3.6 常用SQL操作

3.6.1 union all/ union

该语法用于合并两个SELECT查询结果集。union会对两个查询结果集去除重复的数据。而union all不会对结果集去重。

select * from score1 order by chinese limit 10
union 
select * from score2 order by chinese limit 10;

select * from score1 order by chinese limit 10
union all
select * from score2 order by chinese limit 10;

3.6.2 dense() rank()

用于对结果集排序。

\-- DENSE_RANK函数为各组内值生成连续排序序号，其中，相同的值具有相同序号

CREATE TABLE dense_rank_t1(a int, b int);
INSERT INTO dense_rank_t1 VALUES(1,1),(1,1),(1, 2),(1, 3),(2, 4),(2, 5),(3,6);
SELECT a,b,DENSE_RANK() OVER(PARTITION BY a ORDER BY b) FROM dense_rank_t1;

基于开发者空间OpenGauss数据库的PLPGSQL实践二_华为开发者空间_28

3.6.3 not in/ not exist

查看两个班级相同的科目, 202201班在score2中不存在的成绩, 要求使用not in(考试时详细确认题目要求, 查看是具体哪些科目成绩)

3.6.4 merge into

将目标表和源表中的数据针对关联条件进行匹配, 匹配时对目标表进行update更新操作, 不匹配时对目标表进行insert写入操作

MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id)   
    WHEN MATCHED THEN  
        UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'  
    WHEN NOT MATCHED THEN  
        INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';

3.6.5 首字母大写函数initcap

-- family_name跟first_name用'.'拼接，要求首字母大写
select id, initcap(family_name||'.'||first_name), sexmark, grade from su;

3.6.6 系统视图的权限查询

select t1.*,rolname from (select datname,(aclexplode(datacl)).grantee, (aclexplode(datacl)).privilege_type from pg_database) t1,pg_roles where grantee=pg_roles.oid and rolname='sjh111' and datname not like '%template%';
-- 查看用户user1和数据库的相关权限，题目提示用pg_database和pg_roles,要求显示数据库名、用户名、数据库的权限(一定要背下来，原题，而且不要去格式美化)
SELECT a.datname, b.rolname, string_agg(a.priv_t, ',')
  from (SELECT datname, (aclexplode(COALESCE(datacl, acldefault('d' :: "char", datdba)))).grantee as grantee, (aclexplode(COALESCE(datacl, acldefault('d' :: "char", datdba)))).privilege_type as priv_t
          FROM "pg_database"
         WHERE datname not like '%template%' ) a,
       "pg_roles" b
 WHERE (a.grantee = 0 or a.grantee = b.oid)
   AND b.rolname = 'user1'
 GROUP BY a.datname, b.rolname;