Creating Views

11 Creating Views

Copyright © Oracle Corporation, 2001. All rights

Objectives Objectives After After completing completing this this lesson, lesson, you you should should be be able able to to do do the the following: following: •• Describe Describe aa view view

•• •• ••

Create, Create, alter alter the the definition definition of, of, and and drop drop aa view view Retrieve Retrieve data data through through aa view view Insert, Insert, update, update, and and delete delete data data through through aa view view Create Create and and use use an an inline inline view view

•• •• Perform Perform “Top-N” “Top-N” analysis analysis

11-2

Copyright © Oracle Corporation, 2001. All rights

Database Database Objects Objects

11-3

Object

Description

Table

Basic unit of storage; composed of rows and columns

View

Logically represents subsets of data from one or more tables

Sequence

Generates primary key values

Index

Improves the performance of some queries

Synonym

Alternative name for an object

Copyright © Oracle Corporation, 2001. All rights

What What is is aa View? View? EMPLOYEES Table:

11-4

Copyright © Oracle Corporation, 2001. All rights

Why Why Use Use Views? Views? •• •• •• ••

11-5

To To restrict restrict data data access access To To make make complex complex queries queries easy easy To To provide provide data data independence independence To To present present different different views views of of the the same same data data

Copyright © Oracle Corporation, 2001. All rights

Simple Simple Views Views and and Complex Complex Views Views Feature

Simple Views

Complex Views

Number of tables

One

One or more

Contain functions

No

Yes

Contain groups of data DML operations through a view

No

Yes

Yes

Not always

11-6

Copyright © Oracle Corporation, 2001. All rights

Creating a View • You embed a subquery within the CREATE VIEW statement.

CREATE CREATE [OR [OR REPLACE] REPLACE] [FORCE|NOFORCE] [FORCE|NOFORCE] VIEW VIEW view view [(alias[, [(alias[, alias]...)] alias]...)] AS AS subquery subquery [WITH [WITH CHECK CHECK OPTION OPTION [CONSTRAINT [CONSTRAINT constraint]] constraint]] [WITH [WITH READ READ ONLY ONLY [CONSTRAINT [CONSTRAINT constraint]]; constraint]];

• The subquery can contain complex SELECT syntax.

11-7

Copyright © Oracle Corporation, 2001. All rights

Creating a View •

Create a view, EMPVU80, that contains details of employees in department 80.

CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80; View created.

•

Describe the structure of the view by using the iSQL*Plus DESCRIBE command.

DESCRIBE DESCRIBE empvu80 empvu80

11-8

Copyright © Oracle Corporation, 2001. All rights

Creating a View • Create a view by using column aliases in the subquery.

CREATE VIEW salvu50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50; View created.

• Select the columns from this view by the given alias names.

11-9

Copyright © Oracle Corporation, 2001. All rights

Retrieving Retrieving Data Data from from aa View View

SELECT * FROM salvu50;

11-10

Copyright © Oracle Corporation, 2001. All rights

Querying Querying aa View View

Oracle Server iSQL*Plus SELECT FROM

USER_VIEWS

* empvu80;

EMPVU80

SELECT employee_id, last_name, salary FROM employees WHERE department_id=80;

EMPLOYEES

11-11

Copyright © Oracle Corporation, 2001. All rights

Modifying a View • Modify the EMPVU80 view by using CREATE OR REPLACE VIEW clause. Add an alias for each column name.

CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id) AS SELECT employee_id, first_name || ' ' || last_name, salary, department_id FROM employees WHERE department_id = 80; View created.

• Column aliases in the CREATE VIEW clause are listed in the same order as the columns in the subquery.

11-12

Copyright © Oracle Corporation, 2001. All rights

Creating a Complex View Create a complex view that contains group functions to display values from two tables. CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name; View created.

11-13

Copyright © Oracle Corporation, 2001. All rights

Rules for Performing DML Operations on a View • You can perform DML operations on simple views. • You cannot remove a row if the view contains the following:

11-14

–

Group functions

–

A GROUP BY clause

–

The DISTINCT keyword

–

The pseudocolumn ROWNUM keyword

Copyright © Oracle Corporation, 2001. All rights

Rules for Performing DML Operations on a View You cannot modify data in a view if it contains:

• • • • •

11-15

Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions

Copyright © Oracle Corporation, 2001. All rights

Rules for Performing DML Operations on a View You cannot add data through a view if the view includes:

• • • • • •

11-16

Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view

Copyright © Oracle Corporation, 2001. All rights

Using the WITH CHECK OPTION Clause • You can ensure that DML operations performed on the view stay within the domain of the view by using the WITH CHECK OPTION clause.

CREATE OR REPLACE VIEW empvu20 AS SELECT * FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck ; View created.

• Any attempt to change the department number for any row in the view fails because it violates the WITH CHECK OPTION constraint.

11-17

Copyright © Oracle Corporation, 2001. All rights

Denying DML Operations • You can ensure that no DML operations occur by adding the WITH READ ONLY option to your view definition.

• Any attempt to perform a DML on any row in the view results in an Oracle server error.

11-18

Copyright © Oracle Corporation, 2001. All rights

Denying DML Operations CREATE OR REPLACE VIEW empvu10 (employee_number, employee_name, job_title) AS SELECT employee_id, last_name, job_id FROM employees WHERE department_id = 10 WITH READ ONLY; View created.

11-19

Copyright © Oracle Corporation, 2001. All rights

Removing Removing aa View View You You can can remove remove aa view view without without losing losing data data because because aa view view is is based based on on underlying underlying tables tables in in the the database. database.

DROP DROP VIEW VIEW view; view;

DROP VIEW empvu80; View dropped.

11-20

Copyright © Oracle Corporation, 2001. All rights

Inline Inline Views Views •• An An inline inline view view is is aa subquery subquery with with an an alias alias (or (or •• ••

11-21

correlation correlation name) name) that that you you can can use use within within aa SQL SQL statement. statement. A A named named subquery subquery in in the the FROM FROM clause clause of of the the main main query query is is an an example example of of an an inline inline view. view. An An inline inline view view is is not not aa schema schema object. object.

Copyright © Oracle Corporation, 2001. All rights

Top-N Top-N Analysis Analysis •• Top-N Top-N queries queries ask ask for for the the nn largest largest or or smallest smallest values values of of aa column. column. For For example: example:

–– What What are are the the ten ten best best selling selling products? products? –– What What are are the the ten ten worst worst selling selling products? products?

•• Both Both largest largest values values and and smallest smallest values values sets sets are are considered considered Top-N Top-N queries. queries.

11-22

Copyright © Oracle Corporation, 2001. All rights

Performing Top-N Analysis The high-level structure of a Top-N analysis query is: SELECT SELECT FROM FROM

[column_list], [column_list], ROWNUM ROWNUM (SELECT (SELECT [column_list] [column_list] FROM FROM table table ORDER ORDER BY BY Top-N_column) Top-N_column) WHERE WHERE ROWNUM ROWNUM <= <= N; N;

11-23

Copyright © Oracle Corporation, 2001. All rights

Example Example of of Top-N Top-N Analysis Analysis To To display display the the top top three three earner earner names names and and salaries salaries from from the the EMPLOYEES EMPLOYEES table: table:

1

2

3

SELECT ROWNUM as RANK, last_name, salary FROM (SELECT last_name,salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 3;

1 11-24

2 Copyright © Oracle Corporation, 2001. All rights

3

Summary In this lesson, you should have learned that a view is derived from data in other tables or views and provides the following advantages: • Restricts database access • Simplifies queries • Provides data independence • Provides multiple views of the same data • Can be dropped without removing the underlying data • An inline view is a subquery with an alias name. • Top-N analysis can be done using subqueries and outer queries.

11-25

Copyright © Oracle Corporation, 2001. All rights

Practice 11 Overview This practice covers the following topics:

• • • • • •

11-26

Creating a simple view Creating a complex view Creating a view with a check constraint Attempting to modify data in the view Displaying view definitions Removing views

Copyright © Oracle Corporation, 2001. All rights

11-27

Copyright © Oracle Corporation, 2001. All rights

11-28

Copyright © Oracle Corporation, 2001. All rights