[20200213]函数nullif使用.txt

[20200213]函数nullif使用.txt --//nullif函数自己知道很少使用，我发现人家写的脚本使用它避免做除法时如果除数为0报ORA-01476的问题。 --//通过例子说明，比如我写的脚本： $ cat d_buffer.sql col executions1 new_value x1 col buffer_gets1 new_value x2 col ELAPSED_TIME1 new_value x3 col ROWS_PROCESSED1 new_value x4 col executions2 new_value y1 col buffer_gets2 new_value y2 col ELAPSED_TIME2 new_value y3 col ROWS_PROCESSED2 new_value y4 col var3 new_value var3 select executions executions1,buffer_gets buffer_gets1,elapsed_time elapsed_time1,rows_processed rows_processed1  from v$sqlarea where sql_id='&&1'; prompt ... sleep &&2 , waiting .... host sleep &&2 select executions executions2,buffer_gets buffer_gets2,elapsed_time elapsed_time2 ,rows_processed rows_processed2 from v$sqlarea where sql_id='&&1'; select &y2-&x2 "总buffer_gets", (&y2-&x2)/(&y1-&x1) "每次buffer_gets",&y1- &x1 "执行次数" , &y3-&x3 "总执行时间", (&y3-&x3)/(&y1-&x1) "每次执行时间" , &y4-&x4 "总处理记录数",(&y4-&x4)/(&y1-&x1) "平均处理记录数" from dual ; --//主要是优化sql语句后，我发现有时候清除sql语句从共享池后，如果再次执行后executions,buffer_gets等信息并没有彻底清除。 --//这样我很难比较优化后效果，因此写了上面脚本。脚本有1个问题如果检测时间内没有执行，这样executions没有变化，作为除数就会报错。 SYS@book> @ d_buffer 4xamnunv51w9j 2 EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1 ----------- ------------ ------------- ---------------           1           62          5988               1 ... sleep 2 , waiting .... EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2 ----------- ------------ ------------- ---------------           1           62          5988               1 select         62-        62 "总buffer_gets", (        62-        62)/(         1-         1) "每次buffer_gets",         1-          1 "执行次数" ,       5988-      5988 "总执行时间", (      5988-      5988)/(         1-         1) "每次执行时间" ,          1-         1 "总处理记录数",(         1-         1)/(         1-         1) "平均处理记录数" from dual                                                                      * ERROR at line 1: ORA-01476: divisor is equal to zero --//利用nullif函数就能很好的避免这个问题。 SCOTT@book> select nullif(1,0),nullif(0,0),nullif(1,1) from dual ; NULLIF(1,0) NULLIF(0,0) NULLIF(1,1) ----------- ----------- -----------           1 --//nullif就是比较参数1，参数2，如果相等就输出NULL。 --//只要第1个参数等于第2个参数，输出就是NULL类型。改写如下就不会报错了。 $ cat d_buffer.sql col executions1 new_value x1 col buffer_gets1 new_value x2 col ELAPSED_TIME1 new_value x3 col ROWS_PROCESSED1 new_value x4 col executions2 new_value y1 col buffer_gets2 new_value y2 col ELAPSED_TIME2 new_value y3 col ROWS_PROCESSED2 new_value y4 col var3 new_value var3 select executions executions1,buffer_gets buffer_gets1,elapsed_time elapsed_time1,rows_processed rows_processed1  from v$sqlarea where sql_id='&&1'; prompt ... sleep &&2 , waiting .... host sleep &&2 select executions executions2,buffer_gets buffer_gets2,elapsed_time elapsed_time2 ,rows_processed rows_processed2 from v$sqlarea where sql_id='&&1'; SELECT &y2 - &x2 "总buffer_gets"       , (&y2 - &x2) / NULLIF (&y1 - &x1, 0) "每次buffer_gets"       ,&y1 - &x1 "执行次数"       ,&y3 - &x3 "总执行时间"       , (&y3 - &x3) / NULLIF (&y1 - &x1, 0) "每次执行时间"       ,&y4 - &x4 "总处理记录数"       , (&y4 - &x4) / NULLIF (&y1 - &x1, 0) "平均处理记录数"   FROM DUAL; SYS@book> @ d_buffer 4xamnunv51w9j 1 EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1 ----------- ------------ ------------- ---------------           2           50          3834               2 ... sleep 1 , waiting .... EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2 ----------- ------------ ------------- ---------------           2           50          3834               2 总buffer_gets 每次buffer_gets   执行次数 总执行时间 每次执行时间 总处理记录数 平均处理记录数 ------------- --------------- ---------- ---------- ------------ ------------ --------------             0                          0          0                         0