Synonym (Synonym)

1. Why use Synonyms:

Synonyms, as the name implies, mean that two words share the same meaning and can be replaced with each other. What are their functions:

A's most obvious goal is to simplify SQL. If the name of a database object is too long, you can create a shorter synonym, which is equivalent to the abbreviation.

B. another purpose is to hide the information of some objects. for example, to search for a table in another schema, you must add the schema name before the table name as the prefix. if you create a synonym, you can hide schema information.

2. How to Create a synonym.

Syntax: Create [or replace] [public] synonym synonym_name for [schema.] Object

1. Create a private synonym:

Example: Create synonym Arwen for table_of_arwen

In this way, select * From Arwen or select * From table_of_arwen is the same.

Delete synonym: Drop synoym Arwen.

Note: A private synonym can only be used by users who create it. If other schemas are used, the Creator Schema must be prefixed. This is the same as other objects.

2. Create a public synonym:

Create public synonym Arwen for table_of_arwen

All schemas can use public synonyms. For example, schema Scott can access the table_of_arwen table of Schema Arwen using select * From Arwen.

Some may wonder why the schema prefix is not used in this case. Maybe the system adds the prefix by default.

Therefore, create public synonym Arwen for table_of_arwen or create public synonym Arwen for Arwen. table_of_arwen is equivalent.

Other schemas can be searched using Arwen.

NOTE: If other schemas do not have the permission to search for the table table_of_arwen, the table cannot be searched using its synonyms.

3. Error: synonym conversion is no longer valid.

When a synonym is created, the system does not check whether the object represented by the synonym exists. For example, create public synonym Arwen for no_table_exist

Suppose there is no table named no_table_exist or another database object named this table.

When select * From Arwen is selected, the following error occurs: synonym conversion is no longer valid. Of course, if the table is deleted, the same error occurs.

3. Alias similar to synonym

There is also an alias in Oracle. It is similar to the synonym function. It is only used in a small scope. It is mainly used in an SQL statement and can only work temporarily. It is like a temporary variable.

For example:

Select EMP. ename, bonus. Sal from EMP, bonus where EMP. ename = bonus. ename

We can give EMP an alias E and bonus an alias for Column B. The query statement is as follows:

Select E. ename as myname, B. Sal as mysal from emp e, bonus B where E. ename = B. ename

The keyword "as" is optional. If it is removed, the same function will be implemented. As is available in the column, but aliases for the table cannot be added.