openGauss中存储过程的使用详解

官网的文档并不是很全，也没有给足测试用例，因此此处在这里做一些自己使用过程中的总结

创建一个基本的存储过程

CREATE OR REPLACE PROCEDURE insert_data_to_table(p_id int, p_name text)
AS
BEGIN
  INSERT INTO my_table(id, name) VALUES (p_id, p_name);
END;
/

以上是创建一个基本的存储过程，做表的插入操作，注意end后面需要有/来表示创建语句结束，创建成功后会显示CREATE PROCEDURE。注意这里存储过程中的参数，默认是in。

创建一个带输出的存储过程

当我们想在存储过程中输出一些值的时候，我们需要指定out参数

假设有一个名为my_table的表，其中包含id和name两个字段。我们将创建一个存储过程，接受一个参数p_id，并返回与该ID匹配的行的name值。

首先，创建存储过程来返回select语句的值：

CREATE OR REPLACE PROCEDURE get_name_by_id(p_id int, OUT result_name text)
AS
BEGIN
  SELECT name INTO result_name FROM my_table WHERE id = p_id;
END;
/

那么如果我们直接call这个存储过程的话，会直接在命令行打印result_name

CALL get_name_by_id(1, name_value);

 result_name
-------------
 Bob
(1 row)

我们同样也可以把值保存在变量中，然后例如打印，例如：

DECLARE
  name_value text;
BEGIN
  get_name_by_id(1, name_value);
  
  -- 在消息日志中输出返回的name字段值
  RAISE NOTICE 'Name: %', name_value;
END;
/

当然这里的变量全部都是单个的值，因此我们可以直接将值保存在我们要out的变量中，而当返回值不止一行时，这样的使用就会报错，而此时我们又希望可以对每一行的变量都做相关处理，这时候就可以通过游标的方法来实现

通过游标来处理有多行返回值的存储过程

这里沿用之前的例子，假如我们现在有两个id为1的name

直接上代码看如何创建并使用游标

CREATE OR REPLACE PROCEDURE get_name_by_id_multiprint()
AS
DECLARE
    -- 定义游标
    cur refcursor;
    -- 定义record类型的变量来存储每行的返回值
    row record;
BEGIN
    -- 打开游标
    OPEN cur FOR SELECT id, name FROM my_table;
    
    -- 获取并打印每行返回值
    LOOP
        FETCH NEXT FROM cur INTO row;
        EXIT WHEN NOT FOUND; -- 当找不到时退出
        -- 打印每行的返回值
        RAISE NOTICE 'id: %, name: %', row.id, row.name;
    END LOOP;
    
    -- 关闭游标
    CLOSE cur;
END;
/

游标的使用方法：

定义游标，通过open对一个指定的查询语句打开游标，通过fetch next来将每一条信息存到record类型的变量row中。打印结果如下：

openGauss=# call get_name_by_id_multiprint();
NOTICE:  id: 1, name: Bob
NOTICE:  id: 1, name: Alice
 get_name_by_id_multiprint
---------------------------
 
(1 row)

通过for in select的方式来返回结果

CREATE OR REPLACE PROCEDURE process_data()
LANGUAGE plpgsql
AS $$
DECLARE
    result_value column_type;
BEGIN
    FOR result_value IN SELECT column_name FROM table_name LOOP
        -- 在这里对每个查询结果进行处理
        -- 可以直接使用result_value变量访问查询结果
        -- 例如：RAISE NOTICE 'column_value: %', result_value;
    END LOOP;
END;
$$;

通过return query来按照select的方式返回结果

注意这里的话，我们需要创建的是函数

CREATE OR REPLACE FUNCTION get_multi_rows()
RETURNS TABLE (id int, name text)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 返回查询结果集
    RETURN QUERY SELECT id, name FROM my_table;
END;
$$;

那么之后只需要调用函数即可

SELECT * FROM get_multi_rows();