Apartado de "Vistas" tratado en la asignatura de Base de Datos del Ciclo Superior de
Diseño de Aplicaciones Web impartido en el IES Camp de Morvedre.
INTRODUCCIÓN
El
esquema de una Base de Datos está formado por diversos objetos, entre los que,
a nivel primario, encontramos las tablas,
las secuencias (al menos en Oracle)
y también las vistas.
Las
vistas son objetos que no almacenan datos y que son definiciones basadas en una
consulta, es decir, las vistas proporcionarán una visión, modelada mediante una
consulta, de un subconjunto de componentes de una o varias tablas.
Para
poder trabajar con vistas lo primero que debemos tener en cuenta es si nuestro
usuario tiene los privilegios necesarios. Si no es así lo primero que deberemos
hacer es otorgarnos dichos privilegios. En nuestro caso estamos trabajando en
una base de datos creada con Oracle y realizamos las tareas de gestión a través
del usuario scott. Por tanto para
dotar a scott de privilegios para trabajar con vistas dentro de la base de
datos deberemos entrar como usuarios administradores, con la cuenta system, y generar dichos privilegios. Desde
un programa gestor como puede ser SQLPlus
(que se instala junto con Oracle) entraremos como usuario system, al cual le dimos contraseña durante la instalación de Oracle
y por tanto la conocemos o bien si ya estábamos en SQLPlus cambiaremos de usuario mediante CONNECT SYSTEM/Gladiador12
(Gladiador12 es la contraseña que se le dio en su día a system). System es, junto
con sys, uno de los dos usuarios
administradores que se generan automáticamente con Oracle. Este tipo de
usuarios, por su nivel jerárquico, únicamente deben utilizarse para tareas de
gestión administrativa de la base de datos (como pueda ser en este caso la concesión de
privilegios), tratando en la medida de lo posible de evitar su uso para el resto
de tareas. Bien, desde SQLPlus y ya
como usuario system identificado
tecleamos la siguiente sentencia:
GRANT
CREATE VIEW TO SCOTT;
CONNECT SCOTT/TIGRE;(Tigre es la contraseña que tiene scott en nuestro sistema).
En nuestro caso vamos a usar SQLDeveloper (también se instala junto con Oracle) que es un gestor
mucho más amigable (por la interfaz gráfica de usuario) que SQLPlus (que solo dispone de modo
consola). Por tanto salimos de SQLPlus
y lanzamos SQLDeveloper.
Ahora necesitamos un esquema relacional para poder realizar
ejemplos básicos con las vistas. Para ello partiremos de las siguientes tablas
e inserts:
CREATE TABLE clientes(
dni CHAR(10),
nombre VARCHAR(20) NOT NULL,
constraint
pk_clientes PRIMARY KEY (dni)
);
CREATE
TABLE cuotas (
num_periodo
CHAR(1) NOT NULL,
potencia
CHAR(1) NOT NULL,
precio
NUMBER NOT NULL,
constraint pk_cuotas PRIMARY KEY (num_periodo,potencia),
constraint c_cuotas_opcionPotencia CHECK (potencia IN
('0','1','2','3'))
);
CREATE TABLE contadores(
id_contador CHAR(2),
dni_titular CHAR(10),
potencia_contratada CHAR(1),
dir_suministro VARCHAR(20) NOT NULL,
dir_facturacion VARCHAR(20) NOT NULL,
constraint pk_contadores PRIMARY KEY (id_contador),
constraint fk_contadores_dniTitular FOREIGN KEY
(dni_titular) REFERENCES clientes (dni)
);
CREATE
TABLE facturas(
num_periodo
CHAR(1) NOT NULL,
contador CHAR(2),
num_factura CHAR(10),
lectura NUMBER NOT NULL,
pagado CHAR(1),
fecha_lectura DATE,
constraint pk_facturas PRIMARY KEY (num_periodo,contador),
constraint
c_facturas_RangoPago CHECK (pagado IN (null,'s','S','n','N'))
);
Y los
inserts:
INSERT INTO
clientes VALUES ('11','Pepe');
INSERT INTO
clientes VALUES ('22','Luis');
INSERT INTO
clientes VALUES ('33','Rosa');
INSERT INTO
cuotas VALUES ('1','0','0,15');
INSERT INTO
cuotas VALUES ('1','1','20');
INSERT INTO
cuotas VALUES ('1','2','25');
INSERT INTO
cuotas VALUES ('1','3','30');
INSERT INTO
cuotas VALUES ('2','0','0,17');
INSERT INTO
cuotas VALUES ('2','1','21');
INSERT INTO
cuotas VALUES ('2','2','26');
INSERT INTO
cuotas VALUES ('2','3','30');
INSERT INTO
cuotas VALUES ('3','0','0,17');
INSERT INTO
cuotas VALUES ('3','1','21');
INSERT INTO
cuotas VALUES ('3','2','26');
INSERT INTO
cuotas VALUES ('3','3','30');
INSERT INTO
contadores VALUES ('A1','11','1','c/Mayor','c/Mayor');
INSERT INTO
contadores VALUES ('A2','11','2','Poligono Industrial','c/ Mayor');
INSERT INTO contadores VALUES
('A3','22','2','c/tintorerias','c/Barcelona');
INSERT INTO
facturas VALUES ('0','A1','---','0',null,null);
INSERT INTO
facturas VALUES ('0','A2','---','0',null,null);
INSERT INTO
facturas VALUES ('0','A3','---','0',null,null);
INSERT INTO
facturas VALUES ('1','A1','2013-001','200','n','28/02/13');
INSERT INTO
facturas VALUES ('1','A2','2013-002','350','n','28/02/13');
INSERT INTO
facturas VALUES ('1','A3','2013-003','275','n','28/02/13');
INSERT INTO
facturas VALUES ('2','A1','2013-004','500','n','30/04/13');
INSERT INTO
facturas VALUES ('2','A2','2013-005','565','n','30/04/13');
INSERT INTO
facturas VALUES ('2','A3','2013-006','700','n','30/04/13');
INSERT INTO
facturas VALUES ('3','A1','2013-007','835','n','30/06/13');
INSERT INTO
facturas VALUES ('3','A2','2013-008','865','n','30/06/13');
INSERT INTO
facturas VALUES ('3','A3','2013-009','900','n','30/06/13');
commit;
CREACIÓN, VISUALIZACIÓN Y BORRADO DE VISTAS
Una vez obtenidos los privilegios necesarios vamos a crear
una primera vista mediante la instrucción:
CREATE VIEW vista_de_prueba AS
SELECT dni,nombre FROM clientes;
Si desplegamos el elemento Vistas vemos que efectivamente
aparece la vista que acabamos de crear.
Si ahora comprobamos la consulta que acabamos de usar:
SELECT dni,nombre FROM clientes;
Obtenemos:
Ahora visualizamos la vista:
SELECT * FROM vista_de_prueba;
Y comprobamos cómo el resultado es el mismo. A través de la vista creada estamos viendo una parte de
la tabla clientes, concretamente las
columnas dni y nombre, y solo esas columnas, no la tabla entera.
Podemos borrar la vista con el comando:
DROP VIEW vista_de_prueba;
CONSULTA COMBINADA CON UNA VISTA Y UNA TABLA
Las vistas, como objetos que son, permiten ser relacionadas
con otros objetos como son las tablas. Vamos a ver la lista de clientes de
nuestro esquema, para lo que recurrimos de paso a la vista recientemente
creada:
SELECT * FROM
vista_de_prueba;
Y vamos a hacer una consulta para ver qué clientes tienen
dado de alta un contador:
SELECT nombre FROM contadores,clientes where
clientes.dni=contadores.dni_titular;
Por tanto, de los tres clientes que veíamos anteriormente que existen, hay uno que no es titular
actualmente de un contador.
Ahora, igual que hemos hecho con la consulta, podemos cruzar
la vista vista_de_prueba con la tabla
contadores:
SELECT nombre FROM vista_de_prueba,contadores WHERE
vista_de_prueba.dni= contadores.dni_titular;
Obteniendo el mismo resultado:
Por tanto las vistas funcionan como un objeto semejante a
una tabla, permitiendo realizar combinaciones. Como ya hemos mencionado al
principio las vistas nos dan una visión sesgada de la tabla o tablas que
subyacen tras la consulta. Nuevamente insistir en que las vistas no almacenan datos y que tras
ellas lo que ocurre es que se procesa una consulta que da como resultado una
visión parcial de una o varias tablas.
REALIZACIÓN DE INSERTS A TRAVÉS DE UNA VISTA
Cuando la vista se basa sólo en la consulta sobre una tabla
es posible realizar inserts en esa tabla a través de la vista. No obstante si la vista
combina varias tablas la inserción de datos a través de ella no se contempla.
Los datos insertados deben, eso sí, cumplir con las
restricciones de la tabla de destino. Vamos a realizar un insert en la tabla clientes a través de la vista
vista_de_prueba:
INSERT INTO vista_de_prueba VALUES ('112','Miguelin');
Ahora comprobamos:
SELECT * FROM clientes;
Vemos como efectivamente hemos introducido una nueva fila en
la tabla clientes a través de la vista.
USO DE UNA VISTA COMO RESTRICCIÓN
Podemos definir restricciones para los inserts que se
realicen a través de una vista. Vamos a crear una nueva vista y le vamos a
aplicar una restricción. Vamos a hacer que los nuevos clientes a insertar deban
llamarse ‘Samuel’ para ser “aceptados” en la tabla:
CREATE VIEW
vista_restriccion AS SELECT dni,nombre FROM clientes
WHERE
nombre='Samuel' WITH CHECK OPTION;
Como no hay clientes llamados ‘Samuel’ esta vista está
vacía, pero si ahora intentamos realizar este insert:
INSERT INTO vista_restriccion VALUES ('113','Roberto');
Salta la restricción:
El with check option es un comando que hace que
la cláusula where actúe como filtro
no solo de salida sino también de entrada.
Si tratamos ahora de realizar el siguiente insert vemos como no tenemos ningún
problema:
INSERT INTO vista_restriccion VALUES ('113','Samuel');
Comprobamos la tabla clientes y vemos como efectivamente ahí
está el nuevo dato:
SELECT * FROM clientes;
Si lo que quisiéramos fuera modificar una vista ya creada recurriremos al comando:
CREATE OR REPLACE VIEW nombre_de_la_vista AS SELECT....etc.,etc...
Recapitulando nuevamente: las vistas proporcionan un
subconjunto de datos de una o varias tablas moldeado en combinación de
columnas/filas a nuestro gusto mediante una consulta con select.
Existen además del with
check option otros comandos de interés para las vistas como es el caso de with read only, mediante el cual
restringimos la introducción de de datos a través de la vista a la que se le
aplica permitiendo únicamente el acceso en modo lectura.
VISTA QUE COMBINA VARIAS TABLAS
Si tenemos una consulta como ésta:
SELECT nombre, id_contador as contador, contadores.dir_facturacion,
actual.fecha_lectura,
(actual.lectura- anterior.lectura) as consumo,
((actual.lectura-anterior.lectura)*cuotas.precio) as importe_variable
FROM clientes, contadores, facturas anterior, facturas actual, cuotas
where clientes.dni= contadores.dni_titular
and contadores.id_contador= actual.contador
and anterior.contador=actual.contador
and anterior.num_periodo+1=actual.num_periodo
and actual.num_periodo=2
and cuotas.num_periodo= actual.num_periodo
and cuotas.potencia=0
order by actual.num_periodo, actual.contador;
que nos devuelve los recibos de todos los clientes para el perido 2:
en uso el Where con "clientes.dni=contadores.dni_titular").
CREATE VIEW v_recibos_periodo_2 AS
SELECT nombre, id_contador as contador, contadores.dir_facturacion,
actual.fecha_lectura,
(actual.lectura- anterior.lectura) as consumo,
((actual.lectura-anterior.lectura)*cuotas.precio) as importe_variable
FROM clientes, contadores, facturas anterior, facturas actual, cuotas
where clientes.dni= contadores.dni_titular
and contadores.id_contador= actual.contador
and anterior.contador=actual.contador
and anterior.num_periodo+1=actual.num_periodo
and cuotas.num_periodo=actual.num_periodo
and cuotas.potencia=0
and actual.num_periodo=2;
Igualmente podríamos crear una nueva tabla en lugar de una vista, pero es una tabla que no tiene restricciones, no se actualiza con la actualización de las tablas origen y ocupa espacio aunque una select contra una tabla es mucho más eficiente que una select contra una vista (que tiene que elaborar todo ese lote de datos. Éste tipo de recursos se usan en caso de querer exportar los datos de una organización a otra o de un gestor a otro (de un sistema a otro, de un servidor a otro). Ejemplo Iberdrola subcontratando la impresión, ensobrado y envío por correo de las facturas de sus millones de clientes.
CREATE TABLE t_recibos_periodo_2 AS
SELECT nombre, id_contador as contador, contadores.dir_facturacion,
actual.fecha_lectura,
(actual.lectura- anterior.lectura) as consumo,
((actual.lectura-anterior.lectura)*cuotas.precio) as importe_variable
FROM clientes, contadores, facturas anterior, facturas actual, cuotas
where clientes.dni= contadores.dni_titular
and contadores.id_contador= actual.contador
and anterior.contador=actual.contador
and anterior.num_periodo+1=actual.num_periodo
and cuotas.num_periodo=actual.num_periodo
and cuotas.potencia=0
and actual.num_periodo=2;
Para ver el resultado:
SELECT * from t_recibos_periodo_2;
USO PRINCIPAL DE LAS VISTAS
Las vistas pueden tener múltiples usos pero esencialmente el
ámbito de utilidad es el de la seguridad, es decir, el de la restricción de los
accesos de cada usuario de la base de datos al resto de “parcelas” que la
conforman. Si pensamos, por ejemplo, en el ámbito de un ayuntamiento, en el que
se instala una base de datos para la gestión del municipio y en la que existen
distintas parcelas dirigidas por distintos usuarios (padrón, tráfico, rústica,
urbana, etc.) aparece la problemática de que debe restringirse el acceso de
cada usuario al resto de parcelas pero a la vez debe evitarse la redundancia de
datos y la descentralización. Es decir, no debe permitirse que el usuario
que se encarga de la gestión de la parte de las fincas rústicas pueda entrar en
tráfico y realizar cambios en las tablas de esa parcela (imaginemos a Pepe
Pérez entrando desde rústica y
quitándole una multa de tráfico a su primo Paquito). Pero al mismo tiempo cada
parcela debe tener acceso a ciertos datos que el resto de parcelas poseen para
evitar la redundancia de éstos y la mala eficiencia de una base de datos descentralizada
a la hora por ejemplo de las actualizaciones de datos (Pepe Pérez, de rústica, sí deberá poder tener acceso al
listado de habitantes censados en el municipio que figura en las tablas del usuario que gestiona padrón, por ejemplo. O cuando
se realice el nuevo censo esos datos actualizados deben entrara en el menor
número de tablas posibles y que éstas estén compartidas por todos los usuarios).
Es aquí donde entran en juego las vistas. Mediante ellas se establecen una
serie de filtros y una serie de restricciones de unas partes a otras para
permitir el control en los accesos de cada usuario a cada tabla (o solo al
conjunto de datos de interés de una o unas tablas determinadas) ya sea además
restringiendo la inserción de nuevos datos o funcionando en modo sólo lectura
con el with read only si además se
quiere bloquear la adición de inserts.
No hay comentarios:
Publicar un comentario