Como otimizar o uso de PL/SQL function/procedure no Oracle – PL/SQL From SQL

PL/SQL From SQL

Antes do 12c, era necessário criar uma PL/SQL function/procedure (schema object) para utilizar em conjunto com um comando SQL e para isso era necessário ter privilégios de CREATE PROCEDURE.

Segue um exemplo retirado de uma apresentação do Tom Kyte:

SQL> create table t (x varchar2(5));

Table created.

SQL> insert into t values ( 'a' );
insert into t values ( '1' );
insert into t values ( null );

commit;

Como era antes do 12c:

SQL>create or replace
    function is_number_old(x in varchar2)
    return varchar2
    is
        Plsql_Num_Error exception;
        pragma exception_init(Plsql_Num_Error, -06502);
    begin
        if (To_Number(x) is NOT null) then
          return 'Y';

       else
         return '';

       end if;

   exception
       when Plsql_Num_Error then
         return 'N';

   end;
/

Function created.

SQL> select rownum, x,          
            is_number_old(x) is_num 
       from t;

ROWNUM   X     IS_NUM
---------- ----- --------------------
1          a N
2          1 Y
3

Trace após adicionar 110.000 registros na tabela, para comparar os tempos

Como fica no 12c:

with
      function Is_Number
      (x in varchar2) return varchar2 is
        Plsql_Num_Error exception;
        pragma exception_init(Plsql_Num_Error, -06502);

      begin
        if (To_Number(x) is NOT null) then
          return 'Y';

        else
         return '';

        end if;

     exception
       when Plsql_Num_Error then
         return 'N';

     end Is_Number;

select rownum, x, is_number(x) is_num from t;

ROWNUM     X     IS_NUM
---------- ----- --------------------
1   a     N
2   1     Y
3

Podemos verificar uma diminuição significativa no tempo e cpu.

Abraço