Chapter 8
CHAPTER 8
------
VIEWS, SYNONYMS, AND SEQUENCES
This chapter covers three important types of database objects—views, synonyms, and sequences. A view is a logical or virtual table. Views have several uses. A major advantage of a view is that it can simplify query writing for system users who do not have an extensive background in SQL. This enables system user to write some of their own queries to access database information. Views also provide an element of data security by limiting the data that a system user can access.
Synonyms are simply alias names for database objects. You can create synonyms as well as use synonyms created by other system users. Synonyms also simplify query writing and provide an element of system security by disguising the actual name of a database object.
Sequences are special database objects that support the automatic generation of integer values, and are often used to generate primary key values for tables. For example, order numbers for customer orders can be automatically generated, thus guaranteeing that each customer order number is unique.
Objectives
You will explore the creation and maintenance of each of these three specialized database objects. You will also learn how to alter and drop these objects. In the review section of the chapter, you will be presented with a number of exercises designed to reinforce key learning points for views, synonyms, and sequences. Your learning should focus on the following objectives:
- Create a single table and join table views—include functions; drop a view.
- Insert, update, and delete table rows using a view.
- Create a view with errors.
- Create a materialized view; drop a materialized view.
- Create a public and a private synonym; drop a synonym; rename a synonym.
- Create a sequence including all of the CREATE SEQUENCE options.
- Access sequence values when storing rows in related tables.
- Alter and drop a sequence.
VIEWS
A database view is a logical or virtual table based on a query. Figure 8.1 illustrates this concept. Managers often need to access information from more than one table; however, they rarely need to access data from all the columns in a table. Figure 8.1 shows the employee and department tables that, as you know, are related to one another. Managers working with employee salaries may want to access employee Social Security numbers (SSNs), salary levels, and associated department names to which an employee is assigned. This is made easier for a manager through the creation of the vwEmployeeDepartment view.
Figure 8.1
In fact, it is useful to think of a view as a stored query because views are created with a CREATE VIEW command that incorporates use of the SELECT statement. Further, views are queried just like tables. This means that from your perspective as a developer or from a database system user's perspective, a view looks like a table. The definition of a view as an objectis stored within a database's data dictionary; however, a view stores no data itself. A database also stores the execution plan for creating a view—this means that data can be retrieved rapidly through use of a view even though the actual data presented by a SELECT query of a view is not stored as part of a view. Rather, the data is "gathered together" each time that a view is queried from the database tables for which a view is defined—these are termed base tables.
To understand view objects better, let us begin by creating a view of the employee table. The employee table has a fairly large number of columns. Clearly these columns will not interest all managers. The manager of employee parking is concerned with which employees are assigned to which parking spaces, while a human resources department manager is concerned with other facts such as employee name and gender. The view definition provided in SQL Example 8.1 creates a view named vwEmployeeParking.
/* SQL Example 8.1 */
CREATE OR REPLACE VIEW vwEmployeeParking (Parking, EmployeeName, SSN)
AS
SELECT ParkingSpace, LastName||', '||FirstName, SSN
FROM Employee
ORDER BY ParkingSpace;
View Created.
Examine SQL Example 8.1. First, note that the object name has a two-character prefix "vw" to denote that this is a view. This is a common practice when naming views, although it is not mandatory. The vwEmployeeParking view contains three columns. One of the columns is named EmployeeName. The EmployeeName column is actually created by concatenating the LastName and FirstName columns of the employee table. Further, the rows of the view are sorted by ParkingSpace.
One of the nice features of the vwEmployeeParking view is that it is simple. If you manage employee parking, you can select information about employees without having to deal with columns of data that are irrelevant to your job. This view also limits the information that employee parking personnel can access; thus, columns containing information such as employee salaries are hidden from members of the parking department. After all, parking managers do not have a "need to know" with regard to everyone's salary in an organization. Let us examine the information provided by the view with a SELECT query, as shown in SQL Example 8.2.
/* SQL Example 8.2 */
COLUMN EmployeeName FORMAT A25;
SELECT *
FROM vwEmployeeParking;
PARKING EMPLOYEENAME SSN
------
8 Bock, Douglas 215243964
9 Bordoloi, Bijoy 261223803
10 Sumner, Elizabeth 216223308
more rows are displayed…
Notice that the only columns in the query are those defined as part of the view. Additionally, if the need arises, you can rename the columns in the view so that they are more meaningful to the prospective view user as was done in this example. This is especially useful if the column names in the underlying table or tables are a bit cryptic. Further, while the view rows are sorted automatically by theParkingSpace column, you can alter this sorting by adding an ORDER BY clause to the SELECT command used to access the view. Each of these features adds to the simplicity of data retrieval through view usage.
CREATING A VIEW
Now that you have studied an example view, we will examine the full syntax of the CREATE VIEW command.
CREATE VIEW Syntax
The general syntax is given below. The CREATE VIEW command has a number of options and we will learn about each of these in this section.
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW <ViewName> [(Column Alias Name….)] AS <Query> [WITH [CHECK OPTION] [READ ONLY] [CONSTRAINT]];
The OR REPLACE option is used to re-create a view that already exists. This option is useful for modifying an existing view without having to drop or grant the privileges that system users have acquired with respect to the view. You will learn about user privileges later in your study of SQL. If you attempt to create a view that already exists without using the OR REPLACE option, Oracle will return the ORA-00955: name is already used by an existing object error message and the CREATE VIEW command will fail. The query in SQL Example 8.3 uses the OR REPLACE option.
/* SQL Example 8.3 */
CREATE OR REPLACE VIEW vwJunk AS
SELECT *
FROM ProjectAssignment;
View created.
The FORCE option allows a view to be created even if a base table that the view references does not already exist. This option is used to create a view prior to the actual creation of the base tables and accompanying data. Before such a view can be queried, the base tables must be created and data must be loaded into the tables. This option can also be used if a system user does not currently have privileges on the underlying tables needed to create a view.
The NOFORCE option is the opposite of FORCE and allows a system user to create a view if they have the required privileges to create a view, and if the tables from which the view is created already exist. This is the default option.
The WITH READ ONLY option allows creation of a view that is read-only. You cannot use the DELETE, INSERT, or UPDATE commands to modify data for a read-only view.
The WITH CHECK OPTION clause allows the update of rows that can be selected through the view. It also enables you to specify constraints on values. The CONSTRAINT clause works in conjunction with the WITH CHECK OPTION clause to enable a database administrator to assign a unique name to the CHECK OPTION. If a database administratoromits the CONSTRAINT clause, Oracle will automatically assign the constraint a system-generated name that will not be very meaningful.
Example CREATE VIEW Commands
The CREATE VIEW command shown in SQL Example 8.4 creates a view named vwEmployee8. This view is defined to display each employee's social security number (SSN), first name, and last name, but only for employees assigned to department 8. The view has a different structure than the employee table in terms of the columns in the view. The view stores a subset of the employee table rows because the rows accessible through the view are restricted to employees assigned to department 8.
/* SQL Example 8.4 */
CREATE VIEW vwEmployee8 AS
SELECT SSN, FirstName, LastName
FROM Employee
WHERE DepartmentNumber=8;
View created.
SQL Example 8.5 shows a simple query of the vwEmployee8view.
/* SQL Example 8.5 */
SELECT *
FROM vwEmployee8;
SSN FIRSTNAME LASTNAME
------
981789642 Lester Simmons
890536222 Beverly Boudreaux
890563287 Adam Adams
834576129 Billy Thornton
457890233 William Clinton
It is also possible to create a view that has exactly the same structure as an existing database table. The view named vwDepartment in SQL Example 8.6 has exactly the same structure as department table. Further, the view provides access to all of the rows of the department table.
/* SQL Example 8.6 */
CREATE VIEW vwDepartment AS
SELECT *
FROM Department;
View created.
Now it appears that the vwDepartmentis not terribly useful since it simply duplicates the department table in terms of data row accessibility. However, we can recreate the view by using the OR REPLACE clause to create a view that is read-only by specifying a WITH READ ONLY clause. SQL Example 8.7 shows the revised CREATE VIEW command. The new read-only version of vwDepartment restricts data manipulation language operations on the view to selection. Additionally, anyone that previously had access to the vwDepartment will continue to have access to the new view definition. Note that the CONSTRAINT clause is used to name the WITH READ ONLY constraint as vwDepartmentReadOnly. This enables a DBA to more easily determine the status of constraints for views.
/* SQL Example 8.7 */
CREATE OR REPLACE VIEW vwDepartment AS
SELECT *
FROM Department WITH READ ONLY CONSTRAINT vwDepartmentReadOnly;
View created.
FUNCTIONS AND VIEWS: JOIN VIEWS
In addition to specifying columns from existing tables, you can use single row functions consisting of number, character, date, and group functions as well as expressions to create additional columns in views. This can be extremely useful because the system user will have access to data without having to understand how to use the underlying functions. Consider the query in SQL Example 8.8, which illustrates the usage of the MAX and MIN functions within views.
/* SQL Example 8.8 */
CREATE OR REPLACE VIEW vwDepartmentSalary (Name, MinSalary, MaxSalary,
AvgSalary)
AS
SELECT d.DepartmentName, MIN(e.Salary), MAX(e.Salary), AVG(e.Salary)
FROM Employee e JOINDepartment d
ON(e.DepartmentNumber=d.DepartmentNumber)
GROUP BY d.DepartmentName;
View created.
The vwDepartmentSalary view can be queried to produce a result table with the department name (from the department table), as well as three computed columns. These three columns store the minimum, maximum, and average salaries for members of the department, but the data used to produce a result table with these three columns actually come from the employee table. Thus, this view is also known as a join view because it joins the department and employee tables. Further, the data will be grouped by department name.
Now we will execute the SELECT statement shown in SQL Example 8.9 to display data from the vwDepartmentSalary view. Notice how simple the query is to write as compared to the query used to create the view. This query can be executed by a system user with limited SQL training and enable the system user easy access to information that may be used repeatedly over time.
/* SQL Example 8.9 */
COLUMN MinSalary FORMAT $999,999.99;
COLUMN MaxSalary FORMAT $999,999.99;
COLUMN AvgSalary FORMAT $999,999.99;
SELECT *
FROM vwDepartmentSalary;
NAME MINSALARY MAXSALARY AVGSALARY
------
Admin/Labs $5,500.00 $22,000.00 $15,006.67
Critical Care-Cardiology $22,325.00 $22,325.00 $22,325.00
Emergency-Surgical $16,500.00 $32,500.00 $26,119.00
more rows are displayed…
DROPPING VIEWS
A database administrator (DBA) or view owner can drop a view with the DROP VIEW statement. If a view has defined constraints, then you need to specify the CASCADE CONSTRAINTS clause when dropping a view; otherwise, the DROP VIEW statement fails to process. SQL Example 8.10 drops the vwDepartment object and also illustrates use of the CASCADE CONSTRAINTS clause.
/* SQL Example 8.10 */
DROP VIEW vwDepartment CASCADE CONSTRAINTS;
View dropped.
If another view or other database object such as a synonym or materialized view (both of these objects are discussed later in this chapter) references a dropped view, Oracle does not drop these database objects; rather, Oracle marks them as invalid. You can drop these invalid objects or redefine them in order to make them valid again.
VIEW STABILITY
Remember that a view does not actually store any data. The data needed to support view queries are retrieved from the underlying database tables and displayed to a result table at the time that a view is queried. The result table is stored only temporarily.
If a table that underlies a view is dropped, then the view is no longer valid. Attempting to query an invalid view will produce an ORA-04063:view "VIEW_NAME" has errors error message. We will examine this by creating a test table with a single column. SQL Example 8.11creates a table named test, store two data rows to the table, and display all the data from the table.
/* SQL Example 8.11 */
CREATE TABLE Test (
TestRow VARCHAR2(10));
INSERT INTO Test VALUES ('Test row 1');
INSERT INTO Test VALUES ('Test row 2');
SELECT *
FROM test;
TESTROW
------
Test row 1
Test row 2
As you can see, the test table was created and we were able to access data from the table. The commands in SQL Example 8.12create a view named vwTest with the same structure and data as our test table. The query in SQL Example 8.13 displays all the data from the view vwTest.
/* SQL Example 8.12*/
CREATE VIEW vwTest AS
SELECT *
FROM Test;
/* SQL Example 8.13 */
SELECT *
FROM vwTest;
TESTROW
------
Test row 1
Test row 2
Not surprisingly, the result table for the vwTest is identical to the result table for the test table. Now let’s drop the test table and query the vwTestagain. Oracle returns the ORA-04063 error message because the vwTest object is now invalid.
/* SQL Example 8.14 */
DROP TABLE test;
/* SQL Example 8.15 */
SELECT *
FROM vwTest;
ERROR at line 2:
ORA-04063: view "DBOCK.VWTEST" has errors
What do you think will happen if we simply recreate the test table and again insert the two data rows into the table—will a SELECT statement that accesses vwTest execute properly? SQL Example 8.16 shows the commands to create the table, populate it, and query the view.
/* SQL Example 8.16 */
CREATE TABLE Test (
TestRow VARCHAR2(10)
);
INSERT INTO Test VALUES ('Test row 1');
INSERT INTO Test VALUES ('Test row 2');
SELECT *
FROM vwTest;
TESTROW
------
Test row 1
Test row 2
The query of vwTest again produces the expected result table. This demonstrates that view definitions are stable, but this only holds if the table that is recreated exactly matches the original table definition.
INSERTING, UPDATING, AND DELETING TABLE ROWS THROUGH VIEWS
In this section, you will learn basic concepts regarding how to insert, update, and delete table rows through the use of views. The concept of inserting rows into tables through views is a complex one. Likewise, updating existing table rows through views is equally or more complex. Deleting rows is considerably simpler.
When you execute an UPDATE, DELETE, or INSERT DML statement on a view, you are actually manipulating the data rows for the base table or tables on which the view is defined. There are restrictions on the use of UPDATE, DELETE, and INSERT statements with views.
First, to use the UPDATE, DELETE, or INSERT statement with a view, the view must be updateable. A view is updateable if the SELECT clause does not specify any aggregate function in the SELECT listing. Additionally, the view could not have been created through use of a TOP, GROUP BY, DISTINCT, or UNION clause or clauses. It is permissible for aggregate functions to be used in a SELECT subquery in a FROM clause. Also, the view cannot have any derived columns in the SELECT list.