Stored Procedures no Oracle: Conceitos, Sintaxe e Exemplos
Uma StoredProcedure ou simplesmente Procedure é um bloco PL/SQL que recebe um nome e é armazenado no dicionário de dados do Oracle, podendo ser reutilizada futuramente.
Pode realizar uma ou mais tarefas específicas.
Sua utilização é muito similar a qualquer outra linguagem de programação.
Uma procedure, por padrão, possui um cabeçalho e um corpo.
O cabeçalho contém o nome da procedure e os parâmetros ou variáveis que serão passados para ela.
O corpo consiste nas seções de declaração, execução e tratamento de erros (Exceptions).
Utiliza-se a mesma estrutura aplicada na construção de blocos anônimos:
DECLARE
BEGIN
EXCEPTION
END;
Sintaxe para criar uma procedure
A sintaxe para criar uma procedure é:
CREATE [OR REPLACE] PROCEDURE proc_nome( [lista de parâmetros] )
IS
--Seção de Declaração
BEGIN
--Seção de Execução
EXCEPTION
--Seção de Exceção
END;
Tipos de parâmetros
Os parâmetros passados para uma procedure podem ser dos tipos:
- IN
- OUT
- IN OUT
Uma procedure pode ou não retornar algum valor.
A palavra IS determina o início da procedure e é muito similar à palavra DECLARE de um bloco anônimo de PL/SQL.
O trecho entre as palavras IS e BEGIN é considerado a seção de declaração de variáveis.
CREATE criará a procedure; REPLACE substitui a procedure, caso ela já exista.
Exemplo: Olá Mundo
CREATE OR REPLACE PROCEDURE olamundo
IS
BEGIN
dbms_output.put_line('Olá Mundo');
dbms_output.put_line('Hoje é ' || sysdate);
END;
SET SERVEROUTPUT ON;
Exemplo: Cálculo de soma com parâmetros IN
CREATE OR REPLACE PROCEDURE calculop (v_Num1 IN NUMBER,
v_Num2 IN NUMBER)
IS
v_soma NUMBER;
BEGIN
v_soma := v_Num1 + v_Num2;
dbms_output.put_line('Soma: ' || v_soma);
END calculop;
Exemplo de procedure com INSERT
CREATE OR REPLACE PROCEDURE proc_add_depto (deptId IN NUMBER, deptNome IN VARCHAR2)
IS
BEGIN
INSERT INTO departamento (codigo, nome) VALUES (deptId, deptNome);
END;
Executando uma procedure
1. Através de um comando SQL:
EXECUTE proc_add_depto(10, 'RH');
EXEC proc_add_depto(10, 'RH');
2. Dentro de outra procedure ou bloco anônimo, chamando apenas pelo nome da procedure:
proc_add_depto(10, 'RH');