如何将pl/sql的输出关联数组传递给Java? [英] How can i pass output associative array of pl/sql to java?

问题描述

对于嵌套表，我是否按照以下方式进行? 这是pl/sql存储过程.

For Nested table i have done it in follwing way? This is pl/sql stored procedure.

CREATE or REPLACE PROCEDURE TEST(
activationStartDate IN DATE,
activationEndDate IN DATE,
deActivationStartDate IN DATE,
deActivationEndDate IN DATE
Out_Entity OUT TEST1.RefCsr
)
AS
FirstNameListTable LIST_TABLE;

{--COMMENT :LIST_TABLE is nested table :create or replace TYPE "LIST_TABLE" as table     of varchar2(20);-----Nested Table Declaration
/
}

 totalSameFirstName NUMBER;
 j NUMBER := 1;
 BEGIN



 SELECT first_name BULK COLLECT INTO FirstNameListTable FROM Employee where start_date between activationStartDate AND activationEndDate 
MINUS 
SELECT first_name FROM Employee where start_date between deActivationStartDate AND deActivationEndDate

OPEN Out_Entity FOR SELECT * FROM TABLE(
                                           CAST (
                                                FirstNameListTable AS LIST_TABLE
                                             )
                                ) Nos;

  END;
   /

我的JavaCode是

My JavaCode is

--First getConnection
--prepare sql string : sql = "{ Call Test(?,?,?,?,?) } ";
--Use prepareCall function on Connection object and passed this sql string and retrived CallableStatement class object.
stmt.setTimestamp(1,activationStartDate);
stmt.setTimestamp(2,activationEndDate);
stmt.setTimestamp(3,deActivationStartDate);
stmt.setTimestamp(4,deActivationEndDate);
stmt.registerOutParameter(5,-10);
stmt.execute();
List result = new ArrayList();
         ResultSet rs = (ResultSet)stmt.getObject(5);
        int i=0;
        while (rs.next()) 
        {
            System.out.println(i+". "+rs.getString(1));
            i++;
        }

然后，如果我想从EMPLOYEE中选择多个列并传递给我的javaCode，怎么办……我的javaCode和Pl/Sql会是什么样子?

Then what if i want to select more than one column from EMPLOYEE and pass to my javaCode......How my javaCode and Pl/Sql will look like?

推荐答案

您需要使用嵌套循环

例如在结果集中获取列计数值

for example in the result set get the column count value

尝试这样

List<List> result = new ArrayList()<List>;
List col = new ArrayList();

ResultSet rs = (ResultSet)stmt.getObject(5);
ResultSetMetaData rsMetaData = rs.getMetaData();

        int numberOfColumns = rsMetaData.getColumnCount();
        while (rs.next()) 
        {
            col = new ArrayList();
            for(int j=1;j<numberOfColumns;j++)
                col.add(rs.getString(j);

            result.add(col);
        }

这里是用于获取行的第一个循环，而用于获取列值的嵌套式循环 此值存储在col列表对象中，并且每次创建新对象时都需要

here first loop for getting the rows and the nested loop to getting the columns value this value are store in the col list object and need to each time create new object

这篇关于如何将pl/sql的输出关联数组传递给Java?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！