Package Calculadora

create or replace package pkgCalculadora is
 function suma(p1 number, p2 number) return number;
 function resta(p1 number, p2 number) return number;
 function multiplica(p1 number, p2 number) return number;
 function divide(p1 number, p2 number) return number;
end pkgCalculadora;

create or replace package body pkgCalculadora is
procedure imprime (pvalor number) is
begin
 dbms_output.put_line('Resultado: '|| pvalor);
end imprime;
function suma(p1 number, p2 number) return number is
Begin
 imprime(p1+p2);
 return p1+p2;
End suma;
function resta(p1 number, p2 number) return number is
Begin
 imprime(p1-p2);
 return p1-p2;
End resta;
function multiplica(p1 number, p2 number) return number is
Begin
 imprime(p1*p2);
 return p1*p2;
End multiplica;
function divide(p1 number, p2 number) return number is
Begin
  imprime(p1/p2);
  return p1/p2;
 EXCEPTION
  when Zero_divide then
   dbms_output.put_line ('Error !! Division por Cero');
End divide;
end pkgCalculadora;


declare
 r number(10);
begin
 r := pkgCalculadora.suma(2, 3);
end;

Trigger Para Actualizar Stock

create table Producto(idPro integer primary key, nombre varchar2(25), stockActual integer);
create table Compra(idCompra integer primary key, fecha date);
create table DetalleCompra(Cantidad integer, idCompra references Compra, idPro references Producto);

begin
insert into producto values(1,'Plasma',5);
insert into producto values(2,'MiniComponente',3);
insert into producto values(3,'Dvd',2);
insert into producto values(4,'Lavadora',5);
end;

begin
insert into Compra values(1,'06/06/2011');
insert into Compra values(2,'05/02/2010');
insert into Compra values(3,'05/02/2010');
insert into Compra values(4,'06/03/2012');
end;

begin
insert into DetalleCompra values(6,2,3);
insert into DetalleCompra values(8,1,4);
insert into DetalleCompra values(5,3,2);
insert into DetalleCompra values(7,4,1);
insert into DetalleCompra values(10,2,2);
end;

create or replace trigger actualizaStock
after insert on detalleCompra
for each row
begin
update Producto
set stockActual = stockActual + :new.cantidad
where idPro = :new.idPro;
end;

create table merma(idMerma integer, fecha date, idProducto integer, cantidad integer)
insert into merma values(1, '24/09/2009', 1, 10)
insert into merma values(2, '04/08/2109', 2, 3)

create or replace trigger cantidadMerma
after insert on merma
for each row
begin
update Producto
set stockActual = stockActual - :new.cantidad
where idPro = :new.idProducto;
end;