游标,类型,嵌套表,存储包,存储过程
--定义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