PRCTICA CALIFICADA N 12 y 13

--SECCION I

Crear las tablas anteriores en Oracle 11g con sus respectivas restricciones, teniendo en cuenta la integridad referencial. Luego insertar 5 registros como mnimo en cada Tabla.CREATE TABLE DESPACHOS(numero int primary key,capacidad int);

create table directores(dni char(8) primary key,nomApels varchar(40),DniJefe char(8),Despacho int,foreign key(Despacho) references DESPACHOS(numero));

alter table directores add(foreign key(DniJefe) references directores(dni));

----------------------------------insert into DESPACHOS values(1,20);insert into DESPACHOS values(2,30);insert into DESPACHOS values(3,40);insert into DESPACHOS values(4,50);insert into DESPACHOS values(5,60);------------------------------------insert into directores values('46937287','shirley obregon torres',null,1);insert into directores values('99223457','wendy rojas obregon',46937287,1);insert into directores values('98765434','justin ghio lopez',46937287,2);insert into directores values('87659045','kevin vega cuellar',null,4);insert into directores values('99878777','ariana mendez angeles',87659045,5);

--SECCION II

En base a las tablas anteriores se pide crear un procedimiento almacenado o funcin almacenada (segn sea el caso) para resolver cada una de las consultas siguientes:1. Mostrar el DNI, nombre y apellidos de todos los directores.

create or replace PROCEDURE GESTION.pre01iscursor c_dir is select dni,nomApels from GESTION.directores;begin for r_dir in c_dir loop dbms_output.put_line('DNI: ' || r_dir.dni); dbms_output.put_line('APELLIDOS Y NOMBRES: ' || r_dir.nomApels); end loop;end pre01;

2. Mostrar los datos de los directores que no tienen jefes.

create or replace PROCEDURE GESTION.pre02iscursor c_dir is select * from GESTION.directores WHERE DniJefe IS NULL;begin for r_dir in c_dir loop dbms_output.put_line('DNI: ' || r_dir.dni); dbms_output.put_line('APELLIDOS Y NOMBRES: ' || r_dir.nomApels); dbms_output.put_line('DESPACHO: ' || r_dir.Despacho); end loop;end pre02;

3. Mostrar el nombre y apellidos de cada director, junto con la capacidad del despacho en el que se encuentra

create or replace function GESTION.F_capacidad(numer number) return numberisdesp number;begin select capacidad into desp from GESTION.despachos where numero=numer; return desp;end;--llamar la funcionselect nomapels,GESTION.F_capacidad(despacho) Funcion_despachos from dbgestion.directores4. Mostrar el nmero de directores que hay en cada despacho.create or replace PROCEDURE GESTION.pre04iscursor c_dir is select despacho,count(DNI)NroDirectores from GESTION.directoresgroup by despacho;begin for r_dir in c_dir loop dbms_output.put_line('Despacho: ' || r_dir.despacho); dbms_output.put_line('Nro Directores: ' || r_dir.NroDirectores); end loop;end pre04;5. Mostrar los datos de los directores cuyos jefes no tienen jefes.create or replace PROCEDURE GESTION.pre05iscursor c_dir is select * from GESTION.directores WHERE DniJefe IS NULL;begin for r_dir in c_dir loop dbms_output.put_line('DNI: ' || r_dir.dni); dbms_output.put_line('APELLIDOS Y NOMBRES: ' || r_dir.nomApels); dbms_output.put_line('DESPACHO: ' || r_dir.Despacho); end loop;end pre05;6. Mostrar los nombres y apellidos de los directores junto con los de su jefecreate or replace PROCEDURE GESTION.pre06iscursor c_dir is

select d.dni,d.nomapels,(select distinct nomapels from GESTION.directores where dni=d.dnijefe) as jefefrom GESTION.directores d WHERE d.DniJefe IS not NULL;

begin for r_dir in c_dir loop dbms_output.put_line('DNI: ' || r_dir.dni); dbms_output.put_line('APELLIDOS Y NOMBRES: ' || r_dir.nomApels); dbms_output.put_line('JEFE: ' || r_dir.jefe); dbms_output.put_line('--------------------------------'); end loop;end pre06;7. Mostrar el nmero de despachos que estn sobre utilizadoscreate or replace PROCEDURE GESTION.pre07iscursor c_dir isSELECT DESPACHO,COUNT(DNI)CANTIDAD FROM DBGESTION.DIRECTORES DI INNER JOIN GESTION.DESPACHOS DEON DI.DESPACHO=DE.NUMEROGROUP BY DESPACHO,DE.CAPACIDADhaving COUNT(DNI)>DE.CAPACIDAD;begin for r_dir in c_dir loop dbms_output.put_line('despacho: ' || r_dir.DESPACHO); dbms_output.put_line('capacidad: ' || r_dir.CANTIDAD); end loop;end pre07;8. Anadir un nuevo director llamado Paco Prez, DNI 28301700, sin jefe, y situado en el despacho 124.create or replace PROCEDURE GESTION.pre08(dni varchar,nombres varchar,jefe varchar,despacho number)Asbegin insert into GESTION.directores values (dni,nombres,jefe,despacho);end pre08;--ejecutar el procedimiento para insertarbegin GESTION.pre08('28301700','Paco Prez',NULL,124);end;9. Asignar a todos los empleados apellidados Prez un nuevo jefe con DNI 74568521

create or replace PROCEDURE GESTION.pre09(apellidos varchar,jefe varchar)Asbegin update GESTION.directores set dnijefe=jefe where nomapels=apellidos;end pre09;--ejecutar el procedimiento para actualizarbegin GESTION.pre09('perez','74568521');end;10. Despedir a todos los directores, excepto a los que no tienen jefe

create or replace PROCEDURE GESTION.pre10Asbegin delete from GESTION.directores where dnijefe is not null;end pre10;

--ejecutar el procedimiento para actualizarbegin GESTION.pre10;end;

-----seccion III

create or replace package gestion.Preg010as function GESTION.F_directores(desp number) return number;end Preg010;create or replace package body gestion.Preg010as function GESTION.F_directores(desp number) return number is dir number; begin select count(*) into dir from GESTION.directores where despacho=desp; return dir; end;end Preg010;--llamar la funcionselect numero,Preg010.F_directores(numero)cantDirectores from gestion.despachos

-------------------------------------------------------------------create or replace package gestion.Preg009as PROCEDURE PrcPre009;end Preg009;create or replace package body gestion.Preg009as PROCEDURE gestion.PrcPre009 is begin delete from gestion.despachos where numero in((select numero from gestion.DESPACHOS des inner join gestion.directores di on des.numero=di.despacho group by numero,capacidad having capacidad 4)then raise_application_error(-20600,:new.dnijefe || 'no se puede terner mas de 7 directores'); end if; end;end Preg003;-----------------------------------------------------------------------------create or replace package gestion.Preg002as PROCEDURE PrcPre002;end Preg002;create or replace package body gestion.Preg002as PROCEDURE gestion.PrcPre002 is cursor c_dir is select dir.dni,(select count(dni) from gestion.directores where dnijefe=dir.dni)CanDir from gestion.directores dir where (select count(dni) from gestion.directores where dnijefe=dir.dni)>5 and dir.dnijefe is null; begin for r_dir in c_dir loop dbms_output.put_line('DNI: ' || r_dir.DNI); dbms_output.put_line('CANDIR: ' || r_dir.candir); end loop; end PrcPre002;end Preg002;----------------------------------------------------------------create or replace package gestion.Preg001as PROCEDURE PrcPre001;end Preg001;create or replace package body gestion.Preg001as create or replace PROCEDURE gestion.PrcPre001 is cursor c_dir is select numero,(capacidad - count(dni)) CapacidadDisponible from gestion.DESPACHOS des inner join gestion.directores di on des.numero=di.despacho group by numero,capacidad; begin for r_dir in c_dir loop dbms_output.put_line('NUMERO: ' || r_dir.numero); dbms_output.put_line('Cap Disponible: ' || r_dir.CapacidadDisponible); end loop; end PrcPre001;end Preg001;

----seccion IV

create table g.categoria(cat_id int primary key,cat_categoria varchar(40),cat_descripcion varchar(40));

create table g.producto(pro_id int primary key,cat_id int,pro_nombre varchar(40),pro_cantidad int,pro_unidad varchar(20),pro_precio numeric,pro_stockMinimo int,pro_fechavenc date,foreign key (cat_id) references g.categoria(cat_id));------------------------------------------------------------------------insert into GESTION.CATEGORIA values(1,'Lentejas','Menestras');insert into GESTION.CATEGORIA values(2,'Arroz','Arroz');insert into GESTION.CATEGORIA values(3,'Detergentes','Detergentes');insert into GESTION.CATEGORIA values(4,'Ferreteria','Ferreteria');insert into GESTION.CATEGORIA values(5,'Frutas','Frutas');insert into GESTION.CATEGORIA values(6,'Bebidas','Bebidas');insert into GESTION.CATEGORIA values(7,'Embutidos','Embutidos');---------------------------------------------------------------------select * from g.producto

INSERT INTO G.PRODUCTO VALUES(1,1,'lenteja',2,'kilos',5,2,TO_DATE('26-06-2014 07:01','DD-MM-YYYY HH:MI'));INSERT INTO G.PRODUCTO VALUES(2,1,'alberjas',2,'kilos',4,3,TO_DATE('26-06-2014 07:01','DD-MM-YYYY HH:MI'));INSERT INTO G.PRODUCTO VALUES(3,2,'tomate',5,'kilos',2,1,TO_DATE('26-06-2014 07:01','DD-MM-YYYY HH:MI'));INSERT INTO G.PRODUCTO VALUES(4,3,'ariel',6,'kilos',4,2,TO_DATE('26-06-2014 07:01','DD-MM-YYYY HH:MI'));INSERT INTO G.PRODUCTO VALUES(5,3,'opal',10,'kilos',4,2,TO_DATE('26-06-2014 07:01','DD-MM-YYYY HH:MI'));INSERT INTO G.PRODUCTO VALUES(6,4,'martillo',1,'caja',20,1,TO_DATE('26-06-2014 07:01','DD-MM-YYYY HH:MI'));INSERT INTO G.PRODUCTO VALUES(7,4,'clavos',5,'kilos',10,1,TO_DATE('26-06-2014 07:01','DD-MM-YYYY HH:MI'));INSERT INTO G.PRODUCTO VALUES(8,5,'manzanas',1,'caja',10,3,TO_DATE('26-06-2014 07:01','DD-MM-YYYY HH:MI'));INSERT INTO G.PRODUCTO VALUES(9,6,'gaseosa',1,'paquete',1,1,TO_DATE('26-06-2014 07:01','DD-MM-YYYY HH:MI'));INSERT INTO G.PRODUCTO VALUES(10,6,'cerveza',1,'caja',50,1,TO_DATE('26-06-2014 07:01','DD-MM-YYYY HH:MI'));INSERT INTO G.PRODUCTO VALUES(11,7,'hogdog',4,'embutidos',5,1,TO_DATE('26-06-2014 07:01','DD-MM-YYYY HH:MI'));

1. Mostrar todos los productos con sus respectivas categoras ordenadas alfabticamente

create or replace package g.Pro1as PROCEDURE Pro01;end Pro01;create or replace package body db.P1as PROCEDURE gestion.PrcPre001 is cursor c_prod is select cat.cat_categoria,prod.pro_nombre,prod.pro_cantidad,prod.pro_unidad,prod.pro_precio,prod.pro_fechavenc from DB.categoria cat inner join DB.producto prod on cat.cat_id=prod.cat_id; begin for r_pro in c_prod loop dbms_output.put_line('categoria ' || r_pro.cat_categoria); dbms_output.put_line('producto ' || r_pro.pro_nombre); dbms_output.put_line('cantidad ' || r_pro.pro_cantidad); dbms_output.put_line('producto ' || r_pro.pro_unidad); dbms_output.put_line('producto ' || r_pro.pro_precio); dbms_output.put_line('producto ' || r_pro.pro_fechavenc); end loop; end Pro01;end Pro1;

2. Mostrar los productos ms caros

create or replace PROCEDURE g.Pro2 is cursor c_prod is select cat_categoria,prod.pro_nombre,prod.pro_cantidad,prod.pro_unidad,prod.pro_precio,prod.pro_fechavenc from gestion.categoria cat inner join DB.producto prod on cat.cat_id=prod.cat_id where ROWNUM 3; begin for r_pro in c_prod loop dbms_output.put_line('categoria ' || r_pro.cat_categoria); dbms_output.put_line('cantidad >3 ' || r_pro.cantidad); end loop; end Pro10;

LABORATORIO 13

SESION Icreate table system.pieza(codigo int primary key,nombre varchar(100))

create table system.proveedores(id char(4) primary key,nombre varchar(100))

create table system.suministra(codigo int,idproveedor char(4),precio decimal(7,2),foreign key (codigo)references system.pieza(codigo),foreign key (idproveedor)references system.proveedores(id))

select * from system.suministra;select * from system.pieza;select * from system.proveedores;-----------------------------------------ingresar registro PIEZASinsert into SYSTEM.PIEZA values(1,'MONITORES');insert into SYSTEM.PIEZA values(2,'MOUSE');insert into SYSTEM.PIEZA values(3,'LENTES');insert into SYSTEM.PIEZA values(4,'DISCO DURO');insert into SYSTEM.PIEZA values(5,'PLACA');insert into SYSTEM.PIEZA values(6,'PROCESADOR');insert into SYSTEM.PIEZA values(7,'MICRO SD');insert into SYSTEM.PIEZA values(8,'CPU');insert into SYSTEM.PIEZA values(9,'CASE');insert into SYSTEM.PIEZA values(10,'TECLADO');insert into SYSTEM.PIEZA values(11,'TECLADO');-----------------------------------------ingresar registro PROVEEDORinsert into SYSTEM.PROVEEDORES values('pr01','HP');insert into SYSTEM.PROVEEDORES values('pr02','SAMSUNG');insert into SYSTEM.PROVEEDORES values('pr03','LG');insert into SYSTEM.PROVEEDORES values('pr04','DELL');insert into SYSTEM.PROVEEDORES values('pr05','ASUS');insert into SYSTEM.PROVEEDORES values('pr06','APPLE');insert into SYSTEM.PROVEEDORES values('pr07','TOSHIBA');insert into SYSTEM.PROVEEDORES values('pr08','THINKPAD');insert into SYSTEM.PROVEEDORES values('pr09','AMD');insert into SYSTEM.PROVEEDORES values('pr10','INTEL');-----------------------------------------ingresar registro suminstrainsert into SYSTEM.suministra values (1,'pr10',12.3);insert into SYSTEM.suministra values (2,'pr09',15.3);insert into SYSTEM.suministra values (3,'pr08',11.3);insert into SYSTEM.suministra values (4,'pr07',19.3);insert into SYSTEM.suministra values (5,'pr06',21.3);insert into SYSTEM.suministra values (6,'pr05',34.3);insert into SYSTEM.suministra values (7,'pr04',22.3);insert into SYSTEM.suministra values (8,'pr03',56.3);insert into SYSTEM.suministra values (9,'pr02',34.3);insert into SYSTEM.suministra values (10,'pr01',23.3);

SESION II

1. Obtener los nombres de todas las piezas.create or replace procedure system.proc01ascursor c_proc01 is select * from system.pieza;begin for r_proc01 in c_proc01 loop dbms_output.put_line('CODIGO ' || r_proc01.CODIGO); dbms_output.put_line('CODIGO ' || r_proc01.NOMBRE); end loop;end; begin system.proc01; end;2. Obtener todos los datos de todos los proveedores.create or replace procedure system.proc02ascursor c_proc02 is select * from system.proveedores;begin for r_proc02 in c_proc02 loop dbms_output.put_line('ID ' || r_proc02.ID ); dbms_output.put_line('NOMBRE ' || r_proc02.NOMBRE ); end loop;end; begin system.proc02; end;3. Obtener el precio medio al que se nos suministran las piezas.create or replace procedure system.proc03as

cursor c_media is select avg(precio)as medio from SYSTEM.suministra; med c_media %rowtype; begin open c_media; fetch c_media into med; dbms_output.put_line('medio' || ' ' || med.medio); close c_media; end;

begin system.proc03;end;4. Obtener los nombres de los proveedores que suministran la pieza 1.create or replace procedure system.proc04ascursor c_proc04 is select p.nombre from system.suministra s inner join system.proveedores p on s.idproveedor=p.id inner join system.pieza pi on s.codigo=pi.codigo;begin for r_proc04 in c_proc04 loop dbms_output.put_line('proveedor ' || r_proc04.nombre ); end loop;end; begin system.proc04; end;5. Obtener los nombres de las piezas suministradas por el proveedor cuyo cdigo es HAL.create or replace procedure system.proc05ascursor c_proc05 is select PI.NOMBRE from system.suministra s inner join system.proveedores p on s.idproveedor=p.id inner join system.pieza pi on s.codigo=pi.codigo where p. id='HAL';begin for r_proc05 in c_proc05 loop dbms_output.put_line('PIEZAS ' || r_proc05.NOMBRE ); end loop;end; begin system.proc05; end;6. Obtener los nombres de los proveedores que suministran las piezas ms caras, indicando el nombre de la pieza y el precio al que la suministran.create or replace procedure system.proc06ascursor c_proc06 is select p.nombre proveedor,pi.nombre pieza,s.precio from system.suministra s inner join system.proveedores p on s.idproveedor=p.id inner join system.pieza pi on s.codigo=pi.codigo order by precio desc;begin for r_proc06 in c_proc06 loop dbms_output.put_line('proveedor ' || r_proc06.proveedor ); dbms_output.put_line('pieza ' || r_proc06.pieza ); dbms_output.put_line('precio ' || r_proc06.precio ); end loop;end; begin system.proc06; end;7. Aumentar los precios en una unidad.create or replace procedure system.proc07(aumento decimal)asbegin update system.suministra set precio=precio + aumento;end; begin system.proc07(10); end;

SESION III

--1. declare cursor c_prov is select * from system.proveedores;cursor c_pieza(prov char) is select pi.nombre pieza,s.precio from system.suministra s inner join system.pieza pi on s.codigo=pi.codigo where s. idproveedor=prov;begin for r_prov in c_prov loop dbms_output.put_line('proveedor '||r_prov.nombre); for r_pie in c_pieza(r_prov.id) loop dbms_output.put_line('pieza '||r_pie.pieza); dbms_output.put_line('precio '||r_pie.precio); end loop; dbms_output.put_line('----------------------------------------------'); end loop;end;--2.

declare cursor c_pieza is select * from system.pieza;cursor c_prov(pie char) is select p.nombre proveedor,s.precio from system.suministra s inner join system.proveedores p on s.idproveedor=p.id where s.codigo=pie;begin for r_pie in c_pieza loop dbms_output.put_line('pieza '||r_pie.nombre); for r_prov in c_prov(r_pie.codigo) loop dbms_output.put_line('proveedor '||r_prov.proveedor); dbms_output.put_line('precio '||r_prov.precio); end loop; dbms_output.put_line('----------------------------------------------'); end loop;end;

--3. create or replace trigger system.Tig003 before insert on system.pieza for each row declare dir integer:=1; cursor pie is select * from system.pieza; begin for r_ll in pie loop if (r_ll.nombre=:new.nombre)then dir:=2; end if; end loop; if(dir=2)then raise_application_error(-20600,:new.nombre || 'este director esta registrado'); end if;end;

--4. create or replace function system.Fun_aumento10(precio number) return number is aumento number; begin aumento:=precio+(precio*0.10); return aumento;end;

select p.nombre proveedor,pi.nombre pieza,s.precio,system.Fun_aumento10(s.precio) aumento10 from system.suministra s inner join system.proveedores p on s.idproveedor=p.idinner join system.pieza pi on s.codigo=pi.codigo order by precio desc;

--5.

create or replace procedure pro005ascursor c_pre005 is select p.nombre proveedor,count(pi.codigo) from system.suministra s inner join system.proveedores p on s.idproveedor=p.idinner join system.pieza pi on s.codigo=pi.codigogroup by p.nombrehaving count(pi.codigo)>2;begin for r_pre005 in c_pre005 loop dbms_output.put_line('proveedor '||r_pre005.proveedor); end loop;end;begin system.pro005;end;

--6. declarecursor c_prov(proid char) is select * from SYSTEM.proveedores where id=proid; regis c_prov %rowtype;begin open c_prov('pr01'); fetch c_prov into regis; dbms_output.put_line('medio' || ' ' || regis.id); dbms_output.put_line('medio' || ' ' || regis.nombre); close c_prov; end;

--7.

create or replace procedure pre007ascursor c_pre007 is select p.nombre proveedor from system.suministra s inner join system.proveedores p on s.idproveedor=p.idinner join system.pieza pi on s.codigo=pi.codigogroup by p.nombrehaving count(pi.codigo)=02;begin for r_pre007 in c_pre007 loop dbms_output.put_line('proveedor '||r_pre007.proveedor); end loop;end;begin system.pre007;end;

--9. create or replace procedure system.pre009ascursor c_pre009 is select p.nombre proveedor,s.precio,pi.nombre from system.suministra s inner join system.proveedores p on s.idproveedor=p.idinner join system.pieza pi on s.codigo=pi.codigo order by s.precio desc;begin for r_pre009 in c_pre009 loop dbms_output.put_line('proveedor '||r_pre009.proveedor); dbms_output.put_line('precio '||r_pre009.precio); dbms_output.put_line('pieza '||r_pre009.nombre); end loop;end;begin system.pre009;end;

--10.- crecreate or replace procedure system.pre010(con char,id_i int,nombre_i varchar)asbegin if con='insertar' then insert into system.pieza values (id_i,nombre_i); end if; if con='actualizar' then update system.pieza set nombre=nombre_i where codigo=id_i; end if; if con='eliminar' then delete from system.pieza where codigo=11; end if;end;declarealta char(10):='insertar';modif char(10):='actualizar';elim char(10):='eliminar';begin system.pre010(alta,11,'carbons'); system.pre010(modif,11,'carbones de fierror'); system.pre010(elim,11,'adios registro');end;