gabarito_exercicios

Prévia do material em texto

Correção Exercícios PLSQL
Exercício 01 - Utilizando a modelagem da locadora de vídeo, monte:
1) Uma FUNÇÃO que mostre a última data de locação do código do filme passado como parâmetro
create or replace function func_ultima_data (p_cod_filme filmes.cod_filme%type)
return date is
tmp_data date;
begin
select max(data_locacao) into tmp_data
from locacao,fitas
where locacao.cod_fita = fitas.cod_fita
and fitas.cod_filme = p_cod_filme;
return(tmp_data);
end;
/
2) Uma PROCEDURE que atualize o preço da locação dependendo da categoria do Filme:
a. Musical – 10%
b. Drama – 15%
c. Outros – 5%
create or replace procedure atualiza_locacao is
begin
for dados in (select c.descricao,l.cod_fita,l.cod_cliente,l.data_locacao
 from categorias c,filmes f, fitas fi, locacao l
where c.cod=f.cod_categoria and 
f.cod_filme = fi.cod_filme and
fi.cod_fita = l.cod_fita) loop
if dados.descricao = ‘Musical’ then
update locacao
set valor = valor * 1.1
where dados.cod_fita = cod_fita and
dados.cod_cliente = cod_cliente and
dados.data_locacao = data_locacao;
elsif dados.descricao = ‘Drama’ then
update locacao
set valor = valor * 1.15
where dados.cod_fita = cod_fita and
dados.cod_cliente = cod_cliente and
dados.data_locacao = data_locacao;
else
update locacao
set valor = valor * 1.05
where dados.cod_fita = cod_fita and
dados.cod_cliente = cod_cliente and
dados.data_locacao = data_locacao;
end if;
end loop;
end;
/
3) Uma TRIGGER que realize a atualização automática do valor da locação quando for fim de semana. No sábado, as locações devem ser acrescidas em 10% e na segunda-feira, volta o valor ao normal.
CREATE or replace TRIGGER trg_locacao
 BEFORE INSERT ON locacao
 FOR EACH ROW
BEGIN
If to_char(:new.data_locacao,’d’) in (1,7) then
:new.valor:=:new.valor*1.1;
End if;
END;
/
4) Uma SEQUENCE e uma TRIGGER para preencher automaticamente a chave primária da tabela categoria
create sequence seq_pk_categoria start with 200;
create or replace trigger trg_cod_categoria
before insert on categorias
for each row
begin
select seq_pk_categoria.nextval into :new.cod from dual;
end;
/
5) Uma INSERÇÃO para a categoria AÇÃO.
insert into categorias (descrição) values (‘AÇÃO’);
commit;
Exercício 02 - Dadas os esquemas das tabelas abaixo:
PROJETOS (Cod, Descricao, Prioridade (1,2 ou 3))
FUNCIONARIOS (Matricula, Nome, Salario, Data_Admissao)
PARTICIPAM (Cod_Proj, Matricula, Data_Alocacao)
PARTICIPARAM (Cod_Proj, Matricula, Data_Alocacao, Tempo_alocacao)
Create table PROJETOS (Cod number, Descricao varchar2(4000), Prioridade number);
Create table FUNCIONARIOS (Matricula varchar2(100), Nome varchar2(4000), Salario number, Data_Admissao date);
Create table PARTICIPAM (Cod_Proj number , Matricula varchar2(100), Data_Alocacao date);
Create table PARTICIPARAM (Cod_Proj number, Matricula varchar2(100), Data_Alocacao date, Tempo_alocacao number);
Neste esquema a tabela PARTICIPAM mostra os funcionários que estão “atualmente” alocados aos respectivos projetos e a tabela PARTICIPARAM mostra os funcionários que já participaram de cada um dos projetos relacionados, as datas de alocação e o tempo alocado.
1)
Utilizando os comandos do PL/SQL do Oracle faça as seguintes declarações.
a)
Crie ‘SEQUENCES’ em PL/SQL para gerar automaticamente os Códigos do Projeto.
CREATE SEQUENCE seq_cod_projeto;
b)
Insira 3 registros para PROJETOS utilizando TRIGGERS para inserir as SEQUENCES geradas no exercício anterior.
create or replace trigger trg_projeto
before insert on projetos
for each row
begin
select seq_cod_projeto.nextval into :new.cod from dual;
end;
/
Insert into projetos (descricao,prioridade) values (‘Proj1’,1);
Insert into projetos (descricao,prioridade) values (‘Proj2’,2);
Insert into projetos (descricao,prioridade) values (‘Proj3’,3);
c)
Insira pelo menos 3 registros para FUNCIONARIOS, pelo menos 3 registros para PARTICIPAM e pelo menos 3 registros para PARTICIPARAM. 
Insert into funcionarios values (‘Matr01’,’Jose’,1000,sysdate-9000);
Insert into funcionarios values (‘Matr02’,’Joao’,10200,sysdate-11000);
Insert into funcionarios values (‘Matr03’,’Maria’,4000,sysdate-2000);
Insert into participam values (1,’Matr01’,sysdate-200);
Insert into participam values (2,’Matr02’,sysdate-200);
Insert into participam values (3,’Matr03’,sysdate-200);
Insert into participaram values (1,’Matr02’,sysdate-1000,30);
Insert into participaram values (2,’Matr03’,sysdate-1000,50);
Insert into participaram values (3,’Matr01’,sysdate-1000,100);
d)
Crie uma FUNÇÃO para gerar automaticamente o tempo de alocação do funcionário no projeto utilizando a seguinte regra
i)
Tempo de Alocação = Data do Sistema – Data_Alocacao 
Esta função deve ser utilizada durante a inserção de 1 registro em PARTICIPARAM. 
A data de alocação deve ser passada por parâmetro.
create or replace function func_tempo (p_data date)
return number is
tmp_tempo number;
begin
select sysdate – p_data into tmp_tempo from dual;
return (tmp_tempo);
end;
/
e)
Crie uma TRIGGER que calcule e atualize automaticamente o Salário de cada funcionário. O salário deve ser atualizado seguindo a seguinte regra:
i)
Se prioridade do Projeto no qual ele está alocado = 1  Salário é acrescido de 20%
ii)
Se prioridade do Projeto no qual ele está alocado = 3  Salário é acrescido de 5%
iii)
Considerar que quando a alocação do funcionário no projeto terminar (eliminação da linha de PARTICIPAM), o salário é automaticamente diminuído.
iv)
Não considerar alteração na prioridade do Projeto quando o funcionário já estiver alocado.
Esta TRIGGER deve ser disparada toda vez que for inserido ou apagado um novo registro em PARTICIPAM. 
create or replace trigger trg_atualiza_salario
after insert or delete on participam
for each row
declare
n_prioridade number;
begin
if inserting then
select prioridade into n_prioridade
from projetos
where cod = :new.cod_proj;
if n_prioridade = 1 then
update funcionarios set salario = salario *1.2 where matricula = :new.matricula;
end if;
if n_prioridade = 3 then
update funcionarios set salario = salario *1.05 where matricula = :new.matricula;
end if;
elsif deleting then
select prioridade into n_prioridade
from projetos
where cod = :old.cod_proj;
if n_prioridade = 1 then
update funcionarios set salario = salario *0.8 where matricula = :old.matricula;
end if;
if n_prioridade = 3 then
update funcionarios set salario = salario *0.95 where matricula = :old.matricula;
end if;
end if;
end;
/
f)
Crie uma PROCEDURE que efetue o término da participação de 1 funcionário em um projeto. Para esta PROCEDURE a matrícula e o código do projeto são passados como parâmetros. Esta PROCEDURE, além extrair esta informação de PARTICIPAM, irá preencher a tabela PARTICIPARAM com os dados desse funcionário e do projeto alocado (usar função desenvolvida no exercício d). 
create or replace procedure termino (pmat varchar2, pproj number) is
pdata date;
begin
select data_alocacao into pdata
from participam
where pmat=matricula and pproj=cod_proj;
Insert into participaram values (pproj,pmat,pdata,func_tempo(pdata));
Delete from participam where pmat=matricula and pproj=cod_proj;
Commit;
end;
/
Exercício 03 - Dadas os esquemas das tabelas abaixo:
ALUNOS (Ra, Nome, Data_Nasc, Endereço, Curso, CD)
DISCIPLINAS (Codigo, Nome, Num_Creditos)
CURSARAM (Ra, Cod_Disc, Lancamento_Nota_Final, Freqüência, Nota, Situação (A/R))
CURSAM (Ra, Cod_Disc)
Create table ALUNOS (ra number, data_nasc date, endereco varchar2(4000), curso number, cd number);
Create table DISCIPLINAS (codigo number, Nome varchar2(4000), num_creditos number);
Create table CURSAM (ra number , cod_disc number);
Create table CURSARAM (ra number , cod_disc number, lancamento_nota_final date, frequencia number, nota number, situacao char(1));
Neste esquema a tabela CURSAM mostra os alunos que estão “atualmente” cursandoas respectivas disciplinas e a tabela CURSARAM mostra os alunos que já cursaram cada uma das disciplinas relacionadas, as datas de lançamento da nota final (lancamento_Nota_Final), com as respectivas notas, Freqüência e Situação de aprovação.
1)
Utilizando os comandos do PL/SQL do Oracle faça as seguintes declarações.
a)
Crie ‘SEQUENCES’ em PL/SQL para gerar automaticamente os RA´s de alunos e Códigos das Disciplinas.
CREATE SEQUENCE seq_cod_disc;
CREATE SEQUENCE seq_ra;
b)
Insira 3 registros para ALUNOS e 3 registros para DISCIPLINAS, utilizando TRIGGERS para inserir as SEQUENCES geradas no exercício anterior.
create or replace trigger trg_alunos
before insert on alunos
for each row
begin
select seq_ra.nextval into :new.ra from dual;
end;
/
create or replace trigger trg_disc
before insert on disciplinas
for each row
begin
select seq_cod_disc.nextval into :new.codigo from dual;
end;
/
INSERT COMUM
c)
Insira pelo menos 3 registros para CURSARAM e pelo menos 3 registros para CURSAM.
INSERT COMUM
d)
Crie uma FUNÇÃO para gerar automaticamente a “Situação do Aluno” utilizando as seguintes regras
i)
Se Nota < 5,0 ou Freqüência < 0,75  Situação = ‘R’
ii)
Se Nota > 5,0 e Freqüência > 0,75  Situação = ‘A’
Esta função deve ser utilizada durante a inserção ou alteração de 1 registro em CURSARAM.
create or replace function situacao (p_matr number, p_disc number)
return char is
tmp_nota number;
tmp_frequencia number;
begin
select nota, frequencia into tmp_nota, tmp_frequencia 
from cursaram where ra=p_matr and cod_disc = p_disc;
if tmp_nota>5 and tmp_frequencia > 0.75 then
return(‘A’);
end if;
if tmp_frequencia<5 or tmp_frequencia < 0.75 then
return(‘R’);
end if;
end;
/
Exercício 04 - Dadas os esquemas das tabelas abaixo:
NOTA_FISCAL (Numero, Serie, Data, Peso_Total)
ITEM_NOTA (Numero, Serie, Item, Qtde, Valor_Unitario, Cod_Produto, valor_peso_rateado)
PRODUTO (Codigo, Descricao, Peso)
CREATE TABLE NOTA_FISCAL (Numero NUMBER, Serie NUMBER , Data DATE, Peso_Total NUMBER);
CREATE TABLE ITEM_NOTA (Numero NUMBER , Serie NUMBER , Item NUMBER , Qtde NUMBER , Valor_Unitario NUMBER , Cod_Produto NUMBER , valor_peso_rateado NUMBER);
CREATE TABLE PRODUTO (Codigo NUMBER , Descricao VARCHAR2(4000), Peso NUMBER);
1)
Utilizando os comandos do PL/SQL do Oracle faça as seguintes declarações.
a)
Crie ‘SEQUENCE’ em PL/SQL para gerar automaticamente os Números da NOTA FISCAL e o Código do PRODUTO
CREATE SEQUENCE seq_nota_fiscal;
CREATE SEQUENCE seq_produto;
b)
Crie Triggers que utilize sequences para inserir a chave primária. No caso da NOTA FISCAL a data também é inserida automaticamente com a data do sistema.
create or replace trigger trg_produto
before insert on produto
for each row
begin
select seq_produto.nextval into :new.codigo from dual;
end;
/
create or replace trigger trg_nf
before insert on nota_fiscal
for each row
begin
select seq_nota_fiscal.nextval, sysdate into :new.numero,:new.data from dual;
end;
/
c)
Crie uma FUNÇÃO para gerar automaticamente o valor total da nota fiscal. O valor total é calculado pela regra:
i)
Qtde * Valor Unitário dos itens da nota.
create or replace function func_valor_total (p_numero nota_fiscal.numero%type, p_serie nota_fiscal.serie%type)
return number is
tmp_valor_total number;
begin
select sum(qtde * valor_unitario) into tmp_valor_total
from item_nota
where numero = p_numero and
serie = p_serie;
return (tmp_valor_total);
end;
/
d)
Crie uma TRIGGER que mantenha atualizado automaticamente o peso total da nota fiscal. O peso total é calculado pela multiplicação da qtde do item da nota pelo peso do produto.
Não considerar alterações no peso, mas somente as movimentações de inclusão e exclusão dos itens da nota fiscal.
O item da Nota não pode ser alterado
create or replace trigger trg_atualiza_peso
after insert or delete on item_nota
for each row
declare
n_peso number;
begin
if inserting then
select peso into n_peso
from produto
where codigo = :new.cod_produto;
update nota_fiscal
set peso_total =peso_total+(:new.qtde*n_peso) 
where numero=:new.numero and serie=:new.serie;
elsif deleting then
select peso into n_peso
from produto
where codigo = :old.cod_produto;
update nota_fiscal
set peso_total =peso_total-(:old.qtde*n_peso) 
where numero=:old.numero and serie=:old.serie;
end if;
end;
/
e)
Crie uma PROCEDURE que gere o rateio dos pesos dos itens da nota fiscal. Essa procedure deve seguir as seguintes regras:
1.
Se a quantidade do item da nota for < 10, o valor do peso rateado é igual a 0
2.
Se a quantidade do item for >=10, o valor do peso rateado é igual a 1
create or replace procedure rateio is
begin
for dados in (select * from item_nota) loop
if dados.qtde<10 then
update item_nota 
set valor_peso_rateado=0
where 
numero=dados.numero and
serie = dados.serie and
item = dados.item;
else
update item_nota 
set valor_peso_rateado=1
where 
numero=dados.numero and
serie = dados.serie and
item = dados.item;
end if;
end loop;
commit;
end;
/