Views

1. Views Introduction to Oracle9i: SQL

2. Chapter Objectives • Create a view, using CREATE VIEW command or the CREATE OR REPLACE VIEW command • Employ the FORCE and NO FORCE options • State the purpose of the WITH CHECK OPTION constraint Introduction to Oracle9i: SQL

3. Chapter Objectives • Explain the effect of the WITH READ ONLY option • Update a record in a simple view • Re-create a view • Explain the implication of an expression in a view for DML operations • Update a record in a complex view Introduction to Oracle9i: SQL

4. Chapter Objectives • Identify problems associated with adding records to a complex view • Identify the key-preserved table underlying a complex view • Drop a view • Explain inline views and the use of ROWNUM to perform a “TOP-N” analysis Introduction to Oracle9i: SQL

5. Views • Permanent objects that store no data • Display data contained in other tables • Two purposes: • Reduce complex query requirements for novice users • Restrict users’ access to sensitive data Introduction to Oracle9i: SQL

6. Types of Views Introduction to Oracle9i: SQL

7. CREATE VIEW Command • Use OR REPLACE if view already exists • Use FORCE if underlying table does not exist at time of creation • Provide new column names if necessary Introduction to Oracle9i: SQL

8. CREATE VIEW Options • WITH CHECK OPTION constraint – if used, prevents data changes that will make the data subsequently inaccessible to the view • WITH READ ONLY – prevents DML operations Introduction to Oracle9i: SQL

9. Simple View Only references one table – no group functions, GROUP BY clause, or expressions Introduction to Oracle9i: SQL

10. Simple View – DML Operations • Any DML operations are allowed through simple views unless created with WITH READ ONLY option • DML operations that violate constraints on the underlying table are not allowed Introduction to Oracle9i: SQL

11. Complex View • May contain data from multiple tables or data created with the GROUP BY clause, functions, or expressions • Type of DML operations allowed depends on various factors Introduction to Oracle9i: SQL

12. DML Operations - Complex Views with Expressions Values cannot be inserted into columns that are based on arithmetic expressions Introduction to Oracle9i: SQL

13. DML Operations – Complex Views from Multiple Tables DML operations can not be performed on non key-preserved tables, but they are permitted on key-preserved tables Introduction to Oracle9i: SQL

14. DML Operations – Other Complex Views No DML operations are permitted on complex views based on DISTINCT, ROWNUM, GROUP BY, or a function Introduction to Oracle9i: SQL

15. Dropping a View Use DROP VIEW command Introduction to Oracle9i: SQL

16. Inline View • Temporary table created by using subquery in FROM clause • Can only be referenced while the command is being executed • Most common usage – “TOP-N” Analysis Introduction to Oracle9i: SQL

17. “TOP-N” Analysis • ORDER BY included to identify top values: • descending for highest values, ascending for lowest values • Extract data based on ROWNUM Introduction to Oracle9i: SQL