




Remote DBA services Remote DBA Support Remote DBA RAC Remote DBA Reasons Remote Oracle Tuning Remote DBA Links Oracle DBA Support Oracle DBA Forum Oracle Disaster Oracle Training Oracle Tuning Oracle Training Remote DBA SQL Server Remote MSSQL Consulting Oracle DBA Hosting Oracle License Negotiation

EnterpriseDB: Views

Oracle Tips by Burleson Consulting

Views

CREATE OR REPLACE VIEW <view name>
[ (<column list>) ] AS <query>

A view is nothing more than a stored query. Rather than type a query all over your application, you can create a view and select from the view. That might seem rather redundant but you can create very complex views and reference them with a simple select. Views improve the maintainability of your application and are considered a standard practice of good database design.

You can use a view anywhere you would use a table (read-only). EnterpriseDB does not currently support updateable views. You can join a view to a table or to another view. You can create a view that is based on a view.

When you create a view, you can use CREATE VIEW <view name> or you can use CREATE OR REPLACE VIEW <view name>. In the first case, the view will be created. If you want to change anything in the view, say the where clause if you have one, you will have to drop the view and recreate it. When you drop the view, any access on the view that you have granted will be lost. You will have to re-grant that access after recreating the view.

However, in the second case, you can recompile without dropping the view. All access previously granted will remain. In general, stick with CREATE OR REPLACE VIEW.

CREATE VIEW

We can create a view on the emp table.

CREATE OR REPLACE VIEW emp_vw AS
SELECT *
    FROM emp;

edb=# CREATE OR REPLACE VIEW emp_vw AS
edb-#   SELECT *
edb-#     FROM emp;
CREATE VIEW
edb=# desc emp_vw
                 View "public.emp_vw"

SELECT emp.empno, emp.ename, emp.job, emp.mgr,
emp.hiredate, emp.sal, emp.comm, emp.deptno
FROM emp;

We can create a view that joins emp to dept.

CREATE OR REPLACE VIEW emps_and_depts_vw AS
SELECT dept.DEPTNO as dept_dept_no,
             dept.DNAME as dname,
             dept.LOC as loc,
             emp.EMPNO as empno,
             emp.ENAME as ename,
             emp.JOB as job,
             emp.MGR as mgr,
             emp.HIREDATE as hiredate,
             emp.SAL as sal,
             emp.COMM as comm,
             emp.DEPTNO as emp_dempt_no
    FROM dept
    JOiN emp
      ON dept.deptno = emp.deptno;

edb=# CREATE OR REPLACE VIEW emps_and_depts_vw AS
edb-#   SELECT dept.DEPTNO as dept_dept_no,
edb-#              dept.DNAME as dname,
edb-#              dept.LOC as loc,
edb-#              emp.EMPNO as empno,
edb-#              emp.ENAME as ename,
edb-#              emp.JOB as job,
edb-#              emp.MGR as mgr,
edb-#              emp.HIREDATE as hiredate,
edb-#              emp.SAL as sal,
edb-#              emp.COMM as comm,
edb-#              emp.DEPTNO as emp_dempt_no
edb-#     FROM dept
edb-#     JOiN emp
edb-#       ON dept.deptno = emp.deptno;

CREATE VIEW

edb=# desc emps_and_depts_vw
View "public.emps_and_depts_vw"

View definition:

SELECT dept.deptno AS dept_dept_no, dept.dname, dept.loc,
        emp.empno, emp.ename, emp.job, emp.mgr,
        emp.hiredate, emp.sal, emp.comm, emp.deptno AS emp_dempt_no
   FROM dept
   JOIN emp ON dept.deptno = emp.deptno;

You don't actually have to give an alias to the columns (table.column AS <alias>) unless there is a duplicate name (such as dept_no) or an expression (which I will show below). I think giving your columns an alias is a good programming practice and you should always do so. Also, you can give your columns any alias that follows valid database naming standards.

We can create views with functions and expressions in them also.

CREATE OR REPLACE VIEW my_stuff_vw AS
SELECT text 'Lewis' || 'Cunningham' as my_name,
             sysdate as my_date,
             sqrt(25) as square_root_of_25
    FROM dual;

edb=# CREATE OR REPLACE VIEW my_stuff_vw AS
edb-#   SELECT text 'Lewis' || 'Cunningham' as my_name,
edb-#              sysdate as my_date,
edb-#              sqrt(25) as square_root_of_25
edb-#     FROM dual;

CREATE VIEW

edb=# desc my_stuff_vw
View "public.my_stuff_vw"

SELECT 'Lewis'::text || 'Cunningham'::text AS my_name,
        'now'::text::timestamp(0) without time zone AS my_date,
        sqrt(25::double precision) AS square_root_of_25
   FROM dual;

The second column, my_date, is a date function and EnterpriseDB will create it correctly. The third column is the square root numeric function.

Dual is a special table that exists in EnterpriseDB to provide Oracle compatibility.

We can create a view without aliases to see what the describe on them looks like.

CREATE OR REPLACE VIEW my_stuff_vw AS
SELECT 'Lewis' || 'Cunningham',
             sysdate,
             sqrt(25)
    FROM dual;

edb=# CREATE OR REPLACE VIEW my_stuff_vw AS
edb-#   SELECT 'Lewis' || 'Cunningham',
edb-#              sysdate,
edb-#              sqrt(25)
edb-#     FROM dual;

CREATE VIEW

edb=# desc my_stuff_vw
View "public.my_stuff_vw"

View definition:

SELECT 'Lewis'::text || 'Cunningham'::text,
        'now'::text::timestamp(0) without time zone AS "timestamp",
        sqrt(25::double precision) AS sqrt
   FROM dual;

ALTER VIEW

You cannot alter a view. You need to CREATE or CREATE OR REPLACE it.

GRANT VIEW

Grants to a view work exactly like a grant to a table

GRANT SELECT ON emp_vw TO jschmoe;

REVOKE VIEW

Revokes from a view work exactly like a revoke from a table.

REVOKE SELECT ON emp_vw FROM jschmoe;

DROP VIEW

Use DROP VIEW to drop a view.

DROP VIEW emp_vw;

DROP VIEW emp_vw CASCADE;

The CASCADE is required if you want to drop the view and it has dependencies. If you want to keep the view when it has dependencies, call DROP VIEW without the CASCADE.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider. Get real Remote DBA experts, call
BC Remote DBA today.

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Oracle� is the registered trademark of Oracle Corporation.

Hit Counter