Why use VIEWS in Oracle? & How do I delete a View in Oracle?

VIEWS in Oracle

One essential database object that is widely used in Oracle Database applications for logical data management and presentation is the Oracle view. Developers can design an effective way to store a complex SQL query for later use by using views, which act as an interface layer between an application and the real tables.

Oracle View Definition and Characteristics

A logical table constructed on top of other tables or views is what is commonly referred to as a view. A view is fundamentally a SQL statement that is kept in the database; it is essentially a predetermined subset of data that is taken from one or more underlying tables. The SQL query, or view definition, is kept in the Oracle Data Dictionary.

Importantly, conventional views lack their own data, in contrast to materialised views. In response to a user question, Oracle immediately retrieves the stored definition, runs the query against the underlying tables (often referred to as base tables), and returns the current set of results. Views always reflect the data in the base tables as soon as it changes because of their dynamic nature.

Views provide a number of significant functions:

• Simplifying Data Retrieval: They conceal from the application or end user the intricacy of sophisticated SQL queries (such multi-table joins).
• Security and Data Restriction: Views have the ability to limit users to viewing only a portion of the rows or columns, even when the underlying database has more data.
• Logical Data Independence:  They shield applications from modifications to the underlying tables’ structure. In order to preserve a consistent application interface, it is frequently possible to reconfigure the view whenever a table structure is changed.

In order to illustrate the ideas of views, we will first construct two base tables, DEPT and EMP, then add example data to them:

CREATE TABLE Dept (
    Dept_Id NUMBER CONSTRAINT Dept_PK PRIMARY KEY,
    Dept_Name VARCHAR2(30)
);

INSERT INTO Dept VALUES (10,'HR');
INSERT INTO Dept VALUES (20,'IT');
INSERT INTO Dept VALUES (30,'SALES');

CREATE TABLE Emp (
    Emp_Id NUMBER CONSTRAINT Emp_PK PRIMARY KEY,
    Emp_Name VARCHAR2(30),
    Dept_Id NUMBER,
    Salary NUMBER(7,2),
    CONSTRAINT Emp_FK1 FOREIGN KEY (Dept_Id) REFERENCES Dept(Dept_Id)
);

INSERT INTO Emp VALUES (100,'John',20, 60000.00);
INSERT INTO Emp VALUES (200,'Bob',10, 45000.00);
INSERT INTO Emp VALUES (300,'Craig',20, 75000.00);
INSERT INTO Emp VALUES (400,'Joe',30, 50000.00);
COMMIT;

Output (Implicit):

Table created.
3 rows created.
Table created.
4 rows created.
Commit complete.

Create View

CREATE VIEW serves as the main command for creating a view. The command CREATE OR REPLACE VIEW should be used if you wish to modify an existing view’s definition without first discarding it. You can change the view text in this way and retain any rights that have already been applied to it.

The process of creating a view involves giving it a name and using the AS SELECT statement to specify the query that it is based on.

We can construct a view called EMP_DEPT_V that displays employee data and the complete department name by joining the EMP and DEPT tables:

CREATE OR REPLACE VIEW Emp_Dept_V AS
SELECT
    E.Emp_Id,
    E.Emp_Name,
    E.Salary,
    D.Dept_Name
FROM
    Emp E,
    Dept D
WHERE
    E.Dept_Id = D.Dept_Id;

Output:

View created.

The view can be queried similarly to a table once it has been constructed. The columns specified by the underlying SELECT query are shown in the view description, possibly with the help of column aliases if specified in the view creation DDL:

SELECT * FROM Emp_Dept_V;

Output:

    EMP_ID EMP_NAME      SALARY DEPT_NAME
---------- ---------- --------- ----------
       100 John        60000.00 IT
       200 Bob         45000.00 HR
       300 Craig       75000.00 IT
       400 Joe         50000.00 SALES

4 rows selected.

The underlying SQL query used to create the view is stored in the Data Dictionary and can be retrieved from views such as USER_VIEWS or DBA_VIEWS. Since the TEXT column in these dictionary views is a LONG datatype, you may need to set the LONG parameter in SQL*Plus to a sufficient size (e.g., set long 5000) before querying the text.

Dropping Views

A view can be deleted using the DROP VIEW statement if it is no longer required or if you need to redefine it without utilising the OR REPLACE clause:

DROP VIEW Emp_Dept_V;

Output:

View dropped.

Dropping a view renders any dependant objects (including other views, materialised views, or synonyms based on the deleted view) invalid and removes any grants connected to it.

Updatable Views and Read-Only Views

Generally speaking, views are updatable by default, provided that the owner has the required DML privileges (INSERT, UPDATE, DELETE) on the base tables. DML commands that are run against a view will alter the underlying table data if the view is updatable.

But there are a lot of limitations on DML operations against a view:

• The view cannot be updated, deleted, or inserted if it references the pseudo-column RowNum or contains clauses like GROUP BY or DISTINCT.
• DML is feasible for Updatable Join Views (views that are based on several tables joined together) if Oracle can identify which tables are key-preserved, which means that the view has enough columns from a database to uniquely identify the primary key of that table.

It is advised practice to enforce a Read-Only status for views that are not meant to alter data, especially those that are used only for reporting. Using the WITH READ ONLY clause, this is accomplished:

CREATE OR REPLACE VIEW It_Staff_V AS
SELECT
    Emp_Id,
    Emp_Name,
    Salary
FROM
    Emp
WHERE
    Dept_Id = 20
WITH READ ONLY;

Output:

View created.

If a user (even the owner) attempts a DML operation on a view created WITH READ ONLY, Oracle throws an error.

UPDATE It_Staff_V SET Salary = 90000.00 WHERE Emp_Id = 100;

Output:

ORA-42399: cannot perform a DML operation on a read-only view

With Check Option

To ensure row security in an updatable view, use the WITH CHECK OPTION clause. The WHERE clause used in the view’s definition must be followed by any data that is changed by the view, whether it be updated or added. This is the same as defining a check constraint expressly for the view. ‘V’ is the constraint type that is logged for this constraint in the data dictionary view USER_CONSTRAINTS.

In order to enforce the check option exclusively for IT staff (Dept_Id = 20), let’s change the preceding view to remove the read-only restriction:

CREATE OR REPLACE VIEW It_Staff_Restricted_V AS
SELECT
    Emp_Id,
    Emp_Name,
    Dept_Id,
    Salary
FROM
    Emp
WHERE
    Dept_Id = 20
WITH CHECK OPTION;

Output:

View created.

If we attempt to insert a record into this view that does not satisfy the WHERE clause (i.e., we try to insert an HR employee, Dept_Id = 10), the operation will fail:

-- Attempt to insert an HR employee (Dept_Id = 10) through the IT-restricted view
INSERT INTO It_Staff_Restricted_V (Emp_Id, Emp_Name, Dept_Id, Salary)
VALUES (500, 'Susan', 10, 55000.00);

Output:

ORA-01402: view WITH CHECK OPTION where-clause violation

Because the added row (Dept_Id=10) would not be selectable by the view’s defining query (WHERE Dept_Id = 20), thereby breaking the WITH CHECK OPTION, the view insertion is refused.

Inline Views

The FROM clause of a SELECT query defines an inline view, which is a temporary, unnamed view that is occasionally defined within a UPDATE statement. Similar to a subquery, it works exclusively in the FROM clause to produce a dataset that the outer statement can query. Inline views can be used for data processing.

For example, an inline view can be used for an advanced update action, which frequently makes complex join processes simpler. If Oracle deems the subset of rows to be changed updateable, the usual syntax entails specifying it within the UPDATE command itself.

To demonstrate how to update using an inline view, let’s say we wish to provide a 10% rise to all IT employees (Dept_Id = 20). We must make sure that the update is only applied to records that are correctly connected between EMP and DEPT.

To start, we make sure the original table shows the IT staff’s current salaries:

SELECT Emp_Name, Salary FROM Emp WHERE Dept_Id = 20;

Output:

EMP_NAME      SALARY
---------- ---------
John        60000.00
Craig       75000.00

Now, we use an inline view within the UPDATE statement to calculate the new salary for IT employees (Dept_Id 20):

-- Note: This syntax structure is derived material illustration of
-- updating via an inline view.
UPDATE
    (SELECT E.Salary AS Old_Salary, E.Salary * 1.10 AS New_Salary, E.Emp_Id
     FROM Emp E INNER JOIN Dept D ON E.Dept_Id = D.Dept_Id
     WHERE E.Dept_Id = 20)
SET Old_Salary = New_Salary;

Output (Simulated based on expected DML outcome):

2 rows updated.
Commit complete.

If we check the table, the salaries for John and Craig have been updated:

SELECT Emp_Name, Salary FROM Emp WHERE Dept_Id = 20;

Output:

EMP_NAME      SALARY
---------- ---------
John        66000.00
Craig       82500.00

In the FROM clause, the inner SELECT statement serves as the inline view, providing the outer UPDATE command with the new values (New_Salary) and the target rows (Emp_Id). Together with basic view features, inline views provide strong tools for organising intricate queries and effectively managing database schema objects.