1 / 17

Maximizing Views in Oracle9i SQL: Creating, Updating, and Dropping Views

Explore how to create, update, and manage views in Oracle9i SQL, including simple and complex views, DML operations, and preservation of key tables.

asa
Télécharger la présentation

Maximizing Views in Oracle9i SQL: Creating, Updating, and Dropping Views

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 11Views 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 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 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 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 Oracle9i: SQL

  6. Types of Views 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 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 Oracle9i: SQL

  9. Simple View Only references one table – no group functions, GROUP BY clause, or expressions 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 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 Oracle9i: SQL

  12. DML Operations - Complex Views with Expressions Values cannot be inserted into columns that are based on arithmetic expressions 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 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 Oracle9i: SQL

  15. Dropping a View Use DROP VIEW command 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 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 Oracle9i: SQL

More Related