java - 如何将游标或记录列表传递给pl/sql中的java过程？

Question

我想知道如何实现以下逻辑:

CREATE TABLE STUDENT ( 
   STUDENT_ID       NUMBER (5)      NOT NULL, 
   NAME             VARCHAR2 (255)  NOT NULL, 
   SURENAME         VARCHAR2 (255)  NOT NULL,        
   BIRTH_DATE       DATE            NOT NULL, 
   PRIMARY KEY (STUDENT_ID) 
)

然后假设通过调用 PL/SQL 过程来查找类似的 Students:

CREATE OR REPLACE PROCEDURE findStudentByName(p_name IN STUDENT.NAME%TYPE, c_student OUT SYS_REFCURSOR)
           IS
  BEGIN
       OPEN c_student FOR
       SELECT * FROM STUDENT WHERE NAME LIKE p_name || '%';
  END;
  /

然后...

DECLARE 
    c_student SYS_REFCURSOR;
    temp_student STUDENT%ROWTYPE;
BEGIN
  -- records are assign to cursor 'c_student' after calling findStudentByName procedure
  findStudentByName('John',c_student);

    LOOP
       --fetch cursor 'c_student' into STUDENT table type 'temp_student'
       FETCH c_student INTO temp_student;

       -- exit if no more records
       EXIT WHEN c_student%NOTFOUND;

       -- call java procedure the matched name
       -- HOW CAN I PASS ARRAY OF STUDENT%ROWTYPE to java procedure?

    END LOOP;
    CLOSE c_student;
END;
/

我的问题是如何将记录数组传递给java过程？

Answer 1

您需要以 Oracle 格式或标准 JDBC 可调用语句语法将存储过程作为查询字符串调用。

不需要以 DECLARE .. 开头的最后一个代码块，使用存储过程 findStudentByname 就足够了。

更喜欢使用REGEXP_LIKE作为

打开 c_student FOR SELECT * FROM STUDENT WHERE REGEXP_LIKE(NAME,p_name,'i')。

针对 SQL 语句的大小写敏感问题。

按Oracle 格式:

private void findStudent(String name)
 throws SQLException {
    String qry = "begin ? := findStudentByName(?); end;";
    //presuming you already constructed an Oracle connection such as
    // Class.forName("oracle.jdbc.OracleDriver");
    //con = DriverManager.getConnection("jdbc:oracle:thin:@//"+dbname,uname,passwd); 
    CallableStatement stmt = con.prepareCall(qry);

    // the out parameter should be registered as Oracle specific type
    stmt.registerOutParameter(1, OracleTypes.CURSOR);

    stmt.setFloat(2, name); //pass your filtering parameter

    // execute and retrieve the result set
    stmt.execute();
    ResultSet rs = (ResultSet)stmt.getObject(1);

    // print out the results
    while (rs.next()) {
     System.out.println("STUDENT_ID : "+rs.getLong(1));
     System.out.println("NAME : "+rs.getString(2));
     System.out.println("SURNAME : "+rs.getString(3));
     System.out.println("BIRTH_DATE : "+rs.getDate(4));
   }

   rs.close();
   stmt.close();
}

通过JDBC 语法:

private void findStudent(String name)
 throws SQLException {
    String qry = "{ call ? := findStudentByName(?) }";
    CallableStatement stmt = con.prepareCall(qry);

    stmt.registerOutParameter("out_crs", OracleTypes.CURSOR);    
    stmt.setFloat("in_st_name", name); 

    stmt.execute();
    ResultSet rs = (ResultSet)stmt.getObject("out_crs");

    while (rs.next()) {
     System.out.println("STUDENT_ID : "+rs.getLong("student_id"));
     System.out.println("NAME : "+name);
     System.out.println("SURNAME : "+rs.getString("surname"));
     System.out.println("BIRTH_DATE : "+rs.getDate("birth_date"));
   }

   rs.close();
   stmt.close();
}