Oracle Apps Modules

Oracle DDL Statements: Views, Sequences and Synonyms

Oracle DDL Statements: Views, Sequences and Synonyms


Views

A View presents a query from a table.

Views are useful when you need frequent access from a table.

Here in this tutorial, we will create two views: SALESFORCE and EMP_LOCATIONS

SALESFORCE will contain names and salaries of the employees in Sales department.

EMP_LOCATIONS will contain names and locations of all Employees.


Creating SALESFORCE view using SQL Developer tool

Select New View and follow the process as shown below.

Click on OK.

SALESFORCE view has been created.


Creating EMP_LOCATION view with CREATE VIEW Statement.

CREATE VIEW EMP_LOCATIONS AS
SELECT e.EMPLOYEE_ID,
e.LAST_NAME || ', ' || e.FIRST_NAME NAME,
d.DEPARTMENT_NAME DEPARTMENT,
l.CITY CITY,
c.COUNTRY_NAME COUNTRY
FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l, COUNTRIES c
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND
d.LOCATION_ID = l.LOCATION_ID AND
l.COUNTRY_ID = c.COUNTRY_ID
ORDER BY LAST_NAME;

Oracle DDL Statements: Views, Sequences

View EMP_LOCATION has been created.

Changing the Query in SALESFORCE View

CREATE OR REPLACE VIEW SALESFORCE AS
SELECT FIRST_NAME || ' ' || LAST_NAME "Name",
SALARY*12 "Annual Salary"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80 OR DEPARTMENT_ID = 20;

Oracle DDL Statements: Views, Sequences


Dropping Views

DROP VIEW SALESFORCE;

Oracle DDL Statements: Views, Sequences

 


Creating and Managing Sequence

Sequences are schema objects from which you can generate unique sequential values, which are very useful when you need unique primary keys. The HR schema has three sequences: DEPARTMENTS_SEQUENCE, EMPLOYEES_SEQUENCE, and LOCATIONS_SEQUENCE.

Sequences are used through the pseudocolumns CURRVAL and NEXTVAL, which return the current and next values of the sequence, respectively. After creating a sequence, you must initialize it by using NEXTVAL to get its first value. Only after you initialize a sequence does CURRVAL return its current value.


Creating a Sequence

CREATE SEQUENCE evaluations_sequence
INCREMENT BY 1
START WITH 1 ORDER;

Oracle DDL Statements: Views, Sequences


Dropping a Sequence

DROP SEQUENCE EVALUATIONS_SEQUENCE;

 


Creating and Managing Synonyms

A synonym is an alias for another schema object.

Creating a Synonym

CREATE SYNONYM EMPL FOR EMPLOYEES;


Dropping a Synonym

DROP SYNONYM EMPL;


 

Leave a Reply