PostgreSQL: Funciones, Procedimientos y Vistas
El diagrama siguiente es la consecuencia de la creación de la base de datos con el script descrito en PostgreSQL: Sentencias DDL (1), Es un modelo pequeño, pero servirá para las futuras demostraciones con postgres.
Descripción: Una universidad realiza el registro de contactos, que son las personas que podrían ser potenciales postulantes a diferentes carreras. Los postulantes deberán pertenecer a un periodo académico y deberán elegir una modalidad asì como la carrera a la que quieren postular. Por lo general en un año solo existen dos periodos académicos, por ejemplo los periodos académicos del año 2008 fueron: 2008-1 y 2008-2. El contacto debe tener los datos personales del sujeto asì como la fecha de creación. El postulante debe tener registrado si asistió a su exámen de admisión, y si ingresó o no, debe tener también establecido el puntaje que alcanzó en el exámen.
Con esos datos podemos comenzar a elaborar nuestras primeras consultas usando instrucciones DML.
Antes de ir al grano, aclaro que acostumbro crear funciones cuando se trata de retornar datos, como por ejemplo una consulta que utilice la cláusula SELECT, y procedimientos almacenados únicamente cuando se trata de realizar una operación que no retorne ningún dato, por ejemplo, INSERT, UPDATE o DELETE.
En SQLServer se suele utilizar un Procedimiento Almacenado para cualquiera de los 2 casos descritos, aunque en PostGres también se puede retornar datos con procedimientos almacenados, sería ideal respetar la funcionalidad de cada uno. Las funciones desarrolladas en éste post se realizará haciedno uso del lenguaje procedural ‘plpgsql’, que generalmente trae PostGreSQL.
Si aún no tienes preparado la base de datos, aqui les dejo un script. Es la unión de las DDL y DML creadas en posts anteriores. Ésta creará el entorno en el que se realizarán las demostraciones.
Descargar Script: DBAdmision.sql
FUNCIONES:
1) Cree una función que devuelva los siguientes datos: <ver>
| Periodo | 101 | 309 | 310 | Total |
| 2005-1 | 1 | 7 | 7 | 15 |
| 2005-2 | 0 | 13 | 12 | 25 |
| 2006-1 | 0 | 17 | 18 | 35 |
| 2006-2 | 0 | 23 | 22 | 45 |
| 2007-1 | 0 | 27 | 28 | 55 |
2) Cree una función que devuelva los siguientes datos: <ver>
| Periodo | 101 | 309 | 310 | Total |
| 2005-1 | 1 | 7 | 7 | 15 |
| 2005-2 | 0 | 13 | 12 | 25 |
| 2006-1 | 0 | 17 | 18 | 35 |
| 2006-2 | 0 | 23 | 22 | 45 |
| 2007-1 | 0 | 27 | 28 | 55 |
| TOTAL | 1 | 87 | 87 | 175 |
3) Cree una función que liste todos los postulantes de un determinado periodo académico. La lista debe reiniciar la numeración por orden alfabético. <ver>
| Nº | Apellidos y Nombres |
| 1 | Alanya Padilla Alina Susan |
| 2 | Alarcon Castro Gustavo Claudio Andres |
| … | … |
| 1 | Baldeón Balvín Olger |
| 2 | Baldeón Sanabria Natalia Ivonne |
| … | … |
| 1 | Cachuán Cámac Miguel Fernando |
| 2 | Cajachagua Chui Jose Arturo |
| … | … |
4) Cree una función que liste todos los postulantes, la lista debe estar enumerada y ordenada por apellido paterno, materno y nombres. <Requerimiento> <ver>
| Nº | Apellidos y Nombres |
| 1 | Alanya Padilla Alina Susan |
| 2 | Alarcon Castro Gustavo Claudio Andres |
| 3 | Alarco Lama Ricardo Rafael |
| … | … |
5) Cree una función que reciba como parámetro el id de un contacto y que muestra la siguiente información. Se debe de mostrar todos los periodos académicos y una X en todos aquellos en la cual el contacto postuló. <ver>
| Periodo | Postuló |
| 2005-1 | X |
| 2005-2 | |
| 2006-1 | |
| 2006-2 | |
| 2007-1 | X |
6) Cree una función que muestre un resumen como el siguiente: <ver>
| Apellidos y Nombres | 2005-1 | 2005-2 | 2006-1 | 2006-2 | 2007-1 | 2007-2 | TOTAL |
| Alanya Padilla Alina Susan | X | 1 | |||||
| Alarcon Castro Gustavo Claudio Andres | X | 1 | |||||
| Alarco Lama Ricardo Rafael | X | 1 | |||||
| Altez Yanez Jasmin Gabriela | X | 1 | |||||
| Amable Salva Katerin Lisbet | X | 1 | |||||
| … |
7) Cree una función que muestre un resumen como el siguiente: <ver>
| Puntaje | 2005-1 | 2005-2 | 2006-1 | 2006-2 | 2007-1 | 2007-2 | TOTAL |
| 0 | 1 | 3 | 1 | 2 | 2 | 9 | |
| 12 | 1 | 1 | |||||
| 13 | 1 | 1 | |||||
| 14 | 1 | 1 | |||||
| 21 | 1 | 1 | 2 | ||||
| 23 | 1 | 3 | 4 | ||||
8.) Cree una función que liste todos aquellos postulantes (Apellidos, Nombres, Puntaje) cuyo puntaje es mayor o igual al puntaje promedio obtenido en todos los exámenes. <ver>
VISTAS:
9) Cree una vista que liste a todos aquellos postulantes que no asistieron al examen. Muestre los nombres, Apellidos, edad y género en formato (Varón/Mujer). <ver>
10) Cree una vista que liste a todos aquellos postulantes que ingresaron cuyo cumpleaños sea mañana (Entiéndase mañama como el dia después de la fecha actual). Muestre nombres y apellidos. <ver>
11) Cree una vista que muestre un resumen como el siguiente: <ver>
| Nombre | 2005-1 | 2005-2 | 2006-1 | 2006-2 | 2007-1 | TOTAL |
| Administración | 5 | 8 | 12 | 14 | 23 | 62 |
| Contabilidad | 6 | 7 | 13 | 17 | 22 | 65 |
| Ingeniería Informática | 0 | 0 | 0 | 0 | 0 | 0 |
| TOTAL | 11 | 15 | 25 | 31 | 45 | 127 |
PROCEDIMIENTOS ALMACENADOS:
12) Cree un procedimiento almacenado que permita eliminar los postulantes registrados correspondientes a una modalidad. <ver>
13)Cree un procedimiento almacenado que permita registrar a un nuevo contacto. <ver>
14)Cree un procedimiento almacenado que permita actualizar los datos de un postulante. <ver>
TOPIC:
FUNCTION, PROCEDURE, VIEW *Post no concluido…
Ya esta muy trillado tus ejercicios man…
esas las resolvi hace tiempo…actualizate.
CONSULTAS:
–1
select idperAcad
,sum (case when idCarrera =’101′ then 1 else 0 end) as ‘101′
,sum (case when idCarrera =’309′ then 1 else 0 end) as ‘309′
,sum (case when idCarrera =’310′ then 1 else 0 end)as ‘310′
,count (idpostulante) as TOTAL
from persona.postulante
group by idperacad
–2
select IDPerAcad as periodo,[101], [309] ,[310],[101]+[309]+[310]AS TOTAL
from
(select idcarrera,IDPerAcad from persona.postulante ) s
PIVOT
( COUNT(idcarrera)
FOR idcarrera IN ([101], [309] ,[310] ) ) qq
UNION
select ‘total’
,sum([101]), sum([309]),sum([310])
,sum([101]+[309]+[310])as total
from (
select IDPerAcad,idcarrera
from persona.postulante
) as data
PIVOT
(
count(idcarrera)–lo q va ir dentro de los casilleros
FOR idcarrera IN
([101], [309] ,[310] )
) as PVT
–3
select ROW_NUMBER() OVER(partition by substring(persona.contacto.paterno,1,1) ORDER BY persona.contacto.paterno
,persona.contacto.Nombres) [Nro],persona.contacto.Paterno+’ ‘+ persona.contacto.nombres as [Apellidos y Nombres]
from persona.contacto inner join persona.postulante on persona.contacto.idcontacto=persona.postulante.idcontacto
where persona.postulante.idperacad= ‘2007-1′
group by persona.contacto.Paterno,persona.contacto.Nombres
–4
–PREGUNTA 4: Listar a todos aquellos postulantyes cuyo puntaje es mayor o igual
–al puntaje promedio obtenido en los examenes
SELECT Persona.Contacto.Paterno, Persona.Contacto.Materno, Persona.Contacto.Nombres, Persona.Postulante.Puntaje
FROM Persona.Postulante INNER JOIN
Persona.Contacto ON Persona.Postulante.IDContacto = Persona.Contacto.IDContacto
where Persona.Postulante.Puntaje >=(
select avg(cast(puntaje as decimal)) from persona.postulante
where asistioexamen=’1′)
–5
–PREGUNTA 5:Listar a todos aquellos postulantyes cuyo puntaje es mayor o igual
–al puntaje promedio obtenido en el periodo academico
SELECT Persona.Contacto.Paterno, Persona.Contacto.Materno, Persona.Contacto.Nombres, Persona.Postulante.Puntaje
FROM Persona.Postulante INNER JOIN
Persona.Contacto ON Persona.Postulante.IDContacto = Persona.Contacto.IDContacto
where Persona.Postulante.Puntaje >=(
select avg(cast(puntaje as decimal)) from persona.postulante
where asistioexamen=’1′ and idperAcad=’2005-1′ )
–6
–PREGUNTA 6: Vista que liste a todos aquellos postulantes que nunca postularon
create view v_postulante
as
select c.nombres as Nombres,c.paterno +’ ‘+ c.materno as Apellidos, case c.genero
when ‘1′ then ‘Varon’
when ‘0′ then ‘Mujer’
end as Genero
,datediff(year,c.fechanac,getdate()) as Edad from persona.contacto as c
where not exists (select * from persona.postulante as p
where c.idcontacto = p.idcontacto)
go
select * from v_postulante
–7
–PREGUNTA 7:Vista de postulantes que ingresaron cuyo cumpleaños sea mañana
create view v_fechacumpleaños
as
SELECT Persona.Contacto.Nombres, Persona.Contacto.Paterno+’ ‘+ Persona.Contacto.Materno as Apellidos
, Persona.Postulante.Ingreso, Persona.Contacto.FechaNac
FROM Persona.Contacto INNER JOIN
Persona.Postulante ON Persona.Contacto.IDContacto = Persona.Postulante.IDContacto
where ingreso=’1′ and month(Persona.Contacto.FechaNac)=month (getdate()) and
day(Persona.Contacto.FechaNac)=day((getdate()+2))
go
select * from v_fechacumpleaños
–8
create procedure periodoacad
@acad varchar(6)
as
select row_number() over ( order by (
Paterno ))as [N°], Paterno + ‘ ‘+ Materno+’ ‘ + Nombres as [Apellidos y Nombres]
from persona.contacto p inner join persona.postulante pp on p.idcontacto = pp.idcontacto
where pp.idperacad = @acad
order by [Apellidos y Nombres]
exec periodoacad ‘2006-1′
–9
create schema funciones
go
create function funciones.postul(@cod int)
returns table
return
(
select pp.idperacad,
mAX(case when pp.idcontacto=@cod
then ‘x’ else ”end)as postulo
from personA.contacto as pc inner join persona.postulante as pp
on pc.idcontacto=pp.idcontacto
group by pp.idperacad
)
go
select * from funciones.postul(1)
–10
create procedure usp_nombre_periodo
as
SELECT Paterno +’ ‘+ Materno+’ ‘ + Nombres as [Apellidos y Nmbres]
,sum (case persona.postulante.idperacad when ‘2005-1′ then 1 else ‘ ‘ end) as ‘2005-1′
,sum (case persona.postulante.idperacad when ‘2005-2′ then 1 else ‘ ‘ end) as ‘2005-2′
,sum (case persona.postulante.idperacad when ‘2006-1′ then 1 else ‘ ‘ end) as ‘2006-1′
,sum (case persona.postulante.idperacad when ‘2006-2′ then 1 else ‘ ‘ end) as ‘2006-2′
,sum (case persona.postulante.idperacad when ‘2007-1′ then 1 else ‘ ‘ end) as ‘2007-1′
,sum (case persona.postulante.idperacad when ‘2007-2′ then 1 else ‘ ‘ end) as ‘2007-2′
,count (persona.postulante.idcontacto) as TOTAL
FROM Persona.Contacto INNER JOIN
Persona.Postulante ON Persona.Contacto.IDContacto = Persona.Postulante.IDContacto
group by Persona.Contacto.Paterno,Persona.Contacto.materno,Persona.Contacto.nombres
order by [Apellidos y Nmbres]
go
exec usp_nombre_periodo
–11
–11. Cree un procedimiento almacenado que muestre un cuadro resumen como el siguiente:
create procedure usp_puntaje_periodo
as
SELECT Persona.Postulante.Puntaje
,sum (case persona.postulante.idperacad when ‘2005-1′ then 1 else ‘ ‘ end) as ‘2005-1′
,sum (case persona.postulante.idperacad when ‘2005-2′ then 1 else ‘ ‘ end) as ‘2005-2′
,sum (case persona.postulante.idperacad when ‘2006-1′ then 1 else ‘ ‘ end) as ‘2006-1′
,sum (case persona.postulante.idperacad when ‘2006-2′ then 1 else ‘ ‘ end) as ‘2006-2′
,sum (case persona.postulante.idperacad when ‘2007-1′ then 1 else ‘ ‘ end) as ‘2007-1′
,sum (case persona.postulante.idperacad when ‘2007-2′ then 1 else ‘ ‘ end) as ‘2007-2′
,count (persona.postulante.idcontacto) as TOTAL
FROM Persona.Contacto INNER JOIN
Persona.Postulante ON Persona.Contacto.IDContacto = Persona.Postulante.IDContacto
GROUP BY Persona.Postulante.Puntaje
ORDER BY Persona.Postulante.Puntaje
go
exec usp_puntaje_periodo
jaja, gracias por recordarme, estos dias me tocaba actualizar los post, y gracias por los procedimientos que resolviste, ….