文章/答案/技术大牛

发布

社区首页 >问答首页 >当数据发现块时，pl/sql多个if或多个开始

问当数据发现块时，pl/sql多个if或多个开始
EN

Stack Overflow用户

提问于 2022-02-04 22:28:10

回答 3查看 100关注 0票数 0

我刚开始编写存储过程。我想看看执行如下功能的最佳方法：

cursor c1 is select * from  cases where caseid = '2332534534';
begin
       for t_case in c1
       loop
       /..///
       
if t_case.oCode is not null then
    select id into v_ao from lkp_ao where upper(descr) = upper(nvl(substr(t_case.oCode, 1, instr(t_case.oCode,',')-1), t_case.oCode));
    if v_ao = 0 then
        select id into v_ao from lkp_ao where substr(upper(descr),1,5)  like  substr(upper(nvl(substr(t_case.oCode, 1, instr(t_case.oCode,',')-1), t_case.oCode)),1,5)||'%' and rownum=1;
        if v_ao = 0 then
            select id into v_ao from lkp_ao where substr(upper(descr),1,5)  like  substr(upper(nvl(substr(t_case.oCode, 1, instr(t_case.oCode,',')-1), t_case.oCode)),1,4)||'%' and rownum=1;
            if v_ao = 0 then
                select id into v_ao from lkp_ao where substr(upper(descr),1,5)  like  substr(upper(nvl(substr(t_case.oCode, 1, instr(t_case.oCode,',')-1), t_case.oCode)),1,3)||'%' and rownum=1;
                if v_ao = 0 then
                    v_ao := '';
                end if;
            end if;
        end if;
    end if;  
else
    v_ao := '';          
end if;

/..///
 end loop;       
    commit;
exception
when others then 
    log_error(0,  'INSERT_case - exception outside' || SQLERRM || ' code ' || SQLCODE, 1);
    commit;
end;

这部分代码将无法工作。因此，我将不得不检查，而不是选择id进入v_ao。

select count(*) into v_ao from lkp_ao where upper(descr) = upper(nvl(substr(t_case.oCode, 1, instr(t_case.oCode,',')-1), t_case.oCode));
if v_ao = 0 then

//do something
else 

select id into v_ao from lkp_ao where upper(descr) = upper(nvl(substr(t_case.oCode, 1, instr(t_case.oCode,',')-1), t_case.oCode));
end if;

因此，我正在执行1次计数查询和1次查询，以获得其他部分中的实际id。另一种方法是使用begin，当没有找到数据时，执行第二个查询，在内部没有找到数据，打开另一个begin，依此类推。因此，基本上会有5开始和例外，当没有数据发现块，我觉得是巨大的代码。在oracle存储过程中，做这种情况最简单的方法是什么？提前谢谢。

sql

oracle

stored-procedures

plsql

回答 3

Stack Overflow用户

发布于 2022-02-04 23:02:55

获取单个语句中的所有匹配行(因此不必多次查询表)，然后使用CASE语句对行排序，并且只得到最佳匹配：

DECLARE
  v_substr  CASES.OCODE%TYPE;
  cursor c1 is select * from  cases where caseid = '2332534534';
BEGIN
  for t_case in c1
  loop
    /* ... */
 
if t_case.oCode is not null then
  IF instr(t_case.oCode,',') = 0 THEN
    v_substr := UPPER(t_case.oCode);
  ELSE
    v_substr := UPPER(SUBSTR(t_case.oCode, 1, INSTR(t_case.oCode,',')-1));
  END IF;

  v_ao := ''; -- same as NULL

  BEGIN
    SELECT id
    INTO   v_ao
    from   lkp_ao
    WHERE  substr(upper(descr),1,3) = SUBSTR(v_substr, 1, 3)
    ORDER BY
           CASE
           WHEN upper(descr) = v_substr
           THEN 1
           WHEN substr(upper(descr),1,5) = SUBSTR(v_substr, 1, 5)
           THEN 2
           WHEN substr(upper(descr),1,4) = SUBSTR(v_substr, 1, 4)
           THEN 3
           WHEN substr(upper(descr),1,3) = SUBSTR(v_substr, 1, 3)
           THEN 4
           END
    FETCH FIRST ROW ONLY;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END IF;      

  /* .. */
  end loop;       
  commit;
exception
  when others then 
    log_error(0,  'INSERT_case - exception outside' || SQLERRM || ' code ' || SQLCODE, 1);
    commit; -- Really commit after an exception?
END;
/

如果您想提高效率，那么就执行游标查询中的所有匹配(然后不必在PL/SQL和SQL之间的每次游标迭代中切换上下文)：

DECLARE
  v_substr  CASES.OCODE%TYPE;
  CURSOR c1 IS
    SELECT c.*,
           l.id AS ao
    FROM   (
             SELECT c.*,
                    CASE INSTR(oCode, ',')
                    WHEN 0 THEN oCode
                    ELSE SUBSTR(oCode, 1, INSTR(oCode, ',') - 1)
                    END AS term
             FROM   cases c
           ) c
           LEFT OUTER JOIN LATERAL (
             SELECT id
             from   lkp_ao a
             WHERE  substr(upper(a.descr),1,3) = SUBSTR(c.term, 1, 3)
             ORDER BY
                    CASE
                    WHEN upper(a.descr) = c.term
                    THEN 1
                    WHEN substr(upper(a.descr),1,5) = SUBSTR(c.term, 1, 5)
                    THEN 2
                    WHEN substr(upper(a.descr),1,4) = SUBSTR(c.term, 1, 4)
                    THEN 3
                    WHEN substr(upper(a.descr),1,3) = SUBSTR(c.term, 1, 3)
                    THEN 4
                    END
             FETCH FIRST ROW ONLY
           ) l
           ON (1 = 1)
    WHERE  caseid = '2332534534';
BEGIN
  for t_case in c1
  loop
    /* ... */
 
    v_ao := t_case.ao;

  /* .. */
  end loop;       
  commit;
exception
  when others then 
    log_error(0,  'INSERT_case - exception outside' || SQLERRM || ' code ' || SQLCODE, 1);
    commit; -- Really commit after an exception?
END;
/

票数 2

Stack Overflow用户

发布于 2022-02-04 22:52:23

井,

如果您声明附加变量并运行所有的语句(每个语句都返回自己的v_ao#值)和

应用max函数，这样查询就不会以NO_DATA_FOUND结束；它将返回NULL

SQL>从对偶其中选择1，其中1= 2；没有行选择SQL>，从对偶其中选择最大值(1)；最大(1)- SQL>

这样，您就不必将每个select封装在自己的begin-exception-end块中。

使用嵌套CASE表达式返回最终结果

就像这样：

declare
  v_ao1 number;
  v_ao2 number;
  v_ao3 number;
  v_ao4 number;
  v_ao  number;
begin
  for t_case in c1 loop
    select max(id) into v_ao1 from lkp_ao where upper(descr) = upper(nvl(substr(t_case.oCode, 1, instr(t_case.oCode,',')-1), t_case.oCode));
    select max(id) into v_ao2 from lkp_ao where substr(upper(descr),1,5)  like  substr(upper(nvl(substr(t_case.oCode, 1, instr(t_case.oCode,',')-1), t_case.oCode)),1,5)||'%' and rownum=1;
    select max(id) into v_ao3 from lkp_ao where substr(upper(descr),1,5)  like  substr(upper(nvl(substr(t_case.oCode, 1, instr(t_case.oCode,',')-1), t_case.oCode)),1,4)||'%' and rownum=1;
    select max(id) into v_ao4 from lkp_ao where substr(upper(descr),1,5)  like  substr(upper(nvl(substr(t_case.oCode, 1, instr(t_case.oCode,',')-1), t_case.oCode)),1,3)||'%' and rownum=1;
    
    v_ao := case when nvl(v_ao1, 0) = 0 then 
                 case when nvl(v_ao2, 0) = 0 then
                      case when nvl(v_ao3, 0) = 0 then
                           case when nvl(v_ao4, 0) = 0 then null
                                else v_ao4
                           end
                           else v_ao3
                      end
                      else v_ao2
                 end
                 else v_ao1
             end;
  end loop;
end;

票数 1

Stack Overflow用户

发布于 2022-02-04 23:48:21

有时，在这种情况下，最好得到最长的匹配长度。其中一个变体是使用UTL_RAW.BIT_XOR：

with 
 t(s) as (
    select 'AAAABB' from dual union all
    select 'AAAABC' from dual union all
    select 'AAABBC' from dual union all
    select 'BBBXXXX' from dual union all
    select 'BBBYYY' from dual
)
,search_strings(str) as (
    select 'AAAAB' from dual union all
    select 'BBBZZZ' from dual 
)
select
    t.s,s.str,
    utl_raw.bit_xor(utl_raw.cast_to_raw(t.s),utl_raw.cast_to_raw(s.str)) s_xor,
    length(
        regexp_substr(
          utl_raw.bit_xor(utl_raw.cast_to_raw(t.s),utl_raw.cast_to_raw(s.str))
          ,'^(00)+'
          )
        )/2 as n_matches
from t, search_strings s
;

结果：

S       STR    S_XOR                           N_MATCHES
------- ------ ------------------------------ ----------
AAAABB  AAAAB  000000000042                            5
AAAABC  AAAAB  000000000043                            5
AAABBC  AAAAB  000000030043                            3
BBBXXXX AAAAB  030303191A5858
BBBYYY  AAAAB  030303181B59
AAAABB  BBBZZZ 0303031B1818
AAAABC  BBBZZZ 0303031B1819
AAABBC  BBBZZZ 030303181819
BBBXXXX BBBZZZ 00000002020258                          3
BBBYYY  BBBZZZ 000000030303                            3

10 rows selected.

如您所见，BIT_XOR对相同的字符返回'00‘，这样就可以计数其中的许多00。列n_matches返回匹配的符号数。

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/70993536

复制

相似问题

问当数据发现块时，pl/sql多个if或多个开始
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问当数据发现块时，pl/sql多个if或多个开始EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问当数据发现块时，pl/sql多个if或多个开始
EN