Bom pessoal a pedido de algumas pessoas está ai o tutorial sobre PL/SQL de uma maneira bem simples, utilizando a linguagem PL/SQL para fazer Procedures, e Functions.
Bom então vamos a luta.
Procedures e Funções
Uma função nada mais é do que pedaços de código, definidos pelo utillizador ou pré-definidos pela linguagem, utilizados para manipular dados. Aceitam um ou mais argumentos, devolvendo um valor. O argumento é uma constante, variável ou o nome de uma coluna. O valor devolvido serve para classificar a função: será numérica se o valor devolvido é numérico, char se devolve um char. As funções são utilizadas para manipular dados, tornando mais potentes as consultas. Dividem-se em três grandes grupos: manipulação de linhas, manipulação de grupos de linhas e funções analíticas. Dentro do primeiro grupo há funções para manipular caracteres, números, datas e funções que permitem converter dados de um tipo para outro. As funções de grupo permitem obter um valor que depende do grupo de linhas, por exemplo uma média, variância ou um máximo. As funções analíticas misturam os dois tipos anteriores: devolvem um valor por cada linha, mas esse valor depende do grupo.
E 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/SQLOnde:
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:
FROM scott.emp;
EMPNO SAL
---------
7839 5000
7698 2850
7782 2450CALL 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_dadoIS|ASBloco 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_salFunçã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;
Feed
Feed RSS
FeedBurner
Comentários RSS