PROCEDIMENTOS ARMAZENADOS (STORED PROCEDURES)

Um procedimento armazenado, também conhecido como stored procedure, é um bloco PL/SQL identificado, reutilizável, armazenado como um objeto no banco de dados. Diferentemente dos blocos anônimos, um procedimento pode ter parâmetros de entrada, saída ou entrada/saída. Sua forma geral é:

O comando CREATE PROCEDURE cria a stored procedure. Após o nome, há uma relação de parâmetros entre parênteses. Se não houver parâmetros declarados, os parênteses devem ser omitidos.

Cada parâmetro declarado pode ser de entrada (IN), de saída (OUT) ou de entrada e saída (IN OUT). Se não for especificado, o compilador assume como parâmetro de entrada (IN). A execução da procedure termina quando é encontrado o comando RETURN ou o END do bloco principal. O exemplo a seguir cria a stored procedure fatorial_proc, que retorna o fatorial do número inteiro n.

Na procedure fatorial_proc, foram declarados 2 parâmetros, um de entrada, n, e outro de saída, fatorial, que retorna o valor calculado para o bloco que chamou a procedure. O resultado, após a execução do bloco anônimo, é mostrado a seguir.

No exemplo a seguir, a procedure swap troca o conteúdo de duas variáveis do tipo VARCHAR2.  Ambos os parâmetros são do tipo IN OUT, fornecendo os valores para a procedure e retornado o resultado. Observe que não é necessário definir o tamanho do tipo VARCHAR2 para os parâmetros a e b (na realidade, não é permitido). Isto vale para todos os tipos em que é possível definir o tamanho.

O resultado, após a execução do bloco anônimo, é mostrado a seguir.

Deve-se executar o bloco de criação da procedure antes de qualquer referência a ela para que seja armazenada no banco de dados e passe a “existir”. Após sua criação, a procedure pode ser visualizada no explorador de objetos do SQL Developer:

Figura 1 – Procedures

A alteração de uma procedure é simples. Ao clicar sobre o seu nome, no explorador de objetos, uma janela de edição é aberta. Basta fazer as alterações e clicar em Compile que as alterações são verificadas e, não havendo erros, a nova versão é armazenada. Na figura a seguir é mostrada a janela de edição do SQL Developer com a indicação do comando Compile.

Pode-se remover uma procedure de duas formas: através do comando DROP PROCEDURE nome ou no explorador de objetos, clicando com o botão direito do mouse sobre a procedure e selecionando DROP.

Os parâmetros declarados na definição da procedure são chamados de parâmetros formais, enquanto aqueles utilizados na chamada da procedure são chamados de parâmetros atuais. No momento de chamada de uma procedure e antes de sua execução, os parâmetros atuais devem ser mapeados nos parâmetros formais.

Na chamada de uma procedure, os parâmetros atuais podem ser associados aos formais pela posição (referência posicional) ou associando-os explicitamente (referência por nome)