存储过程demo
PRC：
CREATE OR REPLACE PROCEDURE BUZ_TEXMATCH(
p_date in varchar2, --对账日期
p_file in varchar2, --对账文件名
o_result out NUMBER, --返回对账结果，成功为1 出现异常为0
o_cursor out MYPACKAGE.TEXMATCH_CURSOR--返回本地记录不存在,主机存在的对账记录
) as
--本地记录存在,主机存在的对账，根据对账结果修改本地库成功或失败
CURSOR cur_tb_texmatch (l_date PUPS_DTL_TEXMATCH.HCMPDATE%TYPE,l_file PUPS_DTL_TEXMATCH.FILENAME%TYPE) IS
SELECT *
FROM pups_dtl_texmatch t
WHERE t.hcmpdate = l_date
AND t.filename = l_file;
CURSOR cur_tb_trade (l_date PUPS_DTL_TRADE.TRXDATE%TYPE) IS
SELECT *
FROM pups_dtl_trade t
WHERE t.trxdate = l_date
AND (t.typeno_host = 'TT0001' OR t.typeno_host = 'TT0002');
--本地记录存在,主机不存在的对账，修改本地库失败
CURSOR cur_tb_trade_has (l_date PUPS_DTL_TEXMATCH.HCMPDATE%TYPE,l_file PUPS_DTL_TEXMATCH.FILENAME%TYPE) IS
SELECT * FROM pups_dtl_trade m
WHERE m.trxdate = l_date
AND (m.typeno_host = 'TT0001' OR m.typeno_host = 'TT0002')
AND NOT EXISTS
(
SELECT * FROM pups_dtl_texmatch n
WHERE m.trxno_front = n.rquid
AND n.hcmpdate=l_date
AND n.filename=l_file
);
l_isExist NUMBER; --是否存在相同流水号
l_isCZ NUMBER; --是否返回的原交易已经被冲正
l_trxfront varchar2(50); --原交易的前置流水号
l_hcmpflag varchar2(1); --与主机对账标志
l_trxresult varchar2(1); --交易结果
l_trxflag_host varchar2(1); --主机交易状态
sqlText varchar2(500);
BEGIN
--默认返回无异常
o_result := 1;
--本地记录存在,主机存在的对账，根据对账结果修改本地库成功或失败
l_hcmpflag := '1';
l_trxresult := '2';
l_trxflag_host := '2';
--循环判断是否存在相同流水号
FOR rec_cur_tb_texmatch IN cur_tb_texmatch(p_date,p_file) LOOP
BEGIN
l_isExist := 0;
l_isCZ := 0;
FOR rec_cur_tb_trade IN cur_tb_trade(p_date) LOOP
--存在相同流水号
IF rec_cur_tb_texmatch.rquid = rec_cur_tb_trade.trxno_front THEN
l_isExist := 1;
IF rec_cur_tb_texmatch.txnresult = 'SUCC' THEN
l_trxresult := '1';
l_trxflag_host := '1';
END IF;
IF rec_cur_tb_texmatch.txnresult = '原交易已经被冲正' THEN
l_isCZ := '1';
END IF;
END IF;
END LOOP;
--存在相同流水号
IF l_isExist = 1 THEN
UPDATE pups_dtl_trade
SET trxflag_host = l_trxflag_host,
hcmpflag = l_hcmpflag,
trxresult = l_trxresult
WHERE trxno_front = rec_cur_tb_texmatch.rquid;
END IF;
--如果返回原交易已经被冲正，查询到原交易，交易结果改为已冲正
IF l_isCZ = 1 THEN
UPDATE pups_dtl_trade
SET trxresult = '3'
where trxno_front =
(
SELECT t1.trxno_front FROM pups_dtl_trade t1,pups_dtl_trade t2
WHERE t2.trxno_front = rec_cur_tb_texmatch.rquid
AND t1.agency_uuid = t2.agency_uuid
AND t2.revtrxflag ='1'
AND t1.revtrxflag ='0'
);
END IF;
EXCEPTION when others then
o_result := 0;
END;
END LOOP;
--本地记录存在,主机不存在的对账，修改本地库失败
FOR cur_cur_tb_trade_has IN cur_tb_trade_has(p_date,p_file) LOOP
BEGIN
UPDATE pups_dtl_trade
SET trxflag_host = '2',
hcmpflag = '2',
trxresult = '2'
WHERE trxno_front = cur_cur_tb_trade_has.trxno_front;
EXCEPTION WHEN OTHERS THEN
o_result := 0;
END;
END LOOP;
--本地记录不存在,主机存在的对账，返回值
sqlText := 'SELECT * FROM pups_dtl_texmatch m
WHERE m.hcmpdate='''||p_date||'''
AND m.filename='''||p_file||'''
AND NOT EXISTS
(SELECT * FROM pups_dtl_trade n
WHERE n.trxno_front = m.rquid
AND n.trxdate = '''||p_date||'''
AND (n.typeno_host = ''TT0001'' or n.typeno_host = ''TT0002'')
)';
--dbms_output.put_line(sqlText);
OPEN o_cursor FOR sqlText;
END;
/
CPE：
CREATE OR REPLACE PACKAGE MYPACKAGE AS
TYPE TEXMATCH_CURSOR IS REF CURSOR;
end MYPACKAGE;
/
java调用：
public static Map<String, Object> proc_pldz(String date,String fileName) {
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
conn = ConnectionManager.getInstance().getConnection();
cs = conn.prepareCall("{ call BUZ_TEXMATCH(?,?,?,?) }");
cs.setString(1, date); //"20120626"
cs.setString(2, fileName); //"S56-201206261439087961001165.txt"
cs.registerOutParameter(3, Types.INTEGER);
cs.registerOutParameter(4, OracleTypes.CURSOR);
cs.execute();
int flag = cs.getInt(3); //操作成功标志1成功0异常
rs = (ResultSet) cs.getObject(4);
List<Map<String, Object>> list =CommonUtil.convertList(rs); //本地记录不存在,主机存在的对账记录
Map<String, Object> map = new HashMap<String, Object>();
map.put("trxmatch_flag", flag);
map.put("trxmatch_list", list);
while (rs.next()) {
System.out.println("\t" + rs.getString(1) + "\t" + rs.getString(2) + "\t");
}
return map;
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
if (cs != null) {
cs.close();
}
if (conn != null) {
conn.close();
}
}
} catch (SQLException e) {
}
}
return null;
}
/**
* 将ResultSet转换为List<Map<String, Object>>
* @param rs
* @return
* @throws SQLException
*/
@SuppressWarnings("unused")
public static List<Map<String, Object>> convertList(ResultSet rs) throws SQLException{
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
ResultSetMetaData md = rs.getMetaData();
//获取字段count
int columnCount = md.getColumnCount();
while (rs.next()) {
Map<String, Object> rowData = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i).toLowerCase(), rs.getObject(i));
}
list.add(rowData);
}
return list;
}
