12 Creación de Vistas

Copyright  Oracle Corporation, 1998. All rights reserved.

Objetivos Al ón, deber ía ser Al completar completar esta esta lecci lección, debería ser capaz capaz de de hacer hacer lo lo siguiente: siguiente: •• Describir Describir una una Vista Vista •• Crear Crear una una vista vista •• Recupera Recupera datos datos aa través través de de una una vista vista •• Alterar Alterar la la definición definición de de una una vista vista •• Insertar, Insertar, modificar modificar yy borrar borrar datos datos aa través través de de una una vista vista •• Eliminación Eliminación de de vistas vistas 12-2

Copyright  Oracle Corporation, 1998. All rights reserved.

Objetos de la Base de Datos Objecto

Descripción

Tabla

Unidad básica de almacenamiento; compuesta de registros y columnas.

Vista

Lógicamente representa un subconjunto de una o más tablas.

12-3

Secuencia

Genera valores para claves primarias.

Indice

Mejoran el rendimiento de algunas consultas

Sinónimo

Da nombres alternativos a los objetos.

Copyright  Oracle Corporation, 1998. All rights reserved.

¿Qué es una Vista? EMP Table EMPNO JOB MGR SAL SAL COMM EMPNO ENAME ENAME JOB JOB JOB MGR HIREDATE HIREDATE SAL SAL COMM COMM COMM DEPTNO DEPTNO ----DEPTNO ----- ------DEPTNO ------- ----------------- --------- ----------------- --------- --------- ----------------7839 -----------------------5000 10 ----7839 KING KING --------PRESIDENT PRESIDENT --------- ------- 17-NOV-81 17-NOV-81 --------- ----------5000 --------- ----------10 --7698 7839 30 7698 BLAKE BLAKE MANAGER MANAGER 7839 01-MAY-81 01-MAY-81 2850 2850 30 7839 7782 KING 7839 7782 CLARK CLARK KING 10 7566 10 7566 JONES JONES

MANAGER PRESIDENT 17-NOV-81 5000 MANAGER PRESIDENT 7839 7839 09-JUN-81 09-JUN-81 17-NOV-81 2450 2450 5000 MANAGER 7839 MANAGER 7839 02-APR-81 02-APR-81 2975 2975 7782 7654 MARTIN CLARK SALESMAN MANAGER 7698 7839 28-SEP-81 09-JUN-81 1250 1500 300 7782 7654 MARTIN CLARK SALESMAN MANAGER 7698 7839 28-SEP-81 09-JUN-81 1250 1500 1400 1400 300 10 7499 10 7499 ALLEN ALLEN SALESMAN SALESMAN 7698 7698 20-FEB-81 20-FEB-81 1600 1600 300 300

EMPVU10 View EMPNO 7934 ENAME JOB 7698 7844 TURNER MILLER SALESMAN CLERK 7782 23-JAN-82 7934 7844 TURNER MILLER SALESMAN CLERK 7698 7782 08-SEP-81 08-SEP-81 23-JAN-82 ------ -------- ----------10 7900 7698 10 7900 JAMES JAMES CLERK CLERK 7698 03-DEC-81 03-DEC-81 7839 7566 KING PRESIDENT 7521 WARD JONES SALESMAN MANAGER 7698 7839 22-FEB-81 02-APR-81 7566 7521 WARD JONES SALESMAN MANAGER 7698 7839 22-FEB-81 02-APR-81 7782 2020 CLARK MANAGER 7902 ANALYST 7566 7902 FORD FORD ANALYST 7566 03-DEC-81 03-DEC-81 7369 SCOTT ANALYST 7902 7566 09-DEC-82 7788 7369 SMITH SMITH SCOTT CLERK CLERK ANALYST 7902 7566 17-DEC-80 17-DEC-80 09-DEC-82 7934 7788 MILLER CLERK 20 7788 20 7788 SCOTT SCOTT 7876 7876 ADAMS 7876 7876 ADAMS

ANALYST ANALYST CLERK CLERK CLERK CLERK

20 7934 20 7934 MILLER MILLER CLERK CLERK 7369 CLERK 7369 SMITH SMITH CLERK 20 20 12-4

1500 1300 1500 1300 950 950

MANAGER MANAGER

30 30 30 30

1250 2975 1250 2975 500 500 3000 3000

30 30 20 20

800 3000 800 3000 7566 7566 09-DEC-82 09-DEC-82 3000 3000 7788 12-JAN-83 1100 1100 7788 12-JAN-83 1100 1100 7782 7782 23-JAN-82 23-JAN-82 1300 1300 7902 800 7902 17-DEC-80 17-DEC-80 800

7839 7839 01-MAY-81 01-MAY-81

30 30 30 30

00

7902 ANALYST 7902 FORD FORD ANALYST 7566 7566 03-DEC-81 03-DEC-81 3000 3000 20 20 Copyright  Oracle Corporation, 1998. All rights reserved. 7698 7698 BLAKE BLAKE 30 30

10 10 20 20

2850 2850

20 20 20 20 20 20 10 10

¿Por qué Usar Vistas? •• Para Para restringir restringir el el acceso acceso aa la la B.D. B.D. •• Para Para ralizar ralizar consultas consultas complejas complejas fácilmente fácilmente •• Para Para la la indenpendencia indenpendencia de de los los datos datos •• Para Para presentar presentar diferentes diferentes vistas vistas de de los los mismos mismos datos datos

12-5

Copyright  Oracle Corporation, 1998. All rights reserved.

Vistas Simples y Vistas Complejas Característica

Vis. Simples

Vis. Complejas

Nº de Tablas

Una

Una o más

Contiene funciones

No

Sí

Contiene grup. de datos No

Sí

DML a través de la vista Sí

No siempre

12-6

Copyright  Oracle Corporation, 1998. All rights reserved.

Creación de una Vista •• Puede Puede incluir incluir una una subconsulta subconsulta dentro dentro de de la la sentencia sentencia CREATE CREATE VIEW. VIEW. CREATE CREATE [OR [OR REPLACE] REPLACE] [FORCE|NOFORCE] [FORCE|NOFORCE] VIEW VIEW view view [(alias[, [(alias[, alias]...)] alias]...)] AS AS subquery subquery [WITH [WITH CHECK CHECK OPTION OPTION [CONSTRAINT [CONSTRAINT constraint]] constraint]] [WITH [WITH READ READ ONLY] ONLY]

•• La La subconsulta subconsulta puede puede contener contener una una SELECT SELECT compleja. compleja. •• La La subconsulta subconsulta no no puede puede contener contener la la claúsula claúsula ORDER ORDER BY. BY. 12-7

Copyright  Oracle Corporation, 1998. All rights reserved.

Creación de una Vista •• Cree Cree una una vista, vista, EMPVU10, EMPVU10, que que contenga contenga detalles detalles de de los los empleados empleados del del dpto. dpto. 10 10 SQL> 2 4 5 View

CREATE VIEW AS SELECT FROM WHERE created.

empvu10 empno, ename, job emp deptno = 10;

•• Describa Describa la la estructura estructura de de la la vista, vista, usando usando el el comando comando DESCRIBE DESCRIBE de de SQL*Plus. SQL*Plus. SQL> SQL> DESCRIBE DESCRIBE empvu10 empvu10 12-8

Copyright  Oracle Corporation, 1998. All rights reserved.

Creación de una Vista •• Cree Cree una una vista vista usando usando alias alias de de columna columna en en la la subconsulta. subconsulta. SQL> 2 3 4 5 View

CREATE VIEW AS SELECT FROM WHERE created.

salvu30 empno EMPLOYEE_NUMBER, ename NAME, sal SALARY emp deptno = 30;

•• Seleccione Seleccione las las columnas columnas de de esta esta vista, vista, haciendo haciendo uso uso del del nombre nombre del del alias. alias.

12-9

Copyright  Oracle Corporation, 1998. All rights reserved.

Recuperando Datos de una Vista SQL> 2

SELECT * FROM salvu30;

EMPLOYEE_NUMBER EMPLOYEE_NUMBER ----------------------------7698 7698 7654 7654 7499 7499 7844 7844 7900 7900 7521 7521

NAME SALARY NAME SALARY ------------------- ----------------BLAKE 2850 BLAKE 2850 MARTIN 1250 MARTIN 1250 ALLEN 1600 ALLEN 1600 TURNER 1500 TURNER 1500 JAMES 950 JAMES 950 WARD 1250 WARD 1250

66 rows rows selected. selected.

12-10

Copyright  Oracle Corporation, 1998. All rights reserved.

Consulta a la Tabla del Diccionario de Datos, USER_VIEWS

SQL*Plus SELECT * FROM empvu10;

7839 7782 7934

12-11

USER_VIEWS USER_VIEWS EMPVU10 EMPVU10 SELECT SELECT FROM FROM WHERE WHERE

KING PRESIDENT CLARK MANAGER MILLER CLERK

Copyright  Oracle Corporation, 1998. All rights reserved.

empno, empno, ename, ename, job job emp emp deptno deptno == 10; 10;

EMP

Modificación de una Vista •• Modificar Modificar la la vista vista EMPVU10 EMPVU10 por por medio medio de de la la claúsula claúsula CREATE CREATE OR OR REPLACE REPLACE VIEW. VIEW. Añada Añada un un alias alias para para cada cada columna. columna. SQL> 2 3 4 5 View

CREATE OR REPLACE VIEW empvu10 (employee_number, employee_name, job_title) AS SELECT empno, ename, job FROM emp WHERE deptno = 10; created.

•• Los Los alias alias de de columna columna en en la la claúsula claúsula CREATE CREATE VIEW, VIEW, deberán deberán de de aparecer aparecer en en el el mismo mismo orden orden que que las las columnas columnas en en la la subconsulta. subconsulta. 12-12

Copyright  Oracle Corporation, 1998. All rights reserved.

Creación de una Vista Compleja Cree Cree una una vista vista compleja compleja que que contenga contenga funciones funciones de de grupo, grupo, para para ver ver valores valores de de dos dos tablas. tablas. SQL> 2 3 4 5 6 7 View

12-13

CREATE VIEW AS SELECT FROM WHERE GROUP BY created.

dept_sum_vu (name, minsal, maxsal, avgsal) d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal) emp e, dept d e.deptno = d.deptno d.dname;

Copyright  Oracle Corporation, 1998. All rights reserved.

Reglas para Realizar Operaciones DML sobre Vistas •

Se pueden realizar operaciones DML sobre vistas simples.

•

No se puede eliminar una fila si la vista contiene:

– Funciones de grupo. – Una cláusula GROUP BY. – El comando DISTINCT. 12-14

Copyright  Oracle Corporation, 1998. All rights reserved.

Reglas para Realizar Operaciones DML sobre Vistas •

No es posible modificar datos en la vista si la misma contiene:

– Cualquiera de las condiciones anteriores. – Columnas definidas por expresiones. – La pseudocolumna ROWNUM. •

No se puede agregar datos si la vista contiene:

– Cualquiera de las condiciones anteriores. – Cualquier columna NOT NULL no incluída por la vista. 12-15

Copyright  Oracle Corporation, 1998. All rights reserved.

La Claúsula WITH CHECK OPTION •

Asegura que un comando DML sobre la vista mantiene el dominio de la misma.

SQL> 2 3 4 5 View

•

CREATE OR REPLACE VIEW empvu20 AS SELECT * FROM emp WHERE deptno = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck; created.

Si se intenta cambiar el número de departamento para cualquier fila, la sentencia fallará porque viola la restricción de CHECK OPTION.

12-16

Copyright  Oracle Corporation, 1998. All rights reserved.

Denegar Operaciones DML •

Agregando la opción WITH READ ONLY en la definición de la vista, se asegura que no ocurran operaciones DML.

SQL> 2 3 4 5 6 View

CREATE OR REPLACE VIEW empvu10 (employee_number, employee_name, job_title) AS SELECT empno, ename, job FROM emp WHERE deptno = 10 WITH READ ONLY; created.

• Si se intenta realizar una operación DML sobre cualquier fila en la vista, aparecerá el error ORA-01732 del Oracle8 Server. 12-17

Copyright  Oracle Corporation, 1998. All rights reserved.

Eliminación de una Vista •• Al Al Borrar Borrar una una vista vista no no perderá perderá los los datos, datos, porque porque la la vista vista está está basada basada en en tablas tablas subyacentes subyacentes de de la la B.D. B.D. DROP DROP VIEW VIEW view; view;

SQL> DROP VIEW empvu10; View dropped.

12-18

Copyright  Oracle Corporation, 1998. All rights reserved.

Resumen •

Una vista se deriva de los datos de otras tablas u otras vistas.

•

Una vista es como una ventana sobre los datos subyacentes.

•

Una vista provee las siguientes ventajas:

– Restricción del acceso a los datos. – Simplificación de las consultas. – Independencia de los datos. – Permite varias visiones de los mismos datos. – Puede ser eliminada sin borrar los datos subyacentes. 12-19

Copyright  Oracle Corporation, 1998. All rights reserved.

Visión General de la Práctica • • •

Crear una vista simple.

• • •

Intentar modificar datos en una vista.

Crear una vista compleja. Crear una vista con una restricción check. Mostrar definiciones de una vista. Eliminar vistas.

12-20

Copyright  Oracle Corporation, 1998. All rights reserved.