View is a virtual entity based on table or other views.View is created using database tables(primary storage of data in relational database) or other views.In other words, view is just a stored SELECT statement (a logical table) based on one or multiple tables or views and view does not have its own data, it relies on underlying base tables for result sets.
Note:- Along with table based views, oracle database also support object view or a relational view that supports LOBs, object types, REF datatypes, nested table, varray. XML view can also be created supporting XML types.
Synonym is alternate name for database objects like Table, views, procedure, function , etc. Synonym provides an alias to a database object and gives an opportunity to mask original name & owner of the object.
Synonym is mostly used for providing access on table to external schema by creating Public synonym and by doing this external schema can access table without specifying schema_name where table is existing.
Syntax & semantics of View and Synonym creation:-View creation :- General syntax for table/view based view
CREATE [ OR REPLACE ] [ [ NO ] FORCE ] VIEW [ schema. ]view [ (alias [ inline_constraint [ inline_constraint ]... ] | out_of_line_constraint [, alias [ inline_constraint [ inline_constraint ]... ] | out_of_line_constraint ]... ) | object_view_clause | XMLType_view_clause ] AS subquery [ subquery_restriction_clause ] ;
For creating View in our schema we must have CREATE VIEW and for creating view in anothers schema we must have CREATE ANY VIEW system privilege.
Let's create a view based on multiple table in HR schema - Employees, Departments and Jobs
View can be created using following syntax:-
CREATE VIEW EMPLOYEE_DETAILS AS SELECT emp.FIRST_NAME, emp.EMAIL,dept.DEPARTMENT_NAME, job.JOB_TITLE FROM EMPLOYEES emp, DEPARTMENTS dept ,JOBS job WHERE emp.DEPARTMENT_ID = dept.DEPARTMENT_ID AND job.JOB_ID = emp.JOB_ID;
when we execute above query it will create a view object "EMPLOYEE_DETAILS" in database and it can be queried as a table like this.
Select * from EMPLOYEE_DETAILS;
Other clauses supported in View are - OR REPLACE, FORCE, READ ONLY,CHECK OPTION.
1.OR REPLACE :- If appended, it will recreate view and it is used to alter definition of an existing view without doing rework (dropping, applying grants again, etc).
CREATE OR REPLACE VIEW EMPLOYEE_DETAILS AS SELECT .... FROM ......
2.FORCE:- If appended, it will forcefully creates view even if base tables or view does not exist or grants to accessing object is not there.
Note:- Before executing any select, insert,update query above structure should be in place,otherwise it will throe error.
CREATE OR REPLACE FORCE VIEW EMPLOYEE_DETAILS AS SELECT .... FROM ......
3. READ ONLY:- View created only used for select operation. No insert ,update are allowed on read only views.
CREATE OR REPLACE FORCE VIEW EMPLOYEE_DETAILS AS SELECT emp.FIRST_NAME, emp.EMAIL,dept.DEPARTMENT_NAME, job.JOB_TITLE FROM EMPLOYEES emp, DEPARTMENTS dept ,JOBS job WHERE emp.DEPARTMENT_ID = dept.DEPARTMENT_ID AND job.JOB_ID = emp.JOB_ID WITH READ ONLY;
4. CHECK OPTION:- If check option is enabled, insert or update can be executed but with some restriction.
CREATE OR REPLACE FORCE VIEW EMPLOYEE_DETAILS AS SELECT emp.FIRST_NAME, emp.EMAIL,dept.DEPARTMENT_NAME, job.JOB_TITLE FROM EMPLOYEES emp, DEPARTMENTS dept ,JOBS job WHERE emp.DEPARTMENT_ID = dept.DEPARTMENT_ID AND job.JOB_ID = emp.JOB_ID WITH CHECK OPTION;
Synonym creation :- Synonym is created for an database object to hide details of object and it provides data independence and location transparency too.
Synonym can be of two types: private (default) and public.
For creating private synonym in our own schema we need CREATE SYNONYM privilege and for creating in others schema we need CREATE ANY SYNONYM privilege.For public synonym we need CREATE PUBLIC SYNONYM privilege.
CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM [ schema. ]synonym FOR [ schema. ]object [ @ dblink ] ;
Lets create a synonym for stored procedure and execute it. Use following syntax for creating procedure and synonym:
Create procedure get_employee_names:-
create or replace procedure get_employee_names(v_deptId_in NUMBER, v_cur OUT SYS_REFCURSOR ) IS begin open v_cur for select FIRST_NAME,LAST_NAME from employees where DEPARTMENT_ID = v_deptId_in; end get_employee_names; /
Create a synonym for above stored procedure:-
--GET_EMPLOYEE_NAMES(In NUMBER, OUT REF CURSOR) is a procedure CREATE OR REPLACE SYNONYM "HR"."MY_PROCEDURE_SYNONYM" FOR "HR"."GET_EMPLOYEE_NAMES";
Now we can execute the above stored procedure via synonym with appropriate parameter passing. Refer the following diagram and execute command in following order :-
We can see that, we have not executed original stored procedure instead we executed MY_PROCUDURE_SYNONYM. Similarly, synonym for table and other objects.
Advantage of Views and Synonyms:-
With views: we can hide columns , add predicates (WHERE clause) to restrict rows, rename columns, give a column name to a SQL expression.
With synonym: we can reference objects in other schemas(using public synonym) and databases without qualifying the name.
======End of post======
1. How to test stored procedure in SQL*PLUS and SQL developer
2. Internals of PL/SQL execution in database server