Views

The tables of a database define the structure and organization of its data. However, like a piece of sculpture, information in a database can be viewed from many angles. Each view provides a new Perspective. Using SQL*Plus, you can create alternative views of the information in your tables.

A synonym is a name assigned to a table or view that may thereafter be used to refer to it. If you have access to another user's table, a synonym alone may be created without entering user's name as a qualifier.

A view is a SQL*Plus query that is permanently stored in the database and assigned a name. The results of the stored query are "visible" through the view, and SQL*Plus lets you access these query results as if they were, in fact, a "real" a table in the database.
How the DBMS Handles Views?

When the DBMS encounters a reference to a view in a SQL*plus statement, it finds a definition of the view stored in the database. The DBMS then translates the request that reference the view into an equivalent request against the source tables of the view and carries out the equivalent request.

For simple views, the DBMS may construct each row of the view then & there, drawing the data for row from the source table(s). For more complex views, the DBMS must actually materialize the view; i.e., the DBMS must actually carry out the query that defines the view and store its result in a temporary table.

Views provide a variety of benefits and can be useful in many different types of databases. In a personal computer database, views are usually a convenience, defined to simplify database requests. In a production database *installation, views play a central role in defining the structure of the database for its users and enforcing its security. The following are major benefits:

Security: Each user can be given permission to access the database only through a small set of views
that contain the specific data the user is authorized to see, rather than the entire table, thus restricting
the user's access to stored data.

Query simplicity : A view can draw data from several different table's and present it to the user in a
single table, turning what would have been multi table queries into single table Queries against the
view.

Structural simplicity: Views can give a user a "personalized" view of the database structure, presenting
the database as a set of virtual tables that make sense for that user.

Insulation from change. A view can present a consistent, unchanged image of the structure of the
database, even if the underlying source tables are split'. restructured or renamed. ~

Data integrity. If data is accessed and entered through a view, the DBMS can automatically check
the data to ensure that it meets specified integrity constraints.

To create a view, use the CREATE VIEW command. This command statement assigns a name to the view and specifies the query that defines the view.
CREATE [OR REPLACE] [FORCE/NOFORCE] VIEW view name AS query;

To create the view successfully, you must have permission to access all of the tables referenced in the query.

The CREATE VIEW statement can optionally assign a name to each column in the newly created view. If a list of column names if specified, it must have the same number of items as the number of columns produced by the query. Note that only the column names are specified; the data type, length and other characteristics of each column are derived from the definition of the columns in the source tables.

The OR REPLACE statement recreates the view that already exists. You can use this option to change the definition of an existing view without dropping, recreating, and regranting object privileges previously granted on it.

The FORCE creates the view regardless of whether the view's base table exist or the owner has the privileges on them. But to view the VIEW the owner must have the privileges and also the base table must exist.

NOFORCE creates the view only if only if the base tables exist and the owner of the view has privileges on them. The default is NOFORCE

To define a view of Department 3, enter:

SQL > CREATE VIEW MKTEMP AS
2 SELECT EMPNO, ENAME, JOB
3 FROM EMP
4 WHERE DEPTNO = 3;

View Created

In the previous examples you created the view MKTEMP. Now you can act as if MKTEMP were 'a real table with its rows and columns. You can even ask ORACLE to describe it to you:

SQL > DESCRIBE MKTEMP

You can query, it too, just like a normal table like
1. SELECT *FROM MKTEMP;

2. SQL > SELECT*FROM MKTEMP
WHERE JOB! ='SALESMAN';

3. To promote Ramesh Babu to senior salesman(SR SMAN) and view the changes through

MKTEMP, enter

SQL > UPDATE EMP
2 SET JOB ='SR SMAN'
3 WHERE ENAME = 'Ramesh Babu'
1 row updated.

SQL > SELECT *FROM,MKTEMP;

4. If you INSERT or DELETE rows in the underlying tables, entire rows of data can move in and out

of views. As an example, suppose Department 3 adds a new employee:

SQL >INSERT INTO EMP 2 VALUES(8754,'TULI','SALESMAN,'1997, 3 '04 MAY 94',3350,3,O);

1 Row Created

SQL > SELECT*FROM MKTEMP 20RDERBYEMPNO;

In the above example, we have inserted the row for the new employee in the EMP table and then viewed the contents of the MKTEMP which shows the new row.

You may also need to update information in certain views directly, rather than through the underlying tables.

For example, if iyer refused his promotion to senior salesman; although the information to be updated lies in the EMP table, you can update the view MKTEMP instead.

To restore Iyer's original job through MKTEMP and then verify that EMP was updated, enter:

SQL > UPDATE MKTEMP
2 SET JOB ='SALESMAN'

3 WHERE ENAME ='Ramesh Babu';
1 Row updated.

SQL > SELECT EMPNO, ENAME, JOB
2 FROM EMP
3 WHERE ENAME = 'IYER

A view can be updated if the query that defines the view meets all of the following restrictions:

The FROM clause must specify only one updated table; i.e., the view must have a single source table for which the user has the required privileges. If the source table is itself a view, then that view must meet these criteria.

DISTINCT must not be specified; i.e. duplicate rows must not be eliminated from the query results.

Each SELECT item must be a simple column reference; the SELECT list cannot contain expressions, calculated columns, or columns functions.

The WHERE clause must not include a subquery; only simple row by row SEARCH conditions may appear.

The query must not include a GROUP BY or HAVING clause or a reference to the pseudo column ROWNUM.

Altering Views

If a view is created with the compilation error, the same view can be recompiled using the following command:

ALTER VIEW <view name> COMPILE

You can use the ALTER VIEW command to explicitly recompile a view that is invalid. Explicit recompilation allows you to locate recompilation errors prior to runtime. You may want to explicitly recompile a view after altering one of its base tables to ensure that the alteration does not affect the view or other objects that depend on it.

When you issue an ALTER VIEW statement, ORACLE recompiles the view regardless of whether it is valid or invalid.

Complex Views

You can build views from more than one table, of course with the inherent restriction that they cannot be updated. To define such views use a query containing a join.
For example, suppose you want to create a view called EMPSITE from the EMP and DEPTS taws which lists employee details along with their locations:

SQL > CREATE VIEW EMPSITE
2 (EMPNOENAME, DEPTNODNAME,LOCATION)
3 AS
4 SELECT EMPNO, ENAME, PERS, DEPTNO, DNAME,SITE
5 FROM EMP,DEPTS
6 WHERE EMP. DEPTNO = DEPTS.DEPTNO;

View Created

The five words in parentheses after CREATE VIEW EMPSITE are column headings of the view EMPSITE they correspond to the five columns named in the query. Thus DEPT'S SITE column will be known in the view as LOCATION.

To list information about the employees in departments 2 and 4 from view EMPSITE, enter:

SQL > SELECT *1 FROM EMPSITE
2 WHERE DEPTNO IN(2,4);

You can join any number of tables to form a view. Suppose you want to create a table EMPGRADE that contains grades, employee names, salaries, department locations. This information is scattered among three tables viz., END, DEPTS and GRADES. To create this view from a three way join, enter:

SQL > CREATE VIEW EMPGRADE

2 (GRADE,EMPLOYEEJOB,PAYSITE)
3 AS
4 SELECT GRADE, ENAMEJOB,PAYSITE
5 FROM EMP, GRADES,DEPTS
6 WHERE PAY BETWEEN LOW AND HIGH
7 AND PERS.DEPTNO = DEPTS.DEPTNO;

View Created

Now you can query EMPGRADE as if it were a real table. To list those employees who are in grade 3 & 5, enter:

SQL > SELECT* FROM EMPGRADE
2 WHERE GRADE IN (3,5)

3 ORDER BY GRADE, JOB;
For queries against views such as those shown above, the DBMS has to work hard to generate query results for the single table query against the view. However, for the end user of the database it must be easier to write and understand the single table query that references the view.

You may also join views to tables or to other views, akin to table joins. When you specify the columns to be displayed use the notation view "to refer to " all columns in a view (or a table). "For example, to list all the columns from EMPSITE, plus JOB from EMP, you would say:

SELECT EMPSITE. I,
FROM EMPSITE, EMP .........

The ability to use a view as though it were a table extends to using expressions and functions. In fact you can give a view a pseudocolumn, just like the one you add to a base table, to display the results of mathematical expressions and functions. These expressions appear like other columns in the view, but the values in their "fields" are calculated from the underlying tables each time the view is queried. Since such field are not actually stored in the tables, they are sometimes called virtual columns or fields.

When you define virtual columns in a view, you must give it a name, whether explicitly (in the CREATE VIEW clause) or implicitly (by giving expression a column alias in the query that defines the view).

Suppose you want to define a view SALARY which computes an employees annual salary and presents it as a virtual colurmn then enter:

SQL > CREATE VIEW SALARY
2 (NAME, MONTHLY PAY, ANNUAL PAY, EMPNO)
3 AS SELECT ENAME, PAY, PAY*12, EMPNO
4 FROM EMP;

View created.

SQL > SELECT *FROM SALARY
2 WHERE EMPNO < 3000;

In the above query, when you select from the SALARY view, the virtual column ANNUAL PAY is also displayed.

The difference between the pseudo columns you create with a query and those you create in a view is that, in the later, the columns and their calculated contents will be available whenever you query the view.
You may also define a view using a query with group function's and GROUP BY clauses. This type of view is called a grouped view. Grouped views perform the same function as grouped queries; they group related rows of data and produce one row of query results for each group, summarizing the data in that group. A grouped view makes these grouped query results into a virtual table, allowing you to perform further queries on them.

As an example, "suppose you want to define a view that calculates the compensation statistics for each department in the sample company:

SQL > CREATE VIEW DEPT PAY 2 (DEPTNO, LEASTPAY, MEDPAY, HIGHPAY, TOTALPAY) AS 3 SELECT DEPTNO, MIN(PAY),AVG(PAY), MAX(PAY). SUM(PAY) 4 FROM EMP 5 GROUP BY DEPTNO;

View created.

Now that the view has been defined, you can view the compensation statics by querying the view DEPT PAY

SQL> SELECT*FROM DEPT PAY,

Before we end this chapter, there is a minor piece of house keeping to be done.

SQL > DELETE FROM EMP WHERE ENMAE = 'TULI'

Domain Name Search