Java调用带有记录类型出参的存储过程

Myth丶恋晨 2021-06-24 14:00 279阅读 0赞

## 目的： ##

当过程包中定义了 Type( record ), 并作为包内存储过程的出参，如何通过Java进行存储过程调用？查看了网上的案例，没有找到最终答案。本次测试中，此问题仍未解决。先记录下来，以持续跟踪。

## 实验过程： ##

存储过程包：

create or replace package pkg_test is
      type TYPEINFO is record(
        id    number,
        name  varchar2(10)
      );
      prcedure test(
        i_sno   in pls_integer, 
        o_inf  out TYPEINFO,
        o_flag out varchar2,
        o_msg  out varchar2
      );
    end pkg_test;
    /
    create or replace package body pkg_test is
      procedure test( i_sno in pls_integer, o_inf out TYPEINFO, o_flag out varchar2, o_msg out varchar2 ) is begin select 111 id, 'James' name into o_inf from dual;
        o_flag := '1';
        o_msg := 'ok!';
      end test;
    begin
      null;
    end pkg_test;

Java代码：

import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.sql.Types;
    
    import oracle.jdbc.OracleTypes;
    import oracle.jdbc.driver.OracleCallableStatement;
    import oracle.sql.ARRAY;
    import oracle.sql.ArrayDescriptor;
    import oracle.sql.STRUCT;
    import oracle.sql.StructDescriptor;
    
    import org.junit.Before;
    import org.junit.Test;
    
    public class TypeTest { 
        String driver = "oracle.jdbc.driver.OracleDriver";  
        String strUrl = "jdbc:oracle:thin:@10.0.0.1:1521:orcl";  
        Statement stmt = null;  
        ResultSet rs = null;  
        Connection conn = null;  
        //CallableStatement cstmt = null; 
        OracleCallableStatement cs = null;  
    
        @Before  
        public void init() {  
            try {  
                Class.forName(driver);  
                conn = DriverManager.getConnection(strUrl, "dbusr", "****");  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
        }  
    
        /** * 返回 Record类型的包内存储过程，测试失败 */  
        @Test  
        public void procOutTypeOfRecord() {  
            try {
                System.out.println("start");
                ArrayDescriptor ad = ArrayDescriptor.createDescriptor("PKG_TEST.TYPEINFO", conn);
                System.out.println("ad = "+ad.toString());
                cs = (OracleCallableStatement)conn.prepareCall("{call pkg_test.test(?,?,?,?)}");
                cs.setInt(1, 50);  
                cs.registerOutParameter(2, OracleTypes.ARRAY);
                cs.registerOutParameter(3, OracleTypes.VARCHAR);
                cs.registerOutParameter(4, OracleTypes.VARCHAR);
                cs.execute();
                System.out.println("executed");
                ARRAY t1 = cs.getARRAY(2);
                String fg = cs.getString(3);
                System.out.println("out flag=" + fg);
    
            } catch (SQLException ex2) {  
                ex2.printStackTrace();  
            } catch (Exception ex2) {  
                ex2.printStackTrace();  
            } finally {  
                try {  
                    if (rs != null) {
                        rs.close();
                    }
                } catch(SQLException e){}
                try{
                    if (cs != null) {  
                        cs.close();  
                    }
                } catch(SQLException e){}
                try{
                    if (conn != null) {  
                        conn.close();  
                    }
                } catch(SQLException e){}
    
            }  
        }
    
        /*测试脚本*/
        public static void main(String[] args){
            TypeTest tt = new TypeTest();
            tt.init();
            tt.procOutTypeOfRecord(); //Failed, java.sql.SQLException: 无效的名称模式: PKG_TEST.TYPEINFO
    
        }
    }

报错信息：java.sql.SQLException: 无效的名称模式，应该是无法识别在package中定义的record type。  
如果将此type调整为 ref cursor，则可以通过OracleTypes.CURSOR来解决问题，java代码见下：

/** * 返回 Cursor类型的包内存储过程，测试成功 */  
        @Test  
        public void procOutTypeOfCursor() {  
            try {
                System.out.println("start");
                cs = (OracleCallableStatement)conn.prepareCall("{call pkg_test.test(?,?,?,?)}");
                cs.setInt(1, 0);
                cs.registerOutParameter(2, OracleTypes.CURSOR);
                cs.registerOutParameter(3, OracleTypes.VARCHAR);
                cs.registerOutParameter(4, OracleTypes.VARCHAR);
                cs.execute();
                System.out.println("executed");
                //STRUCT t1 = cs.getSTRUCT(2);
                String fg = cs.getString(3);
                String mg = cs.getString(4);
                System.out.println("out flag=" + fg+"; msg="+mg);
                if ("1".equals(fg)){
                    rs = (ResultSet)cs.getObject(2);
                    while (rs.next()){
                        System.out.println(rs.getString("ATYPE")+" "+rs.getString("ID"));
                    }
                }
            } catch (SQLException ex2) {  
                ex2.printStackTrace();  
            } catch (Exception ex2) {  
                ex2.printStackTrace();  
            } finally {  
                try {  
                    if (rs != null) {
                        rs.close();
                    }
                } catch(SQLException e){}
                try{
                    if (cs != null) {  
                        cs.close();  
                    }
                } catch(SQLException e){}
                try{
                    if (conn != null) {  
                        conn.close();  
                    }
                } catch(SQLException e){}
    
            }  
        }

## 结论： ##

暂无

## 后记： ##

问题仍在分析中，欢迎大家提出建议。