select xs.xsid,
xs.xh,
xs.xm,
xs.sfzh,
xs.nj,
xs.xqdm,
xs.xqmc,
xs.yxdm,
xs.yxmc,
xs.zydm,
xs.zymc,
xs.bjdm,
xs.bjmc,
yj.xn,
nvl(sum(decode(yj.fylx, 1, jl.je)), 0) xf, --已缴学费
nvl(sum(decode(yj.fylx, 2, jl.je)), 0) zsf --已缴住宿费
FROM sfgl_xsxx xs,
sfgl_yjfy yj,
(SELECT t.je, t.yjfyid, t.xn, t.create_by, t.create_time FROM sfgl_jfjl t
WHERE t.jflx in (select * from TABLE(CAST(:p_jflx AS IN_VARCHAR)))
and t.pjh is null
and (t.create_by like:p_czr or :p_czr is null)
and (to_char(t.create_time,'yyyy-MM-dd HH24:mi:ss') >=:p_kssj or :p_kssj is null)
and (to_char(t.create_time,'yyyy-MM-dd HH24:mi:ss') <=:p_jssj or :p_jssj is null)
) jl
where
xs.xsid = yj.yjyh(+)
AND yj.yjfyid = jl.yjfyid
group by xs.xsid,
xs.xh,
xs.xm,
xs.sfzh,
xs.nj,
xs.xqdm,
xs.xqmc,
xs.yxdm,
xs.yxmc,
xs.zydm,
xs.zymc,
xs.bjdm,
xs.bjmc,
yj.xn
order by xs.xh
上图代码中 条件
t.jflx in (select * from TABLE(CAST(:p_jflx AS IN_VARCHAR))) 等于 t.jflx in (:p_jflx)
绑定变量 选 Array 类型,并且勾选Required。
然后oracle 数据中创建 in_varchar 类型:
然后voImpl 中则可以使用查询变量了:
String[] jflxs = jflx.split(",");
Array jflxArr = new Array(jflxs);
HashMap context = new HashMap();
context.put(DomainContext.ELEMENT_SQL_NAME, "IN_VARCHAR");
context.put(DomainContext.ELEMENT_TYPE, String.class);
jflxArr.setContext(null, null, context);
this.setNamedWhereClauseParam("p_jflx", jflxArr);
this.setp_jflx(jflxArr);
this.executeQuery();
功能摘自--收费系统 发票批量打印的查询