Page 220
| NOTE |
When used with a subquery, the CREATE TABLE statement copies only one kind of table or column constraint: the NOT NULL constraint. If you want the new table to have all the existing constraints, you have to specify them with an ALTER TABLE statement. |
A view is a query of one or more tables that provides another way of presenting information. A view does not actually contain or store its own data; in fact, you can think of a view as a virtual table. The only storage that a view actually requires is the SELECT statement that defines it.
NEW TERM
A view is a stored query based on a query of one or more tables.
You can use a view to perform the following tasks:
The syntax for creating a view is really quite simple.
To define a view, use this syntax:
CREATE VIEW view-name (column1,...,columnN) AS select-statement
The variables are defined as follows:
view-name is the name of the view (subject to the same requirements as other Oracle object names).
column1 through columnN are the column names of the view that correspond to the columns referenced in select-statement.
select-statement is a valid Oracle SELECT statement.
The following simple view is based on a single table. Suppose several administrators at
Flugle College have requested a very simple way to retrieve the current cost for each course.
The current cost for a course is the number of units multiplied by $250 per unit plus
any
Page 221
additional fees. Even though you've shown the administrators how to construct a simple query, they want you to streamline the process.
Listing 8.36 illustrates the view that you define to meet their needs. Here is a description of each line in the SQL statement that creates the view. The first line specifies the name of the view: Course_Cost. The second line lists the five columns that compose the view: Course_ID, Department_ID, Title, Description, and Cost. The third line indicates that the view is to be created from a query. The fourth line specifies the first four columns to be returned by the view: Course_ID, Department_ID, Title, and Description. The fifth line indicates that the fifth column in the view—Cost—is calculated by multiplying Units by $250 per unit and adding Additional_Fees.
Listing 8.36. Creating a simple view.
SQL> create view Course_Cost
2 (Course_ID, Department_ID, Title, Description, Cost)
3 as
4 select Course_ID, Department_ID, Title, Description,
5 Units*250 + Additional_Fees
6 from Course;
View created.
SQL> select Department_ID, Course_ID, Title, Cost
2 from Course_Cost
3 order by Department_ID, Course_ID;
DEPARTMENT COURS TITLE COST
---------- ----- ------------------------------ ---------
ANTHRO 101 INTRO TO ANTHROPOLOGY 750
ANTHRO 174 PHYS ANTHRO FIELD TECHNIQUES 805
ANTHRO 189 SEMINAR ON NACIMERA 507.5
BIO 101 INTRO TO BIOLOGY 805
BIO 177 INVERTEBRATE ANATOMY 815
BIO 178 MAMMALIAN ANATOMY 820
ECON 101 INTRO TO ECONOMICS 775
ECON 189 MONETARY POLICY 1500
ECON 199 WORKSHOP ON MARX 750
ENG 101 INTRO TO STRUCTURES 825
ENG 102 INTRO TO CIRCUIT THEORY 795
ENG 103 INTRO TO DYNAMICS 785
ENG 199 SEMINAR ON CHAOS 545
ENGL 101 INTRO TO ENGLISH LIT 750
ENGL 189 EVOLUTIONARY GRAMMAR 500
ENGL 192 MODERN ENGLISH LIT 750
ENGL 193 SEMINAR ON THEME ANALYSIS 500
HIST 115 EARLY AMERICAN HISTORY 750
HIST 184 MODERN EUROPEAN HISTORY 750
HIST 199 WORKSHOP ON JEFFERSON 750
MATH 101 GENERAL CALCULUS 750
continues
Page 222
Listing 8.36. continued
MATH 189 NUMBER THEORY 750 MATH 50 PRE-CALCULUS 750 MATH 51 ADVANCED ARITHMETIC 760 PHILO 101 INTRO TO PHILOSOPHY 750 PHILO 174 ANCIENT GREEK PHILOSOPHERS 750 PHILO 198 MODERN PHILOSOPHY 750 PSYCH 101 INTRO TO PSYCHOLOGY 775 PSYCH 181 WORKSHOP ON NEUROSES 575 PSYCH 183 WORKSHOP ON NORMALITY 545 PSYCH 185 ABNORMAL PSYCHOLOGY 770 PSYCH 501 PSYCH IN FILM 825 32 rows selected.
Oracle provides several mechanisms for restricting data access: views, database triggers, and table and column privileges. Specific privileges on these objects can be granted to database roles and individual users. You need to assess the security requirements of your application to determine which method is most appropriate.
If you use a view to restrict data access, several methods are available to you. Each method offers a different level of control over data access, from coarse to very fine. A coarse control enables you to define a view that is a subset of its base table. For example, the Student table includes address information, which you may not want to share with all users who need access to the table. Listing 8.37 shows how to create a view of the Student table that doesn't include the personal information. A very fine level of control can be achieved with a database trigger; an example is presented on Day 12, "Developer/2000: Introduction to Oracle Forms."
Listing 8.37. Creating a view based on a subset of a table's columns.
SQL> create view Student_No_Personal 2 as 3 select Student_ID, Last_Name, First_Name, MI, Year, Email 4 from Student; View created. SQL> describe Student_No_Personal Name Null? Type ------------------------------- -------- ---- STUDENT_ID NOT NULL VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) FIRST_NAME NOT NULL VARCHAR2(25) MI VARCHAR2(1) YEAR VARCHAR2(25) EMAIL VARCHAR2(100)
Page 223
As you see in Listing 8.37, the SQL*Plus DESCRIBE command doesn't indicate whether the object description belongs to a table or view. Even if the user knows that the student's address is stored in the Student table, the view can't return the column because it isn't contained in the view definition.
During application development, you must often deal with developers and users who have differing organizational perspectives and a broad range of technical sophistication. As a result, you should use the mechanisms that Oracle offers to customize the environment for developers and users.
I include developers in this category because, like users, their knowledge of SQL in general and Oracle in particular does vary. For example, many forms and reports require the joining of several tables. The use of views can simplify this process because, as the application architect, you can define the views with which developers need to be concerned.
Views are also an excellent way to customize the database environment for end users. This is especially true for large organizations that access the same information for different purposes. Typically, each group has its own name for referring to the same piece of information. Because of the widespread use of third-party ad hoc query tools such as Business Objects, IQ Objects, and Oracle Discoverer/2000, a column name should accurately describe the information it contains to help an end user determine which columns to query. By creating a view, you can customize the column names that a group of users sees.
A view can hide the complexity that exists in a multiple-table join. By defining a view, users are freed from learning the idiosyncrasies of the SELECT statement. For instance, if you want to provide a consolidated view of a scheduled class at Flugle College, you might join information from the Class, Course, and Instructor tables. Listing 8.38 contains the CREATE VIEW statement used to construct the view based on the three-table join.
Listing 8.38. A view based on a multiple-table join.
SQL> create view Class_Summary as
2 select Class_ID, Class_Building, Class_Room,
3 CL.Department_ID, CL.Course_ID, CO.Title,
4 Last_Name, First_Name
5 from Class CL, Course CO, Instructor I
6 where
7 CL.Department_ID = CO.Department_ID and
8 CL.Course_ID = CO.Course_ID and
9 CL.Instructor_ID = I.Instructor_ID;
View created.