sql - PL/SQL : IF-ELSIF-ELSE -> ELSE block is not considered as a basic block

Question

这是ORACLE文档提供的一个PL/SQL基本 block 的定义。

A basic block refers to a single entry single exit block of PL/SQL code

这是网上对基本 block 的定义

Basic block is a set of statements that always executes in a sequence one after the other. The characteristics of basic blocks are- They do not contain any kind of jump statements in them. There is no possibility of branching or getting halted in the middle. All the statements execute in the same order they appear.

我正在尝试使用 DBMS_PLSQL_CODE_COVERAGE 查找 PL/SQL 代码的代码覆盖率包，这个包允许找到 PL/SQL block 级覆盖。这里我对IF-ELSIF-ELSE的代码覆盖率有些疑惑。最主要的是 IF-ELSIF-ELSE 的 ELSE block 不被视为基本 block 。请引用以下代码。

这是我的第一个例子。在第二列中，所有值(0 和 1)代表一个基本 block 。在这里您可以清楚地看到 IF 和 ELSE 条件都算作基本 block ，这对我来说非常公平。

现在，这就是困扰我的地方。请引用以下代码。

在这里你可以看到所有的 IF 和 ELSIF block 都算作基本 block ，这没问题。但是 ELSE block 不被认为是基本 block ，也就是说，根据基本 block 的定义，如果执行到最后一个 ELSIF block ，则无论 ELSE 的哪个部分也被执行。这会影响代码覆盖率。

但在实际执行中，并没有发生这种情况。但是 DBMS_PLSQL_CODE_COVERAGE 包给了我这个输出。有人可以解释为什么会这样吗？我不知道这个包的内部实现。我所知道的是它正在使用 DBMS_PROFILER 来获取代码覆盖率。

如果您对此感到好奇，这里是哪个查询为我提供了上述表格的查询。

SELECT LISTAGG(ccb.col, ',') WITHIN GROUP (ORDER BY ccb.col) AS col,
       LISTAGG(ccb.covered, ',') WITHIN GROUP (ORDER BY ccb.col) AS covered,
       s.line,
       LISTAGG(ccb.not_feasible, ',') WITHIN GROUP (ORDER BY ccb.col) AS not_feasible,
       s.text
FROM   user_source s
       JOIN dbmspcc_units ccu ON s.name = ccu.name AND s.type = ccu.type
       LEFT OUTER JOIN dbmspcc_blocks ccb ON ccu.run_id = ccb.run_id AND ccu.object_id = ccb.object_id AND s.line = ccb.line
WHERE  s.name = 'DEMO_UTILITY_TST'
AND    s.type = 'PACKAGE BODY'
AND    ccu.run_id = 248
GROUP BY s.line, s.text
ORDER BY 3;

DBMS_PLSQL_CODE_COVERAGE package documentation

Answer 1

检查您的 plsql_optimize_level .当这是 2 或更高时，编译器可以显着重新排列您的代码。作为docs say :

0 Maintains the evaluation order and hence the pattern of sideeffects, exceptions, and package initializations of Oracle9i andearlier releases. Also removes the new semantic identity ofBINARY_INTEGER and PLS_INTEGER and restores the earlier rules for theevaluation of integer expressions. Although code will run somewhatfaster than it did in Oracle9i, use of level 0 will forfeit most ofthe performance gains of PL/SQL in Oracle Database 10g.

1 Applies a wide range of optimizations to PL/SQL programsincluding the elimination of unnecessary computations and exceptions,but generally does not move source code out of its original sourceorder.

2 Applies a wide range of modern optimization techniques beyondthose of level 1 including changes which may move source coderelatively far from its original location.

3 Applies a wide range of optimization techniques beyond those oflevel 2, automatically including techniques not specificallyrequested.

在您的示例中，第一个 if和 else分支机构 return -1 .因此，编译器将它们合并到 2 级或更高级别的一个 block 中。

举个极端的例子。 if和 else下面函数中的分支 return 0 .函数中没有其他代码。

在 plsql_optimize_level = 1 , block 不变。因此，代码覆盖率报告均已覆盖(假设进行了适当的测试):

alter session set plsql_optimize_level = 1;
create or replace function f ( p int ) 
  return int as
  retval int;
begin
  if p < 10 then
    return 0;
  else
    return 0;
  end if;
end f;
/

declare
  run_id pls_integer;
begin
  dbms_plsql_code_coverage.create_coverage_tables ( true );
  run_id := dbms_plsql_code_coverage.start_coverage('TEST');
  dbms_output.put_line ( f ( 9 ) );
  dbms_output.put_line ( f ( 99 ) );
  dbms_plsql_code_coverage.stop_coverage;
end;
/

select max(ccb.covered) as covered,
       s.line,
       max(ccb.covered) as not_feasible,
       rtrim ( s.text, chr(10) ) text
from   user_source s
join   dbmspcc_units ccu 
on     s.name = ccu.name and s.type = ccu.type
left outer join dbmspcc_blocks ccb
on     ccu.run_id = ccb.run_id and ccu.object_id = ccb.object_id and s.line = ccb.line
group by s.line, s.text
order by s.line;

   COVERED       LINE NOT_FEASIBLE TEXT                                              
---------- ---------- ------------ --------------------------------------------------
         1          1            1 function f ( p int )                              
<null>              2 <null>         return int as                                   
<null>              3 <null>         retval int;                                     
<null>              4 <null>       begin                                             
<null>              5 <null>         if p < 10 then                                  
         1          6            1     return 0;                                     
<null>              7 <null>         else                                            
         1          8            1     return 0;                                     
<null>              9 <null>         end if;                                         
<null>             10 <null>       end f; 

但是将覆盖率增加到 2，编译器会将它们合并在一起。代码覆盖率报告均未被覆盖!

alter session set plsql_optimize_level = 2;
alter function f compile;

declare
  run_id pls_integer;
begin
  dbms_plsql_code_coverage.create_coverage_tables ( true );
  run_id := dbms_plsql_code_coverage.start_coverage('TEST');
  dbms_output.put_line ( f ( 9 ) );
  dbms_output.put_line ( f ( 99 ) );
  dbms_plsql_code_coverage.stop_coverage;
end;
/

select max(ccb.covered) as covered,
       s.line,
       max(ccb.covered) as not_feasible,
       rtrim ( s.text, chr(10) ) text
from   user_source s
join   dbmspcc_units ccu 
on     s.name = ccu.name and s.type = ccu.type
left outer join dbmspcc_blocks ccb
on     ccu.run_id = ccb.run_id and ccu.object_id = ccb.object_id and s.line = ccb.line
group by s.line, s.text
order by s.line;

   COVERED       LINE NOT_FEASIBLE TEXT                                              
---------- ---------- ------------ --------------------------------------------------
         1          1            1 function f ( p int )                              
<null>              2 <null>         return int as                                   
<null>              3 <null>         retval int;                                     
<null>              4 <null>       begin                                             
<null>              5 <null>         if p < 10 then                                  
<null>              6 <null>           return 0;                                     
<null>              7 <null>         else                                            
<null>              8 <null>           return 0;                                     
<null>              9 <null>         end if;                                         
<null>             10 <null>       end f;    

TL;DR 集 plsql_optimize_level = 1在运行代码覆盖率测试之前。