Artigos Banco de Dados PL SQL Functions e Procedures

Uma function é um bloco PL/SQL muito semelhante a uma procedure. O que podemos entender de início entre esses dois tipos de blocos é que os blocos functions retornam valores e as procedures podem ou não retornar um valor. As functions tem duas características que diferem das procedures, as quais não podemos deixar de tratar:

  • As functions sempre retornam valores
  • Functions são usadas como parte de uma expressão.

Para que possamos criar uma função no PL/SQL precisamos entender o seu funcionamento e para isso precisamos entender a sua sintaxe básica. Vejamos a seguir na Listagem 1.

Listagem 1. Estrutura básica de uma function.

CREATE [OR REPLACE] FUNCTION function_name
  [(parameter_name [IN | OUT | IN OUT] type [, ...])]
  RETURN return_datatype
  {IS | AS}
  BEGIN
     < function_body >
  END [function_name];

Vejamos então o que cada um desses termos representa:

  • CREATE [OR REPLACE] FUNCTION: Caso uma function já exista com o mesmo nome, ela será reescrita devido ao termo ‘replace’. Caso contrário, ela será criada de acordo com o termo ‘create’.
  • Function_name: Será o nome atribuído para essa função.
  • Parameters: a lista opcional de parâmetros contém os nomes, os modos e os tipos que esses parâmetros terão. O IN representa o valor que será passado de fora, já o OUT representa que este parâmetro será utilizado para retornar um valor de fora do procedimento.
  • Return_datatype: é o tipo de retorno que será utilizado, sendo este SQL ou PL/SQL. Podemos neste caso utilizar referências como o %TYPE ou %ROWTYPE se necessário, ou mesmo utilizar qualquer tipo de dados escalar ou composto.
  • IS/AS: por convecção, temos o ‘is’ para a criação de funções armazenadas e o ‘as’ quando criamos pacotes (packages).
  • function_body: contém o bloco PL/SQL que inicia com a claúsula BEGIN e finaliza com END [function_name], e executa neste momento todas as instruções necessárias.

Para facilitar o entendimento, vamos ver um exemplo de utilização que será recuperar o primeiro nome do funcionário de id igual a 90. Veja a Listagem 2.

Listagem 2. Exemplo de criação da função chamada primeiro_nome_func.

CREATE OR REPLACE FUNCTION primeiro_nome_func
  RETURN VARCHAR(20);
 IS
  emp_name VARCHAR(20);
BEGIN
SELECT primeiro_nome INTO emp_name
 FROM funcionarios_tbl WHERE ID = 90;
 RETURN emp_name;
END;
/

Neste exemplo estamos apenas recuperando o primeiro nome do funcionário com id 90 para a variável emp_name. O tipo de retorno dessa informação é do tipo Varchar2 e o seu retorno também será do mesmo tipo.

Este é um exemplo fictício de tabela de uma base de dados, mas que demonstra fielmente o que realmente é necessário entender sobre a criação de uma function.

Como executar a nossa function?

Podemos executar a função de várias formas. No caso da função retornar um valor, podemos declará-lo como uma variável, como da seguinte forma:

func_nome :=  primeiro_nome_func;

Ou então como parte de uma instrução select:

SELECT primeiro_nome_func FROM dual;

Ou também como uma instrução PL/SQL:

dbms_output.put_line(primeiro_nome_func);

Neste último caso, apresentamos apenas a informação que é retornada pela função. Vamos a um novo exemplo que será responsável por definir, declarar e chamar uma simples função PL/SQL que irá computar e retornar o máximo entre dois números. Vejamos no código da Listagem 3.

Listagem 3. Exemplo de valor máximo entre dois números inteiros.

SET serveroutput ON;

DECLARE
  a NUMBER;
  b NUMBER;
  c NUMBER;

  FUNCTION max_entre_numeros(
      x IN NUMBER,
      y IN NUMBER)
    RETURN NUMBER
  IS
    z NUMBER;
  BEGIN
    IF x > y THEN
      z := x;
    ELSE
      Z:= y;
    END IF;
    RETURN z;
  END;
  BEGIN
    a := 23;
    b := 45;
    c := max_entre_numeros (a, b);
    dbms_output.put_line('Valor máximo obtido entre os valores ' || a ||
     ' e ' || b || ' foi ' || c);
END;
  /

Quando executamos esta função o valor retornado é 45, como é mostrado pelo prompt SQL:

bloco anônimo concluído
Valor máximo obtido entre os valores: 23 e 45 foi 45

Nota: A tabela dual é uma tabela básica da Oracle por onde os desenvolvedores podem realizar consultas sem a necessidade de extração de dados.

Funções recursivas PL/SQL

Muitas vezes precisamos utilizar um bloco de instruções dentro de outro bloco de instruções. Quando isso acontece, nos referimos a esta função como sendo uma função recursiva. Vamos realizar este procedimento com um exemplo que vai calcular o fatorial de um número qualquer, de acordo com a Listagem 4.

Listagem 4. Representação de uma função recursiva para calcular o fatorial de um número.

DECLARE
  num NUMBER;
  valor NUMBER;
  fatorial NUMBER;
  FUNCTION fact(
      x NUMBER)
    RETURN NUMBER
  IS
    f NUMBER;
  BEGIN
    IF x =0 THEN
      f := 1;
    ELSE
      f := x * fact(x-1);
    END IF;
    RETURN f;
  END;
  BEGIN
    num       := &valor;
    factorial := fact(num);
    dbms_output.put_line(' O valor fatorial de '|| num
     || ' é ' || fatorial);
  END;
  /

Quando executamos esta instrução num prompt SQL, primeiramente seremos questionados quanto ao valor do ‘num’ que será passado pelo usuário e após isso será retornado o resultado do fatorial de acordo com o valor que passarmos. Como, por exemplo, podemos passar o valor 5 e termos como resultado o seguinte:

bloco anônimo concluído
O valor fatorial de 5 é 120

Stored procedures PL/SQL

Uma stored procedure é um bloco de instruções PL/SQL que executa uma ou mais tarefas específicas. Elas são bem similares com as procedures de outras linguagens de programação.

Uma procedure normalmente possui um cabeçalho e um corpo. O cabeçalho consiste do nome e de parâmetros ou variáveis que serão passadas para a procedure. Já o corpo consiste da declaração de uma seção, execução de uma seção e uma seção de exceções muito similar a um bloco geral da PL/SQL. Uma procedure pode ou não ter um valor de retorno. Normalmente as procedures são criadas dentro de pacotes ou em blocos PL/SQL.

Podemos passar os parâmetros para uma procedure de três maneiras:

  1. Parâmetros IN – passamos o valor na própria procedure.
  2. Parâmetros OUT – recebemos o valor a partir da chamada de blocos externos.
  3. Parâmetros IN OUT – passamos um valor inicial para a procedure e recebemos de volta uma atualização.

Criando stored procedures

Vejamos a sintaxe geral da criação de uma procedure para uma melhor compreensão, como segue na Listagem 5.

Listagem 5. Estrutura básica de criação de uma procedure.

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
IS
   Declaration section
BEGIN
   Execution section
EXCEPTION
  Exception section
END;

O ‘Is’ marca o início do corpo de uma procedure e é bem similar ao DECLARE de um bloco anônimo PL/SQL. O código criado entre o IS e o BEGIN forma a seção de declaração da procedure.

A sintaxe entre os colchetes [] indica que é opcional. Mas de igual forma a uma função, a utilização do CREATE é para criar uma procedure e o REPLACE irá sobrepor uma procedure existente com novas informações de código.

Para melhorarmos o aprendizado para utilização deste recurso, vamos a um exemplo prático. O exemplo da Listagem 6 será criar uma procedure chamada de ‘detalhes_dos_funcionarios’ que irá nos retornar informações relativas aos funcionários.

Listagem 6. Criação da procedure detalhes_dos_funcionarios. 

CREATE OR REPLACE
PROCEDURE detalhes_dos_funcionarios
IS
  CURSOR emp_cur
  IS
    SELECT first_name, last_name, salary FROM emp_tbl;
  emp_rec emp_cur%rowtype;
BEGIN
  FOR emp_rec IN sales_cur
  LOOP
    dbms_output.put_line('Nome do funcionário: ' || emp_cur.first_name ||
     '. Sobrenome do funcionário: ' ||emp_cur.last_name ||
     '. Salário do funcionário:
     ' ||emp_cur.salary);
  END LOOP;
END;
/

As informações utilizadas para este exemplo são meramente ilustrativas com a utilização de uma base de dados de exemplo da Oracle.

Vejamos agora um novo exemplo que será utilizado para retornar o valor mínimo entre dois números inteiros. Observe a Listagem 7.

Listagem 7. Criação de uma procedure para retornar o número mínimo utilizando o IN OUT.

DECLARE
   a number;
   b number;
   c number;

PROCEDURE valorMinimo(x IN number, y IN number, z OUT number) IS
BEGIN
   IF x < y THEN
      z:= x;
   ELSE
      z:= y;
   END IF;
END;

BEGIN
   a:= 23;
   b:= 45;
   valorMinimo(a, b, c);
 dbms_output.put_line('O valor mínimo obtido entre os valores ' || a
 || ' e ' || b || ' foi ' || c);
END;
/

Como executamos uma strored procedure? Para este ponto, a resposta é bem simples, pois temos duas maneiras de chamar uma stored procedure:

  1. Usando a palavra-chave EXECUTE;
  2. Chamando o nome da procedure de um bloco PL/SQL.

No primeiro caso, executamos da seguinte forma:

EXECUTE detalhes_dos_funcionarios;

E no nosso outro caso, que é a partir de um bloco, temos:

BEGIN
detalhes_dos_funcionarios
END;
/

Deletando uma stored procedure

Para deletarmos uma procedute, utilizamos a instrução DROP PROCEDURE. Vejamos a seguir a sintaxe para a exclusão:

DROP PROCEDURE nome_da_procedure;

Então podemos utilizar esta instrução também dentro de um bloco PL/SQL e realizar sua exclusão.

Com isso finalizamos este artigo, onde pudemos avaliar conceitos importantes com relação ao uso das functions e também ao uso das stored procedures, além de visualizar na prática estes recursos e ter uma boa noção de sua utilização e de suas diferenças.

Até a próxima!