oracle - 带有子句的 PL SQL 模糊搜索

Question

我正在处理呼叫中心客户搜索查询。如果用户输入姓氏和驾驶执照，我必须从数据库中找出最匹配的客户。我正在使用模糊搜索 soundex() 来比较客户的姓氏。根据不同的情况，我必须继续使用 soundex 功能，它会影响性能。我尝试使用 With Clause，但由于我有很多 IF 和 ELSE 部分，它给出了 table 或 view not found 异常。

我试图在谷歌上找到如何使用 with 子句和 with 子句 with function，它们都没有帮助。

PROCEDURE RetrieveCustomer
(
      i_lastname          IN varchar,
      i_DL             IN varchar, 
      o_cursor            OUT     T_CURSOR
)  
IS
      v_DLAndExactLastNameCnt        Number; 
      v_DLAndLastNameCnt             Number;
      v_DLCnt                        Number;      
  BEGIN

     SELECT COUNT(*) INTO v_DLAndExactLastNameCnt
     FROM   t1, t2
     WHERE t1.DL = i_DL AND (upper(t2.last_name) like upper(i_lastname));

     IF(v_DLAndExactLastNameCnt > 0) THEN
        OPEN o_cursor FOR
            select
              a,
              b,
              'DL + ELN' as search_result_baseon --exact last name match
        from t1, t2, t3, t4
        where t1.DL = i_DL AND (upper(t2.last_name) like upper(i_lastname));

     ELSE
         SELECT COUNT(*) INTO v_DLAndLastNameCnt
         FROM   t1, t2
         WHERE t1.DL= i_DL
               AND p.last_name in (select last_name from t2 where soundex(last_name) = soundex(i_lastname));                                 

          IF(v_DLAndLastNameCnt > 0) THEN   
            OPEN o_cursor FOR                 
                select 
                       a,
                     b,
                     'DL + LN' as search_result_baseon -- last name not exact match
                from t1, t2, t3, t4 
                where
                     t1.DL= i_DL
                     AND p.last_name in (                          
                            select last_name from t2 where soundex(last_name) = soundex(i_lastname)
                                                );                                   
           ELSE
              SELECT COUNT(*) INTO v_DLCnt
               FROM   t1
               WHERE  t1.DL = i_DL;
               IF(v_DLCnt > 0) THEN
                  OPEN o_cursor FOR                 
                   select a
                              b,
                          'DL' as search_result_baseon 
                  from t1, t2, t3, t4
                  where t1.DL = i_DL;

                ELSE
                    OPEN o_cursor FOR                 
                        select 
                          a,
                          b,                          
                          'LN' as search_result_baseon 
                  from t1, t2, t3, t4
                  where t2.last_name IN ( 
                              select last_name from t2 where soundex(last_name) = soundex(i_lastname)
                           )
                 END IF;               
             END IF;                                                
       END IF;  

  END RetrieveCustomer;

有什么方法可以将匹配的姓氏记录存储在某个内存表中，并在该过程中需要时使用它？你们中的任何人都可以建议我编写程序的更好方法：

标签： oraclefuzzy-searchwith-clause