VIEWS importance

View:

Most of the databases concepts are built with the four common properties as Data Integrity, Data security, Data sharing, RDBMS. Let’s see, why we are using the VIEWS concept in real-time and how to use it?  For example, we will take a banking example.  Bank is ready to issue the loan for some specific customer as a crop loan. The customer wants to know about is status or is eligibility to get the loan.  He enter into bank and he went to clerk and enquiring about loan status. Clerk will check his status and he will give a replay as “sir u have perfectly suitable for this loan but I am not an authorized person please try to take some help from Assistant manager [AM]” . The customer went to AM and he is asking about loan. The AM has to check the details if he is an eligible person and he will told you how much amount you got but he was not an authorized person to confirm the loan, he will send you to manager. Manager will confirm your loan and sanction. In the above application we had a bank, a customer and three different authorized persons are able to check the details of a single customer entity. In this case we need to maintain 3 different tables to 3 difference persons means you may get redundancy in your application. No one can allow you to maintain the application like this. Instead of this, you can generate a single table with the multiple copies based on the authority they want but as a DBA we don’t want to waste the physical memory. So we can generate non-space demanding object based on requirement. Exactly with the help of views we can do this.

Definition:

     “Views are database objects which does not store the data and provide the authority level of security”   

-- Views can be created based on the parent table or base table.  Views can be created as object views or         relational views, LOBs, object type, refs , nested tables, varray.

-- We can do the DML operations on one or more tables,  select, desc is also possible.

Advantages:
 -- Views are very secure, it can prevent from all users to accessing the data.
 -- Views can reduce the redundancy in our applications.
 -- We can create a view from another view and Views can also follow the data sharing.

Syntax:

-- Please refer from ORACLE DOCS. It’s totally based on my hands-on practice.

CREATE [OR REPLACE] [FORCE] [NOFORCE] VIEW View_name

AS

QUERY OR [SUBQUERY]

[WITH {CHECK OPTION/READ ONLY} CONSTRAINT constraintName];

We will see how this syntax will helpful to us. Simple example to create a view

Ex:-  CREATE VIEW BLOG AS SELECT * FROM EMP;

Types of views:-

1)  Simple views:-

     -- Simple views can be created based on the single tables. We can create simple views with JOIN condition, GROUP BY,HAVLING CLAUSE, SET OPERATIONS  and DISTINCT or UNIQUE

2) Complex views:-

-- Complex views can be based on the one or more tables, with the combination of joins

Properties of views:- 
-- In all the database systems, whenever we are creating views , it automatically produce its own definition        or structure.
-- In oracle if you want to view, view definitions from “USER_VIEWS” or “DBA_VIEWS” data dictionary.      USER_VIEWS can be seen in your personal account or as a normal user. DBA_VIEWS  can be seen in      your super user as[system, sys, sysdba]

Ex:-

1)       DESC  USER_VIEWS;

2)      SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=’EMP’;

PREREQUISITES:-

-- To create a view in a normal user, you must have CREATE VIEW privileges or you must have CREATE       ANY VIEW privileges.

-- To create a sub view, we need UNDER ANY VIEW system privilege.

-- Note:-  all privileges must be granted the directly , rather than a role.

Example for Create a view:-

i)                    conn system/****

ii)                   SQL>> conn tom/tom

iii)                 TOM>> CREATE VIEW RAJA AS SELECT * FROM EMP;

                                               “ORA-01031:- Insufficient Privileges.” 

 This is the error you got on your console because you are just login as a normal user, so u doesn’t have any permissions or privileges to “CREATE VIEW”. Provide those with the help of SYS user.

iv)                 TOM>> CONN SYSTEM/****

v)                  SYS>> GRANT CREATE VIEW TO TOM;

vi)                 SYS>> CONN tom/tom

vii)               TOM>>  CREATE VIEW RAJA  AS SELECT * FROM EMP;

viii)              TOM>> SELECT * FROM RAJA;

ix)                 TOM>> SELECT EMPNO,ENAME,SAL FROM RAJA;

You can give your own query to retrieve the data. It will work as a normal table. We can perform DML operations on this view.

x)                  TOM>> INSERT INTO RAJA (ENAME,SAL,DEPTNO,COMM) VALUES (‘RAJA’,1000,10,100);

“ORA-

If there is no primary key in the table, the query won’t give this error. Non-preserve key words must be mention in query then we can insert. In the above query , I was miss the empno and it’s a primary key.

                     XI)        TOM>> INSERT INTO RAJA (EMPNO) VALUES (1001);

                   XII)         TOM>> UPDATE RAJA SET EMPNO=10 WHERE EMPNO=7839;

                 XIII)          TOM>> DELETE FROM RAJA;

 Whatever we want, we are able to do on this view but it will also effect on the EMP table. May be it causes to data loss from the original table.  If we are dropping the original table, it will effects on all the views which are constructed based on the original tables. I will be provide some practical issues in my next post “HANDS-ON VIEWS” with the help of  ORACLE-XE 11g edition and examples with EMP,DEPT tables.