Database Reference
In-Depth Information
Imagine that the users who wanted this view for reporting asked you to
add the playing time of the CD into the view. Revise the view by making
changes requested to the original CREATE VIEW command and using
CREATE OR REPLACE instead of CREATE. The following script high-
lights changes:
CREATE
VIEW CD_DETAILS AS
SELECT CD.TITLE CDTITLE, CD.PRESSED_DATE
,
OR REPLACE
, CT.TRACK_SEQ_NO
, S.TITLE SONGTITLE, A.NAME, S.PLAYING_TIME
FROM MUSICCD CD, CDTRACK CT, SONG S, ARTIST A
WHERE CD.MUSICCD_ID = CT.MUSICCD_ID
AND CT.SONG_ID = S.SONG_ID
AND S.ARTIST_ID = A.ARTIST_ID
ORDER BY 1, 3;
CD.PLAYING_TIME CD_TIME
Note:
You can create a brand-new view using CREATE OR REPLACE
VIEW instead of CREATE VIEW.
To drop a view, simply use the DROP VIEW command. The
CD_DETAILS view can be dropped executing the following command:
DROP VIEW CD_DETAILS;
Dropping a view does not affect the base table or tables referenced by
the view.
19.5
Working with Views
Most views are used to query the base tables on which they are built. Some
views are used to insert, update, and delete data in the base tables. The next
sections show you how to query views and how to make changes to data
using views. Following this section, we deal with Oracle Database metadata
data dictionary views.
19.5.1
Querying a View
A query on a view looks just like a query on a table. Behind the scenes,
however, the Oracle Database 10
g
Optimizer merges the query that defines
