Migrate Oracle to PostgreSQL (系)：procedure out 参数

by

在 Oracle 数据库开发中，PROCEDURE + OUT 参数 是非常常见的开发模式。尤其是在GIS、电力、erp系统中，最近在我们的客户从oracle迁移到postgresq系(highgoDB)数据库中存在一些这样的场景，下面简单记录一下在迁移到postgresql实现。

Oracle中的格式

PROCEDURE xxx( p_id IN NUMBER, p_result OUT SYS_REFCURSOR )
-- or --
create type xx as object(xx,xx);
create type yy as table of xx;
PROCEDURE xxx( p_id IN NUMBER, p_result OUT xx );

但在 PostgreSQL 中：

  • 没有 Oracle 风格 OBJECT
  • 没有 TABLE OF
  • Procedure 返回结果集方式不同
  • 更推荐 FUNCTION RETURNS TABLE

在Oracle中的out 自定义type

 select * from t_user

        ID USER_NAME                             AGE
---------- ------------------------------ ----------
         1 Tom                                    18
         2 Jerry                                  20
         3 Lucy                                   22

-- 创建 out 自定义type 
CREATE OR REPLACE PROCEDURE proc_user_list( p_age IN NUMBER, p_users OUT user_table ) 
AS 
BEGIN 
SELECT user_obj( id, user_name, age ) 
BULK COLLECT INTO p_users 
FROM t_user 
WHERE age >= p_age; 
END; 
/

DECLARE
    v_users user_table;
BEGIN
    proc_user_list(18, v_users);
    FOR i IN 1 .. v_users.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE( v_users(i).user_name     );
    END LOOP;
END;
/

-- 调用
CREATE OR REPLACE PROCEDURE proc_user_cursor( p_age IN NUMBER, p_cursor OUT SYS_REFCURSOR ) 
AS 
BEGIN 
OPEN p_cursor FOR 
SELECT id, user_name, age 
FROM t_user 
WHERE age >= p_age; 
END; 
/

在Oracle中的out SYS_REFCURSOR

CREATE OR REPLACE PROCEDURE proc_user_cursor( p_age IN NUMBER, p_cursor OUT SYS_REFCURSOR ) 
AS 
BEGIN 
OPEN p_cursor FOR 
SELECT id, user_name, age 
FROM t_user 
WHERE age >= p_age; 
END; 
/

-- 调用

DECLARE
    v_cursor SYS_REFCURSOR;
    v_id NUMBER;
    v_name VARCHAR2(100);
    v_age NUMBER;
BEGIN
    proc_user_cursor(  18,     v_cursor  );

    LOOP
        FETCH v_cursor
        INTO v_id,
             v_name,
             v_age;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
    CLOSE v_cursor;
END;
/

Oracle vs PostgreSQL 对应关系

OraclePostgreSQL
SYS_REFCURSORrefcursor
AS OBJECTcomposite type
TABLE OFarray / RETURNS TABLE
BULK COLLECTarray_agg
PROCEDURE OUTprocedure refcursor
WHILE LOOPRECURSIVE CTE
procedure返回结果Function
select * from table（）fetch all from

PostgreSQL：

类型能返回记录集调用方式
FUNCTION✅ 推荐SELECT
PROCEDURE❌ 较弱CALL
需求推荐方式
返回单行对象RETURNS TABLE
返回复杂对象RETURNS TYPE
返回多行RETURNS TABLE + RETURN QUERY
Oracle 风格 OUT 参数FUNCTION OUT
真 PROCEDURE不推荐做查询返回

像上面ORACLE的使用out传出参数的话，在Postgresql中是建议使用function 代替 procedure, 如果返回一行记录，也可以使用function(xx, out xx int)这种方式， 当然也可以使用 return table的方式

CREATE OR REPLACE FUNCTION get_user_info(
    p_user_id bigint
)
RETURNS TABLE (
    user_id bigint,
    user_name text,
    age int
)
AS
$$
BEGIN
    RETURN QUERY
    SELECT id, name, user_age
    FROM users
    WHERE id = p_user_id;
END;
$$
LANGUAGE plpgsql;

但是调用的话，需要有原来call procedure改为select * from function.

当然如果一定要procedure + out参数的话，从pg 11以后也是支持的。但一般不推荐。

CREATE OR REPLACE PROCEDURE get_user_info(
    IN p_user_id bigint,
    OUT user_id bigint,
    OUT user_name text
)
LANGUAGE plpgsql
AS
$$
BEGIN
    SELECT id, name
    INTO user_id, user_name
    FROM users
    WHERE id = p_user_id;
END;
$$;

-- call
CALL get_user_info(1, NULL, NULL);

返回单行使用returns table, 而返回多行使用returns table return query ,如下

CREATE OR REPLACE FUNCTION find_next_point(
    p_point integer,
    p_line integer
)
RETURNS TABLE
(
    new_point integer,
    new_line  integer,
    status    integer
)
AS
$$
BEGIN

    RETURN QUERY
    SELECT
        1001,
        2001,
        0;

END;
$$
LANGUAGE plpgsql;

但是PostgreSQL中不支持像oracle一样procedure out table，那就要使用数组 out， 或使用refcursor. 数组out我们不建议，下面演示postgresql中refcursor的用法

CREATE TABLE t_user
(
    id        integer,
    user_name varchar(100),
    age       integer
);

INSERT INTO t_user VALUES (1, 'Tom', 18);
INSERT INTO t_user VALUES (2, 'Jerry', 20);
INSERT INTO t_user VALUES (3, 'Lucy', 22);

CREATE OR REPLACE PROCEDURE proc_get_user(
    IN  p_min_age integer,
    INOUT p_cursor refcursor DEFAULT 'user_cursor'
)
LANGUAGE plpgsql
AS
$$
BEGIN

    OPEN p_cursor FOR

    SELECT
        id,
        user_name,
        age
    FROM t_user
    WHERE age >= p_min_age
    ORDER BY id;

END;
$$;
-- 调用
highgo=# begin;
BEGIN
highgo=*# CALL proc_get_user(    20,    'user_cursor');
  p_cursor
-------------
 user_cursor
(1 row)

highgo=*# FETCH ALL FROM user_cursor;
 id | user_name | age
----+-----------+-----
  2 | Jerry     |  20
  3 | Lucy      |  22
(2 rows)

highgo=*# COMMIT;
COMMIT

在应用程序中的调用

-- java
CallableStatement cs =
    conn.prepareCall(
        "{ call proc_get_user(?, ?) }");

cs.setInt(1, 20);

cs.registerOutParameter(
    2,
    Types.REF_CURSOR
);

cs.execute();

ResultSet rs =
    (ResultSet) cs.getObject(2);

while(rs.next()) {

    System.out.println(
        rs.getInt("id")
    );
}

--myBatis
<select id="callProc"
        statementType="CALLABLE">

    {call proc_get_user(
        #{minAge, mode=IN, jdbcType=INTEGER},
        #{cursor, mode=OUT, jdbcType=CURSOR,
            resultMap=userMap}
    )}

</select>

总结

Oracle 与 PostgreSQL 在存储过程体系上存在明显差异。在oracle中如果使用procedure+out参数，在迁移到PostgreSQL后建议优先使用function + out参数，而procedure要放在事务中调用，而返回结果集时也没有对应的as table of. 推荐使用function + return table的方式。

Leave a Comment