Oracle DDL Statements: Views, Sequences and Synonyms
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;
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;
DROP VIEW SALESFORCE;
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;
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;