Copyright © 2004, Oracle. All rights reserved.

Lecture 3: Lecture 3: Creating Other Schema Creating Other Schema

ObjectsObjects

ORACLEORACLE

ObjectivesObjectives

After completing this lesson, you should be able to do the following:

◦Create simple and complex views◦Retrieve data from views◦Create, maintain, and use sequences◦Create private and public synonyms

Database ObjectsDatabase Objects

Object Description

Table Basic unit of storage; composed of rows

View Logically represents subsets of data from one or more tables

Sequence Generates numeric values

Index Improves the performance of some queries

Synonym Gives alternative names to objects

What Is a ViewWhat Is a View??EMPLOYEES table

Advantages of ViewsAdvantages of Views

To restrict data access

To make complex queries easy

To provide data

independence

To present different views of

the same data

Advantages of ViewsAdvantages of Views

Views restrict access to the data because the view can display selected columns from the table.

Views can be used to make simple queries to retrieve the results of complicated queries. For example, views can be used to query information from multiple tables without the user knowing how to write a join statement.

Views provide data independence for ad hoc users and application programs. One view can be used to retrieve data from several tables.

Views provide groups of users access to data according to their particular criteria.

Simple Views and Complex Simple Views and Complex ViewsViews

Feature Simple Views Complex Views

Number of tables One One or more

Contain functions No Yes

Contain groups of data No Yes

DML operations through a view

Yes Not always

Creating a ViewCreating a View

◦You embed a subquery in the CREATE VIEW statement:

◦The subquery can contain complex SELECT syntax.

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

Creating a ViewCreating a View◦ You can create a view by embedding a subquery in the CREATE VIEW

statement.

◦ In the syntax:◦ OR REPLACE: re-creates the view if it already exists◦ FORCE: creates the view regardless of whether or not the

base tables exist◦ NOFORCE: creates the view only if the base tables exist (This is

the default.)◦ View: is the name of the view◦ Alias: specifies names for the expressions selected by the

view’s query (The number of aliases must match the number of expressions selected by the view.)

◦ Subquery: is a complete SELECT statement (You can use aliases for the columns in the SELECT list.)

◦ WITH CHECK OPTION : specifies that only those rows that are accessible to

the view can be inserted or updated◦ Constraint: is the name assigned to the CHECK OPTION constraint◦ WITH READ ONLY:ensures that no DML operations can be performed on this view

Creating a ViewCreating a View

◦Create the EMPVU80 view, which contains details of employees in department 80:

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

DESCRIBE empvu80

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

Guidelines for Creating a Guidelines for Creating a ViewView::

The subquery that defines a view can contain complex SELECT syntax, including joins, groups, and subqueries.

If you do not specify a constraint name for a view created with the WITH CHECK OPTION, the system assigns a default name in the format SYS_Cn.

You can use the OR REPLACE option to change the definition of the view without dropping and re-creating it or regranting object privileges previously granted on it.

Creating a ViewCreating a View

◦Create a view by using column aliases in the subquery:

◦Select the columns from this view by the given alias names:

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 *FROM salvu50;

Retrieving Data from a Retrieving Data from a ViewView

Modifying a ViewModifying a View

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

◦Column aliases in the CREATE OR REPLACE VIEW clause are listed in the same order as the columns in the subquery.

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.

Creating a Complex ViewCreating 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.

Rules for Performing Rules for Performing DML Operations on a ViewDML Operations on a View

◦You can usually perform DML operationson simple views.

◦You cannot remove a row if the view contains the following: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword

Rules for Performing Rules for Performing DML Operations on a ViewDML Operations on a View

You cannot modify data in a view if it contains:

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

Rules for Performing Rules for Performing DML Operations on a ViewDML Operations on a View

You cannot add data through a view if the view includes:

◦ 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

Using the Using the WITH CHECK WITH CHECK OPTIONOPTION Clause Clause

◦You can ensure that DML operations performed on the view stay in the domain of the view by using the WITH CHECK OPTION clause:

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

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

Denying DML OperationsDenying 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 operation on any row in the view results in an Oracle server error.

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.

Denying DML OperationsDenying DML Operations

Removing a ViewRemoving a View

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

DROP VIEW view;

DROP VIEW empvu80;View dropped.

Practice 1Practice 1::The staff in the HR department wants to hide some of

the data in the EMPLOYEES table. They want a view called EMPLOYEES_VU based on the employee numbers, employee names, and department numbers from the EMPLOYEES table. They want the heading for the employee name to be EMPLOYEE.

Practice 1Practice 1::The staff in the HR department wants to hide some of

the data in the EMPLOYEES table. They want a view called EMPLOYEES_VU based on the employee numbers, employee names, and department numbers from the EMPLOYEES table. They want the heading for the employee name to be EMPLOYEE.

The SQL code:The SQL code:CREATE OR REPLACE VIEW employees_vu

AS

SELECT employee_id, last_name employee, department_id

FROM employees;

Practice 2Practice 2::Confirm that the view works. Display the

contents of the EMPLOYEES_VU view.

Practice 2Practice 2::Confirm that the view works. Display the

contents of the EMPLOYEES_VU view.

The SQL code:The SQL code:SELECT *

FROMemployees_vu;

Practice 3Practice 3::Using your EMPLOYEES_VU view, write a

query for the HR department to display all

employee names and department numbers.

Practice 3Practice 3::Using your EMPLOYEES_VU view, write a

query for the HR department to display all

employee names and department numbers.

The SQL code:The SQL code:SELECT employee, department_id

FROMemployees_vu;

SequencesSequences

Object Description

Table Basic unit of storage; composed of rows

View Logically represents subsets of data from one or more tables

Sequence Generates numeric values

Index Improves the performance of some queries

Synonym Gives alternative names to objects

SequencesSequences

A sequence:◦ Can automatically generate unique

numbers◦ Is a sharable object◦ Can be used to create a primary key value◦ Replaces application code◦ Speeds up the efficiency of accessing

sequence values when cached in memory

1

2 4

3 5

6 8

7

10

9

CREATECREATE SEQUENCESEQUENCE Statement: Statement:SyntaxSyntax

Define a sequence to generate sequential numbers automatically:

CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];

Creating a SequenceCreating a Sequence◦ Automatically generate sequential numbers by using the CREATE SEQUENCE statement.

◦ In the syntax:sequence is the name of the sequence generatorINCREMENT BY n specifies the interval between sequence

numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.)

START WITH n specifies the first sequence number to be generated (If this clause

is omitted, the sequence starts with 1.)

MAXVALUE n specifies the maximum value the sequence can generate

NOMAXVALUE specifies a maximum value of 10^27 for an ascending sequence and –1 for a descending

sequence (This is the default option.)

MINVALUE n specifies the minimum sequence value

NOMINVALUE specifies a minimum value of 1 for an ascending sequence and –(10^26) for a descending sequence (This is the default option.)

Creating a SequenceCreating a SequenceCYCLE | NOCYCLE specifies whether the

sequence continues to generate values after reaching its maximum or minimum value (NOCYCLE is thedefault option.)

CACHE n | NOCACHE specifies how many values the Oracle server preallocates and keeps in memory (By default, the Oracle server caches 20 values.)

Creating a SequenceCreating a Sequence

◦ Create a sequence named DEPT_DEPTID_SEQ to be used for the primary key of the DEPARTMENTS table.

◦ Do not use the CYCLE option.

CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;Sequence created.

NEXTVALNEXTVAL and and CURRVALCURRVAL PseudocolumnsPseudocolumns

◦NEXTVAL returns the next available sequence value. It returns a unique value every time it is referenced, even for different users.

◦CURRVAL obtains the current sequence value. ◦NEXTVAL must be issued for that sequence

before CURRVAL contains a value.

NEXTVALNEXTVAL and and CURRVALCURRVAL PseudocolumnsPseudocolumns

◦ Rules for Using NEXTVAL and CURRVAL◦ You can use NEXTVAL and CURRVAL in the following contexts:

The SELECT list of a SELECT statement that is not part of a subquery

The SELECT list of a subquery in an INSERT statement The VALUES clause of an INSERT statement The SET clause of an UPDATE statement

◦ You cannot use NEXTVAL and CURRVAL in the following contexts: The SELECT list of a view A SELECT statement with the DISTINCT keyword A SELECT statement with GROUP BY, HAVING, or ORDER BY

clauses A subquery in a SELECT, DELETE, or UPDATE statement The DEFAULT expression in a CREATE TABLE or ALTER TABLE

statement

Using a SequenceUsing a Sequence

◦Insert a new department named “Support” in location ID 2500:

◦View the current value for the DEPT_DEPTID_SEQ sequence:

INSERT INTO departments(department_id, department_name, location_id)VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500);1 row created.

SELECT dept_deptid_seq.CURRVALFROM dual;

Caching Sequence ValuesCaching Sequence Values

◦Caching sequence values in memory gives faster access to those values.

◦Gaps in sequence values can occur when: A rollback occurs The system crashes A sequence is used in another table

Modifying a SequenceModifying a Sequence

Change the increment value, maximum value, minimum value, cycle option, or cache option:

ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;Sequence altered.

Guidelines for Modifying Guidelines for Modifying a Sequencea Sequence

◦You must be the owner or have the ALTER privilege for the sequence.

◦Only future sequence numbers are affected.◦The sequence must be dropped and

re-created to restart the sequence at a different number.

◦Some validation is performed.◦To remove a sequence, use the DROP

statement:

DROP SEQUENCE dept_deptid_seq;Sequence dropped.

SynonymsSynonyms

Object Description

Table Basic unit of storage; composed of rows

View Logically represents subsets of data from one or more tables

Sequence Generates numeric values

Index Improves the performance of some queries

Synonym Gives alternative names to objects

SynonymsSynonyms

Simplify access to objects by creating a synonym (another name for an object). With synonyms, you can:

◦ Create an easier reference to a table that is owned by another user

◦ Shorten lengthy object names

CREATE [PUBLIC] SYNONYM synonymFOR object;

Creating and Removing Creating and Removing SynonymsSynonyms

◦Create a shortened name for the DEPT_SUM_VU view:

◦Drop a synonym:

CREATE SYNONYM d_sumFOR dept_sum_vu;Synonym Created.

DROP SYNONYM d_sum;Synonym dropped.

Homework 1:Homework 1:1- View1- View Department 50 needs access to its employee data. Create

a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department 50. You have been asked to label the view columns EMPNO, EMPLOYEE, and DEPTNO. For security purposes, do not allow an employee to be reassigned to another department through the view.

Display the structure and contents of the DEPT50 view.Test your view. Attempt to reassign Mohammed to

department 80.

22 - -SequenceSequenceYou need a sequence that can be used with the primary

key column of the DEPT table. The sequence should start at 200 and have a maximum value of 1,000. Have your sequence increment by 10. Name the sequence DEPT_ID_SEQ.

To test your sequence, write a script to insert two rows in the DEPT table. Be sure to use the sequence that you created for the ID column. Add two departments: Education and Administration. Confirm your additions. Run the commands in your script.

33--SynonymsSynonymsCreate a synonym for your EMPLOYEES

table. Call it EMP.