CREATE [OR REPLACE]
[[NO] FORCE] [EDITIONING] VIEW [schema.] view
[ ( { alias [ inline_constraint... ]
| out_of_line_constraint
}
[, { alias [ inline_constraint...]
| out_of_line_constraint
}
]
)
| object_view_clause
| XMLType_view_clause
]
AS subquery [ subquery_restriction_clause ] ;
object_view_clause::=
OF [ schema. ] type_name
{ WITH OBJECT { IDENTIFIER | ID }
{ DEFAULT | ( attribute [, attribute ]... ) }
| UNDER [ schema. ] superview
}
[ ( { out_of_line_constraint
| attribute { inline_constraint }...
} [, { out_of_line_constraint
| attribute { inline_constraint }...
}
]...
)
]
XMLType_view_clause ::=
OF XMLTYPE [ XMLSchema_spec ]
WITH OBJECT { IDENTIFIER | ID }
{ DEFAULT | ( expr [, expr ]...) }
XMLSchema_spec::=
[ XMLSCHEMA XMLSchema_URL ]
ELEMENT { element | XMLSchema_URL # element }
[ { ALLOW | DISALLOW } NONSCHEMA ]
[ { ALLOW | DISALLOW } ANYSCHEMA ]
subquery_restriction_clause::=
WITH { READ ONLY
| CHECK OPTION
} [ CONSTRAINT constraint ]
Example
CREATE VIEW emp_view AS
SELECT last_name, salary*12 annual_salary
FROM employees
WHERE department_id = 20;
CREATE EDITIONING VIEW ed_orders_view (o_id, o_date, o_status)
AS SELECT order_id, order_date, order_status FROM orders
WITH READ ONLY;
CREATE VIEW emp_sal (emp_id, last_name,
email UNIQUE RELY DISABLE NOVALIDATE,
CONSTRAINT id_pk PRIMARY KEY (emp_id) RELY DISABLE NOVALIDATE)
AS SELECT employee_id, last_name, email FROM employees;
CREATE VIEW clerk AS
SELECT employee_id, last_name, department_id, job_id
FROM employees
WHERE job_id = 'PU_CLERK'
or job_id = 'SH_CLERK'
or job_id = 'ST_CLERK';