java - 同一方法中两个不同过程调用的相同可调用语句变量

Question

我想知道是否可以在同一个方法中调用两个过程。是否会导致超出最大游标等语句泄漏问题。 ？如何克服这个问题？

例如，找到以下代码:

 cstmt = conn.prepareCall("{call prc_gen_los_schedule_rds(?, ?, ?, ?,?,?)}");
  cstmt.setString(1, strOrgCode);
  cstmt.setString(2, strApplNo);
  cstmt.setString(3, "D");
  cstmt.setString(4, "Y");
  cstmt.registerOutParameter(5, 2005);
  cstmt.registerOutParameter(6, 12);
  cstmt.execute();
  aayuxml = cstmt.getString(5);
  strUrl = cstmt.getString(6);
  logger.error("Result is=>" + strOut);
  logger.error("aayuxml is=>" + aayuxml);
  if (StringUtils.hasText(aayuxml))
  {
    logger.error("CALLING getAmortEMI===>");
    amort = aayu.getAmortXML(aayuxml, strUrl);
    logger.error("INSTALLMENT====>" + amort);
    if (StringUtils.hasText(amort))
    {
      cstmt = conn.prepareCall("{call PRC_INS_LOS_SCHEDULE_RDS(?, ?, ?,?)}");
      cstmt.setString(1, strOrgCode);
      cstmt.setString(2, strApplNo);
      cstmt.setString(3, amort);
      cstmt.registerOutParameter(4, 12);
      cstmt.execute();
      strOut = cstmt.getString(4);
      logger.error("strOut=>" + strOut);
    }
    else
    {
      strOut = "ERROR: webservice return blank xml";
    }

Answer 1

最简单(也是最快)的修复可能是这样的，

CallableStatement cstmt = null;
try {
    try {
        cstmt = conn
                .prepareCall("{call prc_gen_los_schedule_rds(?, ?, ?, ?,?,?)}");
        cstmt.setString(1, strOrgCode);
        cstmt.setString(2, strApplNo);
        cstmt.setString(3, "D");
        cstmt.setString(4, "Y");
        cstmt.registerOutParameter(5, 2005);
        cstmt.registerOutParameter(6, 12);
        cstmt.execute();
        aayuxml = cstmt.getString(5);
        strUrl = cstmt.getString(6);
    } finally {
        cstmt.close();
    }
    logger.error("Result is=>" + strOut);
    logger.error("aayuxml is=>" + aayuxml);
    if (StringUtils.hasText(aayuxml)) {
        logger.error("CALLING getAmortEMI===>");
        amort = aayu.getAmortXML(aayuxml, strUrl);
        logger.error("INSTALLMENT====>" + amort);
        if (StringUtils.hasText(amort)) {
            cstmt = conn
                    .prepareCall("{call PRC_INS_LOS_SCHEDULE_RDS(?, ?, ?,?)}");
            cstmt.setString(1, strOrgCode);
            cstmt.setString(2, strApplNo);
            cstmt.setString(3, amort);
            cstmt.registerOutParameter(4, 12);
            cstmt.execute();
            strOut = cstmt.getString(4);
            logger.error("strOut=>" + strOut);
        } else {
            strOut = "ERROR: webservice return blank xml";
        }
    }
} finally {
    if (cstmt != null) {
        cstmt.close();
    }
}

我可能会自己将其提取到单独的方法中，并从通用方法中调用它们。