Creating Views in Oracle


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

No comments:

Post a Comment

Subscribe to: Posts (Atom)