自定义函数实现调用返回非游标类型存储过程
前言
润乾报表提供了存储过程类型的数据集以及在单元格中使用call函数调用存储过程的方式与存储过程交互，但无论使用那种方式都要求存储过程能够返回结果集，无论返回的游标是隐式还是显示的。
这带来了一个问题，就是存储过程只有一些普通类型（非游标类型）的输出参数，这时希望在报表中调用该存储过程并希望得到相应的输出参数值直接是无法达到目标的。
为此，本文介绍如何使用自定义函数给出一个通用的方法解决上述问题。自定义函数使用方法见教程。
实现
指定函数格式
要求该函数尽量通用，在调用的时候可以采用如下格式：
函数名(数据源名称,存储过程名称,输入或输出参数,输入或输出参数,……)
其中，
l 指定数据源为了解决当多数据源时存储过程调用问题；
l 存储过程名称是要调用的存储过程名称；
l 输入输出参数要与存储过程中参数顺序完全一致，若为输出参数则用逗号占位
举例：
callProcNoCursor(“XXMGL”,”db2Proc8″,”aaa”,”bbb”,,)
该存储过程有两个输入参数（前两个）和两个输出参数（后两个）。
程序主要模块
计算自定义函数前两个参数值（数据源名和存储过程名）
Object dsName = Variant2.getValue(param1.calculate(ctx, isInput),false, isInput);//数据源名
Object procName = Variant2.getValue(param2.calculate(ctx, isInput),false, isInput);//存储过程名
这两个参数要求必须给出，所以要做不能为空的处理。
记录输出参数位置
if(paramNum>2){
for(int i=2;i<paramNum;i++){
paramExp = (Expression) this.paramList.get(i);
if(paramExp==null){//如果该参数表达式为null，则证明是输出参数
procOutParamList.add(i-1); //将输出参数位置放入list
}
计算输入参数值和输入参数位置
else{//不为null，证明是输入参数
paramValue = Variant2.getValue(paramExp.calculate(ctx, isInput), false, isInput);
procInParamMap.put(i-1, paramValue); //将存储过程输入参数位置和值放入map
}
调用存储过程得到输出参数
conn = ctx.getConnectionFactory(dsName.toString()).getConnection();
cstmt = conn.prepareCall(callProcStr);
//设置输入参数值
Iterator iter = procInParamMap.entrySet().iterator();
while (iter.hasNext()) {
Map.Entry entry = (Map.Entry) iter.next();
int key = (Integer) entry.getKey();
Object value = entry.getValue();
System.out.println(“key=”+key+”,value=”+value.toString());
cstmt.setObject(key, value);
}
for(int j=0;j<procOutParamList.size();j++){
cstmt.registerOutParameter((Integer)procOutParamList.get(j), Types.JAVA_OBJECT);
}
cstmt.execute();
for(int m=0;m<procOutParamList.size();m++){
outParamValue = cstmt.getObject((Integer)procOutParamList.get(m));
outList.add(outParamValue);//将输出参数值放入list
}
以list形式将输出参数值返回
return callProc(ctx,dsName,procName,paramCount,procInParamMap,procOutParamList);
报表调用
结果如下：
DB2存储过程：
CREATE PROCEDURE db2Proc8(in in_param1 varchar(32),in in_param2 varchar(32),out out_param1 varchar(32),out out_param2 varchar(32))
RESULT SETS 1
LANGUAGE SQL
begin
set out_param1 = in_param1;
set out_param2 = in_param2;
END
源文件
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import com.runqian.base4.resources.EngineMessage;
import com.runqian.base4.resources.MessageManager;
import com.runqian.base4.util.ReportError;
import com.runqian.report4.model.expression.Expression;
import com.runqian.report4.model.expression.Function;
import com.runqian.report4.model.expression.Variant2;
import com.runqian.report4.usermodel.Context;
public class CallProcNoCursor extends Function{
public Object calculate(Context ctx, boolean isInput) {
int paramNum = this.paramList.size();//参数个数
if (paramNum < 1) {
MessageManager mm = EngineMessage.get();
throw new ReportError(“encrypt:”+ mm.getMessage(“function need params”));
}
if (paramNum < 2) {
MessageManager mm = EngineMessage.get();
throw new ReportError(“encrypt:”+ mm.getMessage(“params number wrong”));
}
Expression param1 = (Expression) this.paramList.get(0);
Expression param2 = (Expression) this.paramList.get(1);
if (param1 == null || param2 == null) {// 判断参数是否为空
MessageManager mm = EngineMessage.get();
throw new ReportError(“encrypt:”+ mm.getMessage(“param is empty”));
}
// 算出参数值
Object dsName = Variant2.getValue(param1.calculate(ctx, isInput),false, isInput);//数据源名
Object procName = Variant2.getValue(param2.calculate(ctx, isInput),false, isInput);//存储过程名
// 判断第一个参数值是否为空
if (dsName == null || procName == null) {
return null;
}
// System.out.println(“dsName->”+dsName.toString());
// System.out.println(“procName->”+procName.toString());
//List<Object> procInParamList = new ArrayList<Object>();//存储存储过程输入参数
Map<Integer, Object> procInParamMap = new HashMap<Integer, Object>(); //此map存储存储过程输入参数位置、参数值
List<Integer> procOutParamList = new ArrayList<Integer>(); //存储输入参数位置
Expression paramExp = null; //参数表达式
Object paramValue = null; //计算后参数值
int paramCount = 0; //存储过程参数个数
if(paramNum>2){
for(int i=2;i<paramNum;i++){
paramExp = (Expression) this.paramList.get(i);
if(paramExp==null){//如果该参数表达式为null，则证明是输出参数
procOutParamList.add(i-1); //将输出参数位置放入list
// System.out.println(“参数为空，为存储过程输出参数！”);
}else{//不为null，证明是输入参数
paramValue = Variant2.getValue(paramExp.calculate(ctx, isInput), false, isInput);
procInParamMap.put(i-1, paramValue); //将存储过程输入参数位置和值放入map
// System.out.println(“参数不为空，为存储过程输入参数！”);
}
paramCount++;
}
}
// System.out.println(“paramCount->”+paramCount);
/***********打印存储过程输入输出参数位置或值*****************/
Iterator iter = procInParamMap.entrySet().iterator();
while (iter.hasNext()) {
Map.Entry entry = (Map.Entry) iter.next();
String key = entry.getKey().toString();
String value = entry.getValue().toString();
System.out.println(“输入参数位置=”+key+”,输入参数值=”+value);
}
for(int m=0;m<procOutParamList.size();m++){
System.out.println(“输出参数位置=”+procOutParamList.get(m).toString());
}
/********************************************************/
return callProc(ctx,dsName,procName,paramCount,procInParamMap,procOutParamList);
}
public List<Object> callProc(Context ctx,Object dsName,Object procName,int paramCount,Map procInParamMap,List procOutParamList){
List<Object> outList = new ArrayList<Object>(); //输出参数值
//调用存储过程语句
StringBuffer callProcSB = new StringBuffer();
String callProcStr = “”;
callProcSB.append(“{call “);
callProcSB.append(procName.toString());
callProcSB.append(“(“);
//拼问号
for(int i=0;i<paramCount;i++){
callProcSB.append(“?,”);
}
callProcSB.append(“)}”);
if(callProcSB.toString().lastIndexOf(“,”)==callProcSB.toString().length()-3){
callProcStr = callProcSB.toString().substring(0,callProcSB.toString().length()-3)
+callProcSB.toString().substring(callProcSB.toString().length()-2);
}else{
callProcStr = callProcSB.toString();
}
System.out.println(“call串->”+callProcStr);
//获取数据库连接
Connection conn = null;
CallableStatement cstmt = null;
Object outParamValue = null;
// String outParamValue = null;
try {
conn = ctx.getConnectionFactory(dsName.toString()).getConnection();
cstmt = conn.prepareCall(callProcStr);
//设置输入参数值
Iterator iter = procInParamMap.entrySet().iterator();
while (iter.hasNext()) {
Map.Entry entry = (Map.Entry) iter.next();
int key = (Integer) entry.getKey();
Object value = entry.getValue();
System.out.println(“key=”+key+”,value=”+value.toString());
cstmt.setObject(key, value);
}
for(int j=0;j<procOutParamList.size();j++){
cstmt.registerOutParameter((Integer)procOutParamList.get(j), Types.JAVA_OBJECT);
}
cstmt.execute();
for(int m=0;m<procOutParamList.size();m++){
outParamValue = cstmt.getObject((Integer)procOutParamList.get(m));
// outParamValue = cstmt.getString((Integer)procOutParamList.get(m));
//System.out.println(“第”+m+”个输出参数值：”+outParamValue.toString());
outList.add(outParamValue);//将输出参数值放入list
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(cstmt!=null)
try {
cstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return outList;
}
}