Avoid large changes caused by changes

Because all operations ultimately work on the base table, changes to the base table name and column name will affect these statements. At this time, you must modify all statements, which is not only troublesome, sometimes an error occurs. In addition, some even complicated and obscure table names and column names make it inconvenient to use. How can this problem be solved? To avoid direct dependence on the base table, the methods are as follows:

● Use a view to create an alias for the table name and column name. during the application process, you can use the view name to replace the base table name and column name. When the table name and column name change, you only need to change the definition of the corresponding view.

● Similar to view definition, it only provides a more direct and extensive method to define aliases for various objects, including view objects.

● Use the method of defining the cursor in the program to prevent direct dependence on the table. When the table name changes, you only need to change the definition of the cursor.

I. View usage (omitted)

Ii. Use synonymous names

1. Introduction

Just like using a view to perform operations without directly relying on a base table, you can use a synonym name to achieve this purpose. Unlike a view, a synonym name can be applied not only to table naming, but also to views, sequences, stored procedures, functions, and packages, therefore, it is widely used. The inconvenience of using a synonymous name is that it cannot create an alias for a column, which is not as good as a view.

The syntax for creating a synonymous name is as follows:

Create [public] synonym synonymous name for object;

Where:

● Public: Public synonymous name, which can be referenced by all users. If this keyword is omitted and not written, private synonymous name is used by default, that is, private synonymous name, which can only be used by one user.

● Synonymous name: an alias for an object. You can use this name to replace the original object name when using an object in the future.

● Object: The name of a specific object. It can be a base table, view, sequence, procedure, storage function, storage package, or other synonymous names. You can specify a user when specifying an object, use ". separate.

2. Specific operations

● Create a private synonymous name

[Example] the alias person is used for the persons table.

SQL> Create synonym person for persons;

In addition, the default private synonymous name can only be used by the current user. If you want to specify it as another user, for example, jxl, you can use the following statement to create one.

SQL> Create synonym person fro jxl. Persons;

● Create a public synonymous name

You can also create a public synonymous name for all users.

[Example]

SQL> Create public synonym person fro persons;

● Use synonymous names

After creating a synonym name, you can reference it elsewhere to replace the reference of the base table. As follows:

SQL> insert into person values (98036, 'skert ', 'w', to_date ('25-OCT-71 '));

In future use, if the base table name changes, you only need to modify the definition of the synonymous name. However, the replace command is not supported for creating synonymous names. Therefore, you must delete them and recreate them. The DELETE command is as follows:

SQL> drop synonym person;

If the name of the persons table is changed to "persons_information", the alias is redefined as follows:

SQL> Create synonym person for persons_information;

Like using a view, you do not need to make any changes to reference synonymous names.

● Delete synonymous names

If an object (such as a table) is deleted, the corresponding synonymous name must also be deleted because an error occurs when a synonym name is referenced, and the data dictionary is also cleared. Syntax:

Drop [public] synonym;

Public is used when you delete a public synonymous name. If you delete a user's synonymous name, you must add the user name. The three synonymous names defined above are deleted as follows:

SQL> drop synonym person;

SQL> drop synonym jxl. person;

SQL> drop public synonym person;

In distributed database systems, synonymous names are more important. In a distributed environment, you can use local database objects or reference database objects in other places, and objects in different places may have the same name. In this way, you must specify the location where the object is referenced. However, if you reference a synonymous name, you can hide the location so that all objects can be used transparently, so that you can adapt to various changes and simplify the application.

3. Use the cursor

1. Introduction

You can use an explicit cursor in stored procedures and functions. The cursor defines a query and may use the cursor to query the results in future applications.

When the table name changes, you only need to change the cursor defined in the table in the stored procedure and function. The reference to the cursor does not need to be changed later, thus avoiding the direct dependence on the table operation.

2. Specific operations

● Use the cursor in the program

[Example]

Declare

Person_no number (5 );

Person_name char (10 );

Person_sex char (1 );

Cursor person is select No, name, sex from persons where no <98050;

Begin

Open person;

Fetch person into person_no, person_name, person_sex;

Loop

Exit when person % notfound;

If person_sex = 'M' then

Insert into man values (person_no, person_name );

Else

Insert into woman values (person_no, person_name );

End if;

Fetch person into person_no, person_name, person_sex;

End loop;

Close person;

End;

This PL/SQL program registers male and female employees in the man and woman tables respectively. It uses a person cursor. As you can see, although the persons table is referenced in the program body, the persons table name is not referenced, but only once when the cursor is defined.

● Modify the cursor definition to adapt to changes

When you change the persons table name to "persons_information", you only need to modify the table name at the cursor definition, and the program will complete the same function.

[Example] The cursor is defined as follows:

Cursor person is

Select No, name, sex

From persons_information

Where no <98050;

This program is short, even if you don't need a cursor. When an application is large, it is necessary to consider the use of the cursor, and it is best to put all stored procedures and functions in a package. In this way, you only need to define the cursor once and can use it in all procedures and functions.

Of course, the use of the cursor also has its own shortcomings, it does not intuitively solve the problem of direct trust in the table, the cursor in the network database to reduce the amount of network transmission is more useful.

(From Oracle 9i concise tutorial Tsinghua University Press)