banco de dados ii

1
Curso: Sistema de Informação
Fase: 3
Disciplina: Banco de dados II
Professor: Edson Thizon ([email protected])
Nº de créditos: 04
Carga Horária: 68 horas/aula
BANCO DE DADOS II
1- NOÇÕES FUNDAMENTAIS
1.1. Transações
Transação é uma unidade atômica de trabalho que atua sobre um banco de dados. Uma transação pode
ser constituída por uma ou mais operações de acesso à base de dados. Todas as operações devem ser bemsucedidas, caso contrário os efeitos da transação devem ser revertidos.
Uma transação bem-sucedida termina quando um comando COMMIT é executado. O comando
COMMIT finaliza e efetiva todas as alterações feitas na base de dados durante a transação.
1.2. ROLLBACK
Se uma transação aborta antes de o comando COMMIT ser executado, a transação deve ser
desfeita, isto é, todas as mudanças feitas durante a transação devem ser desconsideradas. O processo de
recuperação automática que permite desfazer as alterações feitas contra a base é chamado ROLLBACK.
O ROLLBACK retorna a situação dos objetos da base alterados na transação à mesma situação em que
se encontravam no início da transação.
O ROLLBACK reverte os efeitos de uma transação como se ela nunca tivesse existido.
1.3. Locks
Quando um dado é acessado por uma transação, o perigo de interferência de outras transações
acessarem o mesmo dado é constante. O gerenciamento de LOCKS ajuda a prevenir isto.
Locks são recursos de compartilhamento de dados, que permitem que o dado seja atualizado e
pesquisados dentro de um ambiente multi-usuário de maneira segura e que lhes garante confiabilidade e
integridade.
Através dos locks é possível garantir que somente um usuário esteja atualizando o dado em um
determinado momento, que vários usuários possam pesquisar o mesmo dado ao mesmo tempo, etc.
Há, normalmente, independente da nomenclatura dada por fornecedores de SGBDs, dois níveis de locks
importantes:
- locks exclusivos: ou XLOCKS, usados para garantir o uso de um determinado dado por um único
usuário. É especialmente utilizado em casos de atualizações.
- locks compartilhados: ou SLOCKS, usados para permitir que mais de um usuário acesse o mesmo
dado ao mesmo tempo. É especialmente utilizado em casos de pesquisa em tela, por exemplo. Os
1
2
SLOCKS garantem que um usuário possa consultar (e somente consultar) um dado se houver outros
usuários acessando o mesmo dado para consulta.
Quando um usuário adquire um nível de lock sobre um dado, ele pode requisitar outro nível ao SGBD.
A este conceito damos o nome de promoção de níveis de LOCK.
Imagine um usuário em nível SLOCK sobre um registro presente na tela. Para alterar o registro,
será necessário que adquira XLOCK, para depois poder alterá-lo. Isto só será possível se mais nenhum
usuário estiver atuando sobre o mesmo registro, seja em SLOCK ou XLOCK. Isto se explica de
maneira muito simples:
- se fosse possível a qualquer usuário alterar valores de registros em uso por parte de outros usuários, a
informação consultada nunca seria garantidamente segura, uma vez que a qualquer momento poderia ser
alterada, inclusive no exato momento de um processamento, quando iniciaria com um valor e terminaria
com outro valor, alterado por outro usuário.
A tabela abaixo mostra as transições de níveis de LOCK possíveis:
Corrente
Requisitado
Slock
OK
OK
Slock
Xlock
Xlock
X
X
A Tabela a seguir mostra a compatibilidade de Locks entre transações :
TX1
TX2
SLOCK
OK
OK
X
SLOCK
XLOCK
XLOCK
OK
X
X
1.4 Promoção de Locks por Objeto
A tabela abaixo mostra as transições de níveis de locks por objeto(tabela ou registro da base de
dados) possíveis:
Corrente
Slock
Registro
Slock
Tabela
Xlock
registro
Xlock
Tabela
Slock
Registro
OK
Requisitado
Slock
Tabela
X
OK
OK
X
X
Ok
X
OK
X
OK
OK
OK
OK
Xlock
Registro
X
Xlock
Tabela
X
2
3
1.5 Gerenciadores de Banco de dados OLTP(On-line Transaction Processing)
Os Gerenciadores de banco de dados OLTP ou transacionais permitem que os dados
armazenados em um banco de dados permaneçam disponíveis 24 horas por dia. Estes Gerenciadores
possuem arquitetura complexa e bastante sofisticada que garante a integridade transacional das operações
efetuadas contra a base de dados. Adotam a filosofia all-or-nothing pela qual uma transação só é
efetivada contra a base ao se alcançar a instrução COMMIT.
Este procedimento garante que processos longos de modificação do estado da base jamais sejam
efetivados parcialmente.
Da mesma forma, este gerenciadores permitem que seja feito bachup do seu conteúdo de maneira
transparente para que está usando os dados armazenados, não sendo necessário desativar o servidor de
dados.
1.6 Log de Transações
O Log de transações, embora possa ser encontrado também em SGBDs OLTP.
Normalmente trata-se de um arquivo que registra todo o desenvolvimento de cada transação
ocorrida ou em curso. Sua importância está na capacitação de efetuação de rollcks e rollforwords, que
possibilitam desfazer e refazer transações.
1.7 Falhas
Os bancos de dados estão sujeitos a diversas causas de falhas:
- queda do fornecimento de energia;
- falhas de hardware;
- falhas ou sabotagem de operação;
- etc.
Os backups e sua administração são os mecanismo determinante da reversibilidade dos efeitos e danos
provocados por falhas. Da mesma forma, a capacitação para desfazer transações incompletas, e refazer
transações completas é de fundamental importância neste sentido.
Após ocorrida uma falha, já na reinicialização do banco de dados, o SGBD deve possuir
ferramentas que possibilitem verificar que um encerramento anormal ocorreu e determinar as
providências a se tomar:
- se o backup deve ser restaurado ou não;
- se será necessário “rolar” log de transações;
- Quantas e quais transações serão desfeitas
1.8 ROLLFORWORD
Quando da reinicialização de um banco de dados após uma falha, algumas transações podem ter
sido perdidas na memória, embora um comando Commit já tenha sido emitido. Isto quer dizer que no log
de transações a transação é considerada completa. No entanto, os efeitos não foram registrados em
definitivo na base de dados.
Isto quer dizer que esta transação deve ser completada novamente. Usando o log de transações o
SGDB "sabe" que partes da transação ainda não foram gravados em definitivo. As etapas que ainda
faltam ser gravadas são então executadas até ser encontrado o comando COMMIT gravado no log de
transações. A este processo chamamos refazer a transação ou rollfoward.
3
4
Diferente do rollback, que pode ser executado por uma aplicação ou no processo de reinicialização do
banco de dados, o rollfoward pode ser apenas executado quando do processo de reinicialização do banco
de dados.
1.9 SYNCPOINT
Embora algumas SGDBs permitam que cada transação completada seja imediatamente gravada na base,
este não é sem dúvida o melhor meio de se efetivar transações, devido à sobrecarga de gravação contra o
banco, o que ocasiona uma queda de performance considerável.
Cada operação que constitui uma transação pode ser mantida em memória (gerenciada por
paginadores, cache managers, etc) e registrada contra o log de transações, sendo gravada contra a base
em intervalos de tempo pré-determinados. Quando terminado o intervalo, todos os efeitos da alterações
mantidos em memória são gravados efetivamente contra a base, sem prejuízo do gerenciamento de lock
em curso. A este "alarme" que permite disparar a efetivações contra a base chamamos syncpoint.
Os syncpoints sincronizam log de transações, base de dados e memória.
2. ARQUITETURAS DE DISTRIBUIÇÃO DE DADOS
Há, na teoria, 2 arquiteturas básica de Bancos de Dados no que se refere à distribuição dos dados:
-Centralizados: os dados encontram-se sob um único gerenciamento, possivelmente no mesmo
equipamento, podendo ser acessados por equipamentos localizados à distância;
-Distribuídos: Os dados encontram-se sob gerenciamento distribuído, possivelmente em equipamentos
separados por qualquer distância, ligados por meios de comunicação de dados. Cada unidade è autônoma
no que se refere a seus próprios dados, podendo acessá-los independentemente de haver conexão ativa
com as demais. Isto é, por exemplo, o caso de uma agência bancária que armazena os dados de seus
clientes: para acessá-los localmente ela não depende ( de fato, não pode depender) das demais agências,
ou se a comunicação com a matriz está no ar ou não. A distribuição dos dados deve ser efetiva: acessar
os dados dos clientes locais não pode de forma alguma depender do acesso a outros equipamentos que
não sejam o servidor de dados local. No entanto, dados de outras agências também devem estar
disponíveis, sendo transparente para o usuário final onde efetivamente se encontra o dado requisitado.
Esta questão abre uma discussão sobre alguns aspectos:
-Se uma agência precisa consultar, por exemplo, um dicionário master de toda a rede, para
descobrir que finalmente os dados que precisa são locais, este BD pode ser considerado distribuído,
especialmente se este master se encontra em outro equipamento à distância?
-Se o master vier a ter algum problema, todas as agências deixam de ter inclusive acesso a seus
próprios dados. Isto pode acontecer em um BD distribuído?
Estes são pontos cruciais sobre os quais se focalizam as querelas entre fornecedores de BD
centralizados e distribuídos. O fato é que, enquanto não há uma palavra final dos organismos
internacionais de padronização sobre o que é totalmente distribuído e o que não é, fornecedores diversos
têm adotado uma filosofia alternativa que se não atende a todas as necessidades da distribuição completa
dos dados, auxilia na obtenção de algumas vantagens que antes só eram alcançadas através desta
distribuição: o processamento distribuído sobre dados centralizados, mais presente hoje na forma clientserver ou cliente servidor.
Na forma tradicional de processamento à distância sobre dados distribuídos, todos os
equipamentos eram considerados terminais da máquina principal ou host. Isto, na maioria das vezes
queria dizer que remotamente, através de modem, linha privada ou discada, redes de pacotes, etc.,
4
5
obtinha-se um terminal a mais, ainda que funcionando a uma velocidade de comunicação inferior aos
terminais locais.
Tecnologicamente, as formas de comunicação de dados sofreram um avanço considerável pelo
uso de protocolos mais rápidos e confiáveis, bem como pela melhoria de equipamentos de comunicação e
meios de transmissão, especialmente a fibra óptica. Isto resolveu o problema da velocidade de
comunicação host-terminal. Subsistia o problema da sobrecarga de processamento, cada terminal a mais
conectado ao host tendia a degradar a performance global do equipamento.
A filosofia cliente-servidor foi a alternativa encontrada para este problema. Passou-se a adotar
uma forma de processamento realmente distribuído, no qual ao invés de se ligar terminais diretamente
sobre o host, ligam-se a servidores de processamento, que conectados ao host, agora chamado servidor de
dados, tornam-se clientes de dados armazenados no servidor.
Assim, cada servidor de processamento dá aos usuários dos terminais ligados a ele a impressão de
que os dados estão efetivamente armazenados nele. De fato, esta é a impressão que a máquina servidora
de processamento tem de si própria, embora ela seja um cliente dos dados do servidor de dados.
Como resultado, todo o processamento dos terminais é feito pelo cliente, que requisita ao servidor
somente os dados necessários ao processamento.
Reduz-se assim, a quantidade de canais de comunicação necessários entre o antigo host e os
terminais, passando a existir canais entre o host, agora chamado servidor, e as máquinas clientes.
Numa palavra final, permanecem centralizados os dados, mas distribui-se o processamento.
2.1 TWO-PHASE COMMIT
Uma das principais características, e também das que apresentam maior dificuldade de
implementação plena, para BD distribuídos é o COMMIT de duas fases ou two-phase commit (TPC).
Imagine uma transação sobre um banco de dados distribuído que atualize tabelas de 3 ou 4
máquinas. O que aconteceria à transação se uma das máquinas que teve alguma tabela já atualizada saísse
do ar enquanto a transação prosseguia até certo ponto quando então um rollfoward fosse solicitado por
qualquer razão?
Ao tentar desfazer a parte da transação ocorrida sobre o equipamento em falha poderia acontecer
de não serem revertidos os efeitos da transação sobre os dados daquele equipamento, mas dos demais sim.
Isto colocaria o banco numa situação de exceção, ou erro transacional, ou ainda de falha da integridade
transacional.
Para evitar este problema, e da mesma forma garantir que quando uma transação emita um
COMMIT, todos os gerenciadores envolvidos efetuem as partes da transação com que estão envolvidos,
faz-se necessário emitir um COMMIT para cada gerenciador. A transação só será efetivamente
"commitada" no caso de todos os gerenciadores envolvidos responderem OK à solicitação.
3 . SISTEMA GERENCIADOR DE BANCO DE DADOS (recapitulação BD)
3.1 CONCEITO
(Sistema Gerenciador de Banco de Dados) - É um programa que gerencia e mantém listas de
informações.
Os dados armazenados em um banco de dados podem ser imaginados como uma população de
informação. Nesse caso, a população não representa apenas um grupo de pessoas que vive na mesma área
geográfica, mas qualquer grupo ou classe de itens ou objetos que podemos definir. Ao criarmos um
banco, a população na qual estamos interessados é aquela que precisamos rastear, e que se torna a base
dos dados.
5
6
Para armazenar os dados em disco, o DBMS tem de fornecer algum tipo de serviço de definição
de dados, a fim de definir os registros e os campos. Precisa, ainda, de um mecanismo interno para manter
os dados no disco e para saber onde está cada elemento em particular.
3.2 Aplicativo de Banco de Dados
São programas que permite restaurar, visualizar e atualizar as informações armazenadas pelo
DBMS.
Domínio
É a categoria e o tipo dos elementos permitidos em um campo particular.
Ex: nome : A..Z, a..z
Modelos de DBMS
- Sistema de Gerenciamento de arquivos;
- Sistema de Banco e Dados Hierárquico
- Sistema de Banco de Dados de Rede;
- Banco de Dados Relacional;
4. SQL
4.1 Histórico
Entre 1974 e 1979, o San José Research Laboratory da IBM desenvolveu um SGDB relacional que ficou
conhecido como Sistema R.
Para a criação e acesso aos dados foi adotada uma linguagem chamada SEQUEL, mais tarde
rebatizada SQL (Structured Query Language).
Embora a query em sua definição, a SQL foi projetada de forma a permitir que além de consultas
(queries), inserções, alterações e deleções fossem feitas, além da própria criação das tabelas e campos.
Dividiu-se a SQL então em duas partes:
-DDL: Data Description Language (Linguagem de Descrição de Dados);
-DML: Data Manipulation Language (Linguagem de Manipulação de Dados).
DDL
A DDL, uma parte muito pequena da SQL, permite a criação e manutenção do dicionário de
dados. O dicionário de dados contém a definição de cada tabela, de cada campo, enfim, contém a
definição da base de dados propriamente dita. Em outras palavras, o dicionário de dados guarda dados
sobre os dados.
Embora existam algumas outras construções, a mais importante das construções da DDL é a destinada a
criação de tabelas. Por exemplo, a sentença para a criação das tabelas funcionário, cidade, e setor, poderia
ser como segue:
Adotada como padrão mundial pela ISSO em 1987, é uma linguagem exclusiva de banco de
dados Cliente/Servidor;
6
7
Não dispõe dos seguinte recursos:
- Repetição e desvio;
- Comandos para manipulação de telas e impressão de relatórios;
Os Fabricantes de SGBD podem expandir a linguagem SQL padrão ANSI, desde que os
comandos básicos sejam aceitos.
CREATE TABLE funcionário
(matrfunc NUMBER(5) NOT NULL,
nomefunc CHAR(40),
cdsetfunc NUMBER(3),
ruanofunc CHAR (40),
bairrofunc CHAR(15),
cdcidfunc NUMBER(5),
salrfunc NUMBER (11,2);
DML
É a parte mais ampla da SQL. Permite pesquisar, alterar, incluir e deletar dados da base de dados.
São quatro as sentenças mais importantes da DML:
-SELECT: permite a pesquisa de dados;
-UPDATE: permite a atualização de dados;
- DELETE: permite a deleção de dados;
- INSERT: permite a inclusão de dados.
4.2 Comandos Básicos
SELECT
- Lista os atributos desejados como resultados de uma consulta;
- Corresponde a operação de projeção da álgebra relacional;
- Lista de atributos pode ser substituído por “*”;
From
- Lista de relação a serem usados na execução da expressão;
Where
- São definidos critérios de pesquisa envolvendo atributos das relações definidas na cláusula from ;
comando opcional;
Order By
- Especifica a seqüência de ordenação da tabela criada pela consulta;
- Comando opcional;
- Qualificador opcional : asc / desc.
Select A1, A2,...,An
From r1,r2,...rm
Where P
7
8
Onde A são os campos a serem selecionados, R são as tabelas, e P é uma condição.
Exemplos:
1- Selecionar todos os atributos de cada funcionário:
SELECT *
FROM funcionário;
2- Selecionar todos os atributos dos funcionários da cidade de Tubarão:
SELECT *
FROM funcionário
WHERE cidafunc = “Tubarão”;
3- Selecionar os nomes de todos os funcionários da cidade de Tubarão e salários maior que R$ 500,00
SELECT nomefunc
FROM funcionário
Where cidafunc = “Tubarão” and salafunc > 500;
4- Selecionar o nome e rua onde moram os funcionários com matrícula maior que 100 e cidade igual a 25:
SELECT nomefunc, ruanofunc
FROM funcionário
WHERE cdcidfunc=25
AND matrfunc>100;
5- Selecionar o nome rua onde moram os funcionários com matrícula maior do que 100 e cidade igual a
25, ordenados por nome em ordem decrescente:
SELECT nomefunc,ruanofunc
FROM funcionário
WHERE cdcidfunc=25
AND matrfunc>100
ORDER BY nomefunc DESC;
6- Selecionar para cada funcionário o seu nome e o de sua cidade:
SELECT nomefunc, nomecid
FROM funcionário, cidade
WHERE cdcidfunc=cdcidade;
Comandos de Comparação de Valores
Between
Faz uma pesquisa entre uma faixa de valores para um campo da tabela.
8
9
Not Between
Faz uma pesquisa descartando uma faixa de valores.
Exemplo
Select *
from funcionario
where idadefunc between 18 and 30;
Select *
from funcionario
where idadefunc not between 18 and 30;
IN
Consulta a presença de um campo em um conjunto de valores
NOT IN
Consulta a não presença de um campo em um conjunto de valores
Exemplo:
Select *
from funcionario
where cdcidfunc in ( 10, 20, 30 ,35);
Like
Compara a existência de uma caracter em uma determinada posição em uma string.
‘_‘ Testa a existência de um caracter não nulo na posição;
‘%’ representa qualquer seqüência de n caracteres.
Not Like
Compara a não existência de um caracter em uma determinada posição em uma string.
Exemplo
Select *
From funcionario
where nomefunc like ‘Rudiney%’
Esta consulta traz todos os funcionários com primeiro nome Rudiney não importa o resto do nome.
Select *
From funcionario
where nomefunc like ‘%da Silva’
9
10
Neste caso localiza todos os funcionários com sobrenome “ da Silva” .
Null
Verifica se o valor do campo comparado é zero ou vazio;
Not Null
Verifica se o valor do campo comparado não é zero ou vazio;
Exemplo: Select *
From funcionario
Where endereco is null;
Exist
Verifica se o resultado do cálculo da subconsulta representada por “select * from “ Não é vazia.
Select x
from A
Where exists (select *
from B
where y=x)
O campo X da relação A será selecionado, se existir um elemento y=x na relação B.
Exemplo
Select *
from alunos
where exists
(select *
from alunos
where curso_alu=”CCP”);
Not Exists
Verifica se o resultado do calculo da subconsulta representada por “Select * from “ é vazia.
FUNÇÕES
Count - Número de valores da coluna
Sum - Soma dos valores da Coluna
AVG - Média dos valores da Coluna
Max - Maior valor da coluna
Min - Menor valor da coluna
Exemplo
10
11
Select max(idade), nim(idade)
from alunos;
Select avg(salario)
From funcionario;
Select nome
From funcionario
Where idade > (select avg(idade)
From funcionario)
Neste exemplo o comando SQL vai selecionar o nome dos funcionários com idade acima da média.
GRUPO BY
Forma grupos com as tuplas da tabela especificada na cláusula from, que possuem o mesmo valor
no atributo especificado na cláusula grupo by;
Para ter resultado em ordem, deve ser especificado também a clausula order by(após a clausula
grupo by).
Exemplo
Select curso_alu, count(curso_alu)
from alunos
group by curso_alu;
HAVING
Having é para os grupos o que where é para as linhas. Em outras palavras, é utilizado para eliminar
grupos, onde where é utilizado para eliminar linhas.
Exemplo:
Select curso_alu, avg(idade)
from alunos
group by curso_alu
having avg(idade) > 18;
11
12
4.3 BANCO DE DADOS ORACLE - Conceitos Básicos
LIMITES DO ORACLE RDBMS
ITEM
Tabela na Base de dados
Linhas por Tabelas
Colunas por tabelas
Indices por tabelas
Tabelas ou views joined em uma query
Niveis de ninho de subqueries
Caracteres em um nome
Colunas por índices
LIMITE
Não há limites.
Não há limites
254
Não há limites
Não há limites
30
255
16
CRIANDO TABELAS
Tipos de Dados(Datatypes)
Char(n)
Varchar2(n)
Long
Number(p,s)
Raw
Long Raw
Date
Tamanho Fixo, pode conter uma seqüência de 1 a 255 bytes
alfanuméricos;
Tamanho Variável, pode conter uma seqüência de 1 a 2000 bytes
- alfanuméricos.
Tamanho Variável até 2 Gigabytes alfanuméricos
nota : só pode existir uma coluna long em cada tabela
Numérico com sinal e ponto decimal,
sendo precisão de 1 a 38 dígitos
Binário - Variável até 255 bytes
Binário - Variável até 2 gigabytes - imagem
Data c/ hora, minuto e segundo
COMANDO CREATE TABLE
CREATE TABLE DEPT
(DEPTNO NUMBER(2) NOT NULL,
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
12
13
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL);
INSERINDO DADOS EM TABELAS
DESC DEPT;
- lista os campos da tabela dept
INSERT INTO DEPT
VALUES (50, ´TESOURARIA', 'TUBARAO',1000);
INSERT INTO DEPT(DNAME,DEPTNO)
VALUES(´CAIXA',60);
13
14
FUNÇÕES ARITMÉTICAS
Funções
Round
ROUND(SAL,2)
Trunc
TRUNC(SAL,2)
Mod
Mod(sal,comm)
Power
Power(sal,2)
Sign
Sign(sal)
SQRT
SQRT(25)
sal é arredondado para 2 casas
apos o ponto decimal
Sal é truncado para 2 dígitos
após o ponto decimal.
Retorna o resto da divisão de
sal/comm
Retorna o sal elevado ao
quadrado.
Se SAL maior que O retorna
+1
Se SAL menor que 0 retorna 1
Se SaL igual a 0 retorna 0.
retorna a raiz quadrada de 25
OPERADORES ARITMÉTICOS
+, -, *, /
Select ename, sal, comm, sal+comm
from emp
where job = 'salesman';
Select ename, sal, comm
from emp
Where comm > 0.25 * sal
Select ename, comm/sal, comm,sal
from emp
where job = 'Salesman'
order by comm/sal asc;
Select ename, sal, comm,12*(sal+comm)
from emp
where job = 'Salesman';
Select ename, sal, sal/22, round(sal/22,0), round(sal/22,2)
from emp
where emp
where deptno = 30;
14
15
select ename, sal, sal/22, trunc(sal/22,0=, trunc(sal/22,2)
from emp
where deptno = 30;
INCLUINDO NOVA COLUNA NA TABELA
ALTER TABLE DEPT
ADD (CCUSTO NUMBER(6));
ALTERANDO O TAMANHO DE UMA COLUNA
ALTER TABLE DEPT
MODIFY DNAME CHAR(20);
CRIANDO VIEWS
CREATE VIEWS MANEGERS AS
SELECT ENAME, JOB, SAL
FROM EMP
WHERE JOB = 'MANAGER';
DELETANDO VIEWS
DROP VIEWS MANAGERS;
15
16
ATUALIZANDO REGISTROS
UPDATE EMP
SET JOB='MANAGER'
WHERE ENAME='MARTIN';
ATUALIZANDO VÁRIOS REGISTROS
UPDATE EMP
SET JOB='VENDEDOR'
WHERE JOB='SALESMAN';
UPDATE EMP
SET DEPTNO=40, JOB='SALESMAN'
WHERE JOB='VENDEDOR';
DELETANDO LINHAS DAS TABELAS
DELETE FROM EMP
WHERE ENAME='MARTINS';
DELETE FROM DEPT
WHERE DEPTNO=60;
FUNÇÕES DE GRUPO
AVG, COUNT, WHERE, MAX, MIN, SUM, STDDEV,VARIANCE
Funções
AVG
COUNT
WHERE
MAX
MIN
SUN
STDDEV
VARIANCE
Exemplo
AVG(SAL)
COUNT(COMM)
COUNT(*)
COUNT(*)
MAX(SAL)
MIN(SAL)
SUM(SAL)
STDDEV(SAL)
VARIANCE(SAL)
Resultado
MADIA DO VALOR DE SAL
CONTA NUMERO DE LINHAS COM COMM
CONTA O NUMERO DE LINHAS CFE.
MAIOR VALOR DE SAL.
MÍNIMO VALOR DE SAL.
SOMOTÓRIA DOS VALORES DE COMM
CÁLCULO DESVIO PADRÃO DA SAL.
CALCULA DA VARIANÇA DE SAL.
UTILIZANDO FUNÇÕES DE GRUPO
1) SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO;
16
17
2) SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO;
17
18
FUNÇÕES DE DATA
FUNÇÕES
ADD_MONTHS
MONTHS_BETWEEN
NEXT_DAY
LAST_DAY
TRUNC
EXEMPLO
RESULTADO
ADD_MONTHS(HIREDATE ADICIONA 5 MESES NA
,5)
DATA HIREDATE
MONTHS_BETWEEN(HIRE CALCULA O NÚMERO DE
DATE,SYSDATE)
MESES BETWEEN ENTRE
AS DATAS
NEXT_DAY(HIREDATE,'FR PROCURA UMA SEXTAIDAY')
FEIRA APÓS HIREDATE
LAST_DAY(SYSDATE)
RETORNA
A
DATA
TOMANDO
COMO
PARAMETRO O 'FMT'
TRUNC(SYSDATE,FMT)
TRUNCA A DATA PARA A
PRIMEIRA DATA DO 'FMT'
Funções de conversão
Funções
TO_CHAR
Exemplo
TO_CHAR(SYSDADE,’YY’)
TO_DATE
TO_DATE(‘15/05/90’,’DD/MM/YY’)
TO_NUMBER
TO_NUMBER(SUBSTR(‘$150’,2,3))
Resultado
CONVERTE
COLUNAS
DO
TIPO
NUMBER E DATA PARA CHAR.
CONVERTE COLUNAS DO TIPO CHAR
PARA O FORMATO DE DATA.
CONVERTE AS 3 ULTIMAS (EM
FORMATO CHAR) PARA MUMBER.
Usando && para substituir variavel
Usando o duplo &, você pode reusar uma variável sem colocar no prompt a cada vez.
EX: Select empno, ename, job, &&_nome_da_coluna
From emp
Order by &column_name
Resultado:
Enter value for nome_da_coluna: deptno
EMPNO ENAME
JOB
----------- ------------------ -------------------------
DEPTONO
-------------------18
19
7839
7782
King
Clark
President
Manager
10
10
5. INTEGRIDADE REFERENCIAL
PRIMARY KEY
CREATE TABLE FILIAL
( CD_EMPRESA NUMBER(6,0),
CD_FILIAL
NUMBER(4,0),
CD_TIPO
VARCHAR2(20),
DT_CADASTRO DATE,
CONSTRAINT PK_FILIAL PRIMARY KEY(CD_EMPRESA,CD_FILIAL));
INTREGRIDADE REFERENCIAL
INTEGRIDADE
No ORACLE 7 todas as regras de integridade de dados e entidade são definidos por objetos
chamados CONSTRAINT. Que podem ser definidos quando da criação da tabela ou posteriori via
camando ALTER TABLE.
Os constraints suportados são :
* NOT NULL
* UNIQUE KEY
* PRIMARY KEY
* FOREIGN KEY
* CHECK
CONSTRAINTS IN-LINE
Exemplo:
CREATE TABLE EMPREGADO
( CD_EMPREGADO NUMBER (6,0) PRIMARY KEY,
...............................................................................
............................................................................... );
CONSTRAINTS OUT-OF-LINE
Exemplo:
19
20
CREATE TABBLE EMPREGADO
( CD_EMPREGADO NUMBER (6,0) NOT NULL,
.....................................................................
.....................................................................
CONSTRAINT PK_EMPREGADO PRIMARY KEY (CD_EMPREGADO) );
Nota : Quando o constraint for definido sem nome, o oracle define um nome para o mesmo - sys_c00n onde n é um número seqüencial crescente.
PRIMARY KEY CONSTRAINT
Valor único que identifica cada linha da tabela.
Exemplo:
CREATE TABLE EMPRESA
( ..........................................................................................................
CONSTRAINT PK_EMPRESA PRIMARY KEY (CD_EMPRESA);
CHECK CONSTRAINT
As validações de colunas são feitas utilizando o CHECK CONSTRAINT.
Exemplo:
CREATE TABLE FILIAL
( CD_EMPRESA NUMBER (6,0) CONSTRAINT FK1_FILIAL
REFERENCE EMPRESA (CD_EMPRESA),
CD_FILIAL NUMBER (4,0) CONSTRAINT CK_FILIAL
CHECK (CD_FILIAL BETWEEN 1 AND 9999) DISABLE,
CD_TIPO VARCHAR2 (20) CONSTRAINT CK_TIPO_FILIAL
CHECK (CD_TIPO IN ('MATRIZ', 'FILIAL', 'FABRICA')),
DT_CADASTRO DATE,
CONSTRAINT PK_FILIAL PRIMARY KEY (CD_EMPRESA, CD_FILIAL));
20
21
FOREIGN KEY CONSTRAINT
* Deve estar associada a uma primary key ou unique key definida anteriormente.
* Pode assumir valor nulo ou igual ao da chave referenciada.
* Não existe limite para um número de foreign keys.
* Garante a consistência com a primary key referenciada.
* Pode fazer referência a própria tabela.
* Não pode ser criada para views, synonyns e remote table
Exemplo:
CONSTRAINT FK1_FILIAL
FOREIGN KEY (CD_EMPRESA)
REFERENCES EMPRESA (CD_EMPRESA)
OU
CONSTRAINT FK1_FILIAL
FOREIGN KEY (CD_EMPRESA)
REFERENCES EMPRESA
CHECK CONSTRAINT
As validações de colunas são feitas utilizando o CHECK CONSTAINT.
Exemplo:
Create table filial
(
cd_empresa number(6,0) constraint fk1_filial
references empresa (cd_empresa),
cd_filial number(4,0) constraint ck_filial
check (cd_filial between 1 and 9999) disable,
cd_tipo varchar2(20) constraint ck_tipo_filial
check (cd_tipo in (´matriz´, ´filial´,´fabrica´, )),
dt_cadastro date,
21
22
constraint pk_filial primary key (cd_empresa, cd_filial));
DEFAULT SPECIFICATION
Podemos atribuir valores default para colunas, visando facilitar a inserção de dados
Create table filial
(
cd_empresa number(6,0) constraint fk1_filial
references empresa (cd_empresa),
cd_filial number(4,0) constraint ck_filial
check (cd_filial between 1 and 9999) disable,
cd_tipo varchar2(20) constraint ck_tipo_filial
check (cd_tipo in (´matriz´, ´filial´,´fabrica´, )),
dt_cadastro date default sysdate,
constraint pk_filial primary key (cd_empresa, cd_filial));
OBS: para ativar a constraint : alter table filial enable constraint fk_filial;
DELETE CASCADE ACTION
Opção a ser utilizada quando da definição do constraint foreing key, para que quando deletamos
registros da tabela pai os registros da tabela filho sejam automaticamente deletados.
Exemplo
create table empregado
(................................
cd_depto number(2)
constraint fk1_emp_dept
references departamento
on delete cascade
..........................);
DELETANDO CONSTRAINT
alter table filial drop constraint fk_filial;
22
23
6 PL/SQL
6.1. PORTABILIDADE
Aplicações escritas em PL/SQL são portáveis para qualquer Máquina que rode ORACLE
RDBMS com PL/SQL.
6.2. INTEGRAÇÃO COM RDBMS
- Variáveis PL/SQL podem ser definidas a partir de definições das colunas das tabelas.
- Redução de manutenção das aplicações, pois estas adaptam-se as mudanças da Base de Dados.
6.3. CAPACIDADE PROCEDURAL
- Comandos de controle de fluxo, comandos de repetições e tratamentos de erros;
6.4. PRODUTIVIDADE
- Desenvolvimento de Procedures e Triggers no Oracle Forms e Oracle Reports.
- Desenvolvimento de Database Triggers, Procedures e Functions a nível do Banco de Dados
6.5 PRINCIPAIS CARACTERÍSTICAS- PL/SQL
ESTRUTURA DE BLOCO
DECLARE - Opcional
Variaveis, cursores, exceptions definidas pelo usuário
BEGIN - Obrigatório
- SQL
- PL/SQL
EXCEPTION – Opcional
Ações que são executadas quando ocorem os erros
END – obrigatório
BLOCO PL/SQL COM SUB-BLOCO
DECLARE
DEFINIÇÃO DE VARIÁVEIS
BEGIN
COMANDOS
DECLARE
DEFINIÇÃO DE VARIÁVEIS
BEGIN
23
24
COMANDOS
EXCEPTION
TRATAMENTO DE ERROS
END;
Exemplo:
Declare
V_variavel varchar2(5);
Begin
Select nome_coluna
Into v_variável
From table_name;
Exeption
When exception_name Then
.....
End;
Obs: Sempre coloque um (;) no fim de SQL ou um PL/SQL.
Tipos de Blocos
Anonymous
Declare
Begin
.......
Execption
Procedure
Function
Procedure name is
Function name return datatype is
Begin
.......
Execption
Begin
.......
Return value;
Execption
End;
End;
UTILIZA COMANDOS SQL
SELECT, INSERT, UPDADE, DELETE, ROLLBACK, COMMIT, SAVAPOINT
DECLARAÇÃO DE VARIÁVEIS E CONSTANTES
DATATYPES MAIS UTILIZADOS
CHAR
VARCHAR2
INTERGER
24
25
NUMBER
DATE
BOOLEAN
Declare
V_data
date;
V_deptno
number(2) := 10;
V_location varchar2(13) := ‘Atlanta’;
V_comm
contant number :=1400;
EXEMPLO
DECLARE
NOME
CHAR(30);
SALARIO
NUMBER(11,2);
DEPART
NUNBER(4);
DTNASC
DATE;
SIM
BOOLEAN;
CONT
NUMBER(6) :=0;
PERC
CONSTANT NUMBER(4,2):= 36.00;
O atributo %TYPE
Declara a variável de acordo com uma coluna definida no Banco de Dados;
Exemplo
V_ename
emp.ename%Type;
V_balance
number(7,2);
V_min_balance v_balance%Type :=10;
CONCEITO DE TRANSAÇÃO
È UMA SEQUENCIA DE COMANDOS SQL DELIMITADOS POR COMANDOS CONNECT,
DISCONECT OU AINDA POR COMANDOS COMMIT.
COMANDOS A SEREM UTILIZADOS EM PL/SQL
1. COMMIT
2. ROLLBACK
3. SAVEPOINT nome-do-ponto
4.ROLLBACK TO SAVEPOINT nome-do-ponto
25
26
EXEMPLO DE CONTROLE DE TRANSAÇÃO
DECLARE
<definições de variáveis>
BEGIN
INSERT INTO TEMP VALUE (null,1,’linha 1’);
SAVEPOINT PONTOUM;
INSERT INTO TEMP VALUE (null,2,’linha 2’);
SAVEPOINT PONTODOIS;
INSERT INTO TEMP VALUE (null,3,’linha 3’);
SAVEPOINT PONTOTRES;
<COMANDOS>
ROLLBACK SAVEPOINT PONTODOIS;
<COMANDOS>
COMMIT;
END;
FUNÇÕES UTILIZADAS EM PL/SQL
Podemos contar com o uso de funções de Caracteres, Numéricas, Data, Conversão, dentre outras.
Exemplo:
Declare
Cargo_atual char(10);
Begin
Select upper(substr(cargo,1,10)) into cargo_atual
from funcionario
where cd_func = 2150;
End;
EM COMANDOS PL/SQL PODEMOS UTILIZAR AS SEGUINTES FUNÇÕES:
FUNÇÕES DE ERRO
sqlerrm, sqlcode
FUNÇÕES DE CARACTERES
ascii, chr, initcap, length, lower, lpad, rpad, ltrim, rtrim, substr, upper.
FUNÇÕES NUMÉRICAS
abs, ceil, floor,mod, power, round, sign, trunc, sqrt........
26
27
FUNÇÕES DE DATA
Add_months, lat_day, months_between, new_time, next_day, round......
CONTROLE DE FLUXO
COMANDO IF
1. IF <condição> THEN
<comandos>
END IF;
2. IF <condição> THEN
<comandos>
ELSE
<comandos>
END IF;
3. IF <condição> THEN
<comandos>
ELSIF <condição> THEN
<comandos>
END IF;
4. IF <condição> THEN
<comandos>
ELSIF <condição> THEN
<comandos>
ELSE
<comandos>
END IF;
5. IF <condição> THEN
IF <condição> THEN
<comandos>
END IF;
END IF;
EXEMPLO
DECLARE
QUANT NUMBER(3);
BEGIN
SELECT ES.NR_QTD INTO QUANT
FROM ESTOQUE ES
WHERE CD_PROD = 30;
IF QUANT > 0 AND QUANT < 3000 THEN
UPDATE ESTOQUE SET NR_QTD = QUANT + 1
WHERE CD_PROD = 30;
27
28
ELSIF QUANT >= 3000 THEN
INSERT INTO ALERTA(PROD,ERRO) VALUES(30,’MÁXIMO’);
ELSE INSERT INTO ALERTA(PROD,ERRO) VALUES(30,’MÍNIMO’);
END IF;
END;
COMANDOS DE REPETIÇÃO
SÃO UTILIZADOS PARA EXECUTAR REPETIDAMENTE O CÓDIGO ESCRITO DENTRO
DELES.
•
LOOP
•
FOR LOOPs
•
WHILE LOOP
•
CURSOR FOR LOOPs
EXEMPLO COMANDO LOOP
DECLARE
X
NUMBER := 0;
COUNTER NUMBER := 0;
BEGIN
LOOP
X := X + 1000;
COUNTER := COUNTER + 1;
IF COUNTER > 4 THEN EXIT;
END IF;
DBMS_OUPUT.PUT_LINE (X ||’ ‘|| COUNTER || ’LOOP’);
END LOOP;
END;
/
EXEMPLO COMANDO FOR .. LOOP
DECLARE
A,B
NUMBER(3):= 0;
BEGIN
28
29
FOR A IN 1..25 LOOP
B:= B + 1;
DBMS_OUTPUT.PUT_LINE(‘LOOP1 - ‘||B);
END LOOP;
END;
EXEMPLO COMANDO WHILE .. LOOP
DECLARE
X NUMBER(3);
Y VARCHAR2(30);
K DATE;
J NUMBER(3);
BEGIN
X:= 0;
WHILE X<= 100 LOOP
K:= SYSDATE-X;
Y := 30;
INSERT INTO TESTE VALUES
(X,Y,K);
X := X + 1;
END LOOP;
COMMIT;
END;
CURSOR
CURSOR < nome-cursor > [parâmetro tipo,...] IS
< comando select >
EXEMPLO:
CURSOR MEU_CURSOR IS
SELECT ENAME, EMPNO, SAL FROM EMP
ORDER BY SAL DESC;
EXEMPLO DE PL/SQL UTILIZANDO CURSOR
DECLARE
CURSOR C1 IS
SELECT ENAME, EMPNO, SAL FROM EMP
ORDER BY SAL DESC;
MY_ENAME CHAR(10);
MY_EMPNO NUMBER (4);
MY_SAL NUMBER (7,2);
BEGIN
OPEN C1;
FOR I IN 1..100 LOOP
FETCH C1 INTO MY_ENAME, MY_EMPNO, MY_SAL;
EXIT WHEN C1%NOTFOUND;
29
30
DBMS_OUPUT.PUT_LINE (MY_SAL || ‘ ‘ || MY_EMPNO || ‘ ‘ || MY_ENAME);
END LOOP;
CLOSE C1;
END;
EXCEÇÕES
SÃO USADAS NO PL/SQL PARA LIDAR COM QUAISQUER ERROS QUE OCORRAM DURANTE
A EXECUÇÃO DE UM BLOCO.
HÁ DOIS TIPOS DE EXECEÇÕES, AS DEFINIDAS INTERNAMENTE PELA PL/SQL E AS
DEFINIDAS PELO USUÁRIO.
NESTA PARTE VEREMOS APENAS ALGUMAS DELAS
SINTAXE:
EXCEPTION
WHEN <nome-exeception> THEN
<comandos>;
WHEN <nome-execetion> THEN
<comandos>;
EXEMPLO EXCEPTION
NO_DATA_FOUND -
Quando um select não retorna nenhuma linha
TOO_MANY_ROWS - Quando um select retorna mais de uma linha
OTHERS - Qualquer tipo de erro
DECLARE
NOME CHAR(15);
CARGO
CHAR(10);
BEGIN
SELECT ENAME, JOB INTO NOME, CARGO
FROM EMP
WHERE EMPNO = 1111;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO PROBLEMA (ERRO,DTERRO)
VALUES (‘REGISTRO INEXISTENTE’,SYSDATE);
WHEN TOO_MANY_ROWS THEN
INSERT INTO PROBLEMA (ERRO,DTERRO)
30
31
VALUES (‘MUITOS REGISTROS’,SYSDATE);
WHEN OTHERS THEN
INSERT INTO PROBLEMA (ERRO,DTERRO)
VALUES (‘OUTRO ERRO QUALQUER’,SYSDATE);
END;
OUTRO EXEMPLO
DECLARE
NM VARCHAR2(30);
BEGIN
SELECT NOME INTO NM
FROM TESTE
WHERE IDADE=30;
DBMS_OUTPUT.PUT_LINE(NM);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('IDADE INEXISTENTE');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('MULTIPLAS LINHAS');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OUTRA SAIDA');
END;
RETORNANDO ERROS
SQLERR - Retorna o número do erro
SQLERRM – Retorna o número e a descrição do erro
Exemplo
BEGIN
INSERT INTO TESTE VALUES
(50,45,SYSDATE);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('ERRO - '||SQLERRM);
END;
EXCEPTION
■
DUP_VAL_ON_INDEX
■ Chave Duplicada
31
32
■
INVALID_CURSOR
■ Operação Ilegal com Cursor
■
INVALID_NUMBER
■ Conversão inválida p/numérico
■
LOGIN_DENIED
■ Usuário/Senha Inválida
■
NO_DATA_FOUND
■ Nenhuma linha retornada
■
NOT_LOGGED_ON
■ Usuário não conectado
■
OTHERS
Erro não declarado em exceptions
■
PROGRAM_ERROR
■ Problema Interno
■
STORAGE_ERROR
■ Falta de Memória
■
TIMEOUT_ON_RESOURCE
■ Tempo de espera
■
TOO_MANY_ROWS
■ Retorna Muitas Linhas
■
TRANSACTION_BACKED_OUT
■ Volta Atrás uma transação
■
VALUE_ERROR
■ Erro Conversão,Expressão
■
ZERO_DIVIDE
Divisão por zero
Exercício
Escreva um bloco PL/SQL para selecionar o nome do cliente tratando os erros de nenhum valor
encontrado e muitos valores enontrados. Adicione também uma exceção geral para cobrir outro erro que
venha a ocorrer.
Exceptions Definidas Pelo Usuário
Exemplo:
DECLARE
X NUMBER := 0;
Y NUMBER := 0;
ESTOURO EXCEPTION;
BEGIN
FOR X IN 1..1000 LOOP
Y:= X + (X/2);
IF Y > 1000 THEN
RAISE ESTOURO;
END IF;
32
33
END LOOP;
EXCEPTION
WHEN ESTOURO THEN
DBMS_OUTPUT.PUT_LINE('ESTOURO DE CAPACIDADE');
END;
PROCEDURE
SINTAXE:
PROCEDURE nome_procedure IS
variable1 datatype;
...
BEGIN
comandos;
...
EXCEPTION
when ...
END nome_procedure;
EXEMPLO PROCEDURE
CREATE OR REPLACE PROCEDURE CHECK_SALARY
(EMP_ID INTEGER, INCREASE REAL) IS
CURRENT_SALARY
REAL;
SALARY_MISSING
EXCEPTION;
BEGIN
SELECT SAL INTO CURRENT_SALARY FROM EMP
WHERE EMPNO = EMP_ID;
IF CURRENT_SALARY IS NULL THEN
RAISE SALARY_MISSING;
ELSE
UPDATE EMP SET SAL = SAL + INCREASE
WHERE EMPNO = EMP_ID;
EXCEPTION
WHERE NO_DATA_FOUND THEN
INSERT INTO EMP_AUDIT VALUES
(EMP_ID, ‘NO SUCH NUMBER’);
WHEN SALARY_MISSING THEN
(EMP_ID, ‘SALARY IS NULL’);
END CHECK-SALARY;
/
FORMA DE CHAMADA CHECK_SALARY(EMP_NUM,AMOUNT);
33
34
PACKAGE
É UM OBJETO DO DATABASE QUE CONTÉM UM GRUPO DE FUNÇÕES RELACIONADAS.
UM PACKAGE PODE CONTER:
•
PROCEDURES
•
FUNCTIONS
•
CURSOR DEFINITIONS
•
VARIABLES AND CONTANTS
•
EXCEPTION DEFINITIONS
EXEMPLO PACKAGE
CREATE REPLACE PACKAGE EMP_ACTION IS
PROCEDURE HIRE_EMPLOYEE (EMPNO NUMBER, ENAME CHAR, .....);
PROCEDURE FIRE_EMPLOYEE (EMP_ID NUMBER);
END EMP_ACTIONS;
/
CREATE OR REPLACE PACKAGE BODY EMP_ACTIONS IS
PROCEDURE HIRE_EMPLOYEE (EMPNO NUMBER, ENAME CHAR, .....); IS
BEGIN
INSERT INTO EMP VALUES (EMPNO, ENAME, .....);
END HIRE_EMPLOYEE;
PROCEDURE FIRE_EMPLOYEE(EMP_ID NUMBER) IS
BEGIN
DELETE FROM EMP WHERE EMPNO = EMP_ID;
END FIRE_EMPLOYEE;
END EMP_ACTIONS;
/
DATABASE TRIGGER
São programas PL/SQL, utilizados para completar a integridade, segurança e regras de negócios retirando
estas tarefas dos programas. Eles estão ligados a uma tabela e associado a um Evento.
CARACTERÍSTICA
34
35
. Disparado automaticamente quando a ocorrência do Evento
PARTES DE UM DATABASE TRIGGER
PARTE
DESCRIÇÃO
TRIGGER TYPE
BEFORE / AFTER (comando, linha)
TRIGGERING EVENT
INSERT / UPDATE / DELETE
TRIGGER RESTRICTION
WHEN (clause opcional)
TRIGGER ACTION
PL/SQL BLOCK
UTILIZAÇÃO – DATABASE TRIGGERS
TRIGGER TYPE
EXEMPLO DE UTILIZAÇÃO
BEFORE STATEMENT
Garantir segurança e integridade
BEFORE ROW
Calcular campos derivados para nova linha
AFTER ROW
Auditoria a nível de valor ou linha
AFTER STATEMENT
Auditoria em geral
PREDICADOS CONDICIONAIS EM DATABASE TRIGGERS
•
INSERTING
•
UPDATING
•
DELETING
35
36
EXEMPLO DATABASE TRIGGER COM PREDICADO CONDICIONAL
CREATE TRIGGER TOTAL_SALARY
AFTER DELETE OR INSERT OR UPDATE OF DEPTNO,SAL
ON EMP
FOR EACH ROW
BEGIN
IF DELETING THEN
UPDATE DEPT_BUDGET
SET TOTAL_SAL = TOTAL_SAL - : OLD.SAL
WHERE DEPTNO =:OLD.DEPTNO;
END IF;
IF INSERTING THEN
UPDATE DEPT_BUDGET
SET TOTAL_SAL = TOTAL_SAL + :NEW.SAL
WHERE DEPTNO =:NEW.DEPTNO;
END IF;
IF UPDATING THEN
UPDATE DEPT_BUDGET
SET TOTAL_SAL = TOTAL_SAL + (:NEW.SAL -:OLD.SAL);
WHERE DEPTNO =:OLD.DEPTNO;
END IF;
END;
ATIVANDO E DESATIVANDO DATABASE TRIGGER
Quando criamos um database trigger ele fica automaticamente ativado. As vezes temos necessidade de
desativá-los como por exemplo:
. Os objetos referenciados pelo database trigger não estão disponíveis;
. Vamos recuperar dados da Tabela
Podemos desativar/ativar um database trigger isoladamente ou todos relacionados a uma tabela específica.
ALTER TRIGGER [schema.] trigger_name {ENABLE | DISABLE }
EXEMPLO:
ALTER TRIGGER TRG_EMPRESA DISABLE;
ALTER TRIGGER TRG_EMPRESA ENABLE;
ALTER TABLE [schema.] table_name { ENABLE | DISABLE } ALL TRIGGERS
36
37
EXEMPLO:
ALTER TABLE EMPRESA DISABLE ALL TRIGGERS;
ALTER TABLE EMPRESA ENABLE ALL TRIGGERS;
ELIMINANDO UM DATABASE TRIGGER
DROP TRIGGER [ schema.] trigger_name
EXEMPLO:
DROP TRIGGER TRG_EMPRESA;
VIEW DO DIOCIONÁRIOS COM INFORMAÇÕES DE DATABASE TRIGGERS
. USER_TRIGGERS
. DBA_TRIGGERS
37
38
DATABASE TRIGGER – REPLICAÇÃO DE TABELA
CREATE TRIGGER REP_EMPRESA
BEFORE INSERT OR UPDATE OR DELETE
ON EMPRESA
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO EMPRESA@SPAULO
VALUES (:NEW.CD_EMPRESA,:NEW.NM_RAZAO_SOCIAL);
ELSIF UPDATING THEN
UPDATE EMPRESA@SPAULO
SET NM_RAZAO_SOCIAL =:NEW.NM_RAZAO_SOCIAL;
WHERE CD_EMPRESA =:OLD.CD_EMPRESA
ELSE
DELETE FROM EMPRESA@SPAULO
WHERE CD_EMPRESA =:OLD.CD_EMPRESA;
END IF;
END;
BIBLIOGRAFIA
.ELMASRI, Ramez e NAVATHE, ShamKant B. Sistemas de Banco de Dados - Fundamentos e
Aplicações. Ed. LTC. Rio de Janeiro, 2000.
.KORTH, Henry F. & SILBERSCHATZ, Abraham. Sistemas de Bancos de Dados, São Paulo.
Ed. Makron Books, 1999.
.DATE, C.J., Introdução a Sistemas de Bancos de Dados, Rio de Janeiro. Ed. Campus, 1991.
ABBEY, Michael. Oracle: guia do usuário / Michael Abbey, Michael J. Corey; tradução João
Eduardo Nóbrega Tortello; revisão técnica Marcus Faversani Hermman. São Paulo: Markon
Books, 1997.
SARAIVA, Armando. Programando em Oracle. Rio de Janeiro: Infobook, 1999.
38