Prova Substitutiva (14/12/04)

1)
create table movimentos (numero number primary key, data date,num_conta number, valor number);
create table contas (numero number primary key,saldo decimal, titular varchar2(50));

2) create sequence seq_num_movimento;

create or replace trigger trg_num_movimento
before insert on movimentos
for each row
begin
     select seq_num_movimento.nextval into :new.numero from dual;
end;

3)create or replace trigger trg_movto_saldo
after insert or update or delete on movimentos
for each row
declare
saldo number;
valor number;
begin
if inserting then
select valor into valor_movto
from movimentos
where numero=:new.numero;
end if;
update contas
set saldo = (saldo + (:new.valor)
where numero = :new.conta
end if;
end;

4)
insert into movimentos (numero,data,num_conta,valor) values (01,to_date('01/10/2004','dd/mm/yyyy'),6587,180);

5)
create or replace function func_movto (num_conta number)
return number is
begin
select num_conta into contas from movimentos
where num.contas=titular.contas
group by contas;
return (valor);
retunr (soma_valor);
end;


6)
create or replace procedure del_movto is
begin
for aux in(select * from movimentos)loop
delete movimentos where to_char(data,'ddd')<180
and movimentos.num_conta=aux.numero;
end loop;
commit;
end;