//
you're reading...
Administration

A Short Guide to Oracle SQL [X] – Other Database Objects (Views)

ViEWS

A view in a datbase is a virtual table (logical structure) based on a SELECT statement. Although a view contains no data itself, it acts much like a table. You can use (view) as the source for query, Some even allow you to INSERT, UPDATE and DELETE operations.

The query on which view(s) are based, reference to Base Table. Base tables can be another tables or views.

      CREATE [OR REPLACE] 
             [ [NO] FORCE] VIEW [schema.]viewname
               [(column_alias[, column_alias]...)]
         AS query
            [WITH {CHECK OPTION|READ ONLY} [CONSTRAINT constraintname]]
    • OR REPLACE re-creates the view if it already exists.
    • FORCE create the view even if the base table does not exist. NOFORCE is default and will cause an error essage if underlying table does not exist.
    • WITH CHECK OPTION, if specified, prevent view to perform any insertion or update operation on rows that are not visible through the view.
    • WITH READ ONLY prevents any DML operations on view.
    • CONSTRAINT used to specify the name of WITH CHECK OPTION or WITH READ ONLY constraint.

Advantages:

    • Simplify the task of writing complex query. A view can be used to query data from different table(s) and present it as a single table. Thus it hides the complexity of the underlying SQL statement from the user.
    • Provides an additional level of security by restricting the data (predetermined set of rows and/or columns) a user can access.
    • Reduce dependency from changes in the base table.

Simple View

Simple view based on a subquery that reference only one base table and does not have any group functions or GROUP BY clause.

hr@O11R2> CREATE VIEW emp_vu
     AS
     SELECT employee_id, first_name||' '||last_name emp_name, job_id, department_id, email, phone_number
     FROM employees
     WITH READ ONLY;                                              -- The table or view cannot be updated

View created.

hr@O11R2> SELECT employee_id, emp_name, email
     FROM emp_vu
     WHERE department_id = 70;

EMPLOYEE_ID EMP_NAME                                       EMAIL
----------- ---------------------------------------------- -------------------------
        204 Hermann Baer                                   HBAER

hr@O11R2> UPDATE emp_vu
     SET email='HBAERR'
     WHERE employee_id=204;
SET email='HBAERR'
    *
ERROR at line 2:
ORA-42399: cannot perform a DML operation on a read-only view

You can perform DML operations on simple view as long as it created without the WITH READ ONLY clause and doesn’t violate any existing constraints on the base table.

hr@O11R2> CREATE OR REPLACE VIEW emp_vu
          AS
          SELECT employee_id, first_name||' '||last_name emp_name, job_id, department_id, email, salary
          FROM employees
          WHERE salary > 13000
          WITH CHECK OPTION CONSTRAINT chk_emp_vu_sal;

View created.

hr@O11R2> SELECT * FROM emp_vu;

EMPLOYEE_ID EMP_NAME                     JOB_ID     DEPARTMENT_ID EMAIL                 SALARY
----------- ---------------------------- ---------- ------------- --------------------- ----------
        100 Steven King                  AD_PRES               90 SKING                 24000
        101 Neena Kochhar                AD_VP                 90 NKOCHHAR              17000
        102 Lex De Haan                  AD_VP                 90 LDEHAAN               17000
        145 John Russell                 SA_MAN                80 JRUSSEL               14000
        146 Karen Partners               SA_MAN                80 KPARTNER              13500

hr@O11R2> UPDATE emp_vu
     SET salary=12999
     WHERE salary = 13500;
UPDATE emp_vu
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

Complex View

Complex view based on a subquery that retreives data from multiple tables and can have expressions, group functions and GROUP BY clause.

hr@O11R2> CREATE VIEW emp_dept_vu
     (eid, ename, job, deptid, dname, email, phone, location)
     AS
     SELECT employee_id, first_name||' '||last_name, job_id, d.department_id, 
            department_name, email, phone_number, city
     FROM employees e, departments d, locations l
     WHERE e.department_id=d.department_id
     AND d.location_id=l.location_id;

View created.

NOTE:

    • DML operations can be perform with simple views. Complex views don’t support DML operations (with exceptions).
    • USER_VIEWS can be used to get information about views owned by current user (incl. view text).

Related Privileges:

    • CREATE VIEW for creating view in your own schema.
    • CREATE ANY VIEW for creating view in another user’s schema.

ALTER and DROP VIEW

The main use of ALTER VIEW command to recompiled the view if it become invalid. You can also use this command to modify views’ constraints.

ALTER VIEW emp_vu COMPILE;
View altered.

hr@O11R2> ALTER VIEW emp_vu DROP CONSTRAINT chk_emp_vu_sal;
View altered.

DROP VIEW statement is used to drop/delete a view.

DROP VIEW [ schema. ] view [ CASCADE CONSTRAINTS ] ;

hr@O11R2> DROP VIEW emp_vu;
View dropped.
Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: