Views let Oracle users manipulate databases in certain ways. Keep reading to find out how. 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).
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 fromSELECTstatements 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 columnSALARY(employees’ monthly salary) or the columnSSN(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 surreptitious 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;
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, associating 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 calledORDER_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 development strategy, it’s time to learn more about Oracle’s view support.
