VISTAS

BASE DE DATOS II – CISC - UG

 Vistas
Una vista no es más que una consulta almacenada a fin
de utilizarla tantas veces como se desee.

Una vista no contiene datos en sí misma; es como una

ventana a través de la cual se pueden ver o cambiar los
datos de las tablas.

Las tablas sobre las cuales se basa una vista se llaman

tablas base.

Una vista suele llamarse también tabla virtual porque los

resultados que retorna y la manera de referenciarlas es la
misma que para una tabla.

Se almacenan en el Diccionario de Datos, USER_VIEWS.

Vistas - Aplicaciones

Realizar consultas complejas

más fácilmente, ya que Proporcionar tablas con
Proporcionar formas
permiten dividir la consulta datos completos, resultados
personalizadas y más
en varias subconsultas formatear o realizar cálculos
entendibles de los datos
(cada una de las cuales es sobre los datos originales
más sencilla que la original)

Ocultar el almacenamiento
intrínseco de la base de
Ser utilizadas como cursores
datos y conseguir una
Restringir el acceso a los de datos en los lenguajes
mayor independencia de
datos originales procedimentales (como
los datos respecto al resto
PL/SQL)
de elementos de la base de
datos.
Vistas - Tipos

Simples.
• Formada por una sola tabla y no contienen funciones de agrupación.
• Ventaja: Permiten siempre realizar operaciones DML sobre ellas.
• Características:
• Sólo pueden referirse a una tabla de la base de datos.
• No pueden contener funciones.
• No pueden contener ninguna cláusula de agrupación
• Admiten realizar sobre ellas operaciones DML.

Complejas.
• Obtienen datos de varias tablas, pueden utilizar funciones de agrupación y
de cualquier otro tipo. No siempre permiten operaciones DML.
Vistas – Sintaxis creación y
eliminación
Vistas – Ejemplos de creación
Creación de la vista “vListaProveedores” a partir de una consulta
a la tabla “suppliers”, la consulta recupera todas las columnas
de la tabla y el resultado aparece ordenado alfabéticamente
de acuerdo con el contenido de la columna “companyName”

create or replace view vListaProveedores as

select supplierId, companyname, contactname, cityId, phone
from suppliers
order by companyName;
Vistas – Ejemplos de creación
Creación de la vista “vListaProveedores y alias para cada una de las
columnas” a partir de una consulta a la tabla “suppliers”, la consulta
recupera todas las columnas de la tabla y el resultado aparece ordenado
alfabéticamente de acuerdo con el contenido de la columna
“companyName”

create or replace view vListaProveedores

(proveedorId, nombreCompania, nombreContacto, ciudadId, telefono) as
select supplierId, companyname, contactname, cityId, phone
from suppliers
order by companyName;
Vistas – Ejemplos de creación

Creación de la vista “vProductosOrdenados” a partir de las

tablas “products, orders, ordersdetail”, la consulta recupera
la cantidad de productos ordenados y su valor monetario.

create or replace view vProductosOrdenados as

select pro.productname, count(det.quantity) CantidadOrdenada,
sum(det.quantity * det.unitprice) Total
from products pro
inner join ordersdetail det on pro.productid = det.productid
inner join orders ord on det.orderid = ord.orderid
group by pro.productname
order by CantidadOrdenada asc;
 Eliminación de la
vista
Vistas – “vListaProveedores”
Ejemplos de
eliminación

Drop view vListaProveedores;

SECUENCIAS BASE DE DATOS II
CISC - UG
Secuencias

Una secuencia se emplea para generar

valores enteros secuenciales únicos y
asignárselos a campos numéricos.

Se utilizan generalmente para las claves

primarias de las tablas garantizando que
sus valores no se repitan.
 • La cláusula "start with" indica el valor desde el cual comenzará
la generación de números secuenciales.
Secuencias • Si no se especifica, se inicia con el valor que indique
Sintaxis "minvalue".

Creación • La cláusula "increment by" especifica el incremento; debe ser un

valor numérico entero positivo o negativo diferente de 0.
• Si no se indica, por defecto es 1.
CREATE SEQUENCE secuencia
• "maxvalue" define el valor máximo para la secuencia.
[INCREMENT BY n] • Si se omite, por defecto es 99999999999999999999999.
[START WITH n]
• "minvalue" establece el valor mínimo de la secuencia.
[{MAXVALUE n|NOMAXVALUE}] • Si se omite será 1.
[{MINVALUE n|NOMINVALUE}]
• La cláusula "cycle" indica que, cuando la secuencia llegue a
[{CYCLE|NOCYCLE}] máximo valor (valor de "maxvalue") se reinicie, comenzando
con el mínimo valor ("minvalue") nuevamente.
• Si se omite, por defecto la secuencia se crea "nocycle".

Si no se especifica ninguna cláusula, excepto el nombre de la secuencia,

por defecto, comenzará en 1, se incrementará en 1, el mínimo valor será 1,
el máximo será 999999999999999999999999999 y "nocycle".
 ALTER SEQUENCE secuencia
Secuencias [INCREMENT BY n]

Sintaxis [START WITH n]

[{MAXVALUE n|NOMAXVALUE}]

modificación [{MINVALUE n|NOMINVALUE}]

[{CYCLE|NOCYCLE}];
y DROP SEQUENCE secuencia;
eliminación
 create sequence sCodigoLibros
start with 1 increment by 1
maxvalue 99999 minvalue 1;
Secuencias
Ejemplos
drop sequence sCodigoLibros;
 create sequence sNumeroSocios
increment by 5
cycle;
Secuencias
Ejemplos Alter sequence sNumeroSocios
increment by 5 minvalue 1
maxvalue 30 cycle;

drop sequence sNumeroSocios;

 Secuencias – Pseudocolumnas
 Después de crear una secuencia, se puede acceder a sus valores con
sentencias SQL invocando el nombre de las siguientes pseudocolumnas:
 CURRVAL, devuelve el valor actual de la secuencia
 NEXTVAL, incrementa la secuencia y devuelve el nuevo valor.

 select sCodigoLibros.nextval from dual;

 select sCodigoLibros.currval from dual;
 select sCodigoLibros.currval from dual;

 insert into libros values (sCodigoLibros.nextval, 'El aleph', 'Borges','Emece');

Secuencias – Consideraciones

La primera vez que una consulta llama a una secuencia, se devuelve

un valor predeterminado.

Cuando se genera un número de secuencia, la secuencia se

incrementa, independientemente de la transacción confirmada o
retrotraída.

Si dos usuarios incrementan simultáneamente la misma secuencia,

entonces los números de secuencia que cada usuario adquiere
pueden tener huecos, ya que el otro usuario está generando números
de secuencia.
 ÍNDICES
BASE DE DATOS II – CISC - UG
Índices

Los índices son objetos asociados a columnas de tablas

que sirven para acelerar las operaciones de consulta y
ordenación.

Se almacenan aparte de la tabla a la que hace

referencia, lo que permite crearles y borrarles de forma
independiente respecto a la tabla.

Cada vez que se añade una nueva fila, los índices

involucrados se actualizan a fin de que su información
esté al día. De ahí que cuantos más índices haya, más le
cuesta a Oracle añadir nuevos datos.
Indices

Se aconseja crear índices en campos que:

• Contengan una gran cantidad de valores
• Sean parte habitual de cláusulas WHERE, GROUP BY u ORDER BY
• Sean parte de listados de consultas de grandes tablas sobre las que
casi siempre se muestran como mucho un 4% de su contenido.

No se aconseja en campos que:

• Pertenezcan a tablas pequeñas
• No se usen a menudo en las consultas
• Pertenecen a tablas cuyas consultas muestran, habitualmente, menos
de un 4% del total de registros
• Pertenecen a tablas que se actualizan frecuentemente
• Se utilizan en expresiones
Índices – Sintaxis Creación -
Eliminación

DROP INDEX NOMBRE_INDICE;

Índices – Ejemplos

CREATE INDEX iClientes_NombreCompleto

ON Clientes (apellidoPat, apellidoMat, nombre);

DROP INDEX iClientes_NombreCompleto;

SINÓNIMOS
BASE DE DATOS II CISC - UG
Sinónimos
Un sinónimo es un nombre alternativo para objetos como: tablas, vistas,
secuencias, procedimientos almacenados y otros objetos de base de datos.

Por lo general, se usan sinónimos cuando se desea otorgar acceso a un

objeto desde otro esquema y no se quiere que los usuarios tengan que
preocuparse por saber qué esquema es el propietario del objeto.

Recuerde:

• Se deben otorgar privilegios apropiados a un usuario antes de que el usuario pueda usar el
sinónimo.
• Ud. puede referirse a sinónimos en las siguientes declaraciones DML: select, insert, update,
delete, explain plan.
Sinónimos – creación
CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name
FOR [schema .] object_name [@ dblink];

OR REPLACE. - Allows you to recreate the synonym (if it already exists) without having to issue a
DROP synonym command.

PUBLIC.- It means that the synonym is a public synonym and is accessible to all users. Remember
though that the user must first have the appropriate privileges to the object to use the synonym.
•To create a private synonym in your own schema, you must have the CREATE SYNONYM system privilege.
•To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM system privilege.
•To create a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

Schema.- The appropriate schema. If this phrase is omitted, Oracle assumes that you are
referring to your own schema.

object_name.- The name of the object for which you are creating the synonym. It can be one of
the following:
•Table, view, sequence, stored procedure, function, package, java class schema object, user-defined object
Sinónimos – eliminación

DROP [PUBLIC] SYNONYM [schema .] synonym_name

[force];

PUBLIC.- Allows you to drop a public synonym. If you have

specified PUBLIC, then you don't specify a schema.

Force.- It will force Oracle to drop the synonym even if it

has dependencies. It is probably not a good idea to use
force as it can cause invalidation of Oracle objects.
Sinónimos - Ejemplos
 CLASE BD (14-01-2020)
begin

dbms_output.put_line('Hola mundo');

end;

---------------------------------

declare

lmensaje varchar(50) := 'Hola';

begin

dbms_output.put_line(lmensaje);

end;

---------------------------------

declare

ltable varchar2(30);

ltablespace varchar2(30);

begin

select table_name, tablespace.name

into ltable, ltablespace

from tabs;

end;

---------------------------------

declare

lciudad varchar2(50);

begin

select ciudadnombre into lciudad

 from ciudades

where ciudadid = 3;

dbms_output.put_line(lciudad);

end;

----------------------------------

declare

lciudad varchar2(50);

begin

select ciudadnombre into lciudad

from ciudades

where ciudadid = 333;

dbms_output.put_line(lciudad);

exception

when no_data_found then

dbms_output.put_line('Ciudad no existe');

end;
 CLASE (16-01-2020)
create sequence SEQCIUDADES MINVALUE 20;

create sequence SEQCOLORES MINVALUE 20;

create or replace procedure pInsertarColor(pColorNombre in varchar2)

is

vColorId number(3);

begin

select SEQCOLORES.nextval into vColorId from dual;

insert into colores(colorId, colorNombre)

values(vColorId, pColorNombre);

end;

----------------------------------------------------------------

//invocacion

declare

vColorNombre varchar2(50) := 'Purpura';

begin

PInsertarColor (vColorNombre);

end;

-----------------------------------------------------------------

create sequence SEQCIUDADES MINVALUE 20;

create or replace procedure pInsertarCiudades(pCiudades in varchar2)

is

vCiudadId number(3);

begin

select SEQCIUDADES.nextval into vCiudadId from dual;

insert into ciudades(ciudadId, ciudadNombre)

values (vCiudadId, pCiudades);

end;
-----------------------------------------------------------------

declare

vCiudadNombre varchar2(50) := 'New York';

begin

pInsertarCiudades(vCiudadNombre);

end;

------------------------------------------------------------------

create or replace procedure pInsertarCiudades(pCiudadNombre in

ciudades.ciudadNombre%type) is

begin

insert into ciudades(ciudadId, ciudadNombre)

values (SEQCIUDADES.nextval, pCiudadNombre);

end;

------------------------------------------------------------------------------------------------

create sequence SEQCONDUCTORES MINVALUE 20;

create or replace procedure pInsertarConductores(pCedula in conductores.cedula%type,

pNombre1 in conductores.nombre1%type, pNombre2 in conductores.nombre2%type,

pApellido1 in conductores.apellidopaterno%type,pApellido2 in
conductores.apellidomaterno%type,

pGrpSanguineo in conductores.grpsanguineoId%type) is

begin

insert into conductores(ConductorId, cedula, nombre1, nombre2, apellidopaterno,

apellidomaterno, grpsanguineoId)

values (SEQCONDUCTORES.nextval, pCedula, pNombre1, pNombre2, pApellido1,

pApellido2, pGrpSanguineo);

end;

----------------------------------------------------------------------------------------------------
declare

vCedula varchar2(50) :='0953458877';

vNombre1 varchar2(50) :='Benita';

vNombre2 varchar2(50) :='Tomura';

vApellido1 varchar2(50) :='Shiragaki';

vApellido2 varchar2(50) :='Carabajo';

vGrpSanguineo number(3) :=2;

begin

pInsertarConductores(vCedula, vNombre1, vNombre2, vApellido1, vApellido2,

vGrpSanguineo );

end;
 CLASE BD (21-01-2020)
-------funciones-----

create or replace function Saludar (nombre in varchar2)

return varchar2 is

begin

return '¡Hola ' || nombre;

end;

--------bloque anónimo para invocar función------

set serveroutput on

declare

vNombre varchar2(50);

vSaludo varchar2(50);

begin

vNombre := 'Mundo!';

vSaludo := Saludar(vNombre);

DBMS_OUTPUT.put_line(vSaludo);

end;

begin

DBMS_OUTPUT.put_line(Saludar('Jeanpier el más fuerte'));

end;

-----------función que no recibe parámetros------------

create or replace function fcontarcolores return number is

vRetorno number;

begin

select count(*) into vRetorno from colores;

 return vRetorno;

end;

begin

DBMS_OUTPUT.put_line(fcontarcolores());

end;

create or replace function fcontarciudades return number is

vRetorno number;

begin

select count(*) into vRetorno from ciudades;

return vRetorno;

end;

begin

DBMS_OUTPUT.put_line(fcontarciudades());

end;

create or replace function fcountInfractionsForCity(codigo in number) return number is

vRetorno number;

begin

select count(*) into vRetorno from detalleinfracciones

where ciudadid = codigo;

return vRetorno;

end;

/
begin

DBMS_OUTPUT.put_line(fcountInfractionsForCity(1));

end;

--devolver siguiente valores de secuencia de colores

create sequence seqColores minvalue 6;

create or replace function fGetSeqColores return number is

vRetorno number;

begin

select seqColores.nextval into vRetorno from dual;

return vRetorno;

end;

begin

DBMS_OUTPUT.put_line(fGetSeqColores());

end;

create or replace procedure pinsertarcolor (pcolornombre in colores.Colornombre%type) is

begin

insert into colores(colorid,Colornombre)

values(fGetSeqColores(),pcolornombre);

end;

begin

pinsertarcolor('turquesa');

end;

/
 BASE DE DATOS II – ISINO55 – 9/Enero/2020

SEGUNDO PARCIAL – TALLER # 1

Generar las instrucciones DDL que permitan data soporte a los siguientes enunciados:

1. Crear una vista que devuelva un listado con los datos de los conductores y su tipo sanguíneo. El
listado debe mostrarse ordenado ascendentemente por Apellido Paterno y Apellido Materno.
2. Crear una vista de solo lectura que devuelva la información de todos vehículos con marca, color y
placa.
3. Crear una vista que muestre los nombres de conductores que han tenido infracciones entre el 1
de enero y el 30 junio del 2018
4. Cree una secuencia que inicie en 10, se incremente de 5 en 5, que el máximo número de secuencia
sea 50 y que sea cíclica.
5. Cree un índice sobre las columnas apellidopaterno y apellido materno de la tabla de conductores.
6. Cree el sinónimo denominado "Colores" para el usuario PRUEBA, el sinónimo hace referencia a la
tabla "Colores" del usuario "USER55'.
7. Eliminar la vista creada en el punto 2.
8. Sintaxis para determinar cuál será el siguiente valor generado por la secuencia creada en el punto
4.
9. Sintaxis para determinar cuál es valor actual de la secuencia creada en el punto 4.
10. Eliminar el sinónimo creado en el punto 6.
 RESOLUCION

/* 1) */

create view vista_datos_conductor as

select c.apellidopaterno, c.apellidomaterno, gr.grpsanguineo

from conductores c

inner join grpsanguineo gr on gr.grpsanguineoid = c.grpsanguineoid

order by c.apellidopaterno, c.apellidomaterno asc;

/* 2) */

create view vista_vehiculos as

select m.marcanombre, c.colornombre, ve.placa

from vehiculos ve

inner join marcas m on m.marcaid = ve.marcaid

inner join colores c on c.colorid = ve.colorid;

/* 3) */

create view vista_infracciones as

select c.nombre1, c.apellidopaterno

from conductores c

inner join detalleinfracciones de on de.conductorid = c.conductorid

where de.fecha > to_date('01/01/2018', 'dd/mm/yyyy') and de.fecha < to_date('30/06/2018',

'dd/mm/yyyy');

/* 4) */

create sequence secuencia

start with 10 increment by 5

maxvalue 500

minvalue 0
cycle;

/* 5) */

create index indice_conductores

on conductores(apellidopaterno, apellidomaterno);

/* 6) */

create synonym prueba.colores for user55.colores;

/* 7) */

drop view vista_vehiculos;

/* 8) */

select secuencia.nextval from dual;

/* 9) */

select secuencia.currval from dual;

/* 10) */

drop synonym prueba.colores;

 BASE DE DATOS II – ISINO55 – 23/Enero/2020

SEGUNDO PARCIAL – TALLER # 2

Generar las instrucciones correspondientes que permitan cumplir los siguientes enunciados:

1. Crear las siguientes tablas:

2. Crear las secuencias: SEQESTUDIANTES y SEQPAISES. Las secuencias deben empezar con el valor
de 1 y su incremento debe ser de 1.
3. Crear los procedimientos que permitan insertar y actualizar registros en cada una de las tablas.
Los procedimientos de ingreso deben obtener los valores de los Id de las secuencias creadas en el
punto dos. Los procedimientos de actualización recibirán como parámetros los datos necesarios
para realizar la operación. En el caso en que la ejecución de algún procedimiento genere una
excepción esta deberá ser controlada y deberá mostrar en pantalla un mensaje con la descripción
de la excepción.
4. Crear una función que reciba como parámetro el código del país y como resultado devuelva
cuantos estudiantes han nacido él.
5. Crear una función que reciba como parámetro una letra y como resultado devuelva cuantos
estudiantes contienen dicha letra dentro de sus apellidos.
 RESOLUCION
/*1) Crear las siguientes tablas */

create table paises(

paisId number(3),

paisNombre varchar2(50 byte),

constraint paises_pk primary key(paisId)

);

create table estudiantes(

estudianteId number(3),

nombres varchar2(50 byte),

apellidos varchar2(50 byte),

email varchar2(50 byte),

paisId number(3),

constraint estudiantes_pk primary key(estudianteId),

constraint estudiantes_pais_fk foreign key(paisId) references paises(paisId)

);

/*INSERTS

insert into paises(paisId, paisNombre) values(1, 'Ecuador');

insert into paises(paisId, paisNombre) values(2, 'Colombia');

insert into paises(paisId, paisNombre) values(3, 'Argentina');

insert into paises(paisId, paisNombre) values(4, 'Peru');

insert into paises(paisId, paisNombre) values(5, 'China');

insert into estudiantes(estudianteId, nombres, apellidos, email, paisId) values( 1,

'Cristhian', 'Manzano', '[email protected]', 1);

insert into estudiantes(estudianteId, nombres, apellidos, email, paisId) values( 2,

'Kevin', 'Velasquez', '[email protected]', 1);

insert into estudiantes(estudianteId, nombres, apellidos, email, paisId) values( 3,

'Matias', 'Carabajo', '[email protected]', 2);

insert into estudiantes(estudianteId, nombres, apellidos, email, paisId) values( 4,

'Gerson', 'Felix', '[email protected]', 3);

*/

/*2) Crear secuencia */

create sequence SEQESTUDIANTES

start with 1

increment by 1;

create sequence SEQPAISES

start with 1

increment by 1;

/*3) */

create or replace procedure pInsertarEnEstudiantes(

pNombre in estudiantes.nombres%type,

pApellido in estudiantes.apellidos%type,

pEmail in estudiantes.email%type,

pPais in estudiantes.paisId%type) is

BEGIN

insert into Estudiantes

values (SEQESTUDIANTES.nextval, pNombre, pApellido, pEmail, pPais);

exception

when others then

raise_application_error(-20001, 'CODIGO DE ERROR: ' || SQLCODE || ': ' ||

SQLERRM);

END;
/*4)*/

create or replace function contarEstudiantes(codigo in number)

return number is vRetorno number;

BEGIN

select count(*) into vRetorno from estudiantes

where paisId = codigo;

return vRetorno;

END;

set serveroutput on

BEGIN

DBMS_output.put_line(contarEstudiantes(1));

END;

/*5)*/

create or replace function fContar(letra varchar2)

return NUMBER is vRetorno NUMBER;

BEGIN

select count(*) into vRetorno from estudiantes

where estudiantes.apellidos like '%'|| letra || '%';

return vRetorno;

END;

BEGIN

DBMS_OUTPUT.put_line(fContar('a'));

END;