Working with Views in an Oracle database

In this post I will be sharing with you the basics on how to create and manage views on an oracle database.

Views are logical tables based on the images of one or more tables in a database.

Views form part of the objects created and managed in a schema.

A schema is a collection of all objects owned by a particular user views being one of them

When a user is created, the schema for the user is automatically created.

Let’s now understand how Views are created and managed.

Note 

This tutorial will be done using SQLPLUS command prompt.

Click here to view this tutorial using Oracle LIVE SQL

Like said earlier views are logical tables based on one or more tables in a database.

Tables on which views obtain their images are called base tables. These base tables are the heart of the views. Once the tables are dropped or deleted, the view will no longer be functional as it is directly dependent on the table’s data. I will demonstrate this to you latter in this post.

You can create a view on a single table or with a query that incorporates multiple base tables.

Advantages of views

·         Views can be used to restrict access to sensitive tables or

·         Reduce the complexity of writing queries by being able to reference multiple joined tables with a single view name.

 You use the CREATE VIEW statement to create a view. Let’s look at the following example.

HR has requested a table be created to directly see Staff and their respective salaries. In addition they also required a way to block staff from viewing the salary payment of all executives earning above $10,000.

You were contacted to provide a solution.

In this example we will do the following:

1.    Create a table called PAYROLL by extracting the data from the employees table

2.    Create a view called PAYROLL _V by taking its data image from the PAYROLL table.

3.    Create a restriction in the view to exclude all staff that earn above $10,000.

Now let’s write the script.

CREATE TABLE PAYROLL as

Select First_name, Last_name, salary from employees;

CREATE VIEW PAYROLL_V as

select * from PAYROLL

where salary <10000;

Now let’s take a count of the PAYROLL table and PAROLL_V view 

As you can see, the number of records has reduced in the PAYROLL_V view.

Let us see if we can see the record of any staff earning above $10,000 in our new view.

Select * from payroll_v

Where salary>10000;

However if we select it from the main table PAYROLL it should show. Let me demonstrate that with a query on the table:

Select * from payroll

Where salary>10000;

So that is how a view works.

It is used to restrict access to any data/record.

Views contain virtual data

As said earlier a view is NOT a table and its data are virtual. They are actually images of the original data in a table.

Hence when a table is dropped the view though exist will stop being functional. Let me demonstrate this.

We are already aware that the recent view we created PAYROLL_V is taken its image from the PAYROLL table.

Now let me delete all data from the PAYROLL table and let see the outcome

Truncate table PAYROLL;

Let us now view the records in our view PAYROLL_V

Select * from PAYROLL_V;

You can now see the dependence of the view on the table it is mapped to.

Now let’s go further to remove the entire table and see the outcome.

Drop table PAYROLL;

Let us now view the records in our view PAYROLL_V

Select * from PAYROLL_V;

As you can see the view no longer functions. This is because the table it references no longer exist.

Deleting a VIEW

You can also delete a view from the database by using DROP VIEW statement.

Let us now delete out invalid view PAYROLL_V

DROP VIEW PAYROLL_V;

Let’s confirm is if the view still exist.

Select * from PAYROLL_V;

As you can now see the view no longer exist.

And that’s it about management of views. I hope this post has been interesting as much as it was informative.

Stay tuned for more.