用于跳过数据库中记录的行的PL/SQL游标

Question

我不明白为什么在我的过程中有光标跳过行。这是我的光标：

OPEN c_denormalized_data FOR lv_sql_str;
    LOOP
 FETCH c_denormalized_data
   INTO r_denormalized_data;
 EXIT WHEN c_denormalized_data%NOTFOUND;

 INSERT INTO bpdev.bp_real_to_fiskal_reference
   (r_customer_trx_id,
    r_trx_number,
    r_trx_date,
    r_amount,
    r_amount_due_rem,
    diff,
    f_trx_number,
    f_trx_date,
    f_amount,
    NULLS,
    notnulls,
    org_id,
    data_loaded_for_days)
 VALUES
   (r_denormalized_data.r_customer_trx_id,
    r_denormalized_data.r_trx_number,
    r_denormalized_data.r_trx_date,
    r_denormalized_data.r_amount,
    r_denormalized_data.r_amount_due_rem,
    r_denormalized_data.diff,
    r_denormalized_data.f_trx_number,
    r_denormalized_data.f_trx_date,
    r_denormalized_data.f_amount,
    r_denormalized_data.nulls,
    r_denormalized_data.notnulls,
    r_denormalized_data.org_id,
    days_before_today);
 COMMIT;   
   END LOOP;

lv_sql_str是动态选择，其执行时间约为30 min。使用游标是因为此选择是根据输入参数动态生成的。如果我只执行SELECT语句，我会收到我期望的所有数据，但是当我执行过程插入数据库时，我会看到出现在SELECT返回的数据中的losе记录。我不明白为什么这些记录被跳过而不被插入到表中。有人知道我的错误在哪里吗？

这是我的完整代码：

PROCEDURE load_denormalized_data(errbuf                      OUT NOCOPY VARCHAR2,
                               retcode                     OUT NOCOPY NUMBER,
                               days_before_today           IN NUMBER,
                               real_legal_entyties_id_list IN VARCHAR2) IS
TYPE tc_denormalized_data IS REF CURSOR;
c_denormalized_data tc_denormalized_data;
TYPE tr_denormalized_data IS RECORD(
  r_customer_trx_id bpdev.bp_real_to_fiskal_reference.r_customer_trx_id%TYPE,
  r_trx_number      bpdev.bp_real_to_fiskal_reference.r_trx_number%TYPE,
  r_trx_date        bpdev.bp_real_to_fiskal_reference.r_trx_date%TYPE,
  r_amount          bpdev.bp_real_to_fiskal_reference.r_amount%TYPE,
  r_amount_due_rem  bpdev.bp_real_to_fiskal_reference.r_amount_due_rem%TYPE,
  diff              bpdev.bp_real_to_fiskal_reference.diff%TYPE,
  f_trx_number      bpdev.bp_real_to_fiskal_reference.f_trx_number%TYPE,
  f_trx_date        bpdev.bp_real_to_fiskal_reference.f_trx_date%TYPE,
  f_amount          bpdev.bp_real_to_fiskal_reference.f_amount%TYPE,
  NULLS             bpdev.bp_real_to_fiskal_reference.nulls%TYPE,
  notnulls          bpdev.bp_real_to_fiskal_reference.notnulls%TYPE,
  org_id            bpdev.bp_real_to_fiskal_reference.org_id%TYPE);

r_denormalized_data tr_denormalized_data;
lv_where_le_ids     VARCHAR2(3000);
lv_sql_str          VARCHAR2(5000);
 BEGIN
bpdev.bp_utils.put_log('>>>--------ПОТРЕБИТЕЛСКИ СЪОБЩЕНИЯ--------<<<');
bpdev.bp_utils.put_log('Изтриване на старите данни.');
DELETE bpdev.bp_real_to_fiskal_reference rfr;
COMMIT;


lv_where_le_ids := bp_utils.set_where_clause('invr.org_id',
                                             real_legal_entyties_id_list,
                                             ',',
                                             ';',
                                             0);
lv_sql_str      :=     
 'SELECT invr.customer_trx_id r_customer_trx_id,
   invr.trx_number r_trx_number,
   invr.trx_date r_trx_date,
   round(invr.extended_amount, 2) r_amount,
   round(nvl(ps.amount_due_remaining, invr.extended_amount), 2) r_amount_due_rem,
   round(invr.amount_without_credits - invf.extended_amount, 3) diff,
   invf.trx_number f_trx_number,
   invf.trx_date f_trx_date,
   round(invf.extended_amount, 2) f_amount,
   round(invr.extended_amount +
         nvl((SELECT SUM(psa.amount_credited)
               FROM ar_payment_schedules_all psa
              WHERE psa.customer_trx_id = invr.customer_trx_id),
             0) - invf.extended_amount,
         1) NULLS,
   round(nvl(ps.amount_due_remaining, (invr.extended_amount * 1.2)), 2) notnulls,
   invr.org_id
FROM (SELECT cth.customer_trx_id,
           cth.trx_number,
           cth.trx_date,
           SUM(ctl.extended_amount) * 1.2 extended_amount,
           round(((SUM(ctl.extended_amount) * 1.2 +
                 nvl((SELECT SUM(psa.amount_credited)
                         FROM ar_payment_schedules_all psa
                        WHERE psa.customer_trx_id = cth.customer_trx_id),
                       0))),
                 2) amount_without_credits,
           cth.org_id,
           nvl(decode(TRIM(ctl.interface_line_attribute13),
                      0,
                      NULL,
                      TRIM(ctl.interface_line_attribute13)),
               cth.interface_header_attribute13) interface_line_attribute13
      FROM ra_customer_trx_all       cth,
           ra_customer_trx_lines_all ctl
     WHERE cth.customer_trx_id = ctl.customer_trx_id AND
           ctl.line_type = ''LINE'' AND
           ctl.set_of_books_id = 1001
          GROUP BY cth.customer_trx_id,
              cth.trx_number,
              cth.trx_date,
              cth.org_id,
              nvl(decode(TRIM(ctl.interface_line_attribute13),
                         0,
                         NULL,
                         TRIM(ctl.interface_line_attribute13)),
                  cth.interface_header_attribute13)) invr,
   (SELECT cth.customer_trx_id,
           cth.trx_number,
           cth.trx_date,
           SUM(ctl.extended_amount) * 1.2 extended_amount,
           round(((SUM(ctl.extended_amount) * 1.2 +
                 nvl((SELECT SUM(psa.amount_credited)
                         FROM ar_payment_schedules_all psa
                        WHERE psa.customer_trx_id = cth.customer_trx_id),
                       0))),
                 2) amount_without_credits,
           cth.org_id,
           nvl(decode(TRIM(ctl.interface_line_attribute13),
                      0,
                      NULL,
                      TRIM(ctl.interface_line_attribute13)),
               cth.interface_header_attribute13) interface_line_attribute13
      FROM ra_customer_trx_all       cth,
           ra_customer_trx_lines_all ctl
     WHERE cth.customer_trx_id = ctl.customer_trx_id AND
           ctl.line_type = ''LINE'' AND
           cth.set_of_books_id = 2001
     GROUP BY cth.customer_trx_id,
              cth.trx_number,
              cth.trx_date,
              cth.org_id,
              nvl(decode(TRIM(ctl.interface_line_attribute13),
                         0,
                         NULL,
                         TRIM(ctl.interface_line_attribute13)),
                  cth.interface_header_attribute13)) invf,
   ar_payment_schedules_all ps
    WHERE invf.interface_line_attribute13(+) = invr.customer_trx_id AND
   ps.customer_trx_id(+) = invr.customer_trx_id AND
   invr.trx_date >= SYSDATE - ' ||
                   days_before_today || ' ' || lv_where_le_ids;

bpdev.bp_utils.put_log('Зареждане на новите данни.');
OPEN c_denormalized_data FOR lv_sql_str;
LOOP
  FETCH c_denormalized_data
    INTO r_denormalized_data;
  EXIT WHEN c_denormalized_data%NOTFOUND;

  INSERT INTO bpdev.bp_real_to_fiskal_reference
    (r_customer_trx_id,
     r_trx_number,
     r_trx_date,
     r_amount,
     r_amount_due_rem,
     diff,
     f_trx_number,
     f_trx_date,
     f_amount,
     NULLS,
     notnulls,
     org_id,
     data_loaded_for_days)
  VALUES
    (r_denormalized_data.r_customer_trx_id,
     r_denormalized_data.r_trx_number,
     r_denormalized_data.r_trx_date,
     r_denormalized_data.r_amount,
     r_denormalized_data.r_amount_due_rem,
     r_denormalized_data.diff,
     r_denormalized_data.f_trx_number,
     r_denormalized_data.f_trx_date,
     r_denormalized_data.f_amount,
     r_denormalized_data.nulls,
     r_denormalized_data.notnulls,
     r_denormalized_data.org_id,
     days_before_today);
END LOOP;
COMMIT;
CLOSE c_denormalized_data;
bpdev.bp_utils.put_log('>>>------КРАЙ ПОТРЕБИТЕЛСКИ СЪОБЩЕНИЯ-----<<<');
 EXCEPTION
WHEN OTHERS THEN
  bpdev.bp_utils.put_log(retcode || errbuf);
    END load_denormalized_data;

Answer 1

我们没有全部的信息，但我怀疑问题就在这里

 invr.trx_date >= SYSDATE - ' ||        days_before_today || ' ' || lv_where_le_ids;

尝试将您期望的静态日期添加，然后再试一次。