Oracle 10g Dicas de PL/SQL Conteúdo

Oracle 10g
Dicas de PL/SQL
Conteúdo
Objetivos............................................................................................................................................................ 2
Comentários ...................................................................................................................................................... 2
Declarações ....................................................................................................................................................... 2
Conversões de variáveis .................................................................................................................................... 2
DDL – Criar, alterar ou apagar tabelas (Create, Alter or Drop table) ................................................................ 3
Restrições na criação da tabela (CONSTRAINTS): .............................................................................................. 4
Select, Insert, Update e Delete .......................................................................................................................... 7
Seleção de dados usando as junções dos relacionamentos entre tabelas ....................................................... 9
Visão (VIEW) .................................................................................................................................................... 10
Seqüência (Sequence) ..................................................................................................................................... 10
Blocos .............................................................................................................................................................. 10
LOOP usando Cursor........................................................................................................................................ 11
Procedimentos armazenados (Stored Procedures)......................................................................................... 12
Gatilhos (Triggers) ........................................................................................................................................... 14
Trigger mutante ............................................................................................................................................... 16
Funções (Function) .......................................................................................................................................... 17
Packages .......................................................................................................................................................... 17
JOB ................................................................................................................................................................... 18
Referências ...................................................................................................................................................... 19
2
Objetivos
Nesse artigo serão relatadas algumas dicas de PL/SQL. Os exemplos de PL/SQL abaixo foram realizados no
Oracle 10g.
A idéia para a elaboração desse artigo é auxiliar o pessoal que está iniciando em PL/SQL.
Comentários
Quando o comentário for de uma linha, pode usar dois hífens.
Por exemplo:
-- Esse seria um comentário de apenas uma linha.
Quando o comentário ultrapassar uma linha usa o /*...*/.
Por exemplo:
/*
Esse seria o comentário
que ultrapassa
uma linha.
*/
Declarações
Tipos de variáveis
Tipo
binary_integer
number(<precisão>,<escala>)
char(<comprimento>)
long
varchar2(<comprimento>)
raw(<comprimento)
Descrição
Numérico para armazenar valores inteiros
Numérico para armazenar valor em ponto flutuante
Alfanumérico de tamanho fixo com até 32767 caracteres
Alfanumérico de tamanho variável com comprimento de
até 32760 caracteres
Alfanumérico de tamanho variável com comprimento de
até 32767 caracteres
Armazena dados binários de até 32767 caracteres
Conversões de variáveis
TO_CHAR - Converter um valor numérico para char
select TO_CHAR(99999,'0000000000.99') from dual
3
TO_CHAR Converter uma data para o formato especifico
select to_char(sysdate, 'YYYY-MM-DD') from dual
Outros formatos de data
CC, SCC
Século
SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
Ano
Q
Quarto de ano
MONTH
Mês por extenso
MON
Mês abreviado para três letras
MM
Mês (numérico)
WW
Semana do ano
WHERE
Semana do mês
WHERE
Início da semana do mês
DDD
Dia do ano
DD
Dia do mês
D
Dia da semana
DAY
Nome do dia
DY
Dia abreviado p/ 3 letras
J
Dia em data Juliana
HH, HH12, HH24
Hora
MI
Minuto
SS
Segundo
TO_NUMBER - Converter um valor char para numérico
select TO_NUMBER('5678.32', '9999.99') from dual
NVL - Se a 1ª expressão vier null ou vazia ele retorna a 2ª expressão
select NVL(123,234) from dual
select NVL('',234) from dual
select NVL(null,234) from dual
DDL – Criar, alterar ou apagar tabelas (Create, Alter or Drop table)
Script para criar tabela:
CREATE TABLE <table_name>
(
<column_name> <datatype> [<expr>],
<column_name> <datatype> [<expr>],
....
)
CREATE TABLE TB_TESTE
(
cod NUMBER,
nm_pessoa VARCHAR2(14),
4
salario NUMBER(10,2)
)
Para visualizar a descrição da tabela TB_TESTE:
DESCRIBE TB_TESTE
DESC TB_TESTE
Com a tabela já existente é possível acrescentar campos ou alterar tipo do campo, desde que, o tipo possa
ser suportado.
Para adicionar campo a tabela:
ALTER TABLE TB_TESTE ADD data_atual date
ALTER TABLE TB_TESTE ADD sexo char(1)
Para alterar o tipo do campo da tabela:
ALTER TABLE TB_TESTE MODIFY (sexo varchar2(15))
Para remover o campo da tabela:
ALTER TABLE TB_TESTE DROP (sexo)
Para mudar o nome da tabela:
RENAME TB_TESTE TO TB_PESSOA
Para apagar a tabela:
DROP TABLE tb_pessoa
Restrições na criação da tabela (CONSTRAINTS):
CONSTRAINT NULL ou NOT NULL
Na criação de tabelas é possível acrescentar algumas restrições para o momento dos inserts. Um exemplo
seria a restrição NULL ou NOT NULL. Essa restrição é para determinar que o campo determinado permite
ou não permite um insert com o campo vazio.
CREATE TABLE TB_PRODUTO
(
cod INT NOT NULL,
nome VARCHAR2(50) NULL
)
INSERT INTO TB_PRODUTO VALUES(1,'A')
5
INSERT INTO TB_PRODUTO VALUES(NULL,'A')
INSERT INTO TB_PRODUTO VALUES(2,NULL)
SELECT * FROM TB_PRODUTO
DROP TABLE TB_PRODUTO
CONSTRAINT UNIQUE
A constraint UNIQUE tem a função de não permitir a inserção de itens repetidos. Na criação da tabela é
permitido adicionar diversos campos com essa restrição.
CREATE TABLE TB_PRODUTO
(
cod INT UNIQUE,
nome VARCHAR2(50) NULL
)
INSERT INTO TB_PRODUTO VALUES(1,'A')
INSERT INTO TB_PRODUTO VALUES(NULL,'A')
INSERT INTO TB_PRODUTO VALUES(1,'B')
SELECT * FROM TB_PRODUTO
DROP TABLE TB_PRODUTO
CONSTRAINT PRIMARY KEY
Tem a função de não permitir a inserção de itens repetidos. Na criação da tabela apenas um campo deve
possuir essa característica.
CREATE TABLE TB_PRODUTO
(
cod INT NOT NULL PRIMARY KEY,
nome VARCHAR2(50) ,
valor number(10,2)
)
INSERT INTO TB_PRODUTO VALUES(1,'A',10)
INSERT INTO TB_PRODUTO VALUES(1,'B',20)
INSERT INTO TB_PRODUTO VALUES(NULL,'C',30)
DROP TABLE TB_PRODUTO
CREATE TABLE TB_PRODUTO
(
cod INT NOT NULL ,
nome VARCHAR2(50),
valor NUMBER(10,2),
CONSTRAINT PK_PRODUTO PRIMARY KEY(cod)
6
)
CONSTRAINT FOREIGN KEY
Usado para relacionamento entre tabelas.
Para inserir os dados em uma tabela que possui relacionamento, será necessário que exista o mesmo dado
na tabela relacionada, se não existir, não será permitido A inserção e ocorrerá erro.
Verifique o exemplo abaixo:
CREATE TABLE TB_VENDA
(
cod INT NOT NULL PRIMARY KEY,
nome VARCHAR2(50),
cod_produto INT,
CONSTRAINT FK_PRODUTO_VENDA FOREIGN KEY (cod_produto)
REFERENCES TB_PRODUTO(cod)
)
INSERT INTO TB_VENDA VALUES(1,'A',1)
INSERT INTO TB_VENDA VALUES(1,'A',2)
SELECT * FROM TB_VENDA
DROP TABLE TB_VENDA
CHECK CONSTRAINT
Restrição para determinar o tipo da informação
Verifique o exemplo abaixo: Nesse exemplo só vai permitir a inserção do cod acima de 0.
CREATE TABLE TB_TESTE1
(
cod INT NOT NULL CHECK( ID > 0 ),
nome VARCHAR2(50)
)
INSERT INTO TB_TESTE1 VALUES(1,'A')
INSERT INTO TB_TESTE1 VALUES(-1,'A')
7
Vamos criar a tabelas para os próximos testes:
CREATE TABLE TB_CARGO
(
cod NUMBER PRIMARY KEY,
ds_cargo VARCHAR2(14)
)
CREATE TABLE TB_PESSOA
(
cod NUMBER PRIMARY KEY,
nm_pessoa VARCHAR2(14),
salario NUMBER(10,2),
cargo NUMBER,
CONSTRAINT fk_cargo_pessoa FOREIGN KEY (cargo) REFERENCES tb_cargo(cod)
)
Select, Insert, Update e Delete
Funções sql para manipular uma tabela
Sintaxe de Insert
Exemplo para inserir na tabela criada anteriormente:
INSERT INTO tb_cargo (cod, ds_cargo) VALUES (1,'Contador')
INSERT INTO tb_cargo (cod, ds_cargo) VALUES (2,'Auxiliar')
INSERT INTO tb_cargo (cod, ds_cargo) VALUES (3,'Gerente')
INSERT INTO tb_pessoa (cod,nm_pessoa, salario, cargo) VALUES (1,'José Silva',700.00,1)
INSERT INTO tb_pessoa (cod,nm_pessoa, salario, cargo) VALUES (2,'Pedro Henrique',1400.00,2)
INSERT INTO tb_pessoa (cod,nm_pessoa, salario, cargo) VALUES (3,'Lucas Andrade',3200.00,3)
Funções sql para manipular uma tabela
SELECT
SELECT <columns> FROM <table>
Exemplo: select * from TB_PESSOA
Funções interessantes para ser usada no select:
Para deixar os caracteres todos em maiúsculo:
UPPER()
Exemplo: select UPPER(nm_pessoa) from tb_pessoa
8
Para deixar os caracteres em minúsculos:
LOWER()
Exemplo: select LOWER(nm_pessoa) from tb_pessoa
Para deixar o primeiro caractere de cada frase em maiúsculo:
INITCAP()
Exemplo: select INITCAP('jose maria dos santos') from dual
Concatenando dados:
select 'nome: ' || nm_pessoa, 'salario: ' || salario from tb_pessoa
Trabalhando com condições (where)
Para trazer apenas uma pessoa específica pesquisando por nome ou por código:
SELECT * FROM tb_pessoa WHERE nm_pessoa = 'José Silva'
SELECT * FROM tb_pessoa WHERE nm_pessoa LIKE 'Lucas%'
SELECT * FROM tb_pessoa WHERE cod = 3
Critérios de ordenação
SELECT * FROM tb_pessoa ORDER BY nm_pessoa
SELECT * FROM tb_pessoa order by cod
Critérios de condições
Símbolo
Descrição
Exemplo
=
<
>
<=
Igual
Menor
Maior
Menor ou
Igual
Maior ou
Igual
Diferente
Diferente
Diferente
Entre
Igual
E
Ou
Negação
SELECT * FROM tb_pessoa WHERE salario = 3000
SELECT * FROM tb_pessoa WHERE salario < 1000
SELECT * FROM tb_pessoa WHERE salario > 1000
SELECT * FROM tb_pessoa WHERE salario <= 1000
>=
<>
!=
^=
BETWEEN ... END …
IN(…)
AND
OR
NOT
SUBSTR()
SELECT * FROM tb_pessoa WHERE salario >= 1000
SELECT * FROM tb_pessoa WHERE salario <> 1000
SELECT * FROM tb_pessoa WHERE salario != 1000
SELECT * FROM tb_pessoa WHERE salario ^= 1000
SELECT * FROM tb_pessoa WHERE salario between 700 and 2000
SELECT * FROM tb_pessoa WHERE salario in(680,1360,2720)
SELECT * FROM tb_pessoa WHERE salario > 1000 AND salario < 2000
SELECT * FROM tb_pessoa WHERE salario = 2000 or salario = 3000
SELECT * FROM tb_pessoa WHERE salario not in(680,1360,2720)
9
Determina quantos caracteres serão exibidos
SELECT SUBSTR('José Silva Santos',1,10) FROM DUAL
INSTR()
Informa em que posição está o primeiro caractere requerido
SELECT INSTR('José Silva Santos','v') FROM DUAL
LENGTH()
Informa a quantidade de caracteres possui o texto informado
SELECT LENGTH('José Silva Santos') FROM DUAL
LPAD()
Preenche A esquerda com o caractere que FOR determinado até preencher A quantidade determinada.
SELECT salario, LPAD(salario,10,'0') FROM tb_pessoa
RPAD()
Preenche A direita com o caractere que FOR determinado até preencher A quantidade determinada.
SELECT salario, RPAD(salario,10,'0') FROM tb_pessoa
TRIM()
Remove o caractere informado na frase
SELECT TRIM('-' FROM '- Primeiro Teste') FROM DUAL
ADD_MONTHS()
Acrescentar meses a frente
SELECT ADD_MONTHS('01-MAI-2013',3) FROM DUAL
Seleção de dados usando as junções dos relacionamentos entre tabelas
INNER JOIN
Seleciona os dados relacionados das duas tabelas.
SELECT p.nm_pessoa, p.salario, cg.ds_cargo FROM tb_pessoa p
INNER JOIN tb_cargo cg ON (cg.cod = p.cargo)
LEFT JOIN
Seleciona os dados relacionados das duas tabelas e da tabela a esquerda seleciona também os dados que
estão nulos.
SELECT p.nm_pessoa, p.salario, cg.ds_cargo FROM tb_pessoa p
LEFT JOIN tb_cargo cg ON (cg.cod = p.cargo)
RIGHT JOIN
Seleciona os dados relacionados das duas tabelas e da tabela a direita seleciona também os dados que
estão nulos
10
SELECT p.nm_pessoa, p.salario, cg.ds_cargo FROM tb_pessoa p
RIGHT JOIN tb_cargo cg ON (cg.cod = p.cargo)
Visão (VIEW)
VIEW é uma tabela lógica que pode ser baseada em uma ou mais tabelas. Uma VIEW conceitualmente se
aplica para minimizar o acesso aos dados de uma tabela ou a composição de um JOIN.
Sintaxe da VIEW:
CREATE OR REPLACE VIEW vw_pessoa_cargo
AS
SELECT p.cod, p.nm_pessoa, p.salario, ca.ds_cargo
FROM tb_pessoa p
INNER JOIN tb_cargo ca ON (ca.cod = p.cargo)
select * from vw_pessoa_cargo
Seqüência (Sequence)
O Oracle gera automaticamente um novo valor para uma chave primária. Constrói o próximo valor da
chave primária referente a uma seqüência através do comando INSERT.
Sintaxe para cria sequence
CREATE SEQUENCE sq_pessoa
INCREMENT BY 1
START WITH 1
MAXVALUE 999999
CYCLE;
Expressões:
NEXTVAL - Gera o próximo valor disponível.
CURRVAL - Faz referência a mais recente valor gerado.
SELECT sq_pessoa.NEXTVAL FROM DUAL;
SELECT sq_pessoa.CURRVAL from DUAL;
Exemplo:
INSERT INTO TB_PESSOA(cod, nm_pessoa, salario, cargo)VALUES(sq_pessoa.nextval, 'Claudio', 1200,2)
Blocos
A estrutura de bloco vai ser muito usada na criação de procedure, trigger, function e package.
11
Estrutura de um bloco
DECLARE - Contém a declaração de todas as variáveis, constantes, cursores e etc.
BEGIN - Contém os comandos
EXCEPTION - Contém as ações que serão tomadas se ocorrer algum erro na execução.
DECLARE
salario number(10,2);
data date;
BEGIN
salario := 1200.00;
data := sysdate;
DBMS_OUTPUT.PUT_LINE('Salário:' || TO_CHAR(salario));
DBMS_OUTPUT.PUT_LINE('Dia:' || TO_CHAR(data, 'day'));
END;
LOOP usando Cursor
Simples LOOP usando variáveis
DECLARE
v_nome VARCHAR2(15);
v_salario number(10,2);
CURSOR my_cursor IS
SELECT nm_pessoa, salario INTO v_nome, v_salario FROM TB_PESSOA;
BEGIN
OPEN my_cursor;
LOOP
FETCH my_cursor INTO v_nome, v_salario;
EXIT WHEN my_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Nome: ' || v_nome || ' | Salário: ' || v_salario);
END LOOP;
END;
Simples WHILE muito bom para ser usado sem variável como o exemplo abaixo
DECLARE
CURSOR my_cursor IS
SELECT * FROM TB_PESSOA;
l_record my_cursor%ROWTYPE;
BEGIN
OPEN my_cursor;
FETCH my_cursor INTO l_record;
while my_cursor%FOUND
LOOP
DBMS_OUTPUT.PUT_LINE('Nome: ' || l_record.nm_pessoa || ' | Salário: ' || l_record.salario);
FETCH my_cursor INTO l_record;
12
END LOOP;
END;
CURSOR usando parâmetro
DECLARE
CURSOR my_cursor(p_cod NUMBER) IS
SELECT nm_pessoa FROM TB_PESSOA WHERE cod = p_cod;
pessoa my_cursor%ROWTYPE;
type tpessoas is table of my_cursor%ROWTYPE;
pessoas tpessoas;
i integer;
BEGIN
OPEN my_cursor(2);
LOOP
FETCH my_cursor INTO pessoa;
EXIT WHEN my_cursor%notfound;
DBMS_OUTPUT.PUT_LINE('Nome: ' || pessoa.nm_pessoa);
END LOOP;
CLOSE my_cursor;
OPEN my_cursor(3);
LOOP
FETCH my_cursor INTO pessoa;
EXIT WHEN my_cursor%notfound;
DBMS_OUTPUT.PUT_LINE('Nome: ' || pessoa.nm_pessoa);
END LOOP;
CLOSE my_cursor;
END;
LOOP usando o FOR
BEGIN
FOR pessoas IN (SELECT * FROM tb_pessoa) LOOP
DBMS_OUTPUT.PUT_LINE('Nome: ' || pessoas.nm_pessoa);
DBMS_OUTPUT.PUT_LINE('Salário: ' || pessoas.salario );
END LOOP;
END;
Procedimentos armazenados (Stored Procedures)
Stored Procedures ou Procedimentos armazenados são subprogramas armazenados com objetos
do banco de dados. As Stored Procedure permitem criar blocos de código que pode conter instruções SQL.
Serve para otimizar perfomance e aumentar a segurança das aplicações, além de promover reusabilidade e
facilidade da manutenção.
13
Procedure usando parâmetro:
CREATE OR REPLACE PROCEDURE SP_INSERIR_PESSOAS(p_nm_pessoa varchar2, p_salario number, p_cargo number)
IS
BEGIN
INSERT INTO tb_pessoa (cod, nm_pessoa, salario, cargo)
VALUES (sq_pessoa.nextval, p_nm_pessoa, p_salario, p_cargo);
END SP_INSERIR_PESSOAS;
/*Para executar*/
BEGIN
SP_INSERIR_PESSOAS('Rita',2300,2);
END;
Procedure com retorno:
CREATE OR REPLACE PROCEDURE SP_INSERIR_PESSOAS(p_nm_pessoa varchar2, p_salario number, p_cargo number,
p_resultado out varchar2)
IS
BEGIN
INSERT INTO tb_pessoa(cod, nm_pessoa, salario, cargo)
VALUES (sq_pessoa.nextval, p_nm_pessoa, p_salario, p_cargo);
p_resultado := 'Cadastro realizado com sucesso! - Nome: ' || p_nm_pessoa;
END SP_INSERIR_PESSOAS;
/*Para executar*/
DECLARE
v_resultado varchar2(80);
BEGIN
SP_INSERIR_PESSOAS('Jorge',2300,2, v_resultado);
dbms_output.put_line(v_resultado);
END;
14
Gatilhos (Triggers)
Functions ou Funções são blocos PL/SQL nomeados, semelhantes às Stored Procedures. A diferença
é que as funções obrigatoriamente devem retornar um valor. Usada para ser executada no momento de
uma instrução SQL.
BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW
BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW
BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW
TRIGGER –TIPO DE EVENTO
Dispara uma vez antes de uma transação INSERT
Dispara antes de cada novo registro criado.
Dispara uma vez depois de uma transação INSERT
Dispara depois de cada novo registro criado.
Dispara uma vez antes de uma transação UPDATE
Dispara antes de cada novo registro alterado.
Dispara uma vez depois de uma transação UPDATE
Dispara depois de cada novo registro alterado.
Dispara uma vez antes de uma transação DELETE
Dispara antes de cada novo registro apagado.
Dispara uma vez depois de uma transação DELETE
Dispara depois de cada novo registro apagado.
Exemplo:
Antes de criar um exemplo de trigger vamos criar uma tabela de log para teste.
CREATE SEQUENCE sq_log_pessoa
INCREMENT BY 1
START WITH 1
MAXVALUE 999999
cycle;
CREATE TABLE tb_log_pessoa (
cod_log NUMBER PRIMARY KEY,
cod_pessoa NUMBER,
salario NUMBER(10,2),
cod_cargo number ,
tipo_operacao varchar2(30)
);
15
Vamos criar uma trigger AFTER INSERT. Quando for inserir um novo registro na tabela Tb_pessoa, a trigger
vai inserir os mesmos dados na tabela Tb_log_pessoa e campo tipo_operacao informe qual tipo de
operação está sendo realizado Ex: (CADASTRO, ALTERAÇÃO, EXCLUSÃO).
CREATE OR REPLACE TRIGGER TG_INSERT_PESSOA
AFTER INSERT ON TB_PESSOA
FOR EACH ROW
BEGIN
INSERT INTO TB_LOG_PESSOA(cod_log, cod_pessoa, salario, cod_cargo, tipo_operacao)
VALUES(sq_log_pessoa.nextval, :NEW.cod, :NEW.salario, :NEW.cargo,'cadastro');
END;
INSERT INTO tb_pessoa (cod, nm_pessoa, salario, cargo) VALUES (sq_pessoa.nextval, 'Junior', 1000,1);
É possível criar uma trigger que possua os eventos (INSERT, UPDATE E DELETE). A trigger vai inserir os
mesmos dados na tabela Tb_log_pessoa e campo tipo_operacao informe qual tipo de operação está sendo
realizado Ex: (CADASTRO, ALTERAÇÃO, EXCLUSÃO).
CREATE OR REPLACE TRIGGER TG_INSERT_PESSOA
AFTER INSERT OR UPDATE OR DELETE ON TB_PESSOA
FOR EACH ROW
BEGIN
IF (INSERTING) THEN
INSERT INTO TB_LOG_PESSOA(cod_log, cod_pessoa, salario, cod_cargo, tipo_operacao)
VALUES(sq_log_pessoa.nextval, :NEW.cod, :NEW.salario, :NEW.cargo,'cadastro');
ELSE
IF (DELETING) THEN
INSERT INTO TB_LOG_PESSOA(cod_log, cod_pessoa, salario, cod_cargo, tipo_operacao)
VALUES(sq_log_pessoa.nextval, :OLD.cod, :OLD.salario, :OLD.cargo,'exclusão');
ELSE
INSERT INTO TB_LOG_PESSOA(cod_log, cod_pessoa, salario, cod_cargo, tipo_operacao)
VALUES(sq_log_pessoa.nextval, :NEW.cod, :NEW.salario, :NEW.cargo,'alteração');
END IF;
END IF;
END;
INSERT INTO tb_pessoa (cod, nm_pessoa, salario, cargo) VALUES (sq_pessoa.nextval, 'Lourdes', 1000,1);
DELETE FROM tb_pessoa WHERE nm_pessoa = 'Lourdes' AND salario = 1000 AND cargo = 1
update tb_pessoa set cargo = 2 where nm_pessoa = 'Junior' and salario = 1000
select * from tb_log_pessoa
16
Trigger mutante
Trigger mutante é quando tentamos criar uma trigger para determinada tabela e dentro dela,
tentamos manipular os dados dessa mesma tabela.
Com toda certeza vai dá o erro: ORA-04091;
Quando a trigger está sendo executada, no momento da manipulação da tabela, a mesma fica no
modo bloqueado até que se encerre o procedimento, ou seja, não é permitido realizar nenhum tipo de
select. Para resolver essa questão é só adicionar a instrução do Oracle chamado “PRAGMA
AUTONOMOUS_TRANSACTION;”
Esta instrução (PRAGMA) funciona como uma diretiva de compilação para o Oracle, que passa a
tratar esta função como um código independente da transação principal que esteja ocorrendo. Assim, o
COMMIT realizado na função, não afeta outras transações abertas.
Exemplo:
Criar uma Trigger na tabela TB_PESSOA que garanta que o soma dos salários por cargo não ultrapasse
13.000.
CREATE OR REPLACE TRIGGER TG_LIMITE_DESPESAS_CARGO
BEFORE INSERT OR UPDATE ON TB_PESSOA
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_total_por_cargo NUMBER;
BEGIN
SELECT sum(salario) INTO v_total_por_cargo FROM tb_pessoa p
INNER JOIN tb_cargo ca ON (ca.cod = p.cargo)
WHERE p.cargo = to_number(:NEW.cargo);
IF(INSERTING)THEN
IF((v_total_por_cargo + to_number(:NEW.salario)) > 13000)THEN
dbms_output.put_line('limite de despesa está ultrapassado seu limite de 13.000,00');
RAISE_APPLICATION_ERROR(-20001,'Atenção: limite de despesa está ultrapassado seu limite de 13.000,00.');
END IF;
ELSE
IF(((v_total_por_cargo - to_number(:OLD.salario)) + to_number(:OLD.salario)) > 13000)THEN
dbms_output.put_line('limite de despesa está ultrapassado seu limite de 13.000,00');
RAISE_APPLICATION_ERROR(-20001,'Atenção: limite de despesa está ultrapassado seu limite de 13.000,00.');
END IF;
END IF;
END;
17
Funções (Function)
Função (Function) é um conjunto de PL/SQL. É muito semelhante ao Store Procedure, a diferença é
que a função sempre deve retornar um valor.
CREATE OR REPLACE FUNCTION FC_SALARIO(p_cod NUMBER)
RETURN NUMBER
IS
v_salario number(10,2);
BEGIN
SELECT salario INTO v_salario FROM tb_pessoa WHERE cod = p_cod;
RETURN v_salario + (v_salario * 10) / 100;
END FC_SALARIO;
Execução:
DECLARE
v_valor number;
BEGIN
v_valor := FC_SALARIO(1);
dbms_output.put_line('teste ' || v_valor);
end;
select salario,FC_SALARIO(cod) from tb_pessoa;
Packages
O Package é um objeto do banco de dados capaz de armazenar procedures e functions integradas,
que podem ser executadas separadamente como se fossem parte de uma biblioteca ou à partir de uma
execução provocar várias execuções encadeadas.
Packages são compostos de duas partes: A parte pública e a parte privada.
Construção Pública:
Descrição Pode ser referenciada de qualquer ambiente no Oracle.
Localização Declarar dentro da package especification e definir dentro da package body
Construção Privada:
Descrição Pode ser referenciada somente dentro da mesma package
Localização Declarar e definir dentro da package body
o Digitar o código fonte através da um editor de texto, criando-se primeiro a package
especification
e depois a package body.
o Executar o Script a partir do SQL*PLUS, através do comando Start / @.
o Utilizar o comando execute para executar a Procedure / Function de dentro da package.
18
Simples exemplo de Package:
CREATE OR REPLACE PACKAGE PCK_PESSOA
IS
PROCEDURE PROC_CALC (p_codigo IN NUMBER);
END PCK_PESSOA;
CREATE OR REPLACE PACKAGE BODY PCK_PESSOA
IS
PROCEDURE PROC_CALC(p_codigo IN NUMBER)
IS
v_salario NUMBER(10,2);
v_nm_pessoa varchar2(30);
BEGIN
SELECT salario, nm_pessoa INTO v_salario, v_nm_pessoa FROM tb_pessoa WHERE cod = p_codigo;
DBMS_OUTPUT.PUT_LINE('Salario de ' || v_nm_pessoa ||': ' || TO_CHAR(v_salario+(v_salario*15/100)) );
END;
END PCK_PESSOA;
Para executar a package:
BEGIN
PCK_PESSOA.PROC_CALC(8);
END;
JOB
Exemplo da procedure:
CREATE OR REPLACE PROCEDURE SP_DELETE_PESSOA
IS
BEGIN
DELETE FROM TB_PESSOA
END SP_DELETE_PESSOA;
Criando uma nova JOB
BEGIN
-- JOB DEFINED ENTIRELY BY THE CREATE JOB PROCEDURE.
DBMS_SCHEDULER.CREATE_JOB
(
JOB_NAME
=> 'JOB_SP_DELETE_PESSOA',
JOB_TYPE
=> 'PLSQL_BLOCK',
JOB_ACTION
=> 'BEGIN SP_DELETE_PESSOA(); END;',
START_DATE
=> SYSTIMESTAMP,
REPEAT_INTERVAL
=> 'sysdate + 3/1440',
END_DATE
=> NULL,
19
ENABLED
COMMENTS
);
END;
=> TRUE,
=> 'JOB PARA EXECUTAR PROCEDURE SP_DELETE_PESSOA.'
Referências
- Devmedia : www.devmedia.com.br
- Apostilando : www.apostilando.com