java调用oracle存储过程返回多条记录(结果集)

游标，类型，嵌套表，存储包，存储过程

 

--定义TYPE
CREATE OR REPLACE TYPE OrderAttrType AS OBJECT (
    CharacterId               VARCHAR2(60),
    CharacterName             VARCHAR2(60),
    CharacterValue            VARCHAR2(4000),
    CharacteValueName         VARCHAR2(4000),
    OldCharacterValue         VARCHAR2(4000),
    OldCharacteValueName      VARCHAR2(4000)
);

--定义嵌套表
CREATE OR REPLACE TYPE OrderAttrTypeTable IS TABLE OF OrderAttrType;

--定义包
CREATE OR REPLACE PACKAGE HN_PKG_RES IS
    --声明游标映射
    TYPE refcorsor IS REF CURSOR;
    --声明存储过程
    PROCEDURE makeOrderAttrs(p_orderId IN OM_ORDER.ID%TYPE, p_orderAttrList OUT refcorsor);
END HN_PKG_RES;

--实现包体
CREATE OR REPLACE PACKAGE BODY HN_PKG_RES IS
    PROCEDURE makeOrderAttrs(p_orderId IN OM_ORDER.ID%TYPE, p_orderAttrList OUT refcorsor)
    IS
        v_orderAttrList  OrderAttrTypeTable;
        CURSOR c1 IS
            SELECT PPC.CODE            CharacterId,
               PPC.NAME                CharacterName,
               OPR.CHARACTER_VALUE     CharacterValue,
               OPR.CHARACTER_NAME      CharacteValueName,
               OPR.OLD_CHARACTER_VALUE OldCharacterValue,
               OPR.OLD_CHARACTER_NAME  OldCharacteValueName
          FROM OM_GROUP_ORDER_RELA OGR
          JOIN OM_GROUP_ORDER_RELA OGR2 ON OGR.GROUP_SERVICE_ORDER_ID =
                                           OGR2.GROUP_SERVICE_ORDER_ID
          JOIN OM_SERVICE_ORDER OSO ON OSO.ID = OGR.SERVICE_ORDER_ID
          JOIN OM_SERVICE_ORDER OSO2 ON OSO2.ID = OGR2.SERVICE_ORDER_ID
          JOIN OM_INDEP_PROD_ORDER_ATTR OPR ON OSO2.ID = OPR.SERVICE_ORDER_ID
          JOIN PM_PRODUCT_CHARACTER PPC ON OPR.CHARACTER_ID = PPC.ID
         WHERE OGR.SERVICE_ORDER_ID = p_orderId
           AND OGR2.SERVICE_ORDER_ID <> p_orderId
           AND OGR2.MAIN_FLAG = '1'
           AND OSO.INDEP_PROD_ID = 100000
           AND OSO2.INDEP_PROD_ID = 102017
           --AND PPC.CODE = '16275'
           ; --1014501
    BEGIN
        v_orderAttrList := OrderAttrTypeTable();--初始化嵌套表
        --OPEN c1;
        --FETCH c1 BULK COLLECT INTO v_orderAttrList;
        v_orderAttrList.extend;
        v_orderAttrList(v_orderAttrList.count) := OrderAttrType('1','2','3','4','5','6');
        FOR i IN c1 LOOP
            v_orderAttrList.extend;
            v_orderAttrList(v_orderAttrList.count) := OrderAttrType(i.CharacterId,i.CharacterName,
                 i.CharacterValue,i.CharacteValueName,i.OldCharacterValue,i.OldCharacteValueName);
        END LOOP;
        -- 把嵌套表进行类型转换为table
        -- 打开游标并关联查询
        OPEN p_orderAttrList FOR SELECT * FROM TABLE( CAST( v_orderAttrList AS OrderAttrTypeTable ) );
    END;
END HN_PKG_RES;

 

Java代码：  
  

public void callPrTest() {   
     Long orderId = new Long(6019214);
        Connection conn = null;   
        ResultSet rs = null;   
        CallableStatement cs = null;   
        try {   
            conn = this.getConnection();   
            cs = conn.prepareCall("{ call HN_PKG_RES.makeOrderAttrs(?,?) } ");
            cs.setLong(1, orderId.longValue());
            //注册输出类型
       cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);   
            cs.execute();
            ResultSet rsResult = (ResultSet) cs.getObject(2);   
            while (rsResult.next()) {   
              System.out.println(new StringBuffer().append(rsResult.getString("CharacterId"))
               .append("\t").append(rsResult.getString("CharacterName"))
               .append("\t").append(rsResult.getString("CharacterValue"))
               .append("\t").append(rsResult.getString("CharacteValueName"))
               .append("\t").append(rsResult.getString("OldCharacterValue"))
               .append("\t").append(rsResult.getString("OldCharacteValueName"))
               );  
            } 
        } catch (SQLException e) {   
            e.printStackTrace();   
        } finally {   
         cleanUp(conn, null, null, rs);
        }   
    }  

 

输出：
1 2 3 4 5 6
10000 计费类型 c 付费 null null
10001 付费方式 2 后付费 null null
20000 销售品 2124 总机服务  null null
16005 产品密码 123123 123123 null null
16275 集团短号长度 4 4 null null
16277 话务员轮询方案 1627701 轮寻 null null
16292 集团类型 1629201 省级 null null