SQL*PL 11.
VISTAS
US
Índice
1. Introducción 2
2. Creación de vistas 3
3. Vistas con columnas virtuales 5
4. Combinaciones para definir vistas 6
5. Manipulación de datos y vistas 7
6. Borrado de vistas 9
7. Resumen 10
8. Ejercicios propuestos 11
Página 1 de 12
SQL*PL 11. VISTAS
US
1. INTRODUCCIÓN
Una vista es una ventana a través de la cual vemos subconjuntos de datos de tablas ya
existentes.
Los "datos" de las vistas no se almacenan físicamente, se obtienen de las tablas que se
están especificando.
Las vistas se definen por medio de sentencias SQL, que se almacenan en la vista del
diccionario de datos VIEWS.
La información se recupera a través de las vistas con la misma sintaxis que hemos
utilizado para las tablas poniendo en vez del nombre de la tabla el nombre de la vista.
Una ventana puede especificar un subconjunto de datos vertical (subconjunto de filas)
y/o horizontal (subconjunto de columnas) de una tabla.
Los datos se pueden obtener a partir de múltiples tablas: unión de tablas y
combinación de tablas.
Razones para la utilización de vistas
1.- Simplificar consultas
Algunas aplicaciones requieren consultas muy complejas, almacenando
consultas intermedias se permite a los usuarios simplificar sus operaciones.
2.- Seguridad
Utilizando los mecanismos de seguridad sobre tablas y vistas (lenguaje de
control de datos DCL) y las vistas se tiene un sistema potente y flexible de control de acceso
a los datos.
3.- Almacenamiento
Las vistas son proposiciones SQL que se almacenan en el diccionario de datos de
forma compilada, por lo que el costo de análisis y compilación sólo se produce al crearlas.
Las vistas contienen sólo datos virtuales, los datos reales se almacenan en los
bloques de datos de las tablas, por lo tanto sólo se almacena la definición compilada, por lo
que el gasto de almacenamiento es mínimo.
4.- Independencia lógica
Conforme va creciendo la base de datos las tablas se pueden alterar, unir y
combinar con otras, etc ...
Creando vistas de esas tablas originales se garantiza la independencia lógica de
los datos, los usuarios y las aplicaciones podrán seguir refiriéndose a los datos como si estos
tuviesen la misma estructura que antes.
Página 2 de 12
SQL*PL 11. VISTAS
US
2. CREACION DE VISTAS
Para crear vistas se utiliza el comando del lenguaje de definición de datos CREATE
VIEW, que sirve para darle un nombre a un comando SQL válido.
CREATE VIEW <nombre_de_la_vista> [<lista_de_columnas>]
AS <sentencia_SQL_válida>;
Se utiliza la lista_de_columnas para cambiarle el nombre a las columnas que se
obtienen con la sentencia SQL.
Se quiere crear una vista que contenga información sobre los jefes y el presidente.
SQL>CREATE VIEW jef_pres (id_emp, id_dept, nombre, sueldo)
2 AS SELECT empno, deptno, ename, sal
3 FROM emp
4 WHERE job IN ( 'MANAGER', 'PRESIDENT');
View created.
Como comentamos anteriormente las vistas se tratan como las tablas para recuperar
datos. Seleccione los jefes y el presidente que ganen más de 2000.
SQL>SELECT * FROM jef_pres
2 WHERE sueldo >= 2000;
ID_EMP ID_DEPT NOMBRE SUELDO
------- ------- ---------- -------
7566 20 JONES 2975
7698 30 BLAKE 2850
7782 10 CLARK 2450
7839 10 KING 5000
4 records selected.
Página 3 de 12
SQL*PL 11. VISTAS
US
El departamento financiero necesita información sobre los sueldos y números de
empleado, su comisión si existe y su número de departamento, para realizar la nómina. Se
omitirán todas las otras columnas que no se utilicen para confeccionar la nómina por motivos
de seguridad.
SQL>CREATE VIEW nómina (id_emp, id_dept, sueldo, comisión)
2 AS SELECT empno, deptno, sal, comm
3 FROM emp;
View created.
SQL> SELECT * FROM NOMINA;
ID_EMP ID_DEPT SUELDO COMISION
------- ------- ------- -------- -------------
7369 20 800
7499 30 1600 300
7521 30 1250 500
7566 20 2975
7654 30 1250 1400
7698 30 2850
7782 10 2450
7788 20 3000
7839 10 5000
7844 30 1500 0
7876 20 1100
7900 30 950
7902 20 3000
7934 10 1300
14 records selected.
Cada vez que se crea una vista se actualizan las tablas del diccionario de datos para
incluir el nombre y la definición de este nuevo objeto.
Para comprobarlo los usuarios pueden consultar la vista del diccionario de datos
VIEWS.
Página 4 de 12
SQL*PL 11. VISTAS
US
SQL> SELECT * FROM USER_VIEWS
2 WHERE VIEWNAME = 'NOMINA' OR VIEWNAME = 'JEF_PRES';
VIEWNAME
------------------------------
VIEWTEXT
---------------------------------------------------------------
JEF_PRES
SELECT EMPNO,DEPTNO,ENAME,SAL
FROM EMP
WHERE JOB IN ('MANAGER','PRESIDENT')
NOMINA
SELECT EMPNO,DEPTNO,SAL,COMM
FROM EMP
3. VISTAS CON COLUMNAS VIRTUALES
Las columnas que se crean con una vista no tienen por que ser columnas de la tabla de
la que se obtiene la vista, pueden ser también columnas virtuales en las que se agrupa la
información de las columnas de la tabla.
El departamento financiero necesita una vista que le proporcione información
estadística sobre los sueldos de los empleados agrupados por departamento y tipo de empleo.
SQL>CREATE VIEW estadística
2 (departamento,empleo, salario_mínimo, salario_máximo,
3 salario_medio, suma_de_salarios )
4 AS SELECT deptno, job, min(sal), max(sal), avg(sal),
5 sum(sal) FROM emp
6 GROUP BY deptno, job;
View created.
SQL> SELECT * FROM ESTADISTICA;
Página 5 de 12
SQL*PL 11. VISTAS
US
DEPARTAMENTO EMPLEO SALARIO_MINIMO SALARIO_MAXIMO SALARIO_MEDIO SUMA_DE_SALARIOS
------------------- ------------- --------------------- -------------------- ------------------ -------------------------
10 CLERK 1300 1300 1300 1300
10 MANAGER 2450 2450 2450 2450
10 PRESIDENT 5000 5000 5000 5000
20 ANALYST 3000 3000 3000 6000
20 CLERK 800 1100 950 1900
20 MANAGER 2975 2975 2975 2975
30 CLERK 950 950 950 950
30 MANAGER 2850 2850 2850 2850
9 records selected.
4. COMBINACIONES PARA DEFINIR VISTAS
Se pueden definir vistas que obtengan los datos de la combinación de varias tablas.
Queremos que en la vista anterior se recupere el nombre del departamento, en vez de
su número.
SQL>CREATE VIEW estadística_1
2 (departamento,empleo, salario_mínimo, salario_máximo,
3 salario_medio, suma_de_salarios )
4 AS SELECT dname, job, min(sal), max(sal), avg(sal),
5 sum(sal) FROM emp,dept
6 WHERE dept.deptno = emp.deptno
7 GROUP BY dname, job;
View created.
SQL>SELECT * FROM ESTADISTICA1;
DEPARTAMENTO EMPLEO SALARIO_MINIMO SALARIO_MAXIMO SALARIO_MEDIO SUMA_DE_SALARIOS
---------------- --------- ------------------ ------------------- ------------------ ----------------------
ACCOUNTING CLERK 1300 1300 1300 1300
ACCOUNTING MANAGER 2450 2450 2450 2450
ACCOUNTING PRESIDENT 5000 5000 5000 5000
RESEARCH ANALYST 3000 3000 3000 6000
RESEARCH CLERK 800 1100 950 1900
RESEARCH MANAGER 2975 2975 2975 2975
SALES CLERK 950 950 950 950
SALES MANAGER 2850 2850 2850 2850
SALES SALESMAN 1250 1600 1400 5600
9 records selected.
Página 6 de 12
SQL*PL 11. VISTAS
US
5. MANIPULACION DE DATOS A TRAVES DE LAS VISTAS
Actualización de las tablas
Como las vistas son ventanas a través de las que se ven los datos de las tablas,
cualquier actualización (inserción, borrado, modificación) de una fila quedará
automáticamente reflejada en la vista.
Si insertamos un nuevo empleado en la tabla emp se reflejará en las vistas nómina,
estadística_1 y estadística_2.
SQL>INSERT INTO emp VALUES
2 (8712, 'FERREIRA','ANALISTA',0054,SYSDATE,2000,NULL,
3 30);
1 record created.
SQL> SELECT * FROM NOMINA;
ID_EMP ID_DEPT SUELDO COMISION
----- ------- ------- --------
7369 20 800
7499 30 1600 300
7521 30 1250 500
7566 20 2975
7654 30 1250 1400
7698 30 2850
7782 10 2450
7788 20 3000
7839 10 5000
7844 30 1500 0
7876 20 1100
7900 30 950
7902 20 3000
7934 10 1300
8712 30 2000
15 records selected.
Actualización de las vistas
También se puede utilizar el lenguaje de manipulación de datos con las vistas, y los
cambios quedarán automáticamente reflejados en los bloques de datos de las tablas.
Al igual que con las tablas se pueden conceder privilegios (comando GRANT) sobre las
vistas a otros usuarios.
Página 7 de 12
SQL*PL 11. VISTAS
US
Al insertar filas a través de una vista hay que tener en cuenta que las columnas no
especificadas en la definición de la vista tomarán valores nulos, por lo que si una está
definida como no nula, no se podrán insertar filas a través de la vista.
La manipulación de datos a través de las vistas se podrá realizar sólo si:
- la vista está basada en una única tabla
- su definición no contiene ni funciones ni expresiones
- su definición no contiene cláusulas GROUP BY
Ejemplo:
SQL>INSERT INTO nómina VALUES
2 (5543, 30, 5000, 100);
1 record created.
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- --------- ----- --------- ------- ------- -------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
8712 FERREIRA ANALISTA 54 09-JAN-89 2000 30
16 records selected.
Página 8 de 12
SQL*PL 11. VISTAS
US
Claúsula "with check option"
Esta opción fuerza a que las inserciones y modificaciones hechas a través de la vista
afecten sólo a filas que cumplan las condiciones de creación de la vista.
CREATE VIEW <nombre_de_la_vista> [<lista_de_columnas>]
AS <sentencia_SQL_válida>
WITH CHECK OPTION;
Ejemplo:
SQL>CREATE VIEW libros_caros
2 AS SELECT autor, titulo, precio
3 FROM libros
4 WHERE precio >= 4000
5 WITH CHECK OPTION;
View created.
SQL>INSERT INTO libros_caros
2 VALUES ( 'CERVANTES', 'DON QUIJOTE DE LA MANCHA',
3 3000);
*
Error at line 2: ORA-1402: view WITH CHECK OPTION where-clause violation
6. BORRADO DE VISTAS
El comando DROP VIEW borra la definición de las vistas del diccionario de datos, por lo
que no se podrán volver a utilizar.
Las tablas referenciadas en la vista borrada no se verán afectadas.
DROP VIEW <nombre_de_la_vista>;
No existe un comando para modificar la definición de una vista, si se desea hacerlo
habrá que borrarla y volver a crear la vista.
Si se borra una tabla o vista que estuviese contenida en la definición de una vista, se
borrará esta última vista también.
Página 9 de 12
SQL*PL 11. VISTAS
US
7. RESUMEN
Los nombres por omisión de las columnas de las vistas coinciden con los nombres en las
tablas de las que se han obtenido.
Cuando se seleccionen columnas virtuales para crear vistas se les deberá dar un nuevo
nombre.
Cuando se seleccionen columnas de varias tablas habrá que cambiar el nombre a las
columnas que resulten ambiguas.
Para definir vistas se pueden utilizar otras vistas o tablas.
Se pueden utilizar funciones, expresiones, la cláusula "group by" para definir nuevas
vistas.
No se puede utilizar la cláusula "order by" para definir nuevas vistas, se podrá utilizar
cuando se recuperen los datos de las vistas.
Página 10 de 12
SQL*PL 11. VISTAS
US
8. EJERCICIOS PROPUESTOS
1º. Cree una vista llamada MEJORES que contenga a los empleados que ganen más de 3000.
Debe contener sólo el número del empleado, el salario, la comisión y el trabajo.
2º. Seleccione todos los datos utilizando la vista anterior, para su comprobación.
3º. Crear una vista, que obtenga una estadística de los salarios medios, máximos y la suma
de salarios por cada tipo de empleo, y que figure el nombre de su función.
4º. Presentar la vista del ejercicio 1 MEJORES, ordenada por sueldo descendente.
5º. Crear una vista llamada ventas que contenga a los CLIENTES( cod_cliente, fecha_pedido
cod_pediod y cod_productos) que hicieron el pedido en febrero del 1989
6º. Presentar la vista del ejercicio anterior
7º. Crea una vista llamada trabajadores_dep que contenga cuantos trabajadores tiene cada
departamento de cada localidad.
8º. Presentar la vista del ejercicio anterior
9º. Crear una vista llamada dep23 que contenga a los trabajadores del departamento 23
que cobren comison. De ellos solo queremos saber el cod_empleado, salario, comision y
departamento al que pertenecen.
10º. Presentar la vista del ejercicio anterior
11º. Insertar los siguientes valores en la vista dep23 cod_empleado=1000, salario=785,
comision =50, departamento=10.
12º.- comprobar que se ha actualizado la vista y la tabla employee
13º.Los empleados del departamento 23 con comision 100 pasan a tener una comision 500.
Actualizar la comision a traves de la vista.
Página 11 de 12
SQL*PL 11. VISTAS
US
14º. Comprobar que se han actualizado dichos empleados en la tabla employee y en la
vista.
15º. Borrar a los empleados del departamento 23 con comision 300 a traves de la vista.
16º. Consultar los datos de las vistas en el diccionario de datos
17º borrar la vista MEJORES
Página 12 de 12
