- Código Fonte
- Artigos e Tutoriais
- Vídeo Aulas
- Notícias
- Entrevistas
- Downloads
- Conteúdo MX
- Fórum
- Atividades
PUBLICIDADE
Este artigo tem nota
10
imprimir
indique
assine
retwiteAutor
Peterson Ferreira dos Santos
membro deste março de 2008
Tags
Compartilhe e guarde
PUBLICIDADE
Enquete
Você se preocupa em desenvolver usando Web Standards?
domingo, 28 de dezembro de 2008
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.
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 é:
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:
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:
Agora podemos verificar o funcionamento de nossa procedure:
Ou
Podemos notar que o salário do funcionário 7839 aumentou em 10%.
Sintaxe Básica:
Ao contrário das procedures as funções tem que retornar ao menos um valor.
Executando
Função para calcular CPMF
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;
Este artigo foi útil para você?
Regras:
Os comentários seguem as seguintes regras:
Os comentários seguem as seguintes regras:
- Todos os comentários são enviados por membros cadastrados no site.
- A pontuação de comentários de conteúdo do próprio membro não serão computados.
- Os comentários serão pré-aprovados, porém a equipe do site revisará o conteúdo de todos os comentários podendo ser invalidados (inclusive a pontuação).
- Os comentários precisam ter, obrigatóriamente, coerência sobre o conteúdo que será comentado.
- O membro que atingir 5 comentários invalidados não poderá mais comentar dentro do site. Com isso o membro não poderá mais ganhar pontos com comentários.
- Há um limite de 10 comentários por dia para cada membro.
- Qualquer comentário após ter sido publicado poderá ser retirado através da opção (
denunciar)
Comente
© 2006-2009 R&W Informática Ltda. - Todos os direitos reservados.