预定义规则 取范围数据

CREATE OR REPLACE PROCEDURE Campaignprize_range(ServerIndex VARCHAR2, var_array OUT varchar_array) IS
   indx number;
   hashcoded VARCHAR2(8);
   prizetypenum number(6);
   prizetypenumold number(6);
   prizetypenumc VARCHAR2(6);
   totalcount number(6);
   totalcounta number(12);
   flag number(1);
   single number(6);
   RETVAL VARCHAR2(2000);
BEGIN
  var_array :=varchar_array();
  indx:=1;
   with cet as ( select  distinct t.campaignid from mkt_campaignprize t join mkt_marketcampaign m on t.campaignid=m.campaignid and m.state=0 where t.issend=0 and t.hashcode like ''||ServerIndex||'%' group by t.campaignid, t.prizetypeid )
   select count(1) into totalcounta from cet;
   var_array.extend(totalcounta);
  DECLARE CURSOR c0 IS
  select distinct t.campaignid,t.prizetypeid from mkt_campaignprize t join mkt_marketcampaign m on t.campaignid=m.campaignid and m.state=0 where t.issend=0 and t.hashcode like ''||ServerIndex||'%' group by t.campaignid, t.prizetypeid ;
    BEGIN
     FOR r0 IN c0 LOOP
      flag:=0;
      RETVAL :='';
      single:=0;
      SELECT count(1) into totalcount FROM mkt_campaignprize a WHERE a.campaignid=r0.campaignid AND a.issend=0 and a.prizetypeid=r0.prizetypeid  and a.hashcode like ''||ServerIndex||'%'  order by a.campaignprizeid asc;
      DECLARE CURSOR c1 IS
          SELECT a.hashcode,a.campaignprizeid  FROM mkt_campaignprize a WHERE a.campaignid=r0.campaignid AND a.issend=0 and a.prizetypeid=r0.prizetypeid  and a.hashcode like ''||ServerIndex||'%'  order by a.campaignprizeid asc;
      BEGIN
          FOR r1 IN c1 LOOP
             single:=single+1;
             if flag=0 then
              prizetypenumold:= to_number( substr(r1.hashcode,3,6));
              RETVAL :=prizetypenumold;
               if totalcount=single then
                 RETVAL :=RETVAL||','||prizetypenumold||'$';
                 end if;
              flag:=1;
              else
                 prizetypenum:=prizetypenumold+1;
                 prizetypenumc:=lpad(prizetypenum,6,'0');
                 hashcoded:=ServerIndex||prizetypenumc;
                 if hashcoded=r1.hashcode then
                   prizetypenumold:=prizetypenum;
                 else
                   RETVAL :=RETVAL||','||prizetypenumold||'$';
                   prizetypenumold:= to_number( substr(r1.hashcode,3,6));
                   RETVAL :=RETVAL||prizetypenumold;
                 end if;
                 if totalcount=single then
                 RETVAL :=RETVAL||','||prizetypenumold||'$';
                 end if;
              end if;
          END LOOP;
         
      END;
      if indx <=totalcounta then
      var_array(indx):=RETVAL||ServerIndex||r0.campaignid||r0.prizetypeid;
      indx:=indx+1;
      end if;
     end loop;
   end;

END Campaignprize_range;

public String[] runProcedure_1(String callProcedure, Object[] param,
            String serverName) {
        String[] obj = null;
        logger.info("开始运行存储过程！");
        Connection conn = null;
        CallableStatement callableStatement = null;
        OracleConnection connection = null;
        DataSource dataSource = null;
        try {
            if (serverName.equals("tomcat")) {
                conn = initConnection();
                logger.info("服务器为： " + serverName);
                C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor();
                connection = (OracleConnection) cp30NativeJdbcExtractor
                        .getNativeConnection(conn);
            } else if (serverName.equals("webSphere")) {
                logger.info("服务器为： " + serverName);
                dataSource = SessionFactoryUtils
                        .getDataSource(getBaseQueryDao().getSessionFactory());
                logger.info("获得dataSource成功");
                conn = dataSource.getConnection();
                connection = (OracleConnection) WSCallHelper
                        .getNativeConnection(conn);
                logger.info("获得oracleConnection成功");
            }
            // callProcedure = "{call ASSIGN_PRIZE(?,?,?,?,?)}"
            callableStatement = connection.prepareCall(callProcedure);
            logger.info("开始获得ArrayDescriptor");
            ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor(
                    "VARCHAR_ARRAY", connection);
            logger.info("获得ArrayDescriptor成功");
            for (int i = 0; i < param.length; i++) {
                if (param[i] instanceof Long) {
                    callableStatement.setObject(i + 1, param[i]);
                }
                else if(param[i] instanceof String)
                {
                    callableStatement.setObject(i + 1, param[i]);
                }
                else {
                    ARRAY array = new ARRAY(descriptor, connection,
                            ((List) param[i]).toArray());
                    callableStatement.setArray(i + 1, array);

                }
            }
        
            callableStatement.registerOutParameter(param.length + 1,OracleTypes.ARRAY,"VARCHAR_ARRAY");
            callableStatement.execute();
            java.sql.Array result = callableStatement.getArray(param.length + 1);  
            if ( result  != null )
            {
                obj = (String[])result.getArray();
                logger.info(obj.length);
                logger.info(obj[0]);
            }
            
            
            
        } catch (Exception e) {
            logger.error("数据库异常: " + e);
        } finally {
            try {
                closeConnection(conn);
            } catch (SQLException e) {
                logger.error("关闭连接异常，请检查数据库连接: " + e);
            }
        }
        return obj;
    }

private void loadData(String serverIndex )
    {
        String key="";
          String procedure = "{call campaignprize_range(?,?)}";
            Object[] objs =ObjectUtils. asArray( serverIndex);
             String serverName = "tomcat";
                if(ServerDetector.isTomcat()){
                    serverName = "tomcat";
                }
                else if(ServerDetector.isWebSphere()){
                    serverName = "webSphere";
                }
                logger.info("服务器名称为： "+serverName);
            logger.info("执行存储过程,参数:"+serverIndex);
            
            
            String [] retval = campaignPrizeDao.runProcedure_1(procedure, objs, serverName);
          
            try{
               for(int i=0;i<retval.length;i++)
               {
                  String retStr=retval[i];
                    List<CampaignPrizeRange> campaignPrizeRanges=new ArrayList<CampaignPrizeRange>();
                     StringTokenizer stringTokenizer = new StringTokenizer(retStr, "$");
                        while (stringTokenizer.hasMoreTokens()) {
                            String level1Token = stringTokenizer.nextToken();
                            if(level1Token.indexOf(",")>0)
                            {
                                CampaignPrizeRange campaignPrizeRange =new CampaignPrizeRange();
                                String[] StartAndEnd=level1Token.split(",");
                                if(StartAndEnd==null||StartAndEnd.length<2){
                                    continue;
                                }
                                logger.info("startAndEnd---[0]:"+StartAndEnd[0]);
                                logger.info("startAndEnd---[1]:"+StartAndEnd[1]);
                                campaignPrizeRange.setStartValue(Long.parseLong(StartAndEnd[0]));
                                campaignPrizeRange.setEndValue(Long.parseLong(StartAndEnd[1]));
                                campaignPrizeRanges.add(campaignPrizeRange);
                            }
                            else
                            {
                                key=level1Token;
                            }
                        }
                    
                    getCampaignPrizeFromCache.saveToCache(key,campaignPrizeRanges);
            
               }
            }
            catch(Exception e)
            {
                logger.info(e.getMessage());
              
            }
    
    }

CREATE OR REPLACE TYPE varchar_array is Table OF varchar2(128);