Database Reference
In-Depth Information
If there is no primary key, all the columns of a unique index on one
of the tables must be included.
The next view is an example of an updatable join view:
CREATE OR REPLACE VIEW STUDIOARTISTS AS
SELECT S.STUDIOTIME_ID, A.NAME, S.ARTIST_ID
, S.SESSION_DATE, MINUTES_USED, AMOUNT_CHARGED
FROM STUDIOTIME S JOIN ARTIST A
ON (A.ARTIST_ID = S.ARTIST_ID);
The columns from the STUDIOTIME table are updatable. The follow-
ing command will insert a new row:
INSERT INTO STUDIOARTISTS (STUDIOTIME_ID, ARTIST_ID
, SESSION_DATE, MINUTES_USED)
VALUES (STUDIOTIME_ID_SEQ.NEXTVAL
, (SELECT ARTIST_ID FROM ARTIST
WHERE NAME = 'Barry Manilow'), '15-MAY-02', 180);
The view can be updated with a script such as this one:
UPDATE STUDIOARTISTS SET AMOUNT_CHARGED =
MINUTES_USED*6
WHERE AMOUNT_CHARGED IS NULL;
And finally, delete using the view:
DELETE FROM STUDIOARTISTS WHERE MINUTES_USED=180;
19.6
Metadata Views
USER_VIEWS
. Describes view and view column details.
USER_UPDATABLE_COLUMN
. Joins view columns, which can
be updated or can have underlying table column values changed
using DML commands.
