trigger con variables

create or replace
TRIGGER LIQUIDACIONPROCESADA
after insert on liquidacion
for each row
declare
  vRut empleado.rut%type;
  vIdEmpleado empleado.idempleado%type;
begin
select idempleado,rut into vIdEmpleado,vRut from empleado where idempleado = :new.idempleado;
insert into funcionariosprocesados(rut,glosa,idempleado) values(vRut,'Funcionario procesado',vIdEmpleado);
end;

Codigo Control, package con Funcion - Rodrigo Rojas

create or replace package pkDreamHome is
function CtaPropiedad (pNumEmpleado char) return number;
end pkDreamHome;
create or replace package body pkDreamHome is
 FUNCTION CtaPropiedad (pNumEmpleado char)
 RETURN NUMBER is
total char;
BEGIN
  select count(*) into total
  from Propiedad
  where numempleado = pNumEmpleado;
  RETURN total;
END CtaPropiedad;
end pkDreamHome;
select pkDreamHome.CtaPropiedad('SL21') from dual;

Funcion De Control - Propiedad por Empleado - Rodrigo Rojas

create or replace FUNCTION CtaPropiedad (pNumEmpleado char)
return integer is
total integer :=0;
begin
select count (numPropiedad)into total
from  Propiedad
where numEmpleado = pNumEmpleado;
return total;
end CtaPropiedad;
select CtaPropiedad('SL21')from dual;

Triggers y Sequence

create sequence seqLiquidacion
start with 1
increment by 1
nomaxvalue;

create trigger liquidacionIncr
before insert on liquidacion
for each row
begin
select seqLiquidacion.nextval into :new.idliquidacion from dual;
end;

Package Luis en clases

create or replace package pkgLuis is
  function cantidadEmpleados(pDepto IN employees.department_id%type) return number;
end pkgLuis;

create or replace
package body pkgLuis is
  FUNCTION cantidadEmpleados(
 pDepto IN employees.department_id%type
)
  RETURN NUMBER AS
 
  totalEmpleados integer;
  BEGIN
    select count(*) into totalEmpleados
    from Employees
    where department_id = 90;
    RETURN totalEmpleados;
  END cantidadEmpleados;
end pkgLuis;

select * from employees where department_id = 90;

set serveroutput on
begin
  dbms_output.put_line('Cantidad :'||pkgLuis.cantidadEmpleados(90));
end;
set serveroutput on
declare
  cantidad number;
begin
  select pkgLuis.cantidadEmpleados(90) into cantidad from dual;
  dbms_output.put_line('Cantidad :'||cantidad);
end;

select pkgLuis.cantidadEmpleados(90) from dual;

package - 06-06-2012

create or replace package pkg_uno is
function CUENTAEMPLEADOS (pDepto IN employees.department_id%type) return number;
end pkg_uno;

create or replace package body pkg_uno is
 FUNCTION CUENTAEMPLEADOS (pDepto IN employees.department_id%type)

 RETURN NUMBER AS
totalEmpleados integer;
BEGIN
  select count(*) into totalEmpleados
  from Employees
  where department_id = pDepto;
  RETURN totalEmpleados;
END CUENTAEMPLEADOS;
end pkg_uno;



    select pkg_uno.CUENTAEMPLEADOS(90) from dual;

Clase Trigger

create table Autor(
idAutor int,
nombre varchar(30),
constraint Pk_idAutor primary key (idAutor)  
);

begin
insert into Autor values(7);
insert into Autor VALUES(1);
insert into Autor VALUES(2);
insert into Autor VALUES(3);
insert into Autor VALUES(4);
insert into Autor VALUES(5);
end;

create table Libro(
IdLibro integer,
IdAutor integer,
constraint Pk_idLibro primary key (IdLibro),
constraint Fk_IdAutor foreign key (IdAutor) references Autor
);

begin
insert into Libro values(1, 1);
insert into Libro values(2, 1);
insert into Libro values(3, 2);
insert into Libro values(4, 3);
end;

create sequence seqAutor
start with 1 increment by 10
nocache
nocycle;

create or replace trigger MiPrimerTrigger
after delete on Autor
for each row
begin
  DELETE FROM Libro
  WHERE IdAutor = :old.IdAutor;
end;

begin
delete from Autor
where idAutor = 1;
end;

select * from Libro;