Banco de Dados - OracleFeed de artigos deste autor

PL/SQL - Procedures e Funções

Uma procedure nada mais é do um bloco PL/SQL nomeado. A grande vantagem sobre um bloco PL/SQL anônimo é que pode ser compilado e armazenado no banco de dados como um objeto de schema...

por Milton Goya



Procedures

Uma procedure nada mais é do um bloco PL/SQL nomeado. A grande vantagem sobre um bloco PL/SQL anônimo é que pode ser compilado e armazenado no banco de dados como um objeto de schema. Graças a essa característica as procedures são de fácil manutenção, o código é reutilizável e permitem que trabalhemos com módulos de programa.

Uma procedure é, então, 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:
         ; < an identifier > < a double-quoted delimited
         identifier >
         delete exists prior <a single-quoted SQL string>
         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.

Funções

Sintaxe Básica:

CREATE [OR REPLACE] FUNCTION nome_da_função
[( parameter1 [ mode1] datatype1,
parameter2 [ mode2] datatype2,
. . .)]
RETURN tipo_de_dado
IS|AS
Bloco PL/SQL;

Ao contrário das procedures as funções tem que retornar ao menos um valor.

CREATE OR REPLACE FUNCTION pega_sal
       (p_id IN emp.empno%TYPE)
       RETURN NUMBER
     IS
       v_sal emp.sal%TYPE :=0;
     BEGIN
       SELECT sal
         INTO v_sal
         FROM scott.emp
        WHERE empno = p_id;
     RETURN v_sal;
END pega_sal;
/

Executando

VARIABLE g_sal NUMBER

EXECUTE :g_sal := pega_sal(7839)

PRINT g_sal

Função para calcular CPMF

CREATE OR REPLACE FUNCTION cpmf(p_value IN NUMBER)
       RETURN NUMBER IS
BEGIN
  RETURN (p_value * 0.038);
END cpmf;
/
SELECT empno, ename, sal, cpmf(sal)
FROM scott.emp
WHERE deptno = 10;
Milton Goya

Milton Goya - Trabalhou como desenvolvedor em COBOL, CICS e DB2 de 1982 até 2001 em empresas como Brinquedos Estrela, Banco Itaú e Bradesco. No final de 2001 resolveu dar uma guinada em sua vida e, a convite do IBTA, partiu para a área acadêmica. Fez as certificações OCP Oracle e DB2 e hoje ministra aulas de Fundamentos de Software, Plataforma de Bancos de Dados e Administração de Banco de Dados na Faculdade de Tecnologia IBTA, unidade Vergueiro. Também é instrutor Oracle e DB2 no Centro de Treinamento IBTA. Atualmente faz mestrado em ensino no Centro Paula Souza (Fatec).