【IT168 服务器学院】1、建Type
   create or replace type t_array as table of varchar2(4000);

2、package中 声明cursor type T_cur is ref cursor;
   存储过程
    procedure pro_StorageStatus(as_month  varchar2, --查询月份
                              cur_1      out t_cur, --返回
                              as_OutMsg out varchar2, --传出参数
                              an_Res    out int) is
    l_jg t_array := t_array();
    ls_temp varchar2(4000); 
    begin
      ...
      l_jg.extend;
      l_jg(l_jg.last) := ls_temp;
      ...
      open cur_1 for select * from table(cast (l_jg as t_array));
    end;

3、Java中调用
 
    CallableStatement  cs = conn.prepareCall("call　pk_cxtj.pro_StorageStatus(?,?,?,?)");
    cs.setString(1,month);
    cs.registerOutParameter(2,OracleTypes.CURSOR);
    cs.registerOutParameter(3, Types.VARCHAR);
    cs.registerOutParameter(4, Types.NUMERIC);
    try {
      cs.execute();
      if (cs.getInt(4) < 0) {
        throw new SQLException(cs.getString(3));
      }
      else {
        rs = (ResultSet)cs.getObject(2);
........

４、oracle中输出IndexTable,java中调用
  OracleCallableStatement cs = null;
  cs = (OracleCallableStatement)conn.prepareCall("begin pk_cxtj.pro_StorageStatus(?,?,?,?); end;");
  cs.setString(1, "2006-01-01");
  //cs.registerOutParameter(2, OracleTypes.ARRAY,"T_ARRAY"); //传出参数
  cs.registerIndexTableOutParameter(2,200,OracleTypes.VARCHAR,4000);
  cs.registerOutParameter(3, Types.VARCHAR); //传出参数sOutMsg
  cs.registerOutParameter(4, Types.NUMERIC); //传出参数iRes
  try {
      cs.execute();
      if (cs.getInt(4) < 0) {
        throw new SQLException(cs.getString(3));
      }
      String[ ] values =(String[])cs.getPlsqlIndexTable(2);
........

５、oracle中输出可变数组，java中调用
    OracleCallableStatement cs = null;
    cs = (OracleCallableStatement)conn.prepareCall("begin pk_cxtj.pro_StorageStatus(?,?,?,?); end;");
    cs.setString(1, "2006-01-01");
    cs.registerOutParameter(2, OracleTypes.ARRAY,"T_ARRAY"); //传出参数
    //cs.registerIndexTableOutParameter(2,4000,OracleTypes.VARCHAR,4000);
    cs.registerOutParameter(3, Types.VARCHAR); //传出参数sOutMsg
    cs.registerOutParameter(4, Types.NUMERIC); //传出参数iRes
    try {
        cs.execute();
        if (cs.getInt(4) < 0) {
          throw new SQLException(cs.getString(3));
        }
        Array lll =  (Array)cs.getArray(2);
        String[ ] values = (String[])lll.getArray();
        for (int i = 0;i<values.length;i++){
          System.out.println(values[i]);
        }
....