Creating Views in Oracle Database Using TOAD

A view logically represents subsets of data from one or more tables. You can restrict a user’s access to data in a table by using views. A view does not have a structure of its own and is stored as a SELECT statement in the database.

Views are of two types, simple and complex:

• Simple view: Retrieves data from only one table and contains no functions or group data. You can perform DML operations through a simple view.
• Complex view: Retrieves data from multiple tables or contains functions or group data. A complex view does not always allow you to perform DML operations.


Creating Simple View



You create a simple view based on only one table. In other words, the SELECT statement of the view retrieves data from only one table. For example:-

CREATE OR REPLACE VIEW EMP_VU
(EMPNO, EMP_NAME, ANNUAL_SALARY, DEPTNO)
AS SELECT e.EMPNO, e.EMP_NAME, e.SALARY*12, e.DEPTNO FROM employee e;

Write the SQL script in SQL tab and press F5 to execute.




Verify emp_vu View by using browser schema tab. 




You can create a view based on a SELECT statement with a WHERE condition. You can modify the previous example to include a WHERE condition, as follows:

You can modify a view by using CREATE OR REPLACE statement.

CREATE OR REPLACE VIEW EMP_VU
(EMPNO, EMP_NAME, ANNUAL_SALARY, DEPTNO)
AS 
SELECT e.EMPNO, e.EMP_NAME, e.SALARY*12, e.DEPTNO
FROM employee e
WHERE e.DEPTNO = 3;



Verify modified  emp_vu View and its data by using browser schema tab. 



Creating Complex View

A complex view contains group functions or displays data from multiple tables. The following example creates a complex view, which retrieves data from multiple tables and includes a group function:

CREATE OR REPLACE VIEW DEPT_VU
(DNAME, MINSAL, MAXSAL, TOTAL)
AS 
SELECT d.DEPT_NAME , MIN(e.salary), MAX(e.salary), SUM(salary)
FROM department d, employee e
WHERE e.deptno = d.deptno
GROUP BY d.DEPT_NAME;



Removing a View

You can remove a view from the database using the DROP VIEW statement, as follows:
DROP VIEW dept_vu;

Comments

  1. Hi,
    Can U plz tell Me that hOw can We "Create" or "Call" a procedure on Our Oracle Forms to Create a View???

    regards.

    ReplyDelete

Post a Comment

Popular posts from this blog

How to create Oracle stored Procedures using TOAD for Oracle

Create Tables in Oracle Database using TOAD

How to Create Packages in Oracle Database using TOAD for Oracle