Procedures e Funções em PL/SQL

Bom galera a pedido, está o tutorial sobre PL/SQL de uma maneira bem simples, utilizando a linguagem PL/SQL para fazer Procedures, e Functions.

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 ser 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%.

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;

Qual é a sua opinião ou dúvida?