The CREATE VIEW statement creates a view on one or more tables or views. You can specify a subselect clause that will influence the rows included in the view. If you do not specify names for the selected columns, the system uses the names from the result table.
You can also ensure that every row that is inserted or updated through the view conforms to the definition of the view by specifying the WITH CASCADED CHECK OPTION. A row that does not conform to the definition of the view is a row that cannot be retrieved using that view.
You can also specify WITH LOCAL CHECK OPTION. WITH LOCAL CHECK OPTION is identical to WITH CASCADED CHECK OPTION except that it is still possible to update a row so that it no longer conforms to the definition of the view. This can only happen when the view is directly or indirectly dependent on a view that was defined without either WITH CASCADED CHECK OPTION or WITH LOCAL CHECK OPTION clauses.
Create a view named MA_PROJ over the PROJECT table that contains only those rows with a project number (PROJNO) starting with the letters 'MA' and only the columns for project number (PROJNO), project name (PROJNAME) and employee in charge of the project (RESPEMP). Additionally, call the column for the employee in charge of the project IN_CHARGE.
CREATE VIEW MA_PROJ (PROJNO, PROJNAME, IN_CHARGE)
AS SELECT PROJNO, PROJNAME, RESPEMP FROM PROJECT
WHERE SUBSTR (PROJNO, 1, 2) = 'MA'
To illustrate using WITH CHECK OPTION, consider the following views:
CREATE VIEW V1 AS SELECT COL1 FROM T1 WHERE COL1 > 10
CREATE VIEW V2 AS SELECT COL1 FROM V1 WITH CHECK OPTIONThe following INSERT statement using V2 will result in an error because V2 has a WITH CHECK OPTION and the insert would produce a row that did not conform to the definition of V2.
INSERT INTO V2 VALUES(5)
For more information, see CREATE VIEW in the SQL Reference topic in the
Information Center.