Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 35

The capability to prejoin two or more tables or restrict the columns or rows on a single table has long been available for Oracle users by using views. Views are database objects that look a lot like
tables, but are instead derived from SELECT statements performed on one or more tables.
This chapter begins by briefly introducing the concept of views and the various advantages of incorporating views into your development strategy. It then discusses Oracle’s view support, showing you how to create, execute, and manage views. Finally, you’ll learn how to incorporate views into your PHP applications.

Introducing Views

Even relatively simplistic data-driven applications rely on queries involving several tables. For instance, suppose you want to create an interface that displays each employee’s name, department, and department location. The query might look like this:

select employee_id, last_name, first_name, department_id, department_name, city, state_province, country_name
from employees
join jobs using(job_id)
join departments using(department_id)
join locations using(location_id)
join countries using(country_id)
order by last_name;

Queries of this nature are enough to send shudders down one’s spine because of their size, particularly when they need to be repeated in several locations throughout the application. Another side effect of such queries is that they open up the possibility of someone inadvertently disclosing potentially sensitive information. For instance, what if in a moment of haze you accidentally insert the column SALARY (employees’ monthly salary) or the column SSN (employees’ Social Security number) into this query? This would result in each employee’s salary and SSN being displayed to anybody with the ability to review the query’s results. Yet another side effect of such queries is that any third-party contractor assigned to creating similar interfaces would also have essentially surrep- titious access to sensitive data, opening up the possibility of identity theft and, in other scenarios, corporate espionage.
What’s the alternative? After all, queries are essential to the development process, and unless you want to become entangled in managing column-level privileges (using techniques that are beyond the scope of this book), it seems you’ll just have to grin and bear it. Or you could use views. For example, if you take the time to create a view of the preceding example query, you can execute that query as simply as this:

SELECT * FROM employee_department_view;

621

Also known as a virtual table or a stored query, a view consists of a set of rows that is returned if a particular query is executed. A view isn’t a copy of the data represented by the query, but rather it simplifies the way in which that data can be retrieved by abstracting the query. A view does not contain its own data; the contents of a view are dynamically retrieved every time you access the view. Therefore, any changes to the rows in the underlying tables are automatically reflected in the view the next time you reference it. Views can be quite advantageous for a number of reasons, including the following:

• Simplicity: Certain data items are subject to retrieval on a frequent basis. For instance, asso- ciating an order’s line item with a customer’s order would occur quite often in a customer order management and fulfillment application. Therefore, it might be convenient to create a view called ORDER_ITEM_VIEW, saving you the hassle of repeatedly querying multiple tables to retrieve this information.
• Security: As highlighted previously, there may be situations in which you’ll want to make quite certain some information is made inaccessible to third parties, such as the Social Security numbers and salaries of employees in a corporate database. A view offers a practical solution to implement this safeguard.
• Maintainability: Just as an object-oriented class abstracts underlying data and behavior, a view abstracts the sometimes gory details of a query. Such abstraction can be quite beneficial in instances where that query must later be changed to reflect modifications to the schema.

Now that you have a better understanding of how views can be an important part of your devel- opment strategy, it’s time to learn more about Oracle’s view support.

Creating and Executing User Views

Creating a view is accomplished with the CREATE VIEW statement. Its syntax is as follows:

CREATE VIEW view_name (alias1[, alias2] ...) AS subquery;

The subquery clause is a SELECT statement that may contain one table or join many tables. It can also have a WHERE clause, an ORDER BY clause, and a GROUP BY clause—in other words, anything that a SELECT statement allows. You can specify column aliases for the columns returned from the subquery. Creating a view based on the SELECT statement at the beginning of the chapter looks like this:
CREATE VIEW employee_department_view AS
select employee_id, last_name, first_name, department_id, department_name, city, state_province, country_name
from employees
join jobs using(job_id)
join departments using(department_id)
join locations using(location_id)
join countries using(country_id)
;

Creating a view based on a query can be a boon to users who typically don’t use SQL to join
tables but need to see employee data with the associated location information. In this example, a user wants to see employees whose office is in Seattle:

select * from employee_department_view where city = 'Seattle'
order by last_name;

Running this query using the Oracle Database XE SQL Commands interface produces the results shown in Figure 35-1.

Figure 35-1. View query results from the Oracle Database XE Web interface

Note that in many ways Oracle Database XE treats a view just like any other table. In fact, if you execute the command DESCRIBE EMPLOYEE_DEPARTMENT_VIEW, you’ll see the view characteristics just as you might see a table’s characteristics:

Name Null? Type
------------------------------- -------- -------------------- EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(20) DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE VARCHAR2(25) COUNTRY_NAME VARCHAR2(40)

You might be surprised to know that you can even create views that are updatable. That is, you can insert new rows and update existing ones. This concept is introduced in the later section “Updating a View.”
In yet another similarity to a table, you can select all columns from a view using select * from
tablename, or select specific columns from the view. For instance, it’s possible to return only the employees’ last name and department name:

SELECT last_name, department_name FROM employee_department_view;

This returns the results shown in Figure 35-2.

Figure 35-2. View query results using a subset of view columns

You can also override any default ordering clause when invoking the view; this is assuming the view itself has an ORDER BY clause, which, as we mentioned earlier in the chapter, is not recommended when you create a view. For instance, the EMPLOYEE_DEPARTMENT_VIEW view definition has no ORDER BY clause. What if you want to order the results according to the employee ID number? This is not a problem; the ORDER BY clause you specify when accessing the view overrides any sorting specified in the view definition itself. Just provide your own ORDER BY clause, like this:
SELECT employee_id, last_name, department_name
FROM employee_department_view
ORDER BY employee_id;

This results in the output shown in Figure 35-3.

Figure 35-3. Reordering view query results by employee ID

For that matter, views can be used in conjunction with all clauses and functions, meaning that you can use SUM(), LOWER(), ORDER BY, GROUP BY, or any other clause or function that strikes your fancy.
Table column naming conventions are generally a product of programmer convenience or corporate naming standards, occasionally making for cryptic reading when presented to an end user. When using views, you can improve upon these names by specifying alternate column headings via the optional alias parameters. The following example recreates the EMPLOYEE_DEPARTMENT_VIEW view, replacing the default column names with something a bit more readable:

CREATE VIEW nicer_employee_department_view
("Employee ID", "Last Name", "First Name", "Department ID", "Department Name", "Department City", "Department State/Province", "Department Country")
AS
select employee_id, last_name, first_name, department_id, department_name, city, state_province, country_name
from employees
join jobs using(job_id)
join departments using(department_id)
join locations using(location_id)
join countries using(country_id)
order by last_name;

Here is an example of how you would retrieve rows from the new view:

select * from nicer_employee_department_view;

The results look like those shown in Figure 35-4. And remember, if you cannot change a view’s definition to name columns as you like, you can always specify column aliases within the SELECT statement as you would with any table.

Figure 35-4. A view with more readable column names

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. The OR REPLACE clause 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 use OR REPLACE? Mainly because you might want to know if the view is already there and you forgot how to query the USER_VIEWS view to see what views you already created. (We will tell you about the predefined view USER_VIEWS later 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 the NICER_EMPLOYEE_DEPARTMENT_VIEW view, 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 as SUM().

• It contains DISTINCT, GROUP BY, HAVING, UNION, or UNION 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 the
UPDATE query 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 to UPDATE 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 column CEIL(salary)
will render the view uninsertable.

These rules may be hard to remember; an easy way to know which columns in a view are updat- able is to use the data dictionary view USER_UPDATABLE_COLUMNS and 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 view USER_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

Other View Types

There are a couple of other types of views that you will encounter—data dictionary views and dynamic performance views—especially if you need to query database metadata or information about the running instance. They are very similar to views you create against your own tables, except that they are created when you install Oracle Database XE and are owned by SYS. We give you a brief overview of these two view categories in the following sections.

Data Dictionary Views

Data dictionary views are predefined views that contain metadata about tables, views, indexes, users, and other objects in the database. Like other views, data dictionary views are based on one or more tables. The main difference between data dictionary views and user-created views is that the user SYS owns data dictionary views; in addition, the views themselves may appear to have different results depending on who is accessing them.
Data dictionary views have one of three prefixes:

• USER_: Object metadata of the structures owned by the user (in the user’s schema). These views are accessible by every user and will return different results for each user (unless the user has exactly the same list of tables as another user).
• ALL_: Object metadata of the structures that the user has access to, including both objects owned by the user and objects to which other users have granted the user access. Each of these views contains a column called OWNER because many of the objects in the ALL_ views may reside in different schemas.
• DBA_: Object metadata of all structures in the database. These views are accessible only to users with the DBA system privilege. Like the ALL_ views, these views have an OWNER column.

A common data dictionary view is ALL_TABLES (or USER_TABLES, or DBA_TABLES). If the user HR accesses the ALL_TABLES view, the results contain all tables that the HR user has access to, including both tables owned by the HR schema as well as tables in other schemas that HR could access because of permissions granted by the users in the other schemas. In contrast, USER_TABLES contains only the tables in the user’s schema. The data dictionary view DICTIONARY provides you with the names of all data dictionary views:

describe dictionary

Name Null? Type
----------------------------------------- -------- ----------------------- TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)

Here is the entry for USER_VIEWS:

select * from dictionary where table_name = 'USER_VIEWS';

TABLE_NAME
------------------------------ COMMENTS
------------------------------------------------------------------------ USER_VIEWS
Description of the user's own views

To see the columns within each data dictionary view, you can use the data dictionary view
DICT_COLUMNS:

describe dict_columns

Name Null? Type
----------------------------------------- -------- ---------------------------- TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(30) COMMENTS VARCHAR2(4000)

Most of the metadata in the USER_ category is available in the Oracle Database XE GUI via the Object Browser. To see what objects you have access to in other schemas, you will have to use a SQL command against the ALL_ views. For example, if the HR user wants to know what tables owned by the OE user are available, the HR user would run this query in the SQL Commands GUI or in SQL*Plus:

select * from all_tables where owner = 'OE';

The results returned will look something like this:

OWNER TABLE_NAME
------------------------------ ------------------------------ OE CUST_ORDERS
OE CUST_ORDER_ITEMS

Similarly, you can see the views you have created in your own schema by looking at the
USER_VIEWS view:

select view_name from user_views;

This gives these results for the HR user:

VIEW_NAME
------------------------------ EMP_DETAILS_VIEW EMPLOYEE_DEPARTMENT_VIEW

Dynamic Performance Views

Dynamic performance views are similar to data dictionary views, with one important difference: dynamic performance views are continuously updated while the database is open; they are repopu- lated when the database is shut down and restarted. In other words, the contents of these views are not based on any physical table and instead reside in memory only. The contents of dynamic perfor- mance views primarily relate to the performance of the database. Dynamic performance views begin with the prefix V$ to help distinguish them from data dictionary views.
One common dynamic performance view is V$INSTANCE. This view returns one row of statistics
for each Oracle instance running against the database; since Oracle Database XE only supports one instance per database, V$INSTANCE will always have one row.
Using the following query, you can retrieve some basic information about the instance, including how long the database has been up since the last restart:
select instance_name, host_name, version, edition, startup_time, round(sysdate-startup_time,2) UPTIME from v$instance;

The results of this query look like this:

INSTANCE_NAME HOST_NAME VERSION EDITION STARTUP_T UPTIME
--------------- ------------- ------------- ------- --------- ---------- XE phpxe 10.2.0.1.0 XE 21-JAN-07 4.09

In other words, the database has been up for slightly more than four days.

Using Views to Restrict Data Access

Views also help to facilitate data security: for example, you might want to provide access to some columns in selected HR tables but not others, such as SSNs, birth dates, and other sensitive informa- tion. To solve this problem without data duplication or raising privacy concerns, you can use views such as EMPLOYEE_DEPARTMENT_VIEW to allow other departments to access selected columns from specific tables without allowing access to columns in the view’s base tables.
By default, the user FC (or any other user) cannot access any of HR’s tables; this query returns the message “Table or View Does Not Exist”:

select * from hr.employees;

The user FC does not have access to any of HR’s views either; but the HR user can give access to the view like this:

grant select on employee_department_view to fc;

Now the user FC can see the rows in the view but not in the underlying tables:

select * from hr.employee_department_view;

Incorporating Views into Web Applications

Like the examples in previous chapters, incorporating views into your Web applications is a rather trivial affair. After all, views are virtual tables and can be managed much in the same way as a regular Oracle table, using SELECT, UPDATE, and DELETE to retrieve and manipulate the content they represent. As an example, let’s retrieve rows from the EMPLOYEE_DEPARTMENT_VIEW view created earlier in this chapter. The following PHP script calls the view to retrieve the first ten rows and outputs the results in HTML format:
<?php
// Connect to Oracle Database XE
$c = oci_connect('hr', 'hr', '//localhost/xe');

// Create and execute the query
$result = oci_parse($c,
"select employee_id, last_name, first_name, department_name" . " from employee_department_view where rownum < 11");
oci_execute($result);

// Format the table
echo "<table border='1'>";
echo "<tr>";

// Output the column headers
for ($i = 1; $i <= oci_num_fields($result); $i++)
echo "<th>".oci_field_name($result, $i)."</th>";

echo "</tr>";

// output the results
while ($employee = oci_fetch_row($result)) {
$emp_id = $employee[0];
$last_name = $employee[1];
$first_name = $employee[2];
$dept_name = $employee[3];
echo "<tr>";
echo "<td>$emp_id</td><td>$last_name</td>";
echo "<td>$first_name</td><td>$dept_name</td>";
echo "</tr>";
}

echo "</table>";
oci_close($c);
?>

Executing this code produces the output displayed in Figure 35-6.

Figure 35-6. HTML output from a PHP script accessing the view EMPLOYEE_DEPARTMENT_VIEW

Summary

This chapter introduced views, a feature available in Oracle Database since version 5. Views can greatly cut down on otherwise repetitive queries in your applications as well as enhance security and maintainability. In this chapter you learned how to create, execute, modify, and delete Oracle views and incorporate them into your PHP-driven applications.
The next chapter delves into the topic of functions and procedures to help you encapsulate business rules and iterative tasks into an Oracle object in much the same way that a view can encap- sulate repetitive queries. Both of these enable easy reuse, giving you more time to spend coding your PHP applications.