时间戳

首页 > 解决方案 > 如何在 pl/pgsql 中创建返回 refcursor 和 totalRow 的函数/过程？

java - 如何在 pl/pgsql 中创建返回 refcursor 和 totalRow 的函数/过程？

问题描述

在pl/pgsql，我想创建函数/过程来返回表和totalRecord的结果列表（返回的记录），所以我创建了这个函数：

CREATE OR REPLACE FUNCTION GET_LIST_NOTIFY(    
        -- Cursor param
             out PO_Cursor refcursor, -- return Resutlset
             out PO_ErrorCode   VARCHAR,
             out PO_ErrorDesc    VARCHAR,
             OUT PO_TotalRow        VARCHAR
     )
      as
      $$
    declare
      -- Variable Declare
       vSqlSel               VARCHAR(20000); -- Sql select
      -- END Variable Declare
    begin       
      PO_ErrorCode := 'CODE';
      PO_ErrorDesc := 'MSG_DESC';

       vSqlSel := 'SELECT ID, TITLE ' ||
                  ' FROM USER_NOTIFICATION ';

       raise info 'sql select : %', vSqlSel;

        -- open cursor
       OPEN PO_Cursor for execute vSqlSel;
        EXECUTE 'SELECT count(*) FROM USER_NOTIFICATION' INTO PO_TotalRow;
       EXCEPTION
        WHEN OTHERS THEN             
        PO_ErrorCode := 'COMMONERROR_CODE';
        PO_ErrorDesc := substr(SQLERRM,1,200);

          RAISE;

    END;
$$  LANGUAGE plpgsql;
     -- END GET_LIST_NOTIFY

但是当我调用这个函数时，返回的结果是：

- refcursor is "<unnamed portal 1>"
- PO_ErrorCode  is "CODE"
- PO_ErrorDesc  is "MSG_DESC"
- PO_TotalRow   is "10"

所以，我不能使用 refcursor 来检索和显示java. 我该如何解决这个问题？

在 oracle pl/sql 中，我可以这样写：

PROCEDURE GET_LIST_NOTIFY(
        -- Fields param
             PI_USERNAME    IN   VARCHAR2,
        -- END Fields param

        -- Cursor param
             PO_Cursor        OUT  REF CURSOR,
              PO_TotalRow     OUT   VARCHAR2,
             PO_ErrorCode   OUT VARCHAR2,
             PO_ErrorDesc   OUT VARCHAR2
     )
      AS
          -- Variable Declare
       vSqlSel               VARCHAR2(20000); -- Sql select
          -- END Variable Declare

    BEGIN

      PO_ErrorCode := 'SUCCESS_CODE';
      PO_ErrorDesc := 'SUCCESS_MSG';

       vSqlSel := 'SELECT ID, TITLE ' ||
                  ' FROM USER_NOTIFICATION ';

       dbms_output.put_line('sql select :' || vSqlSel);
       EXECUTE IMMEDIATE 'SELECT count(*) FROM USER_NOTIFICATION' INTO PO_TotalRow;
        -- open cursor
       OPEN PO_Cursor FOR
            vSqlSel;


       EXCEPTION
        WHEN OTHERS THEN
          IF PO_Cursor%ISOPEN THEN
            CLOSE PO_Cursor;
          END IF;

        PO_ErrorCode := 'COMMONERROR_CODE';
        PO_ErrorDesc := substr(DBMS_UTILITY.format_error_backtrace || ' ' ||
                        SQLERRM,1,200);

          RAISE;

    END;
     -- END GET_LIST_NOTIFY

在我的 java 代码中，我将这个“get_list_notify”函数称为：

            RowMapper rowMapper = new RowMapper() {
                @Override
                public Object mapRow(ResultSet rs, int rownum) throws SQLException {
                    // TODO Auto-generated method stub
                    OptionDTO dto = new OptionDTO();
                    try {

                        dto.setValue(Utils.validateHTMLParam(rs.getString(1).trim(), false));
                        dto.setText(Utils.validateHTMLParam(rs.getString(2).trim(), false));

                    } catch (Exception e) {
                        ErrorHelper.PrintStackTrace(this.getClass().getName(), e, "DataProcessing.getDrowdownData2.OptionMapper.mapRow error : ");
                    }

                    return dto;
                }
            };

           SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(DBConnector.getConnection())
                        .withFunctionName(procedureName).withoutProcedureColumnMetaDataAccess();
                Map<String, Object> params = new LinkedHashMap();
                for (String key : parameters.keySet()) {
                    simpleJdbcCall.addDeclaredParameter(new SqlParameter(key, parameters.get(key).getType()));
                    if(Utils.isNummericOracleType(parameters.get(key).getType()) && parameters.get(key).getData() != null)
                        params.put(key, new BigDecimal(parameters.get(key).getData().toString()));
                    else
                        params.put(key, parameters.get(key).getData());
                }

                simpleJdbcCall.addDeclaredParameter(new SqlOutParameter("PO_Cursor", Oid.REF_CURSOR, rowMapper));
                simpleJdbcCall.addDeclaredParameter(new SqlOutParameter("PO_ErrorCode", Oid.VARCHAR));
                simpleJdbcCall.addDeclaredParameter(new SqlOutParameter("PO_ErrorDesc", Oid.VARCHAR));

                Map<String, Object> map = simpleJdbcCall.execute(params);

但是在执行时，simpleJdbcCall.execute(params)引发异常如下：

[err] -----org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call get_list_notify(?, ?, ?)}]; SQL state [34000]; error code [0]; ERROR: cursor "<unnamed portal 1>" does not exist; nested exception is org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist
[err] --------------- At classes : ---------------
[err] ----- at com.bidv.bidvwas.common.DataProccessing.getDrowdownData(DataProccessing.java:399)

如何准确转换 pl/pgsql 并解析来自 java 的调用函数。我使用 springjdbc-4.2.5 和 postgres 10.4

标签： javapostgresqlplpgsql

解决方案

你的代码有点奇怪（太奇怪了——我知道几乎所有的反模式:)——在一个例子中）。你试图加入应该分开的东西，你正在尝试糟糕地使用存储过程。通常程序不应该提供视图（不是在所有没有 MSSQL 的数据库中）。使用过程，您的代码应该更具可读性，而不是更少。

你是小姐命令FETCH。您应该将其用于<unnamed portal>.

CREATE OR REPLACE FUNCTION public.fx(par text, INOUT r refcursor, OUT result integer)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
BEGIN
  result := 10;
  OPEN r FOR SELECT * FROM pg_class WHERE relname LIKE par;
END;
$function$

postgres=# SELECT fx('pg_c%', 'my_name_for_cursor');
┌──────────────────────────┐
│ 效果 │
╞═════════════════════════╡
│ (my_name_for_cursor,10) │
└──────────────────────────┘
(1 行)

postgres=# FETCH 10 FROM my_name_for_cursor;
┌────────────────────────────────────┬────────────── ─┬─────────┬────────────┬──────────┬───────┬─────── ──────┬──────────────
│ relname │ relnamespace │ reltype │ reloftype │ relowner │ relam │ relfilenode │ reltablespac
╞═══════════════════════════════════╪═════════════ ═╪═════════╪═══════════╪══════════╪═══════╪═══════ ══════╪═════════════
│ pg_cast_oid_index │ 11 │ 0 │ 0 │ 10 │ 403 │ 2660 │             
│ pg_cast_source_target_index │ 11 │ 0 │ 0 │ 10 │ 403 │ 2661 │             
│ pg_class_oid_index │ 11 │ 0 │ 0 │ 10 │ 403 │ 0 │             
│ pg_class_relname_nsp_index │ 11 │ 0 │ 0 │ 10 │ 403 │ 0 │             
│ pg_class_tblspc_relfilenode_index │ 11 │ 0 │ 0 │ 10 │ 403 │ 0 │             
│ pg_collat​​ion_name_enc_nsp_index │ 11 │ 0 │ 0 │ 10 │ 403 │ 3164 │             
│ pg_collat​​ion_oid_index │ 11 │ 0 │ 0 │ 10 │ 403 │ 3085 │             
│ pg_constraint_conname_nsp_index │ 11 │ 0 │ 0 │ 10 │ 403 │ 2664 │             
│ pg_constraint_conrelid_index │ 11 │ 0 │ 0 │ 10 │ 403 │ 2665 │             
│ pg_constraint_contypid_index │ 11 │ 0 │ 0 │ 10 │ 403 │ 2666 │             
└────────────────────────────────────┴───────────── ─┴─────────┴────────────┴──────────┴────────┴─────── ──────┴──────────────
（10 行）
postgres=#提交；

更多 - 您使用动态 SQL 太多（命令EXECUTE）。不需要时使用较新的动态 SQL。在您的示例中，不应使用动态 SQL。

捕获所有错误通常也是个坏主意。这是客户端的工作，而不是存储过程中的代码。仅处理您可以正确解决的错误。所有关于异常的信息也可以在客户端获取，您不需要编写这些晦涩的包装器。

推荐阅读