PL/SQL - Synonym
Synonyms:
->synonym acts as a reference of a datatable, that does not take up space. The concept is of Aliasing a table or giving another name for the table.
->any changes made to the table affects the synonym & vice versa.
->we can only refer the entire table to a synonym.
->cannot filter rows / cols & keep in a synonym.
->diff. users of ORACLE can be given diff. names / access names to a table & they would all refer to the same table.
->Synonym Creation:
CREATE SYNONYM synonymname
FOR tablename;
->Accessing a synonym is done in the same manner as accessing a datatable
->Dropping a synonym:
DROP SYNONYM synonymname;
->synonyms can be used to refer to the local tables of a user.
CREATE SYNONYM synonymname
FOR username.tablename;
Practicals:
-----------------------------------------------------------------------------
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 3 20:20:52 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: system
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> DESC stutabnew;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLL NUMBER(5)
NAME VARCHAR2(12)
M1 NUMBER(4,1)
M2 NUMBER(4,1)
PER NUMBER(4,1)
DOB DATE
SQL> CREATE SYNONYM syn_stu
2 FOR stutabnew;
Synonym created.
SQL> DESC syn_stu;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLL NUMBER(5)
NAME VARCHAR2(12)
M1 NUMBER(4,1)
M2 NUMBER(4,1)
PER NUMBER(4,1)
DOB DATE
SQL> SELECT * FROM syn_stu;
ROLL NAME M1 M2 PER DOB
---------- ------------ ---------- ---------- ---------- ---------
1010 Amit Singh 89.5 34.5 62 20-JAN-91
1011 Amit 90 30.3 60.2 05-MAY-99
1012 Raj Singh 27.5 49 38.3 18-JAN-91
1013 Adi Singh R 34.5 55 44.8 23-NOV-93
1014 Rohit 27.5 78 52.8 11-JAN-91
1111 Anrun Singh 22 22 22
111 Arjun Singh 88 88 88
2000 test 89 54 10-JAN-00
2001 newname 59 34 01-JAN-00
2002 anothername 49 64 11-JAN-00
10 rows selected.
SQL> SELECT * FROM syn_stu
2 WHERE per > 60;
ROLL NAME M1 M2 PER DOB
---------- ------------ ---------- ---------- ---------- ---------
1010 Amit Singh 89.5 34.5 62 20-JAN-91
1011 Amit 90 30.3 60.2 05-MAY-99
111 Arjun Singh 88 88 88
SQL> UPDATE syn_stu
2 SET DOB='05-JUN-99'
3 WHERE roll=1011;
1 row updated.
SQL> SELECT * FROM syn_stu;
ROLL NAME M1 M2 PER DOB
---------- ------------ ---------- ---------- ---------- ---------
1010 Amit Singh 89.5 34.5 62 20-JAN-91
1011 Amit 90 30.3 60.2 05-JUN-99
1012 Raj Singh 27.5 49 38.3 18-JAN-91
1013 Adi Singh R 34.5 55 44.8 23-NOV-93
1014 Rohit 27.5 78 52.8 11-JAN-91
1111 Anrun Singh 22 22 22
111 Arjun Singh 88 88 88
2000 test 89 54 10-JAN-00
2001 newname 59 34 01-JAN-00
2002 anothername 49 64 11-JAN-00
10 rows selected.
SQL> SELECT * FROM stutabnew;
ROLL NAME M1 M2 PER DOB
---------- ------------ ---------- ---------- ---------- ---------
1010 Amit Singh 89.5 34.5 62 20-JAN-91
1011 Amit 90 30.3 60.2 05-JUN-99
1012 Raj Singh 27.5 49 38.3 18-JAN-91
1013 Adi Singh R 34.5 55 44.8 23-NOV-93
1014 Rohit 27.5 78 52.8 11-JAN-91
1111 Anrun Singh 22 22 22
111 Arjun Singh 88 88 88
2000 test 89 54 10-JAN-00
2001 newname 59 34 01-JAN-00
2002 anothername 49 64 11-JAN-00
10 rows selected.
SQL> DROP SYNONYM syn_stu;
Synonym dropped.
SQL> SELECT * FROM syn_stu;
SELECT * FROM syn_stu
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> SELECT * FROM stutabnew;
ROLL NAME M1 M2 PER DOB
---------- ------------ ---------- ---------- ---------- ---------
1010 Amit Singh 89.5 34.5 62 20-JAN-91
1011 Amit 90 30.3 60.2 05-JUN-99
1012 Raj Singh 27.5 49 38.3 18-JAN-91
1013 Adi Singh R 34.5 55 44.8 23-NOV-93
1014 Rohit 27.5 78 52.8 11-JAN-91
1111 Anrun Singh 22 22 22
111 Arjun Singh 88 88 88
2000 test 89 54 10-JAN-00
2001 newname 59 34 01-JAN-00
2002 anothername 49 64 11-JAN-00
10 rows selected.
SQL>
0 Comments