Creando Vistas en la Base de Datos (PL/SQL)

¿Qué es una Vista?

Una Vista es una representación lógica (no física como una tabla) basada en una tabla, varias tablas o en otra vista. No contiene datos, pero es similar a una “ventana” que a través de ella se pueden ver o modificar datos. Las tablas sobre las cuales está basada la vista se llaman Tablas Base. Las vista se almacenan como una sentencia SELECT en el diccionario de datos de la BD.

• Las vistas restringen el acceso a los datos porque puede mostrar columnas seleccionadas desde la tabla.
• Una vista puede ser usada para construir una consulta simple que recupere datos desde otra consulta compleja.
• Las vistas proporcionan independencia de los datos entre el usuario y la aplicación.
• Las vistas proporcionan a grupos de usuarios acceder a los datos de acuerdo a criterios en particular.

Vistas Simples y Complejas

Existen dos clasificaciones para las vistas: simples y complejas. La diferencia básica está relacionada con las operaciones DML (INSERT, UPDATE y DELETE).

Una Vista Simple es aquella que:

• Deriva de datos de una sola tabla
• No contiene funciones o grupos de datos.
• Se puede realizar operaciones de DML a través de la vista.

Una Vista Compleja es aquella que:

• Deriva de datos de una o más tablas.
• Contiene funciones o grupos de datos.
• No siempre permite operaciones DML través de la vista.

Creando una Vista

Para crear una vista se utiliza la sentencia CREATE VIEW.
Se debe incorporar una subconsulta en la sentencia CREATE VIEW.
La subconsulta puede contener sentencias SELECT complejas.

En la sintaxis:

• OR REPLACE: recrea (vuelve a crear) la vista si ésta ya existe.
• FORCE: crea la vista sin importar que no existan las tablas bases.
• NOFORCE: crea la vista sólo si las tablas bases existen. Es la opción por defecto.
• nombre_vista: es el nombre de la vista que se desea crear.
• alias: especifica el nombre para las expresiones seleccionadas por la consulta de la vista. Deben coincidir con el número de expresiones seleccionadas por la vista. Si no se especifican alias, los nombres de columnas de la vista serán las columna seleccionadas en la lista de la cláusula SELECT.
• subconsulta: es una sentencia SELECT completa.
• WITH CHECK OPTION : Especifica que solo las filas accesibles para la vista pueden ser insertadas o modificadas. La restricción que sigue a esta sección es el nombre que se le da a la restricción de tipo CHECK OPTION. Si no le asigna un nombre a la restricción, el sistema asigna un nombre por defecto en el formato SYS_Cn
• WITH READ ONLY: especifica que la vista sea sólo de lectura.

NOTA: Los tipos de datos de las columnas de la vista corresponden a los tipos de datos de las columnas de la tabla base.

-- Ejemplo

CREATE VIEW v_emp_depto_80
AS SELECT employee_id, last_name, salary
   FROM employees
   WHERE department_id = 80;

DESC v_emp_depto_80;

SELECT *
FROM v_emp_depto_80
ORDER BY salary, last_name;

En el ejemplo, se crea la vista v_emp_depto_80 que mostrará la identificación, apellido y salario de los empleados que trabajan en el departamento 80. Para ver la estructura de la vista creada se puede usar el comando DESCRIBE (DESC). Al momento de mostrar los datos, se puede mostrar todo el contenido que retorna la vista o restringir las filas y columnas a través de alguna condición (de la misma manera como se realiza en una sentencia Select basada en una tabla). En el ejemplo se muestran todas las filas y columnas de la vista v_emp_depto_80 ordenadas por salario y apellido en forma ascendente.

Se pueden controlar los nombres de columna de la vista mediante la inclusión de los alias de columna en la subconsulta.

-- Ejemplo

CREATE VIEW v_salarios_depto_50
AS SELECT employee_id ID_EMPLEADO, last_name APELLIDO,
          salary SALARIO_MENSUAL, salary*12 SALARIO_ANUAL
    FROM employees
    WHERE department_id = 50;
 
SELECT *
FROM v_salarios_depto_50;

El ejemplo, se crea la vista v_salarios_depto_50 que contiene la identificación del empleado con el alias ID_EMPLEADO, su apellido con el alias APELLIDO, su salario con el alias SALARIO_MENSUAL y el salario multiplicado por 12 con el alias SALARIO_ANUAL para todos los empleados del departamento 50. Alternativamente, se puede utilizar un alias en la cláusula CREATE VIEW. El número de alias enumerados debe coincidir con el número de expresiones seleccionadas en la subconsulta.

Modificando una Vista

Con la opción OR REPLACE, una vista se puede crear incluso si ya existe con el mismo nombre, sustituyendo así la antigua versión de la vista. Esto significa que la vista puede ser modificada sin eliminar o tener que volver a asignar privilegios sobre el objeto. Al asignar alias de columna en la cláusula CREATE OR REPLACE VIEW, se debe recordar que los alias se enumeran en el mismo orden que las columnas de la subconsulta.

-- Ejemplo

CREATE OR REPLACE VIEW v_emp_depto_80
(id_empleado, nombre, salario, id_departmento)
AS SELECT employee_id, first_name || ' ' || last_name, salary, department_id
   FROM employees
   WHERE department_id = 80;
   

DESC v_emp_depto_80;

SELECT nombre, salario
FROM v_emp_depto_80
WHERE salario > 10000;

En el ejemplo, a la vista v_emp_depto_80 (creada anteriormente), se le asigna un alias por cada nombre de columna en la cláusula CREATE OR REPLACE VIEW. Por lo tanto, para la columna employee_id la vista tendrá el alias id_empleado, para las columnas first_name y last_name (cocatenadas) se le asigna el alias nombre, a la columnas salary el alias salario y para la columna department_id el alias departamento. Posteriormente, se muestra el nombre y salario de la vista creada para los empleados con salario mayor a 10000.

-- Ejemplo

CREATE OR REPLACE VIEW v_dept_sum
(nombre_depto, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),  
   ROUND(AVG(e.salary))
   FROM employees e JOIN departments d
   ON (e.department_id = d.department_id)
   GROUP BY d.department_name;
   
SELECT * 
FROM v_dept_sum
ORDER BY minsal, nombre_depto;

En el ejemplo, la vista v_dept_sum contiene funciones de grupo y muestra valores desde dos tablas. La vista contendrá el nombre de departamento (cuyo código exista en las tablas employees y departments), su salario mínimo, salario máximo y salario promedio. En este caso, a los resultados de cada función de grupo se le debe asignar una alias en la vista ya que es un requisito si cualquier columna de la vista se deriva de una función o una expresión. La información de la vista se muestra ordenada por salario mínimo y nombre de departamento en forma ascendente.
Recordar que si se desea ver la estructura de la vista, se puede usar DESCRIBE.

Reglas para Efectuar Operaciones DML sobre una Vista

• Generalmente se pueden realizar operaciones DML sobre vistas simples.
  
• No se pueden eliminar filas si la vista contiene: funciones de grupo, cláusula GROUP BY, la palabra DISTINCT o una pseudocolumna (ej. ROWNUM).
  
• No se pueden modificar datos en una vista si contiene: funciones de grupo, cláusula GROUP BY, la palabra DISTINCT, una pseudocolumna (ej. ROWNUM), columnas definidas por una expresión o si las columnas NOT NULL en la tabla base no han sido seleccionadas en la vista
  

Uso de la Cláusula WITH CHECK OPTION

A través de las vistas es posible realizar controles de la integridad referencial. Por tanto, una vista puede servir de mecanismo para proteger la integridad de los datos. La vista se puede utilizar para proteger la integridad de datos, pero el uso es limitado. La cláusula WITH CHECK OPTION especifica que las inserciones y actualizaciones que se realizan a través de la vista no pueden crear filas que la vista no selecciona. Por lo tanto, permite que las restricciones de integridad y controles de validación de datos se deban ejecutar en los datos que se insertan o actualizan. Por lo tanto, en el ejemplo, la cláusula "with check option", no permite modificar la columna department_id porque al hacerlo la fila ya no aparecería en la vista; sí se pueden actualizar las demás columna. Por ejemplo, si se intenta actualizar a 10 la columna employee_id de alguna fila mediante la vista, Oracle muestra un mensaje de error.

La misma restricción surge al ejecutar un INSERT sobre la vista; solamente se pueden agregar filas el valor 30 para la columna department_id; si se intenta ingresar filas con un valor diferente de 30 a esta columna, Oracle mostrará un mensaje de error.

-- Ejemplo

CREATE OR REPLACE VIEW v_dept30
AS SELECT *
FROM employees
WHERE department_id=30
WITH CHECK OPTION CONSTRAINT control_v_dept30;

-- Ejemplo

UPDATE v_dept30
SET department_id=10
WHERE employee_id = 115;

Si a través de la vista creada anteriormente se intenta una operación INSERT o UPDATE con un departamento que no sea 30 se generará el error ORA-01402

Uso de la Cláusula WITH READ ONLY

Para asegurar de que no se efectúen operaciones DML en la vista se debe agregar la opción WITH READ ONLY al momento de crear la vista.

Cuando se intenta efectuar una operación DML sobre cualquier fila de la vista el servidor Oracle retornará un error ORA-42399. Del mismo modo, cualquier intento de insertar o modificar una fila usando la vista con una restricción de sólo lectura se traduce en el mismo error.

-- Ejemplo

CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
   FROM employees
   WHERE department_id = 10
   WITH READ ONLY ;
   
DELETE FROM empvu10
WHERE  employee_number = 200;