遇到一段跑起来费解的代码

Wayne posted @ Thu, 12 Jan 2012 17:25:30 +0000 in Experience , 2265 readers

今天校准数据库的时候,发现了一个执行结果不同的现象。经过定位,发现问题出在下面这段代码上。这段代码的执行结果被一个v_request_num的变量所左右。 v_request_num 越小,最后的结果就越少, v_request_num 越大,最后的结果就越多。如果说单次执行的话,这个是废话。但多次执行,直到将表中数据计算完毕后的结果也是这样,着实费解。

 

代码如下:

  /********************************************************************************************/
  /*                  最小阀值                                                                 */
  /********************************************************************************************/
  begin
    select min(index_value) into v_min_value
      from  indexvalue
     where exec_index = v_exec_index;
  exception
    when others then
      v_error_no := 23122003;
      p_error_info := '[23122003]取最小index_value失败' ||
                         ' v_exec_index = ' || v_exec_index;
    return v_error_no;
  end;

  /********************************************************************************************/
  /* 取已执行流水号                                                                           */
  /********************************************************************************************/
  begin
    select nvl(count(*), 0)
      into v_count
      from icsentrustno
     where exec_index = v_exec_index and branch_no = 0;
  exception
    when others then
      null;
  end;

  if v_count = 0 then
    begin
      insert into icsentrustno values(v_exec_index, 0, 0);
    exception
      when others then
        v_error_no := 23122003;
        p_error_info := '[23122003]插入流水号表失败'
                        || 'exec_index = ' || v_exec_index;
      return v_error_no;
    end;
  else
    begin
      select record_id
        into v_record_id
        from icsentrustno
       where exec_index = v_exec_index
         and branch_no = 0;
    exception
      when others then
        v_error_no := 23122004;
        p_error_info := '[23122004]执行流水号不存在'
                        || 'exec_index = ' || v_exec_index;
      return v_error_no;
    end;
  end if;

  /********************************************************************************************/
  /*重置起始流水号                                                                            */
  /********************************************************************************************/
  begin
    select nvl(min(record_id),0)
      into v_beginrecord_id
      from entrust;
  exception
    when others then
      v_error_no := 23122013;
      p_error_info := '[23122013]取最小流水号失败' ||
                      ' v_exec_index = ' || v_exec_index;
    return v_error_no;
  end;

  if v_record_id < v_beginrecord_id then
    begin
      update icsentrustno
         set record_id = v_beginrecord_id - 1
       where exec_index = v_exec_index;
    exception
      when others then
        v_error_no := 23122013;
        p_error_info := '[23122013]更新执行流水号表失败' ||
                        ' v_exec_index = ' || v_exec_index;
      return v_error_no;
    end;

    v_record_id := v_beginrecord_id - 1;
  end if;

  /********************************************************************************************/
  /* 取执行结束流水号                                                                         */
  /********************************************************************************************/
  begin
    select nvl(MAX(record_id),0)
      into v_endrecord_id
      from entrust;
  exception
    when others then
      v_error_no := 23122007;
      p_error_info := '[23122007]取执行结束流水号失败'
                     || ' 执行指标: ' || v_exec_index;
    return v_error_no;
  end;

  if v_endrecord_id = v_record_id then --没有新增流水
    return 0;
  end if;

  if v_request_num <> 0 and  v_endrecord_id > (v_record_id + v_request_num) then
    v_endrecord_id :=  v_record_id + v_request_num;    --只取v_request_num条记录
  end if;

  --取配置计算时间设置
  begin
    select nvl(int_config, 15)
      into v_up_time
      from consysconfig
     where config_no = 4121;
  exception
    when others then
      v_up_time := 15;
  end;

  begin
    select cast(nvl(str_config, 1.02) as number(19,2))
      into v_ratio
      from  consysconfig
     where config_no = 4120;
  exception
    when others then
      v_ratio := 1.02;
  end;

  begin
    select nvl(int_config, -1)
      into v_diff
      from consysconfig t
     where config_no = 4207;
  exception
    when others then
      v_diff := -1;
  end;

  /********************************************************************************************/
  /* 以下 计算指标,记录超标流水,置执行流水号                                                  */
  /********************************************************************************************/
  begin
    select min(entrust_time),max(entrust_time)
      into v_min_entrust_time,v_max_entrust_time
      from entrust
     where record_id > v_record_id
       and record_id <= v_endrecord_id
       and entrust_time between 0 and 150000;
  exception
    when others then
      v_error_no := 22702307;
      p_error_info := '[22702307]获取区间最小及最大委托时间失败'
                     || ' 执行指标: ' || v_exec_index;
    return v_error_no;
  end;



  v_begin_time := case when v_min_entrust_time - v_up_time*60 < 0 then 0 else fn_gettime_diffbyminute(v_min_entrust_time, v_up_time) end;
  v_end_time := v_max_entrust_time;

  begin
    insert into t_231220
    select *
      from entrust
     where entrust_time >= 92000
       and entrust_time between v_begin_time and v_end_time
       and stock_type not in ('D','F')
       and abs(entrust_amount * entrust_price)  >= v_min_value
       and entrust_type in ('0','6','7','9')
       and entrust_prop = '0';
   
  exception
    when others then
      v_error_no := 22702307;
      p_error_info := '[22702307]插入临时表[t_231220]失败'
                     || ' 执行指标: ' || v_exec_index;
    return v_error_no;
  end;

  declare cursor cur_record is
  select record_id, oc_date, entrust_time, client_id, fund_account, branch_no, operator_no,
         exchange_type, stock_code, entrust_price, entrust_bs, entrust_amount, entrust_price,
   entrust_type
    from t_231220 a
   where record_id > v_record_id
     and record_id <= v_endrecord_id
     and exists(select 1 from t_231220  b
                 where b.fund_account <> a.fund_account
                   and b.exchange_type = a.exchange_type
                   and b.stock_code = a.stock_code
                   and b.entrust_time between case when a.entrust_time - v_up_time*60 <0 then 0 else fn_gettime_diffbyminute(a.entrust_time,v_up_time) end and fn_gettime_diffbyminute(a.entrust_time,-v_up_time)
                   and b.entrust_bs <> a.entrust_bs
                   and b.entrust_bs = case a.entrust_bs when '1' then '2' when '2' then '1' end
                   and (b.entrust_type = case a.entrust_type when '6' then '0' when '7' then '0' when '9' then '0' end or
                        b.entrust_type = case a.entrust_type when '0' then '6' end or
                        b.entrust_type = case a.entrust_type when '0' then '7' end or
                        b.entrust_type = case a.entrust_type when '0' then '9' end)
                   and (case when b.entrust_price > a.entrust_price then b.entrust_price/a.entrust_price
                             else a.entrust_price/b.entrust_price
                        end) < v_ratio
                   and (v_diff = -1 or ABS(b.entrust_amount - a.entrust_amount) <= v_diff))
   order by record_id;

这里entrust_time是个时间转化出来的number类型,格式是yyyymmdd,8位整数。

v_up_time的单位是分钟。

fn_gettime_diffbyminute(entrust_time,v_up_time) 的作用是得到 entrust_time前 v_up_time的时间,比如当entrust_time为150000 (15点整),v_up_time为10 (10分钟)时,这个函数的结果就是 145000 (14点50分)。类似的,参数变成-v_up_time,就是取entrust_time的后v_up_time的时间,比如说151000。

 
手动执行的时候,删掉了record_id,相当于从0到最大。如果直接设置v_request_num 这个步长变量到很大,也可以达到同样效果。程序自己跑的话,v_request_num 为500。
步长非常大,比如说2000000,跟步长500相比,唯一影响在于游标取值时的record_id条件上, 可这个条件为什么会导致最后结果变少了呢?
 
 
 
 
 

我错了……  我光被最后一个游标的条件吸引了注意力,却没注意到最开始取 v_min_entrust_time 和 v_max_entrust_time 时也用到了 record_id。  前面就用到了,导致 t_231220 中的记录集差很多,那么自然执行结果也差很多…… 这么显而易见的问题,居然给忽视了

 
 
 
netflix. com/tv8 said:
Sat, 22 Jul 2023 15:55:34 +0000

Netflix com/tv8 ist der offizielle Link zur Aktivierung auf einer Reihe von Geräten, einschließlich intelligenter Fernseher, Spielkonsolen und Streaming-Media-Playern. Diese URL Netflix com/tv8 gibt Ihnen eine 8-stellige Nummer. netflix. com/tv8 Sie können diesen Code verwenden, um Ihr Konto auf jedem Ihrer Smart-Geräte zu aktivieren. Bevor Sie es verwenden können, müssen Sie zuerst die Netflix-App auf diesen Geräten installieren, dann zeigt das Gerät den achtstelligen Code an.

Gujarat 2nd Class T said:
Tue, 22 Aug 2023 11:51:41 +0000 Gujarat Board Primary School Academic Year Close in Every Year Month of April, Gujarat Board Elementary School new Academic Year Open in Every Year Month of Jun, Every Year Gujarat State Wise Class More Than 50 Laks of Students Attended, Every Year Primary School Education Department Government of Gujarat Distribution in Textbook in Gujarati / English / Hindi Medium.The Printed Gujarat Class Gujarat 2nd Class Textbook 2024 Textbook 2024 are Distributed Through Cooperative Institutions All over Gujarat. Vendors are Linked to the Distribution of Textbooks with Distributors in each District. Gujarat Board Class Book 2024 are easily Accessible to All Students of This System, This will assist in improving the Quality of Teaching by Understanding the Type of Difficulties Faced by Students.

Login *


loading captcha image...
(type the code from the image)
or Ctrl+Enter