Views

Perspective (view) has the following features:

• is defined on the basis of a base table or a different perspective
• is stored in a SELECT command that defines it
• Do not store the data - operating data stored in the base tables

Perspectives we use to:

• restrict access to all data from the table
• help users to download the results of complex queries
• to free users from going into a data structure
• to provide users with basic data is formatted and presented in a readable way.

Creating a perspective

The CREATE VIEW

The perspective is created with the CREATE VIEW:
CREATE [OR REPLACE] VIEW view_name
[(Column-list)]
AS
SELECT ...

-List is a list of names of columns in perspective, they have to choose items from a list of SELECT.

If the prospect of that name already exists, to create the new one we need to remove old or use the OR REPLACE option.

Example

To create a perspective that contains some data about staff employed as 'MANAGER', we write:

CREATE VIEW Emp_Manager
AS
SELECT empno, ename, sal, DEPTNO
FROM emp
WHERE job = 'MANAGER';

Perspective is used like a normal table:

SELECT *
FROM Emp_Manager
ORDER BY ename;

The definition of Perspective may not be an ORDER BY clause.

Using view

Prospects can also serve to modify the data in the base table.
If you modify the data through the perspective of the base table, you may find that after the modification will be available through this perspective. To avoid this, we can apply CHECK OPTION option, so that they were not allowed such modifications of rows that will throw them out of perspective.

Example

CREATE VIEW Emp_Manager
AS
SELECT empno, ename, sal, job, DEPTNO
FROM emp
WHERE job = 'MANAGER'
WITH CHECK OPTION;

The system will not now change the line of the job the manager to another value.

Removing the view

View is removed with DROP VIEW:
DROP VIEW view_name;

The prospect can only be removed by the owner or administrator.

Anyone who starts working with the Oracle must be identified by entering the ID and password to be able to perform the operations to which as the user is authorized.