module15-syn_seq_view.htm; updated July 11, 2005; figures shown in these notes are from Oracle document D11321GC11, Production 1.1, December 2001, D34286

Module 15 – Synonyms, Sequences and Views

Objectives

Create and drop synonyms and public synonyms.

Create sequences.

Generate values from sequences and use them to store rows to data tables.

Create, alter, and drop views.

Create JOIN views.

Learn the advantages associated with views.

Synonyms

Facts about Synonyms

Synonyms are a form of database shorthand.

Synonyms allow the specification of a short reference name for a long, complex object name, e.g. sys.dba_tablespaces may be shortened to tabspaces by creating a synonym named tabspaces.

Synonyms allow access to other databases on other nodes of a distributed database network that is transparent to the system user.

Normally only private synonyms are created by system users – public synonyms are created by database administrators.

CREATE SYNONYM Command

The syntax for the CREATE SYNONYM command is:

CREATE [PUBLIC] SYNONYM tabspaces FOR sys.dba_tablespaces;

In order to create a private synonym you must have the CREATE SYNONYM privilege.

Normally, only the owner of an object (such as a table) can create a synonym.

Individuals with the CREATE PUBLIC SYNONYM privilege can also create synonyms for objects in other schemas. Example:

CREATE SYNONYM co FOR dbock.customer_orders;

Public synonyms must be unique names – for this reason DBAs tend to discourage the creation of public synonyms. The CREATE PUBLIC SYNONYM privilege will usually not be granted to regular system users.

Altering and Dropping Synonyms

Synonyms cannot be altered

Use the DROP command to drop a synonym. Example:

DROP [PUBLIC] SYNONYM tabspaces;

Sequences

Facts About Sequences

Sequences are special database objects used to generate numbers in sequential order, typically to be stored as values in data rows for a data table.

Primary use: To generate unique key values for tables that can be used to link to other tables or that will serve as primary keys (sequence generated primary keys are termed surrogate keys).

Example: Use a Sales_Order_Number_Sequence for a customer ORDERS table where the value of the Sales_Order_Number_Sequence must be unique and in numerical sequence.

CREATE SEQUENCE Command

This example shows the syntax for the CREATE SEQUENCE command.

CREATE SEQUENCE Sales_Order_Number_Sequence
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 99999999 (or NOMAXVALUE)
    MINVALUE 1 (or NOMINVALUE)
    CYCLE (or NOCYCLE - the default)
    CACHE n (or NOCACHE)
    ORDER (or NOORDER)

INCREMENT BY defaults to a value of 1. 

· Specifying a positive number increment will generate ascending sequence numbers with an interval equal to the value you select. 

· Specifying a negative number will generate descending sequence numbers.

START WITH is the starting number for the sequence. 

· The default for START WITH is MAXVALUE for a descending sequence and MINVALUE for an ascending sequence. 

· START WITH overrides the default value.

MINVALUE is the lowest sequence number generated.  MIN VALUE and START WITH default to 1 for ascending sequences.

MAXVALUE is the largest sequence number generated. 

· For descending sequences, it defaults to -1.

· To allow unlimited sequence number generation only use MINVALUE for ascending and MAXVALUE for descending sequences.

· Specifying a limit with MINVALUE or MAXVALUE will force an error when an attempt is made to generate a number exceeding the limit and when you have specified NOCYCLE.

CYCLE causes automatic cycling to start a sequence over once the MAXVALUE for an ascending sequence or MINVALUE for a descending sequence is reached. The default MAXVALUE is 10e27 - 1 (a very large number).

CACHE is an option to cache the specified number of sequence values into buffers in the SGA.

· This speeds access, but loses the cached numbers if the database is shut down.

· The default value for cached numbers is 20 if you do not specify NOCACHE.

ORDER forces sequence numbers to be output in order, and is usually used where the sequence number is used for time stamping.

Altering and Dropping Sequences

Use the ALTER SEQUENCE command to alter a sequence.

· The syntax is like that shown for the CREATE SEQUENCE command.

· Most parameters may be altered, but only future sequence numbers are affected.

· You cannot alter the START WITH clause without dropping and recreating the sequence.

Use the DROP SEQUENCE sequence name command to drop a sequence. Example:

DROP SEQUENCE sales_order_number_sequence;

If you have created a trigger or procedure that references a sequence, the trigger/procedure will fail if the sequence is dropped.

Using Sequences

In order to use a sequence, you must first generate the initial sequence number by using the NEXTVAL option. 

An example using NEXTVAL.

INSERT INTO sales_order (order_number, order_date, salesperson)
    VALUES (sales_order_number_sequence.NEXTVAL, SYSDATE, 'Bock' );

The CURRVAL option returns the current sequence number, but will not execute unless the sequence has been called at least one time using the NEXTVAL option.

CURRVAL is used instead of NEXTVAL to use the same sequence number more than once, for example, when you are inserting rows into a related table where referential integrity must be enforced.

An example using CURRVAL.

INSERT INTO order_details (order_number, product_number, quantity, price)

VALUES(sales_order_number_sequence.CURRVAL, 14985, 6, 79.95)

If you use NEXTVAL and CURRVAL in the same SQL statement, both of these values will be the value retrieved by NEXTVAL.

You cannot use NEXTVAL or CURRVAL in subqueries as columns in a select clause where you use DISTINCT, UNION, INTERSECT, or MINUS or in ORDER BY, GROUP BY, or HAVING clauses.

Views

Facts about Views

Views are virtual tables – that is, they are a logical representation of a collection of columns from one or more (related) base tables or other views. Views are defined in a way such that they make it easier for a user to access needed information from a database.

For example, a salesperson may be accustomed to viewing sales orders according to the traditional sales order form that the firm uses.

· In most firms, the sales order requires data from the CUSTOMERS, SALES_ORDER, ORDER_DETAILS, and PRODUCTS tables.

· Creating a view of the data from these tables provides the customer a single "object" with which to process information about a sales order.

· Views are selected (queried) just like a table.

As another example, consider the "tables" you access that comprise the data dictionary. Most of these are actually views of the data.

Advantages of Views

· Views don't exist until they are queried except as specification statements stored in the database's data dictionary. Views do NOT actually store any data.

· Views are efficient unless views are stacked, i.e. one view references another view - then performance problems can occur.

· Views allow the designer to restrict access to specific columns within a table or tables.

· Views can provide derived column values (calculated fields) that are not normally stored as part of tables, and display the derived column data along side actual data.

· Views can filter data.

· Depending on how views are constructed, inserts and deletions can be accomplished by using views, or data manipulation for views may be restricted to simply selecting data.

CREATE VIEW Command

The general CREATE VIEW syntax is shown here

CREATE VIEW ViewName
        (Column#1-Alias, Column#2-Alias, . . . )
AS
SELECT table1.column1, table1.column2,
           table2.column1, table2,column2, . . .
      FROM table1, table2, . . .
     WHERE condition1, condition2, . . . ;

The specification of Column Alias Names is optional. This is usually done in order to provide the system user with column names that are meaningful to them according to the terminology they use when performing their jobs.

View privileges:

· To create a view in your schema you need the CREATE VIEW system privilege.

· To create a view in another schema you need the CREATE ANY VIEW system privilege.

· Both of these privileges may be acquired through roles or can be granted to you explicitly.

· A view owner must have been granted explicit privileges to access all objects referenced in a view definition – these privileges cannot be obtained through roles.

· For a view owner to grant view access to others the owner must have received object privileges on the base objects with the GRANT OPTION or the system privileges with the ADMIN OPTION.

Example: User1 has the SELECT and INSERT privileges for the EMPLOYEE table owned by User2. Any view on the EMPLOYEE table created by User1 can only support SELECT and INSERT operations. DELETE and UPDATE operations are not supported.

Example Create View:

This example uses the EMPLOYEE table for the Company Database in the DBOCK schema. Here is a description of the table.

DESC employee;

Name Null? Type

----------------------- -------- --------------

EMP_SSN NOT NULL CHAR(9)

EMP_LAST_NAME NOT NULL VARCHAR2(25)

EMP_FIRST_NAME NOT NULL VARCHAR2(25)

EMP_MIDDLE_NAME VARCHAR2(25)

EMP_ADDRESS VARCHAR2(50)

EMP_CITY VARCHAR2(25)

EMP_STATE CHAR(2)

EMP_ZIP CHAR(9)

EMP_DATE_OF_BIRTH DATE

EMP_SALARY NUMBER(7,2)

EMP_PARKING_SPACE NUMBER(4)

EMP_GENDER CHAR(1)

EMP_DPT_NUMBER NUMBER(2)

EMP_SUPERSSN CHAR(9)

A SELECT statement displays four of the columns from the table.

COLUMN emp_last_name FORMAT A15;

COLUMN emp_first_name FORMAT A15;

SELECT emp_ssn, emp_last_name, emp_first_name, emp_dpt_number

FROM employee;

EMP_SSN EMP_LAST_NAME EMP_FIRST_NAME EMP_DPT_NUMBER

--------- ------------- --------------- --------------

999666666 Bordoloi Bijoy 1

999555555 Joyner Suzanne 3

999444444 Zhu Waiman 7

999887777 Markis Marcia 3

999222222 Amin Hyder 3

999111111 Bock Douglas 7

999333333 Joshi Dinesh 7

999888888 Prescott Sherri 7

A CREATE VIEW statement creates a view named DEPT_7 to display only data for employees belonging to department 7.

CREATE VIEW dept_7

AS

SELECT emp_ssn, emp_last_name, emp_first_name, emp_dpt_number

FROM employee WHERE emp_dpt_number = 7

WITH CHECK OPTION CONSTRAINT dept_7_staff_ck;

A check constraint limits INSERT and UPDATE operations against the view such that only rows that the view can select can be inserted or updated. This INSERT command updates the underlying EMPLOYEE table.

INSERT INTO dept_7 VALUES ('999888889', 'Smith', 'Charles', 7);

This INSERT command fails to update the underlying EMPLOYEE table because the command tries to insert an employee for Department 3.

INSERT INTO dept_7 VALUES ('999888882', 'Smith', 'Alice', 3);

ERROR at line 1:

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

JOIN Views

A JOIN VIEW includes column data from more than one base table. Example:

CREATE VIEW company_staff

AS

SELECT emp_ssn, emp_last_name, emp_first_name, emp_dpt_number, dpt_name

FROM employee e, department d

WHERE e.emp_dpt_number = d.dpt_no;

Join views are updateable, but there are a number of restrictions regarding updating this type of view. For example, only one underlying table can be modified by an INSERT, UPDATE, or DELETE operation. These rules are covered in detail in the assigned readings.

Altering and Dropping Views

Views are not normally altered unless the underlying base tables or referenced views change – then the view should be explicitly recompiled.

· You can alter any view in your schema.

· To alter a view in another schema you need the ALTER ANY TABLE system privilege to execute an ALTER VIEW command. Example:

ALTER VIEW dept_7 COMPILE;

Use the DROP VIEW command to drop a view that is no longer needed.

· You can drop any view in your schema.

· To drop a view from another schema you need the DROP ANY VIEW system privilege.

DROP VIEW dept_7;

There are two ways to replace views:

· Drop and then create the view again.

· Use the CREATE OR REPLACE VIEW command. Example:

CREATE OR REPLACE VIEW company_staff

AS

SELECT emp_ssn, emp_last_name, emp_first_name, emp_dpt_number, dpt_name

FROM employee e, department d

WHERE e.emp_dpt_number = d.dpt_no;

DML Restrictions on Views

When can INSERT, UPDATE, and DELETE operations be used for base tables defined by a view?

· A view defined by a SELECT statement that contains the SET or DISTINCT operators, a GROUP BY clause, or a group function cannot be used for INSERT, UPDATE, or DELETE operations.

· If a view cannot select a row from a base table (due to a WITH CHECK OPTION), INSERT and UPDATE operations cannot be used through the view – see the earlier example for the view named DEPT_7.

· If a view omits a NOT NULL column (that does not have a DEFAULT clause), then INSERT operations cannot be used through the view.

Example: Attempt to insert a NULL value for the EMP_FIRST_NAME column fails.

INSERT INTO dept_7 VALUES ('555444545', 'Bock', NULL, 7);

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DBOCK"."EMPLOYEE"."EMP_FIRST_NAME")

· A view created by using an expression cannot be used to INSERT or UPDATE rows in a base table.

· A JOIN VIEW can be updated, but is limited to updating only one base table participating in the view. There are numerous rules regarding updating a JOIN VIEW.

o UPDATE – updatable columns must map to columns of a key-preserved table – that is a table where the key can also be a key of the virtual table resulting from the join.

o DELETE – rows from a JOIN VIEW can be deleted providing there is only one key-preserved table in the JOIN – a view created with the WITH CHECK OPTION clause with a key preserved table that is repeated cannot have the rows deleted through the view.

o INSERT – rows inserted cannot reference columns of any non-key-preserved table. INSERT operations are not permitted if the JOIN VIEW is defined with the WITH CHECK OPTION clause.

The DBA_UPDATABLE_COLUMNS shows all columns in all tables and views that are modifiable with DML statements.

Example: This query shows which columns are updatable from the COMPANY_STAFF view. Note the use of the TABLE_NAME column name from the DBA_UPDATABLE_COLUMNS view even though it is a view that is being evaluated.

SELECT column_name, updatable

FROM dba_updatable_columns

WHERE table_name = 'COMPANY_STAFF';

COLUMN_NAME UPD

------------------------------ ---

EMP_SSN YES

EMP_LAST_NAME YES

EMP_FIRST_NAME YES

EMP_DPT_NUMBER YES

DPT_NAME NO

---

END OF NOTES