Procedimentos (stored procedure)

Procedimentos (stored procedure)

Boa tarde pessoal,

Resolvi dedicar este post a um assunto muito importante dentro de PL/SQL e importantíssimo para quem deseja trabalhar com Oracle: Procedure. Ainda sente dúvida em Procedure? Então leia este post:

Procedimentos (stored procedure)

Uma procedure nada mais é que um bloco PL/SQL nomeado que pode aceitar argumentos (também chamado de parâmetros) e pode ser chamada por um programa, uma sessão SQL ou uma trigger.

Durante a instalação do banco de dados Oracle um script é executado automaticamente e cria toda a estrutura necessária para que as procedures sejam executadas. Eventualmente esse procedimento automático pode falhar devido a alguma falha física no disco rígido, nesse caso o usuário SYS pode recriar a estrutura através do script SQL DBMSSTDX.SQL.

Para criar uma procedure o usuário precisa ter o privilégio de sistema CREATE PROCEDURE, para criar a procedure em outros schemas o usuário deve ter o privilégio de CREATE ANY PROCEDURE. Este é um ponto muito interessante sobre as procedures, os privilégios para criação de procedures têm que concedidos explicitamente, ou seja, não pode ser adquirido através de roles.

Para executar uma procedure externa é necessário ter o privilégio de EXECUTE. Caso queira alterar a procedure de outro schema deve ter o privilégio de sistema ALTER ANY PROCEDURE.

A sintaxe básica de uma procedure é:

CREATE [OR REPLACE] PROCEDURE [schema.]nome_da_procedure
[(parâmetro1 [modo1] tipodedado1,
parâmetro2 [modo2] tipodedado2,
...)]
IS|AS
Bloco PL/SQL

Onde:

REPLACE – indica que caso a procedure exista ela será eliminada e substituída pela nova versão criada pelo comando;
BLOCO PL/SQL – inicia com uma cláusula BEGIN e termina com END ou END nome_da_procedure;
NOME_DA_PROCEDURE – indica o nome da procedure;
PARÂMETRO – indica o nome da variável PL/SQL que é passada na chamada da procedure ou o nome da variável que retornará os valores da procedure ou ambos. O que irá conter em parâmetro depende de MODO;
MODO – Indica que o parâmetro é de entrada (IN), saída (OUT) ou ambos (IN OUT). É importante notar que IN é o modo default, ou seja, se não dissermos nada o modo do nosso parâmetro será, automaticamente, IN;
TIPODEDADO – indica o tipo de dado do parâmetro. Pode ser qualquer tipo de dado do SQL ou do PL/SQL. Pode usar referencias como %TYPE, %ROWTYPE ou qualquer tipo de dado escalar ou composto. Atenção: não é possível fazer qualquer restrição ao tamanho do tipo de dado neste ponto.
IS|AS – a sintaxe do comando aceita tanto IS como AS. Por convenção usamos IS na criação de procedures e AS quando estivermos criando pacotes.
BLOCO PL/SQL – indica as ações que serão executadas por aquela procedure.

Vamos ver um exemplo de procedure para ajudar nosso entendimento:

CREATE OR REPLACE PROCEDURE aumenta_sal
(p_empno IN emp.empno%TYPE)
IS
BEGIN
UPDATE scott.emp
SET sal = sal * 1.10
WHERE empno = p_empno;
END aumenta_sal;
/

Neste exemplo estamos criando uma procedure para aumentar o salário de um funcionário em 10%. A primeira linha define o NOME DA PROCEDURE, que vai ser AUMENTA_SAL.

A linha dois define o parâmetro P_EMPNO no modo IN. Ou seja, vai ser um dado informado na chamada da procedure. Em seguida determinamos que ele será do mesmo tipo e tamanho que a coluna EMPNO da tabela EMP. Isso é feito através da referencia EMP.EMPNO%TYPE.

Podemos verificar o estado de nossa procedure através de uma simples consulta:

SELECT object_name, status
FROM user_objects
WHERE object_name LIKE '%AUMENTA%';
Agora podemos verificar o funcionamento de nossa procedure:
SELECT empno, sal
FROM scott.emp;

EMPNO SAL
---------- ----------
7839 5000
7698 2850
7782 2450

CALL AUMENTA_SAL(7839);
Ou
EXECUTE AUMENTA_SAL(7839);

SELECT empno, sal
FROM scott.emp;

EMPNO SAL
---------- ----------
7839 5500
7698 2850
7782 2450

Podemos notar que o salário do funcionário 7839 aumentou em 10%. É interessante notar que neste momento é possível executar a instrução ROLLBACK;

É possível desfazer as alterações porque os dados passados através dos modos OUT e IN OUT são registrados no arquivo de redo log e no segmento de rollback. Isso é perfeito quando trabalhamos com parâmetros pouco extensos, mas pode causar impacto no sistema quando trabalhamos com parâmetros extensos como, por exemplo, um registro ou um VARRAY. Para resolver esse problema podemos usar a opção de NOCOPY. Nossa procedure ficaria assim com a opção NOCOPY:

CREATE OR REPLACE PROCEDURE aumenta_sal
(p_empno IN OUT NOCOPY emp.empno%TYPE)
IS
BEGIN
UPDATE scott.emp
SET sal = sal * 1.10
WHERE empno = p_empno;
END aumenta_sal;
/

Com nossa alteração o valor passado em nosso parâmetro não é gravado no arquivo de redo log e nem no segmento de rollback. Isso implica que, neste caso, NÃO É POSSÍVEL FAZER ROLLBACK. A documentação Oracle afirma que há ganho de performance de 30% a 200% nos casos em que tabelas PL/SQL eram passadas como parâmetro na procedure.

Notem que a procedure pôde ser chamada através do comando CALL quanto pelo comando EXECUTE. Isso ocorre porque uma procedure pode ser chamada a partir de qualquer uma das ferramentas de desenvolvimento Oracle como, por exemplo, o SQL*Plus. Uma das vantagens das procedures é que elas podem ser chamadas a partir de uma aplicação, de outra procedure, de uma trigger e até mesmo a partir de uma simples query.

Exemplo:

BEGIN
AUMENTA_SAL(7839);
END;
/

Durante a criação de nossa procedure pode ocorrer algum erro. Nesse caso será mostrada uma mensagem semelhante a esta:

Aviso: Procedimento criado com erros de compilação.

Ou

MGR-00072: Warning: Procedure AUMENTA_SAL created with compilation errors

Nesse caso o erro pode ser determinado através do SHOW ERROR pode ser usado para listar a linha/coluna onde o erro ocorreu. O comando SHOW ERROR sem parâmetros adicionais mostra os erros da última compilação. Podemos qualificar o comando usando o nome de nosso pacote, procedure, função, trigger ou corpo de pacote.

Por exemplo:

SHOW ERROR aumenta_sal
Ou
SHOW ERROR PROCEDURE aumenta_sal

Vamos criar uma procedure com erro para ver como o comando funciona:

CREATE OR REPLACE PROCEDURE mand_embora
(emp_num NUMBER) IS
BEGIN
DELETE FROM emp
WHER empno = emp_num;
END
/

Notem que falta a letra E em WHERE e falta um ponto-e-vírgula no final de END. Ao executarmos o SHOW ERROR teremos:

SQL> SHOW ERROR
Erros para PROCEDURE MAND_EMBORA:

LINE/COL ERROR
-------- ----------------------------------------------------
5/14 PLS-00103:Encontrado o símbolo "EMPNO" quando um dos
seguintes símbolos era esperado:
; return returning where
O símbolo "where" foi substituído por "EMPNO" para
continuar.

7/0 PLS-00103: Encontrado o símbolo "end-of-file" quando
um dos seguintes símbolos era esperado:
;
delete exists prior
O símbolo ";" foi substituído por "end-of-file" para
continuar.

Notem que foram listadas as linhas e a colunas onde ocorreram os erros. O ponto-e-vírgula foi mostrado na linha 7 porque só no momento em que foi encerrado o bloco PL/SQL que o compilador “notou” a falta do último ponto-e-vírgula.

O SHOW ERROR é muito útil, mas, eventualmente, temos necessidade de obter mais dados sobre os erros. Neste caso é possível consultar as views de dicionário de dados:

• USER_ERRORS
• ALL_ERRORS
• DBA_ERRORS

Caso haja necessidade é possível obter o código fonte da procedure através das views de dicionário de dados ALL_SOURCE, USER_SOURCE e DBA_SOURCE.
Exemplo:

SELECT text
FROM user_source
WHERE name = 'MAND_EMBORA'
ORDER BY line;

Eventualmente podemos precisar ver todas procedures e todas as funçoes do nosso usuário. Nesse caso podemos usar:
COL FOR object_name A35

SELECT object_name, object_type
FROM user_objects
WHERE object_type in ('PROCEDURE',
'FUNCTION')ORDER BY object_name;

Caso precisemos apenas dos argumentos de nossa procedure o comando DESC permite identifica-los rapidamente.
Exemplo:

DESC mand_embora

Vejamos o uso de uma chamada de procedure com o uso do modo OUT. Vamos criar uma procedure que consulte a tabela de empregados através do número do empregado e retorne o salário e o cargo do mesmo.

CREATE OR REPLACE PROCEDURE query_emp
(p_empid IN emp.empno%TYPE,
p_sal OUT emp.sal%TYPE,
p_job OUT emp.job%TYPE)
IS
BEGIN
SELECT sal, job
INTO p_sal, p_job
FROM scott.emp
WHERE empno = p_empid;
END query_emp;
/

Agora vamos usar nossa procedure. Note que ela deve ser chamada com um parâmetro de entrada e com dois parâmetros de saída. Vamos declarar duas variáveis globais para receber os valores da procedure: G_SAL e G_JOB:

VARIABLE g_sal NUMBER
VARIABLE g_job VARCHAR2(15)
EXECUTE query_emp (7900, :g_sal, :g_job)
PRINT g_sal
PRINT g_job

Caso não usemos todos os parâmetros definidos para nossa procedure quando formos chamá-la teremos um erro:

SQL> call query_emp(7900);
call query_emp(7900)
*
ERRO na linha 1:
ORA-06553: PLS-306: número incorreto de tipos de argumentos na chamada para 'QUERY_EMP'
Também ocorrerá um erro caso o empregado pesquisado não exista. Exemplo:
SQL> EXECUTE query_emp (120, :g_sal, :g_job)
BEGIN query_emp (120, :g_sal, :g_job); END;

*
ERRO na linha 1:
ORA-01403: dados não encontrados
ORA-06512: em "SYS.QUERY_EMP", line 7
ORA-06512: em line 1

Esse tipo de erro pode ser tratado pelo próprio programador. Vamos criar uma procedure que elimine todos os funcionários com o cargo que for informado pelo usuário e apresente um erro caso o cargo não exista:

CREATE OR REPLACE PROCEDURE del_job
(p_jobid IN emp.job%TYPE)
IS
BEGIN
DELETE FROM scott.emp
WHERE job = p_jobid;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20203,'Cargo não existe.');
END IF;
END DEL_JOB;
/

Ao executarmos nossa procedure com um cargo que não exista obteremos a mensagem de erro que definimos:

SQL> execute del_job('Presidente')
BEGIN del_job('Presidente'); END;

*
ERRO na linha 1:
ORA-20203: Cargo não existe.
ORA-06512: em "SYS.DEL_JOB", line 8
ORA-06512: em line 1
Veja, logo após "linha 1:" o código de erro "Cargo não existe".
Vamos ver como uma procedure pode agir como um subprograma. Primeiro vamos criar uma procedure para calcular o valor de Delta.
CREATE OR REPLACE PROCEDURE delta
(p_a IN number,
p_b IN number,
p_c IN number,
p_delta OUT number)
IS
BEGIN
p_delta := (p_b * p_b) - (4 * p_a * p_c);
END delta;
/

Agora vamos criar uma procedure que calcule o valor das raízes de delta:

CREATE OR REPLACE PROCEDURE eq2g
(p_a IN number,
p_b IN number,
p_c IN number,
p_x1 OUT number,
p_x2 OUT number)
IS
p_delta NUMBER;
BEGIN
delta(p_a, p_b, p_c, p_delta);
IF p_delta < 0 then
p_x1 := -1;
p_x2 := -1;
ELSE
p_x1 := -1 * p_b + sqrt(p_delta)/(2 * p_a);
p_x2 := -1 * p_b - sqrt(p_delta)/(2 * p_a);
end if;
end eq2g;
/

Notem que P_DELTA foi declarado depois de IS, mas sem um DECLARE. Isto foi feito porque em uma procedure não aceita DECLARE e sua seção de declaração fica entre IS e BEGIN. Nossa procedure está chamando a procedure DELTA para calcular o delta de nossa equação. Agora vamos executar nossa procedure.

VARIABLE g_x1 NUMBER
VARIABLE g_x2 NUMBER
EXECUTE eq2g (1, 4, 2, :g_x1, :g_x2)
PRINT g_x1
PRINT g_x2

Em nossa procedure quando o delta for negativo, os valores das raízes X1 e X2 retornam com -1. É claro que existem soluções melhores do que esta, trabalhar com raízes imaginárias ou dar uma mensagem de erro quando isso acontecer.

Fonte

• Artigo – Milton Goya

Grande abraço e sucesso!