java - 将整数列表从 Java 传递到 Oracle 函数

Question

I know this may be duplicate question, I checked few solutions related and couldn't proceed ahead.

我在从 Java 传递整数列表并在 Oracle 函数中使用它时遇到一些问题。

1. 运算符列表以以下格式从 Java 传递到 Oracle 函数:“11111, 22222”。 11111 和 22222 是数字，但用单引号传递，以便将其作为一个变量传递。有没有更好的方法来传递这个变量？

   Java 代码片段:

   List<Integer> operatorList = new ArrayList<>();
   

修改operatorList以在开头和结尾包含单引号并传递给函数。 --> 不确定这是否是正确的方法

userOrgList = sessionFactory.getCurrentSession().createSQLQuery
            ("SELECT message FROM TABLE (FUNC_GetMessages(**:operatorList**))")
                                    .setParameter("operatorList", inputOperatorList)
                                    .setFetchSize(5000).list();

在 Oracle 中，正如我所期望的那样，

从表中选择消息 (FUNC_GetMessages('11111, 22222'));

这样，我可以删除引号并将值直接作为参数传递给表。

创建类型来处理此参数。

甲骨文:

create or replace TYPE OPERATOR_ARRAY_DEL AS TABLE OF INTEGER;
/

create or replace FUNCTION FUNC_GetMessages (
    OperatorList IN VARCHAR2
)   

RETURN T_MSGTABLE AS
vMsg_List       T_MSGTABLE;
v_Operator_List VARCHAR(50);
v_operatorList    VARCHAR(100) :='';
v_operatorFinalList    VARCHAR(100) ;
v_operatorId     VARCHAR(20);
v_operatorFinal OPERATOR_ARRAY_DEL := OPERATOR_ARRAY_DEL();
BEGIN

FOR iOperator IN 
        (SELECT to_number(column_value) as v_operatorId FROM xmltable(OperatorList)) 
        LOOP
        v_operatorList := v_operatorList  ||  iOperator.v_operatorId || ',';

        SELECT SUBSTR(v_operatorList, 1, LENGTH(v_operatorList) - 1) INTO v_operatorFinalList FROM DUAL; 

  END LOOP;   

... some code here
...
....

  SELECT SUBSTR(v_operatorList, 1, LENGTH(v_operatorList) - 1) INTO v_operatorList FROM DUAL;   

    SELECT T_MSGTABLE( message) 
    BULK COLLECT INTO vSMs_List
    FROM 
    (  
        SELECT MESSAGE FROM MESSAGETABLE WHERE OPERATOR IN (v_operatorFinalList);
    );
END;    

函数执行:

从表中选择消息 (FUNC_GetMessages('11111, 22222')); --> 此处错误:数字无效

ORA-01722: invalid number
ORA-06512: at "FUNC_GetMessages", line 29
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

我不确定如何解释运算符列表并传递到表以获取结果。

预期结果是 select 语句中的多行

非常感谢任何指示和建议。谢谢:)

Answer 1

可能有一种更简单的 Java 方法可以做到这一点，但您的函数也变得过于复杂。该 xmltable 查询将您的列表转换为可以迭代的表 - 无需再次将其放回 varchar2。

create or replace FUNCTION FUNC_GetMessages (
    OperatorList IN VARCHAR2
)   
RETURN T_MSGTABLE AS
  vMsg_List       T_MSGTABLE;
BEGIN 

... some code here
...
....

    SELECT T_MSGTABLE( message) 
    BULK COLLECT INTO vMsg_List
    FROM 
    (  
        SELECT MESSAGE FROM MESSAGETABLE WHERE OPERATOR IN 
          (SELECT to_number(column_value) as opId FROM xmltable(OperatorList));
    );
END;