通过使用变量限制游标中的记录 [英] limit records in cursor by using a variable

问题描述

我不确定如何最好地描述我的问题

i'm not sure how to best describe my problem

我有一个表格(NR_POSTAL_ABBR)，其中包含邮政缩写及其代表的含义，例如ST和STREET，AVE和AVENUE.我希望能够用该表中的值替换地址字段.

i have a table (NR_POSTAL_ABBR) that has postal abbreviations and what they stand for, like ST and STREET, AVE and AVENUE. i want to be able to replace the address field with the values from that table.

如果我的地址为ST Main 123，我想说123 Main Street.我有一个执行此操作的程序(如下).速度很慢，现在我有一张带有3M条记录的表，并且它必须经过400个缩写对才能永久运行.

if i have an address 123 Main ST i want it to say 123 Main Street. i have a procedure that does this (below). it's slow and now that i have a table with 3M records and it has to go through 400 abbreviation pairs it will run forever.

所以我正在考虑加快速度的方法.一种方法是只遍历地址"字段中具有缩写的记录，这样执行以下操作，但由于位置错误而无法正常工作.

so i'm thinking of ways to speed this up. one way would be to only loop through the records that have the abbr in the Address field, so doing something like below but it doesn't work because it's in the wrong place.

那么两部分问题

1. 是否有更好的方法来实现我要完成的任务?
2. 如果不是，那么我如何解决以下过程，以仅通过地址具有NR_POSTAL_ABBR表中的ReplaceWhat值的记录来限制记录

谢谢

ETA，NewAddress字段的地址带有.，:;；取而代之的是空格.这样，如果它们是整个单词(st，而不是west)，我就只能替换它们.

ETA the NewAddress field has addresses with .,:; replaced with spaces. this way i'm able to only replace the values if they're whole words ( st, not west)

我想用第二个CURSOR替换

what i'd like to replace the second CURSOR with

     CURSOR readMainTable IS
       SELECT A.*
         FROM analyst.NR_TMP_105 A
WHERE NeweAddress LIKE '%' || VariableThatHoldsTheReplaceWhatValue || '%';

我目前拥有的程序可以运行，但是速度很慢

the procedure i currently have and that works but is very slow

DECLARE
     CURSOR getReplsStrng IS
       SELECT replacewhat
          ,replacewith
         FROM analyst.NR_POSTAL_ABBR
        WHERE ReplaceOrder = 1;

     CURSOR readMainTable IS
       SELECT A.*
         FROM analyst.NR_TMP_105 A;

     lvAddress VARCHAR2(5000);
     lvTmpAddress VARCHAR2(5000);
     lnPos NUMBER(10);
     lnPosPls NUMBER(10);
     lvPosPls VARCHAR2(500);
     lvPosMinus VARCHAR2(500);
     lnLoopCnt NUMBER := 0;
     lvCty VARCHAR2(200);
     lvBfUpd VARCHAR2(500);
     lvAfterUpd VARCHAR2(500);
BEGIN
     lnLoopCnt := 0;

     FOR getRec IN readMainTable LOOP
       lnLoopCnt := lnLoopCnt + 1;

       lvAddress := NULL;
       lvTmpAddress := NULL;

       lvAddress := getRec.NewAddress;
       lvTmpAddress := getRec.NewAddress;

       FOR getInnerRec IN getReplsStrng LOOP
         lvPosPls := NULL;
         lvPosMinus := NULL;
         lnPos := 0;
         lnPos := INSTR(UPPER(lvAddress), UPPER(getInnerRec.replacewhat), 1, 1);

         IF lnPos > 0 THEN
              NULL;

              lvPosPls := SUBSTR(UPPER(lvAddress)
                       ,((  INSTR(UPPER(lvAddress), UPPER(getInnerRec.replacewhat), 1, 1)
                          + LENGTH(UPPER(getInnerRec.replacewhat))))
                       ,1);
              lvPosMinus := SUBSTR(UPPER(lvAddress), (INSTR(UPPER(lvAddress), UPPER(getInnerRec.replacewhat), 1, 1) - 1), 1);

              IF     (   lvPosPls IS NULL
                OR lvPosPls = CHR(32))
              AND (   lvPosMinus = CHR(32)
                OR lvPosMinus = CHR(32)) THEN
                lvAddress := REPLACE(UPPER(lvAddress), UPPER(getInnerRec.replacewhat), UPPER(getInnerRec.replacewith));

                lvAddress := LOWER(lvAddress);
              ELSIF lvPosPls IS NOT NULL THEN
                NULL;
              END IF;
              BEGIN
                UPDATE analyst.NR_TMP_105 b
                SET b.NewAddress = lvAddress
                 WHERE   b.NewAddress = lvTmpAddress
                    AND b.UniqueID = getRec.UniqueID;
                COMMIT;
              EXCEPTION
                WHEN OTHERS THEN
                  DBMS_OUTPUT.put_line('Error in updating the address : ' || lvTmpAddress);
              END;
         ELSIF lnPos = 0 THEN
              NULL;
         END IF;

         lvTmpAddress := lvAddress;

         IF MOD(lnLoopCnt, 200) = 0 THEN
              COMMIT;
              NULL;
         END IF;
       END LOOP;
     END LOOP;

     COMMIT;
EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.put_line('Error in main process:' || SUBSTR(SQLERRM, 1, 200));
END;

推荐答案

如果我得到您的第二部分，那么您可以按照以下步骤做一些事情

If I get your second part then you can do something along the lines of

CURSOR readMainTable IS
   SELECT DISTINCT A.*
     FROM analyst.NR_TMP_105 A
     INNER JOIN NR_POSTAL_ABBR B
     ON (A.NewAddress like ' %'||B.replacewhat||'%'
    WHERE B.ReplaceOrder = 1

那应该只为您提供需要替换的地址(或可能需要替换的地址-您可能需要对其进行优化). 有没有更好的办法?也许可以，但是如何进行决定了是否需要付出额外的努力.

That should give you just the addresses that require replacements (or potentially require them - you might need to refine it). Is there a better way? Maybe, but how you go with this determines whether you need to put the extra effort in or not.

这篇关于通过使用变量限制游标中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！