过程

action:

//调存储过程，校验卡号是否合格,并进行卡延期

            String result = (String) cardPutOffService.putOffStandardBatchCardNum(batchNo, userName);

service:

   //调用存储过程，批量上传
    public Object putOffStandardBatchCardNum(String batchNo, String userName) {
        String result = null;
        Map paramMap = new HashMap();
        paramMap.put("batchNo", batchNo);
        paramMap.put("userName", userName);
        sqlMapClientTemplate.insert("cardPutOff_putOffStandardCardNum",paramMap);
        //sqlMapClientTemplate.insert("CHARGE_BATCHREQUEST",paraMap);
        result = paramMap.get("result").toString();
        return result;
    }

dao:

    <parameterMap class="java.util.HashMap" id="putOffStandardCardNum">  
             <parameter property="batchNo"  javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>  
            <parameter property="userName"  javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>  
            <parameter property="result"  javaType="java.lang.String" jdbcType="VARCHAR" mode="OUT"/>  
  </parameterMap>  
  <procedure id="cardPutOff_putOffStandardCardNum" parameterMap="putOffStandardCardNum">
   <![CDATA[
      {call SP_PUTOFF_STANDARDCARDNUM(?,?,?)}
   ]]>         
  </procedure>

过程：

create or replace PROCEDURE SP_PUTOFF_STANDARDCARDNUM
(
  IN_BATCHNO IN VARCHAR2,--批次号
  IN_USERNAME IN VARCHAR2,--用户
  OUT_RESULT OUT VARCHAR2
) AS
/********************************************************************
过程名称：查询批量延期卡的信息
过程说明：查询批量延期卡是否符合延期条件，将信息返回
*********************************************************************
---------------------------------------------------------------------
修改内容：初版
********************************************************************/
    T_BATCHNO VARCHAR(20); --批量号
    T_USERNAME VARCHAR(20); --操作人
    T_CARDNO VARCHAR(20); --卡号
    T_CARDEXPDATE VARCHAR(20); --延期卡有效时间
    T_CIEXPDATE VARCHAR(20); --信息卡有效时间
    T_YEAR NUMBER; --年数
    T_DATECOUNT VARCHAR(20);--延期次数
    T_RESULT VARCHAR(20); --成功标志
    T_COUNT NUMBER;
    T_PUTOFFEXPDATE VARCHAR(20);--延期后日期
    TT_PUTOFFEXPDATE VARCHAR(20);--延期后日期
    T_STATUS VARCHAR(20);--卡状态
    
  --读取临时表数据，校验
    CURSOR CUR_cardputoff_temp
     IS
         SELECT  CPO_BATCHNO,
                 CPO_USERNAME,
                 CPO_CARDNO
         FROM JFK_CARDPUTOFF_TEMP where CPO_BATCHNO=IN_BATCHNO and CPO_USERNAME=IN_USERNAME;

BEGIN
    --临时变量赋值
    T_BATCHNO := '';
    T_USERNAME := '';
    T_CARDNO := '';
    T_CARDEXPDATE := '';
    T_CIEXPDATE := '';
    T_YEAR := 0;
    T_DATECOUNT := '';
    T_RESULT := '';
    T_COUNT := 0;
    T_PUTOFFEXPDATE := '';
    TT_PUTOFFEXPDATE := '';
    T_STATUS := '';
    
    OPEN CUR_cardputoff_temp;
    LOOP
      FETCH CUR_cardputoff_temp
         INTO   T_BATCHNO,T_USERNAME,T_CARDNO;  
      EXIT WHEN CUR_cardputoff_temp%NOTFOUND;
      
      --判断延期表中是否有延期卡号
      select count(*) into T_COUNT from JFK_CARDPUTOFFLOG where CPO_CARDNO = T_CARDNO;
      IF T_COUNT > 0 --有延期卡号
          THEN select CPO_RESULT,CPO_DATECOUNT into T_RESULT,T_DATECOUNT from JFK_CARDPUTOFFLOG where CPO_CARDNO = T_CARDNO;
          IF  ((T_RESULT IS NULL) and (T_DATECOUNT IS NULL)) or ((T_RESULT = '1') and (T_DATECOUNT = '1')) --判断是否操作第一次延期
              THEN select CI_EXPDATE,CI_STATUS into T_CARDEXPDATE,T_STATUS from JFK_CARDINFO where CI_CARDNUM = T_CARDNO;
                  SELECT (sysdate - TO_DATE(T_CARDEXPDATE,'YYYY-MM-DD HH24:MI:SS')) DD into T_YEAR FROM DUAL;
                  IF ((T_YEAR > 366) and (T_STATUS = 'ACT'))
                      THEN
                          select (to_number(to_char(SYSDATE,'yyyy'))-1)||'-12-31 23:59:59' into T_PUTOFFEXPDATE from DUAL;
                          insert into JFK_CARDPUTOFFLOG(CPO_BATCHNO,CPO_USERNAME,CPO_CARDNO,CPO_RESULT,CPO_EXPDATE,CPO_REMARK,CPO_DATECOUNT,CPO_OPTTIME,CPO_PUTOFFEXPDATE)
                          values(T_BATCHNO,T_USERNAME,T_CARDNO,'0',T_CARDEXPDATE,'第一次延期成功','1',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),T_PUTOFFEXPDATE);
                          update JFK_CARDINFO set CI_EXPDATE=T_PUTOFFEXPDATE where CI_CARDNUM=T_CARDNO;--修改卡信息表有效期数据
                          OUT_RESULT := '0';
                  ELSE
                      IF T_YEAR < 366
                      THEN
                           insert into JFK_CARDPUTOFFLOG(CPO_BATCHNO,CPO_USERNAME,CPO_CARDNO,CPO_RESULT,CPO_EXPDATE,CPO_REMARK,CPO_DATECOUNT,CPO_OPTTIME)
                           values(T_BATCHNO,T_USERNAME,T_CARDNO,'1',T_CARDEXPDATE,'第一次延期卡时间未超过一年，延期失败','1',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
                      ELSE
                           insert into JFK_CARDPUTOFFLOG(CPO_BATCHNO,CPO_USERNAME,CPO_CARDNO,CPO_RESULT,CPO_EXPDATE,CPO_REMARK,CPO_DATECOUNT,CPO_OPTTIME)
                           values(T_BATCHNO,T_USERNAME,T_CARDNO,'1',T_CARDEXPDATE,'第一次延期卡状态不为激活，延期失败','1',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
                      END IF;
                      OUT_RESULT := '0101';
                  END IF;
             
          ELSIF ((T_RESULT = '0') and (T_DATECOUNT = '1')) or ((T_RESULT = '1') and (T_DATECOUNT = '2'))  --判断是否操作第二次延期
                  THEN --select CPO_EXPDATE into T_CARDEXPDATE from JFK_CARDPUTOFFLOG where CI_CARDNUM = T_CARDNO;
                       --select CPO_PUTOFFEXPDATE into T_PUTOFFEXPDATE from JFK_CARDPUTOFFLOG where CI_CARDNUM = T_CARDNO;
                       select CI_EXPDATE,CI_STATUS into T_PUTOFFEXPDATE,T_STATUS from JFK_CARDINFO where CI_CARDNUM = T_CARDNO;
                       SELECT (sysdate - TO_DATE(T_PUTOFFEXPDATE,'YYYY-MM-DD HH24:MI:SS')) DD into T_YEAR FROM DUAL;
                       IF ((T_YEAR > 366) and (T_STATUS = 'ACT'))
                            THEN
                               select (to_number(to_char(SYSDATE,'yyyy'))-1)||'-12-31 23:59:59' into TT_PUTOFFEXPDATE from DUAL;
                               insert into JFK_CARDPUTOFFLOG(CPO_BATCHNO,CPO_USERNAME,CPO_CARDNO,CPO_RESULT,CPO_EXPDATE,CPO_REMARK,CPO_DATECOUNT,CPO_OPTTIME,CPO_PUTOFFEXPDATE)
                               values(T_BATCHNO,T_USERNAME,T_CARDNO,'0',T_PUTOFFEXPDATE,'第二次延期成功','2',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),TT_PUTOFFEXPDATE);
                               update JFK_CARDINFO set CI_EXPDATE=TT_PUTOFFEXPDATE where CI_CARDNUM=T_CARDNO;--修改卡信息表有效期数据
                               OUT_RESULT := '0';
                       ELSE
                            IF T_YEAR < 366
                            THEN
                                insert into JFK_CARDPUTOFFLOG(CPO_BATCHNO,CPO_USERNAME,CPO_CARDNO,CPO_RESULT,CPO_EXPDATE,CPO_REMARK,CPO_DATECOUNT,CPO_OPTTIME,CPO_PUTOFFEXPDATE)
                                values(T_BATCHNO,T_USERNAME,T_CARDNO,'1',T_PUTOFFEXPDATE,'第二次延期卡时间未超过一年，延期失败','2',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),T_PUTOFFEXPDATE);
                            ELSE
                                insert into JFK_CARDPUTOFFLOG(CPO_BATCHNO,CPO_USERNAME,CPO_CARDNO,CPO_RESULT,CPO_EXPDATE,CPO_REMARK,CPO_DATECOUNT,CPO_OPTTIME,CPO_PUTOFFEXPDATE)
                                values(T_BATCHNO,T_USERNAME,T_CARDNO,'1',T_PUTOFFEXPDATE,'第二次延期卡状态不为激活，延期失败','2',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),T_PUTOFFEXPDATE);
                            END IF;
                            OUT_RESULT := '0101';
                       END IF;
          ELSE --已做第二次延期
                       select CPO_EXPDATE,CPO_PUTOFFEXPDATE into T_PUTOFFEXPDATE,T_PUTOFFEXPDATE from JFK_CARDPUTOFFLOG where CPO_CARDNO = T_CARDNO;
                       insert into JFK_CARDPUTOFFLOG(CPO_BATCHNO,CPO_USERNAME,CPO_CARDNO,CPO_RESULT,CPO_EXPDATE,CPO_REMARK,CPO_DATECOUNT,CPO_OPTTIME,CPO_PUTOFFEXPDATE)
                       values(T_BATCHNO,T_USERNAME,T_CARDNO,'1',T_CARDEXPDATE,'该卡已经为第二次延期，无法延期操作','3',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),T_PUTOFFEXPDATE);
                       OUT_RESULT := '0101';
          END IF;
             
      ELSE    --无延期卡号
          select CI_EXPDATE,CI_STATUS into T_CIEXPDATE,T_STATUS from JFK_CARDINFO where CI_CARDNUM = T_CARDNO;
          insert into JFK_CARDPUTOFFLOG(CPO_BATCHNO,CPO_USERNAME,CPO_CARDNO,CPO_EXPDATE) values(T_BATCHNO,T_USERNAME,T_CARDNO,T_CIEXPDATE);
          SELECT (sysdate - TO_DATE(T_CIEXPDATE,'YYYY-MM-DD HH24:MI:SS')) DD into T_YEAR FROM DUAL;
          IF ((T_YEAR > 366) and (T_STATUS = 'ACT'))
             THEN
                select (to_number(to_char(SYSDATE,'yyyy'))-1)||'-12-31 23:59:59' into T_PUTOFFEXPDATE from DUAL;
                update JFK_CARDPUTOFFLOG set CPO_RESULT='0',CPO_PUTOFFEXPDATE=T_PUTOFFEXPDATE,CPO_REMARK='第一次延期成功',CPO_DATECOUNT='1',
                       CPO_OPTTIME=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
                where CPO_CARDNO = T_CARDNO;
                update JFK_CARDINFO set CI_EXPDATE=T_PUTOFFEXPDATE where CI_CARDNUM=T_CARDNO;--修改卡信息表有效期数据
                OUT_RESULT := '0';
          ELSE
                IF T_YEAR < 366
                THEN
                    update JFK_CARDPUTOFFLOG set CPO_RESULT='1',CPO_REMARK='第一次延期卡时间未超过一年，延期失败',CPO_DATECOUNT='1',
                    CPO_OPTTIME=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
                    where CPO_CARDNO = T_CARDNO;
                ELSE
                    update JFK_CARDPUTOFFLOG set CPO_RESULT='1',CPO_REMARK='第一次延期卡状态不为激活，延期失败',CPO_DATECOUNT='1',
                    CPO_OPTTIME=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
                    where CPO_CARDNO = T_CARDNO;
                END IF;
                OUT_RESULT := '0101';
          END IF;
     END IF;
      
     END LOOP;
     CLOSE CUR_cardputoff_temp;
    
    --删除临时表数据
    delete from JFK_CARDPUTOFF_TEMP where CPO_BATCHNO=IN_BATCHNO and CPO_USERNAME=IN_USERNAME;
    
    

 
  COMMIT;
 
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
            OUT_RESULT := '10010';  --数据库异常
      ROLLBACK;
        RETURN;
    WHEN OTHERS THEN
        OUT_RESULT := '10001';     --数据库异常
    ROLLBACK;
        RETURN;

END SP_PUTOFF_STANDARDCARDNUM;