'pl/sql multiple if or multiple begin when data found block

I am new to writing stored procedures. I want to see which is the best way to execute a functionality like below:

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;

This part of code would not work. so instead of select id into v_ao , i will have to check

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;

So i am executing 1 query for count and 1 query to get the actual id in the else part. The other way of doing is using begin when no data found execute the 2nd query and inside that no data found open another begin and so on. So basically there will be 5 begin and exception when no data found block which i feel is huge code. Whats the simplest way to do this kind of condition in oracle stored procedure? thanks in advance.

Solution 1:^[1]

Get all the matching rows in a single statement (so you do not have to query the table multiple times) and then use a CASE statement to order the rows and only get the best match:

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;
/

If you want to make it even more efficient then do all the matching in the cursor query (then you do not have to context switch in every iteration of the cursor between PL/SQL and 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;
/

Solution 2:^[2]

Well,

if you declare additional variables and run all select statements (each of them returning its own v_ao# value) and
apply the max function so that query wouldn't end up with NO_DATA_FOUND; it'll return NULL instead
```
SQL> select 1 from dual where 1 = 2;

no rows selected

SQL> select max(1) From dual where 1 = 2;

    MAX(1)
----------


SQL>
```
then you wouldn't have to enclose each select into its own begin-exception-end block
use nested CASE expressions to return the final result

Something like this:

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;

Solution 3:^[3]

Sometimes, in such cases, it's better to get length of longest match. One of the variants for this is to use 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
;

Results:

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.

As you can see, BIT_XOR returns '00' for equal chars so you can count a number of 00 in it. Column n_matches returns this number of matched symbols

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	Littlefoot
Solution 3	Sayan Malakshinov

'pl/sql multiple if or multiple begin when data found block

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]