General |
NOTE: A view is a logical
entity. It is a SQL statement stored in the database in the system tablespace. Data for a
view is built in a table created by the database engine in the TEMP tablespace. |
View Related Data Dictionary Objects |
view$
|
viewcon$
|
|
dba_contraints |
all_constraints |
user_constraints |
dba_cons_columns |
all_cons_columns |
user_cons_columns |
dba_tab_cols |
all_tab_cols |
user_tab_cols |
dba_updatable_columns |
all_updatable_columns |
user_updatable_columns |
dba_views |
all_views |
user_views |
|
System Privileges |
create view |
create any view |
drop any view |
under any view |
|
|
Create View |
Create Single Table Basic View |
CREATE OR REPLACE VIEW <view_name> AS
SELECT <column_name>
FROM <table_name>; |
CREATE TABLE person (
person_id NUMBER(3),
first_name VARCHAR2(25),
last_name VARCHAR2(25),
title_1 VARCHAR2(10),
title_2 VARCHAR2(10),
socsecno VARCHAR2(11));
INSERT INTO person
VALUES (1, 'Dan', 'Morgan', 'BS', 'PhD', '123-54-0987');
INSERT INTO person
VALUES (1, 'Helen', 'Lofstrom', 'BA', 'MA', '987-03-4793');
INSERT INTO person
VALUES (1,'Tara','Havemeyer','BA',NULL,'402-87-1005');
COMMIT;
set linesize 141
SELECT * FROM person;
CREATE OR REPLACE VIEW person_view AS
SELECT first_name AS FNAME, last_name LNAME, socsecno
FROM person;
SELECT * FROM person_view; |
Create Single Table View With WHERE Clause |
CREATE OR REPLACE VIEW <view_name> AS
SELECT <column_name>
FROM <table_name>
WHERE <column_name> = <value or condition>; |
CREATE OR REPLACE VIEW person_two_titles_view AS
SELECT first_name, last_name, socsecno
FROM person
WHERE title_1 IS NOT NULL
AND title_2 IS NOT NULL;
SELECT * FROM person_two_titles_view; |
Create Single Table View With FUNCTION in the SELECT clause and column alias |
CREATE OR REPLACE VIEW function_view AS
SELECT UPPER(first_name), LOWER(last_name), socsecno
FROM person;
-- alias the columns
CREATE OR REPLACE VIEW function_view AS
SELECT UPPER(first_name) FIRST,
LOWER(last_name) LAST, socsecno
FROM person;
SELECT * FROM function_view; |
Create Single Table View For Security |
CREATE OR REPLACE VIEW person_security_view AS
SELECT first_name || ' ' || last_name NAME,
'***-**-' || SUBSTR(socsecno,8) SSN
FROM person;
SELECT * FROM person_security_view; |
Create Single Table View With FUNCTION in the WHERE clause |
ALTER TABLE person
ADD (created_by VARCHAR2(30), created_dt DATE);
UPDATE person
SET created_by = 'UWCLASS', created_dt = SYSDATE
WHERE last_name = 'Morgan';
UPDATE person
SET created_by = 'IDS', created_dt = SYSDATE+2
WHERE last_name = 'Lofstrom';
UPDATE person
SET created_by = 'UWCLASS', created_dt = SYSDATE-10
WHERE last_name = 'Havemeyer';
CREATE OR REPLACE VIEW user_view AS
SELECT first_name, last_name, title_1, title_2
FROM person
WHERE created_by = USER;
SELECT * FROM user_view;
CREATE OR REPLACE VIEW date_view AS
SELECT first_name, last_name, title_1, title_2
FROM person
WHERE created_dt > SYSDATE-1;
SELECT * FROM date_view; |
Create Multi-table View |
CREATE OR REPLACE VIEW explan_four AS
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
SELECT * FROM explan_four;
CREATE OR REPLACE VIEW explan_six AS
SELECT srvr_id
FROM servers s
WHERE EXISTS (
SELECT srvr_id
FROM serv_inst i
WHERE s.srvr_id = i.srvr_id);
SELECT * FROM explan_six; |
|
Force View |
Force ... Forces The Creation Of A View Even When The View Will Be Invalid. NoForce Is The Default |
CREATE FORCE VIEW <view_name> AS
<select statement>; |
-- assuming the table xyz does not exist
CREATE OR REPLACE FORCE VIEW view_force AS
SELECT * FROM xyz;
-- ignore the error message
col object_name format a30
SELECT object_name, status
FROM user_objects
WHERE object_type = 'VIEW'; |
|
Drop View |
Drop View |
DROP VIEW <view_name>; |
SELECT view_name
FROM user_views;
DROP VIEW date_view; |
|
Updatable Views |
Note: Updateable
views can not include:
|
Updateable View |
CREATE TABLE demo_tab (
person_id NUMBER(3),
first_name VARCHAR2(20),
last_name VARCHAR2(20));
CREATE OR REPLACE VIEW upd_view AS
SELECT * FROM demo_tab;
INSERT INTO demo_tab
(person_id, first_name, last_name)
VALUES
(1, 'Daniel', 'Morgan');
INSERT INTO demo_tab
(person_id, first_name, last_name)
VALUES
(2, 'Helen', 'Lofstrom');
COMMIT;
SELECT * FROM upd_view;
UPDATE upd_view
SET person_id = person_id * 10;
SELECT * FROM upd_view;
desc user_updatable_columns
SELECT table_name, column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name IN (
SELECT view_name
FROM user_views); |
Non-updatable View |
CREATE OR REPLACE VIEW nonupd_view AS
SELECT DISTINCT *
FROM demo_tab;
SELECT table_name, column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'NONUPD_VIEW';
SELECT * FROM nonupd_view;
UPDATE nonupd_view
SET person_id = person_id * 10; |
|
Primary Key - Type P |
Primary Key on a view |
CREATE OR REPLACE VIEW <view_name>
(<column_name, column_name>
UNIQUE RELY DISABLE NOVALIDATE,
CONSTRAINT <constraint_name>
PRIMARY KEY (<column_name>) RELY DISABLE NOVALIDATE) AS
<select statement>; |
CREATE OR REPLACE VIEW person_pk_view
(person_id, last_name
UNIQUE RELY DISABLE NOVALIDATE,
CONSTRAINT pk_person_view
PRIMARY KEY (person_id) RELY DISABLE NOVALIDATE) AS
SELECT person_id, last_name FROM person;
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'PERSON_PK_VIEW'; |
|
Read Only Constraint - Type O |
Read Only on a view |
CREATE OR REPLACE VIEW <view_name> AS
<select statement>
WITH READ ONLY; |
CREATE OR REPLACE VIEW person_reg_view AS
SELECT first_name, last_name, ssn
FROM person;
SELECT first_name FROM person;
UPDATE person_reg_view
SET first_name = 'Dan'
WHERE first_name = 'Daniel';
SELECT first_name FROM person;
CREATE OR REPLACE VIEW person_ro_view AS
SELECT first_name, last_name, ssn
FROM person
WITH READ ONLY;
UPDATE person_ro_view
SET first_name = 'Dan'
WHERE first_name = 'Daniel'; |
|
Check Option - Type V |
Check option |
Specify WITH CHECK OPTION to indicate that Oracle prohibits any changes to
the table or view that would produce rows that are not included in the subquery.
CREATE OR REPLACE VIEW <view_name> AS
<select statement with WHERE clause>
WITH CHECK OPTION; |
CREATE OR REPLACE VIEW insertable_view AS
SELECT person_id, first_name, last_name, dob, ssn
FROM person
WHERE person_id < 10000;
SELECT person_id, first_name, last_name FROM person;
INSERT INTO insertable_view
SELECT 7, 'Allen', 'Richards', dob, ssn
FROM person
WHERE ROWNUM = 1;
INSERT INTO insertable_view
SELECT 77777, 'Richard', 'Allen', dob, ssn
FROM person
WHERE ROWNUM = 1;
SELECT person_id, first_name, last_name FROM person;
CREATE OR REPLACE VIEW checkoption_view AS
SELECT person_id, first_name, last_name, dob, ssn
FROM person
WHERE person_id < 10
WITH CHECK OPTION;
INSERT INTO checkoption_view
SELECT 8, 'Natalie', 'Dressed', dob, ssn
FROM person
WHERE ROWNUM = 1;
INSERT INTO checkoption_view
SELECT 88, 'Rich', 'Poorly', dob, ssn
FROM person
WHERE ROWNUM = 1;
SELECT person_id, first_name, last_name FROM person; |
|
View Related Queries |
Retrieve View Source Code |
set linesize 121
set long 1000000
SELECT owner, text
FROM all_views
WHERE owner = 'UWCLASS'
AND view_name = 'PERSON_VIEW';
SELECT dbms_metadata.get_ddl('VIEW', 'PERSON_VIEW')
FROM DUAL; |
Information On columns In A View |
SELECT view_name
FROM user_views;
col data_type format a30
SELECT column_name, data_type, data_length
FROM user_tab_cols
WHERE table_name = 'PERSON_VIEW'; |
Information About Columns In Which
Deletes, Inserts, and Updates |
SELECT table_name, column_name, updatable, insertable, deletable
FROM user_updatable_columns
ORDER BY 1, 2; |
To Force A CHAR field In A View |
CREATE TABLE t (
col1 VARCHAR2(20));
CREATE OR REPLACE VIEW char_view AS
SELECT col1, CAST(RPAD(col1, 1, ' ') AS CHAR(10))
col2
FROM t;
desc char_view |
The cost of using a view |
set linesize 121
CREATE TABLE t AS
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) < 'X';
SELECT COUNT(*) FROM all_objects
WHERE SUBSTR(object_name,1,1) < 'X';
SELECT COUNT(*) FROM t;
set autotrace traceonly
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) < 'X';
SELECT * FROM t
WHERE SUBSTR(object_name,1,1) < 'X';
set autotrace off |