Clase 3 Funciones

Script Ejemplo de Funciones

--Inner Join

select * from dbo.cargo
inner join dbo.empleado
on cargo.codcargo=empleado.codcargo;

--Vista de Join

create view dbo.vistaemp as
select departamento.coddep,nomdep,cargo.codcargo,
nomcargo,salario,codemp,
primnom||' '||primapel as primapel,edad
 from dbo.departamento
inner join (dbo.cargo inner join dbo.empleado
on cargo.codcargo=empleado.codcargo)
on departamento.coddep=cargo.coddep;

select * from dbo.vistaemp;

update dbo.empleado
set codcargo='101-2',
segnom='jose'
where codemp='73121295';

---update

update dbo.departamento
set numcargo=numcargo+1
where coddep='101';

--Rutina Funcion Ejercicio 1

create or replace function
dbo.sumavoid(n integer,m integer)
returns void as $$
declare suma integer;
begin
suma:= n+m;
RAISE NOTICE 'suma=%',suma;
end
$$ language plpgsql;

select dbo.sumavoid(4,5);

Rutina Ejercicio 1 V2

create or replace function
dbo.sumavoidv2(n integer,m integer)
returns void as $$
declare suma integer;
begin
suma:= n+m;
RAISE NOTICE '%+%=%',n,m,suma;
end
$$ language plpgsql;
select dbo.sumavoidv2(4,5);


Rutina Ejericicio 2

create or replace
function dbo.areacirculo(radio double precision)
returns void as $$
declare
area double precision;
PI double precision;
begin
PI=3.1416;
area:= PI*(radio ^ 2);
RAISE NOTICE 'area circulo=%',area;
end
$$ language plpgsql;
select dbo.areacirculo(34.65);

rutina iva

CREATE OR REPLACE FUNCTION dbo.calculoivav1(compra double precision)
  RETURNS void AS $$
declare
iva double precision;
comprasal character(30);
ivasal character(30);
horasal character(30);
fecha date;
hora time;
begin
iva:=compra*0.19;
fecha=now();
hora=now();
comprasal:=to_char(compra,'LFM9,999,999,999,999.00');
ivasal:=to_char(iva,'LFM9,999,999,999,999.00');
horasal=to_char(now()::Time, 'HH12:MI:SS AM');
RAISE NOTICE 'ecompra %',comprasal;
RAISE NOTICE 'iva % ',ivasal;
RAISE NOTICE 'fecha %',fecha;
RAISE NOTICE 'hora %',horasal;
END;
$$ language plpgsql;


select dbo.calculoivav1(45000000);


funcion iva v2

CREATE OR REPLACE FUNCTION dbo.calculoivav2(compra double precision)
  RETURNS void AS $$
declare
iva double precision;
comprasal character(30);
ivasal character(30);
horasal character(30);
total double precision;
totalsal character(30);
fecha date;
hora time;
begin
iva:=compra*0.19;
total:=compra+iva;
fecha=now();
hora=now();
comprasal:=to_char(compra,'LFM9,999,999,999,999.00');
ivasal:=to_char(iva,'LFM9,999,999,999,999.00');
totalsal:=to_char(total,'LFM9,999,999,999,999.00');
horasal=to_char(now()::Time, 'HH12:MI:SS AM');
RAISE NOTICE 'ecompra %',comprasal;
RAISE NOTICE 'iva % ',ivasal;
RAISE NOTICE '--------------';
RAISE NOTICE 'Total:%',totalsal;

RAISE NOTICE 'fecha %',fecha;
RAISE NOTICE 'hora %',horasal;
END;
$$ language plpgsql;



select dbo.calculoivav1(45000000);

--if en funciones

create or replace function dbo.validaredad(edad integer)
returns void as $$
begin
IF(edad>=18) then
RAISE NOTICE 'MAYOR DE EDAD';
ELSE
RAISE NOTICE 'MENOR DE EDAD';
END IF;
END
$$ language plpgsql;


funcion edad v2

create or replace function dbo.validaredadv2(edad integer)
returns character(20) as $$
begin
IF(edad>=18) then
return 'MAYOR DE EDAD';
ELSE
return 'MENOR DE EDAD';
END IF;
END
$$ language plpgsql;

select dbo.validaredadv2(19);

Lados triangulo

create or replace function
dbo.tipotriangulo(l1 double precision,
l2 double precision,l3 double precision)
returns void as $$
begin
IF((l1=l2) and (l2=l3) and (l1=l3)) then
RAISE NOTICE 'Triangulo Equilatero';
ELSEIF ((l1<>l2) and (l2<>l3) and (l1<>l3)) then
RAISE NOTICE 'Triangulo Escaleno';
ELSE
RAISE NOTICE 'Triangulo Isosceles';
END IF;
END;
$$ language plpgsql;

select dbo.tipotriangulo(34.5,34.5,34.5);



funcion mostrar numero de cargos por departamentos

CREATE OR REPLACE FUNCTION dbo.reporte(
    coddepe character,
    nomdepe character)
  RETURNS character AS $$

declare
cont integer;
begin
cont:=(select count(codcargo) from dbo.cargo where coddep=coddepe);
RAISE NOTICE '*****************************';
RAISE NOTICE '****Codigo Departamento %',coddepe;
RAISE NOTICE '****Nombre Departamento %',nomdepe;
RAISE NOTICE 'Numero Cargo: %',cont;
RAISE NOTICE '*****************************';
return 'Codigo Departamento: '||coddepe|| ' Nombre Departamento: '||nomdepe||' Total: '||cont;
end;
 $$  LANGUAGE plpgsql;

 select dbo.reporte(coddep,nomdep) from dbo.departamento;



funcion crear empleado

create or replace function dbo.crearempleado(
codemp character(10),
primnom character(20),segnom character(20),
primapel character(20),segapel character(20),
codcargo character(10),edad integer)
returns integer as $$
begin
    insert into dbo.empleado values(codemp,primnom,segnom,
    primapel,segapel,codcargo,edad);
    return 1;
exception
    when unique_violation then
        return 0;
    when foreign_key_violation then
       return -1;   
end;
 $$ language plpgsql;