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 AS SELECT FROM ; | 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, 'Jack', 'Cline', '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 AS SELECT FROM WHERE = ; | 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 = 'Cline';
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 AS ; | -- 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 ; | SELECT view_name FROM user_views;
DROP VIEW date_view; | | Updatable Views | Note: Updateable views can not include:
- Set Operators (INTERSECT, MINUS, UNION, UNION ALL)
- DISTINCT
- Group Aggregate Functions (AVG, COUNT, MAX, MIN, SUM, etc.)
- GROUP BY Clause
- ORDER BY Clause
- CONNECT BY Clause
- START WITH Clause
- Collection Expression In A Select List
- Subquery In A Select List
- Join Query
If pseudocolumns are present they can not be included in an update statement.
| 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, 'Jack', 'Cline');
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 ( UNIQUE RELY DISABLE NOVALIDATE, CONSTRAINT PRIMARY KEY () RELY DISABLE NOVALIDATE) AS ; | 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 AS
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 AS
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 wner = '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 |
|