EXP NO: 3
DATE:
Views, Sequences, Synonyms
AIM: To create views, sequences and synonyms in RDBMS.
VIEW
Views are used to provide security for the data. They provide a virtual image for a database.
#Create View:
This command is used to create database view in RDBMS.
Syntax
CREATE VIEW view_name as select <columnname> from <tablename>;
#Update View:
This command is used to change a value of a record in the database.
Syntax
UPDATE view_name SET column name =new value WHERE column name=some value;
#Drop View:
Drop command is used to drop the view.
Syntax
DROP view view_name;
SYNONYM
Description
A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and
other database objects.
Syntax
The syntax to create a synonym in Oracle is:
CREATE OR REPLACE SYNONYM <synonym_name> FOR <object name>;
OR REPLACE allows you to recreate the synonym (if it already exists) without having to issue a DROP
synonym command. The object_name is the name of the object for which you are creating the synonym.
It can be one of the following:
table
view
sequence
stored procedure
function
synonym
Drop Synonym:
Once a synonym has been created in Oracle, you might at some point need to drop the synonym.
Syntax
The syntax to drop a synonym in Oracle is:
DROP SYNONYM <synonym_name>;
SEQUENCES (AUTONUMBER)
Learn how to create and drop sequences in Oracle with syntax and examples.
Description
In Oracle, you can create an auto number field by using sequences. A sequence is an object in Oracle that
is used to generate a number sequence. This can be useful when you need to create a unique number to act
as a primary key.
Create Sequence:
You may wish to create a sequence in Oracle to handle an auto number field.
Syntax
The syntax to create a sequence in Oracle is:
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
Using Sequences:
<sequence name>.NEXTVAL;
INSERT INTO <tablename> VALUES (<sequence name>.NEXTVAL);
Drop Sequence:
Once you have created your sequence in Oracle, you might find that you need to remove it from the
database.
Syntax
The syntax to a drop a sequence in Oracle is:
DROP SEQUENCE <sequence_name>;
sequence_name is the name of the sequence that you wish to drop.
OUTPUT:
CREATE VIEW:
SQL> create view depview as select id,sname from depmt;
View created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- -----------------------------
STUD TABLE
EPP TABLE
EMPL TABLE
EMP TABLE
DVIEW VIEW
DEPMT TABLE
SQL> select * from depview;
ID SNAME
---------- -----------------------------
6 ABHI
2 ARUN
3 ARNAV
4 ABHINAV
5 JAY
UPDATE VIEW:
SQL> update depview set id=8 where id=4;
1 row updated.
SQL> select * from depview;
ID SNAME
---------- ----------------------------
6 ABHI
2 ARUN
3 ARNAV
8 ABHINAV
5 JAY
DROP VIEW:
SQL> drop view depview;
View dropped.
SQL> select * from depview;
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STUD TABLE
EPP TABLE
EMPL TABLE
EMP TABLE
DEPMT TABLE
SYNONYM:
SQL> create synonym d for depmt;
Synonym created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STUD TABLE
EPP TABLE
EMPL TABLE
EMP TABLE
DEPMT TABLE
D SYNONYM
SQL> select * from d;
ID SNAME SDEPT
---------- -------------------- --------------------
6 ABHI MCA
2 ARUN ECE
3 ARNAV EEE
8 ABHINAV MECH
5 JAY IT
DROP SYNONYM:
SQL> DROP synonym d;
Synonym dropped.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- -------------------
STUD TABLE
EPP TABLE
EMPL TABLE
EMP TABLE
DEPMT TABLE
SEQUENCES:
SQL> create table stu(sid number(3),sname varchar2(10));
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- -------------------
STUD TABLE
EPP TABLE
EMPL TABLE
EMP TABLE
DEPMT TABLE
STU TABLE
CREATE SEQUENCE:
SQL> create sequence seq_stud minvalue 100 maxvalue 999 start with 100 increment
by 2 cache 20;
Sequence created.
SQL> insert into stu values(seq_stud.nextval,'&sname');
Enter value for sname: abhi
old 1: insert into stu values(seq_stud.nextval,'&sname')
new 1: insert into stu values(seq_stud.nextval,'abhi')
1 row created.
SQL> /
Enter value for sname: arun
old 1: insert into stu values(seq_stud.nextval,'&sname')
new 1: insert into stu values(seq_stud.nextval,'arun')
1 row created.
SQL> select * from stu;
SID SNAME
---------- --------------------
102 abhi
104 arun
DROP SEQUENCE:
SQL> drop sequence seq_stud;
Sequence dropped.
RESULT:
Thus Views, Sequences and Synonyms have been created and updated in
RDBMS.