0% found this document useful (0 votes)
12 views30 pages

Chapter 13

The document outlines the objectives and functionalities of views in Oracle 12c SQL, including how to create, update, and manage both simple and complex views. It emphasizes the constraints and limitations associated with DML operations on views, particularly regarding key-preserved tables and expressions. Additionally, it introduces inline views, 'TOP-N' analysis, and materialized views for data replication and storage.
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
12 views30 pages

Chapter 13

The document outlines the objectives and functionalities of views in Oracle 12c SQL, including how to create, update, and manage both simple and complex views. It emphasizes the constraints and limitations associated with DML operations on views, particularly regarding key-preserved tables and expressions. Additionally, it introduces inline views, 'TOP-N' analysis, and materialized views for data replication and storage.
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
You are on page 1/ 30

About the Presentations

• The presentations cover the objectives found in the


opening of each chapter.
• All chapter objectives are listed in the beginning of
each presentation.
• You may customize the presentations to fit your
class needs.
• Some figures from the chapters are included. A
complete set of images from the book can be found
on the Instructor Resources disc.
©2016. Cengage Learning. All rights reserved.
Oracle 12c: SQL

Chapter 13
Views

©2016. Cengage Learning. All rights reserved.


Objectives

• Create a view by using CREATE VIEW command


or the CREATE OR REPLACE VIEW command
• Employ the FORCE and NOFORCE options
• State the purpose of the WITH CHECK OPTION
constraint
• Explain the effect of the WITH READ ONLY option
• Update a record in a simple view
• Re-create a view

©2016. Cengage Learning. All rights reserved. 3


Objectives (continued)
• Explain the implication of an expression in a view
for DML operations
• Update a record in a complex view
• 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
• Create a materialized view to replicate data
©2016. Cengage Learning. All rights reserved. 4
Views
• Permanent objects that store no data
• Store a query
• Two purposes
– Reduce complex query requirements
– Restrict users’ access to sensitive data

©2016. Cengage Learning. All rights reserved. 5


Types of Views

©2016. Cengage Learning. All rights reserved. 6


Creating a View

• You use the CREATE VIEW keywords to create a view


• Use OR REPLACE if the view already exists
• Use FORCE if the underlying table does not exist at
the time of creation
• Provide new column names if necessary

©2016. Cengage Learning. All rights reserved. 7


Creating a View (continued)
• 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

©2016. Cengage Learning. All rights reserved. 8


Creating a Simple View

• Only references one table – no group


functions, GROUP BY clause, or expressions

©2016. Cengage Learning. All rights reserved. 9


DML Operations on a Simple View
• 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

©2016. Cengage Learning. All rights reserved. 10


Creating a Complex View

• A 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

©2016. Cengage Learning. All rights reserved. 11


DML Operations on a Complex View
with an Arithmetic Expression

©2016. Cengage Learning. All rights reserved. 12


DML Operations on a Complex View
Containing Data from Multiple Tables
• DML operations cannot be performed on non-
key-preserved tables, but they are permitted
on key-preserved tables

©2016. Cengage Learning. All rights reserved. 13


DML Operations on a Complex View
Containing Data from Multiple Tables
(continued)

©2016. Cengage Learning. All rights reserved. 14


DML Operations on a Complex View
Containing Functions or Grouped
Data
• DML operations are not permitted if the view
includes a group function or a GROUP BY
clause

©2016. Cengage Learning. All rights reserved. 15


DML Operations on a Complex View
Containing Functions or Grouped
Data (continued)

©2016. Cengage Learning. All rights reserved. 16


DML Operations on a Complex View
Containing DISTINCT or ROWNUM
• DML operations on a view that contains the
DISTINCT keyword or ROWNUM are not
permitted

©2016. Cengage Learning. All rights reserved. 17


Dropping a View

• Use DROP VIEW command

©2016. Cengage Learning. All rights reserved. 18


Creating an Inline View
• An inline view is a temporary table created by using
a subquery in the FROM clause
• It can only be referenced while the command is
being executed
• Most common usage – “TOP-N” analysis

©2016. Cengage Learning. All rights reserved. 19


“TOP-N” Analysis

• ORDER BY included to identify top values:


– Descending for highest values
– Ascending for lowest values
• Extract data based on ROWNUM

©2016. Cengage Learning. All rights reserved. 20


“TOP-N” Analysis (continued)

©2016. Cengage Learning. All rights reserved. 21


“TOP-N” Analysis (continued)
• Oracle 12c introduces a new row limiting
clause (# rows)

©2016. Cengage Learning. All rights reserved. 22


“TOP-N” Analysis (continued)
• Oracle 12c introduces a new row limiting
clause (percent of rows)

©2016. Cengage Learning. All rights reserved. 23


Cross & Outer Apply Joins
• A column of the joining table may be used
to produce the result set of the inline view

©2016. Cengage Learning. All rights reserved. 24


Materialized Views
• Replicate data
• Store data retrieved from view query
• Referred to as “snapshots”

©2016. Cengage Learning. All rights reserved. 25


Materialized Views (continued)

©2016. Cengage Learning. All rights reserved. 26


Materialized Views (continued)

©2016. Cengage Learning. All rights reserved. 27


Summary
• A view is a temporary or virtual table that is used to
retrieve data that exists in the underlying database
tables
• The view query must be executed each time the view is
used
• A view can be used to simplify queries or to restrict
access to sensitive data
• A view is created with the CREATE VIEW command
• A view cannot be modified; to change a view, it must be
dropped and then re-created, or the CREATE OR
REPLACE VIEW command must be used

©2016. Cengage Learning. All rights reserved. 28


Summary (continued)

• Any DML operation can be performed on a simple query


if it does not violate a constraint
• A view that contains expressions or functions, or that
joins multiple tables, is considered a complex view
• A complex view can be used to update only one table;
the table must be a key-preserved table
• Data cannot be added to a view column that contains an
expression
• DML operations are not permitted on non-key-preserved
tables

©2016. Cengage Learning. All rights reserved. 29


Summary (continued)
• DML operations are not permitted on views that include group
functions, a GROUP BY clause, the ROWNUM pseudocolumn, or
the DISTINCT keyword
• Oracle 12c assigns a row number to every row in a table to
indicate its position in the table; the row number can be referenced
by the keyword ROWNUM
• A view can be dropped with the DROPVIEW command; the data is
not affected, because it exists in the original tables
• An inline view can be used only by the current statement and can
include an ORDER BY clause
• “TOP-N” analysis uses the row number of sorted data to determine
a range of top values
• Materialized views physically store view query results

©2016. Cengage Learning. All rights reserved. 30

You might also like