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;
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;
