PLSQL com Consultas SQL em Tempo de Execução - Oracle Database 12c R1

Olá pessoal,

Agora com Oracle Database 12c Release 1 (12.1.0.1) podemos declarar e definir funções e procedimentos PL/SQL na cláusula WITH (plsql_declarations) de uma consulta SQL. Você pode utilizar estes objetos PL/SQL que foram declarados em suas consultas e subconsultas.

Contanto, abaixo duas observações:

• Se a instrução de nível superior for uma instrução SELECT, ela deve ter uma cláusula WITH ou o hint: WITH_PLSQL.
• Se a instrução de nível superior for uma instrução DELETE, MERGE, INSERT ou UPDATE, deve ter o hint WITH_PLSQL.

O hint: WITH_PLSQL apenas permite que você especifique a cláusula WITH dentro da declaração. NÃO é uma sugestão ao otimizador!

Vejamos três exemplos.

Primeiro conectamos em nosso banco de dados Oracle:

[oracle@serv01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Feb 26 19:17:15 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Ok, agora nós criamos uma função que retorna o mês por extenso, baseado no numero do mês informado como parâmetro.

Repare que é um a declaração de uma function padrão que criamos, porém sem o create function e repare também que a declaração da function está dentro da cláusula WITH.

Outro ponto de atenção é que logo abaixo a declaração da função, já estamos utilizando em tempo de execução a function em nosso select: [select get_mesExtenso(11) from dual], veja:

SQL> with
2      function get_mesExtenso(p_mes number) return varchar2 is
3      begin
4           return (case
5                    when p_mes = 1 then 'Janeiro'
6                    when p_mes = 2 then 'Fevereiro'
7                    when p_mes = 3 then 'Março'
8                    when p_mes = 4 then 'Abril'
9                    when p_mes = 5 then 'Maio'
10                    when p_mes = 6 then 'Junho'
11                    when p_mes = 7 then 'Julho'
12                    when p_mes = 8 then 'Agosto'
13                    when p_mes = 9 then 'Setembro'
14                    when p_mes = 10 then 'Outubro'
15                    when p_mes = 11 then 'Novembro'
16                    when p_mes = 12 then 'Dezembro'end);
17      end;

18  select get_mesExtenso(11)from dual;
19  /

GET_MESEXTENSO(11)
--------------------------------------------------------------------------------
Novembro

SQL>

Certo, vamos ao segundo exemplo.

Baseado nas observações que passamos acima, nossa instrução de nível superior é um select, sendo assim utilizamos o hint: WITH_PLSQL para permitir que utilizemos a declaração de função dentro do subselect, veja:

SQL> select /*+ WITH_PLSQL */ *
2    from
3  (
4  with
5      function get_mesExtenso(p_mes number) return varchar2 is
6      begin
7           return (case
8                    when p_mes = 1 then 'Janeiro'
9                    when p_mes = 2 then 'Fevereiro'
10                    when p_mes = 3 then 'Março'
11                    when p_mes = 4 then 'Abril'
12                    when p_mes = 5 then 'Maio'
13                    when p_mes = 6 then 'Junho'
14                    when p_mes = 7 then 'Julho'
15                    when p_mes = 8 then 'Agosto'
16                    when p_mes = 9 then 'Setembro'
17                    when p_mes = 10 then 'Outubro'
18                    when p_mes = 11 then 'Novembro'
19                    when p_mes = 12 then 'Dezembro'end);
20      end;
21  select get_mesExtenso(4)from dual
22  );
23  /

GET_MESEXTENSO(4)
--------------------------------------------------------------------------------
Abril

SQL>

E para finalizar nosso terceiro exemplo.

Aqui nós criamos uma procedure (prc_calcula) em memória, que nos devolverá(out) o total que é calculado ( unitário * quantidade).

Logo abaixo criamos a function(func_retorna_calculo) que chama a procedure acima criada e devolve pela function o valor calculado pela procedure.

E por fim nosso select na última linha que faz a chamada da função(func_retorna_calculo), passando como parâmetro o valor unitário e a quantidade, veja:

SQL> WITH
2      PROCEDURE prc_calcula(p_unit IN NUMBER, p_qtde in number, p_total out number) IS
3      BEGIN
4          p_total := p_unit * p_qtde;
5      END;
6
7      FUNCTION func_retorna_calculo(p_unit IN NUMBER, p_qtde in number) RETURN NUMBER IS
8        vRetorno number;
9      BEGIN
10          prc_calcula(p_unit, p_qtde, vRetorno);
11          RETURN vRetorno;
12      END;
13      SELECT func_retorna_calculo(350.25 , 2) as total from dual;
14  /

TOTAL
----------
700.5

SQL>

Abraço,

Ronaldo.