Guide to Oracle SQL (Buffalo State University Version)

Creating Other Schema Objects

Most database end-users will never use SQL to query the database. The SQL developers create VIEWS that the end-user can execute to get the data they need.

SQL Views

A view stores an SQL query that is executed whenever you refer to the view. This is a convenient way to get the desired data because it is easier to run a query stored in a view than to types a query from scratch. Using a view also allows you to provide access to just the data that particular user needs - and nothing else. The view creates the table structure for a result set and then fills in the data when the view is invoked.

CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYSTEM"."QUERY_JIMTABLE" ("Name") AS 
SELECT LASTNAME || ', ' || FIRSTNAME "Name"    
FROM JIMTABLE
ORDER BY "Name";

Figure 13-1: Creating View

Using a `view`

You call a view using the statement, just as you would any other table.

SELECT * FROM query_jimtable WHERE "Name" like '%Jim%';

Output:

Name                                                
----------------------------------------------------
Gerland, Jim

Figure 13-2: Using a View