In this second part of a three-part article on using views in PHP and Oracle, you will learn how to modify, delete, and update a view. You will also learn about different view types. This article is excerpted from chapter 35 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
Modifying a View
An existing view can be modified using the CREATE OR REPLACE VIEW statement instead of just CREATE VIEW. In other words, you recreate the view as if the view never existed. TheOR REPLACEclause replaces (drops and recreates) the view if it already exists, or just creates it if it does not exist. So you might ask, why not always useOR REPLACE? Mainly because you might want to know if the view is already there and you forgot how to query theUSER_VIEWSview to see what views you already created. (We will tell you about the predefined viewUSER_VIEWSlater in this chapter in the section “Data Dictionary Views.”) You can also use the Oracle Database XE Object Browser from the Oracle Database XE home page to query your database objects such as tables, views, indexes, and so forth.
Deleting a View
Deleting an existing view is accomplished with the DROP VIEW statement. The basic syntax looks like this:
DROP VIEW view_name;
For instance, to delete theNICER_EMPLOYEE_DEPARTMENT_VIEWview, execute the following command:
DROP VIEW nicer_employee_department_view;
Updating a View
The utility of views isn’t restricted solely to abstracting a query against which a user can execute SELECT statements. It can also act as an interface from which the underlying tables can be updated. For example, suppose that an office assistant is tasked with updating key columns in a table consisting of employee contact information. The assistant should be able to view and modify only the employee’s first name, last name, and department ID, and should be prevented from viewing or manipulating other columns such as monthly salary. The view EMPLOYEE_DEPARTMENT_VIEW, created earlier in this chapter, will satisfy both conditions, acting as both an updatable and a selectable view. Here are a few restrictions on when a view against a single table or joined tables cannot be updated:
It contains an aggregate function such asSUM().
It containsDISTINCT,GROUP BY,HAVING,UNION, orUNION ALL.
It contains a subquery.
It updates columns from more than one table in a multitable view.
It refers solely to literal values and single-row function results, meaning there are no tables to update.
For example, to modify employee David Austin’s last name to Houston, you can execute theUPDATEquery against the view, like this:
update employee_department_view set last_name = 'Houston' where employee_id = 105;
The term updatable view isn’t restricted solely toUPDATE queries; you can also insert new rows into a view defined against a single table, provided that the view satisfies some additional constraints that include the following:
The view must contain all the columns in the underlying table that aren’t assigned a default value.
The view columns cannot contain an expression. For example, the view columnCEIL(salary)will render the view uninsertable.
These rules may be hard to remember; an easy way to know which columns in a view are updatable is to use the data dictionary viewUSER_UPDATABLE_COLUMNSand query for columns in your view. We discuss data dictionary views later in this chapter. To see which columns in your view are updatable, insertable, or deletable, use this query against the data dictionary viewUSER_UPDATABLE_COLUMNS:
select * from user_updatable_columns where table_name = 'employee_department_view';
The query results in Figure 35-5 show you the columns that are updatable, insertable, and deletable.
Figure 35-5.Updatable, insertable, or deletable view columns using USER_UPDATABLE_COLUMNS
