根据需求分析
1.动态sql查询字段不固定，故 ibatis 返回List<Map> 结果集,select * 查出的结果集字段顺序会乱，返回值需使用LinkedHashMap
<select id = "searchHashlsdzShlson" resultClass="java.util.LinkedHashMap" remapResult="true">
<![CDATA[
SELECT * FROM ZHDZT
]]>
</select>
2.由于每次查询字段不固定，Oracle没有现成的函数支持，故写存储过程，使用EXECUTE IMMEDIATE 执行 拼接的动态sql,创建临时表，并插入数据 create table as select * from ...
防止字段数量过多，导致拼接的sql很长，采用 clob 字段存储动态sql,拼接采用DBMS_LOB.append()
存储过程如下
CREATE OR REPLACE PROCEDURE SHZHDZON(FSMCHTNO IN VARCHAR2,--商户号
FSDATE IN VARCHAR2,--对账日期
RTNSTATE OUT NUMBER,--执行状态 0：成功 -1：失败
OUTMSG OUT VARCHAR2)--失败原因
AUTHID CURRENT_USER --解决存储过程中创建表权限不足问题
IS
SQLSTR CLOB;--存储动态sql
NUM NUMBER;
MARK NUMBER;
SIGN NUMBER;
CURSOR ASSEMBLE IS
SELECT TRIM(T1.FL_FIELDNAME) AS FL_FIELDNAME
FROM HA_SHLSDZ_MOULD T1,
HA_SHLSDZ_MCHTINF T2
WHERE T1.FL_MOULDNUM = T2.FL_ON_MOULDNUM
AND T2.FS_MCHTNO = FSMCHTNO
ORDER BY T1.FL_SEQ;
CURRENT_ROW ASSEMBLE%ROWTYPE;--游标当前行
LAST_ROW ASSEMBLE%ROWTYPW;--游标最后一行
BEGIN
RTNSTATE:=0;--默认执行状态为成功
OUTMSG:='执行成功';--默认返回信息为执行成功
MARK:=1;
SIGN:=1;
SELECT COUNT(1) INTO NUM FROM USER_TABLES WHERE TABLE_NAME = 'ZHDZT';--判断临时表存在，则删除临时表
IF NUM>0 TNEN
EXECUTE IMMEDIATE 'DROP TABLE ZHDZT';
END IF;
DBMS_LOB.createtemporary(SQLSTR,true);--创建一个临时的lob
DBMS_LOB.append(SQLSTR,'cretae table zhdzt as SELECT ');--开始拼接创建临时表的SQL语句
OPEN ASSEMBLE;--打开游标
FETCH ASSEMBLE INTO LAST_ROW;--游标嵌套一层，便于循环时判断最后一条记录
WHILE ASSEMBLE%FOUND THEN
LOOP
FETCH ASSEMBLE INTO CURRENT_ROW;
IF ASSEMBLE%NOTFOUND THEN--如果游标循环到最后一次，拼接SQL时，不加最后那个逗号
IF INSTR(LAST_ROW.FL_FIELDNAME,'''')>0 THEN--判断结果为空字符串
DBMS_LOB.append(SQLSTR,''' '''||' AS TEMP'||MARK);--为字段起临时别名
MARK:=MARK+1;
ELSE
DBMS_LOB.append(SQLSTR,LAST_ROW.FL_FIELDNAME||' AS '||LAST_ROW.FL_FIELDNAME||SIGN);--为防止字段重复，为每个字段加上编号
END IF;
ELSE
IF INSTR(CURRENT_ROW.FL_FIELDNAME,'''')>0 THEN--判断结果为空字符串
DBMS_LOB.append(SQLSTR,''' '''||' AS TEMP'||MARK||',');--为字段起临时别名
MARK:=MARK+1;
ELSE
DBMS_LOB.append(SQLSTR,CURRENT_ROW.FL_FIELDNAME||' AS '||LAST_ROW.FL_FIELDNAME||SIGN||',');--为防止字段重复，为每个字段加上编号
END IF;
END IF;
SIGN:=SIGN+1;
END LOOP;
CLOSE ASSEMBLE;
DBMS_LOB.append(SQLSTR,' from ha_shlddz_shlson where fs_date = '||FSDATE);
DBMS_LOB.append(SQLSTR,' AND fs_mcht_no in (select fs_mchtno1 from ha_shlsdz_mchtno='||FSMCHTNO||')');
EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
WHEN OTHERS THEN
RTNSTATE:=-1;--异常时，返回执行状态置为失败
OUTMSG:='执行失败:'||SUNSTR(SQLERRM,1,100);--返回信息，对异常进行截取
ROLLBACK;--回滚
END;
java调用
@Resource(name="ibatisPersistence")
private IbatisPersistence ibatisPersistence;
Map<String,Object> map = new HashMap<String,Object>();
map.put("FSMCHTNO","12345");
map.put("FSDATE","20210601");
ibatisPersistence.findObject("HashlsdzShlon.HashlsdzShlonPro",map);
int result = map.get("RTNSTATE");--执行结果
String msg = map.get("OUTMSG");--返回执行信息