Because all the operations are finally active on the base table, the change in the base table name and the column name will have an impact on these statements. At this time, all statements must be modified, which is not only trouble, sometimes even errors. In addition, some or even the name and the column name are complex, and it is extremely inconvenient. How can I solve this problem? Avoid direct problems that depend on the base table, the methods are:
● Using the view as a table name and column name, you can replace the base table name and column name in the application process, when the table name and column name change, simply change the definition of the corresponding view.
● Similar to the view definition, but only provides a more direct and broader approach to define an alias for various objects, including view objects.
● The method of defining the cursor in the program is prevented from being directly dependent on the table. When the table name changes, simply change the cursor definition.
First, use views (omitted)
Second, use the same name
1 Introduction
And using the view to implement the operation is not directly dependent on the base table, and the same name can also achieve this. Unlike the view, the same name is not only available in the name of the table, but also in the view, sequence, stored procedures, and functions, so that its application range is wider. It is not a view that it is better to list alias for the inconvenience of the same name.
The syntax for creating the same name is as follows:
Create [public] synonym synonym with a FOR object;
among them:
● Public: Public inquiry name, all users can reference, if this keyword is omitted, the default is the private synonymous name, that is, private synthetic name, it can only be used for a user.
● Synonymous Name: The alias from the object can be used instead of the original object name when using the object later.
● Object: A particular object name, which can be a base table, a view, a sequence, a process, a store function, a storage package, and other synonymous names, which specifies the user, intermediate "." When specifying an object.
2, specific operation
● Create a private synergy
[Example] Gets the same name to Persons.
SQL> Create Synonym Person for Persons;
In addition, it is the default private synonymation name, which can only be used for current users. To specify it as another user, such as user JXL, you can create the following statement.
SQL> CREATE SYNONYM PERSON FRO JXL.PERSONS;
● Create a common same name
You can also create a common symbol name to all users.
【example】
SQL> CREATE PUBLIC SYNONYM PERSON FRO PERSONS;
● Use the same name
Once you have created the same name, you can reference it elsewhere to replace the base table. As follows:
SQL> INSERT INTO PERSON VALUES (98036, 'skert', 'w', to_date ('25 -OCT-71 '));
In the process of use, if the name of the base table changes, simply modify the definition of the same name. But the same name is created does not support the replace command, so you must delete it and recreate it. The deletion command is as follows:
SQL> Drop synonym person;
If the Persons table is renamed as Persons_Information, the redifice is as follows:
SQL> CREATE SYNONYM PERSON for PERSONS_INFORMATION;
In this way, like using the view, all places where the reference to the same name does not have to do any changes.
● Delete the same name
If an object is deleted, it is also necessary to delete the corresponding synonymous name, because the reference synonym is incorrect, and the data dictionary is also cleaned. Delete the synonymous syntax as follows: Drop [public] Synonym;
Where PUBLIC is used in the case of deleting the common symbolization, if you delete the synonymous name of a user, you must add your username. The three types of penders defined earlier are deleted as follows:
SQL> Drop synonym person;
SQL> Drop synynym jxl.Person;
SQL> Drop Public Synonym Person;
In a distributed database system, the same meaning is greater. Because in a distributed environment, you can use local database objects or database objects, and the objects of different places may be the same name. This must be indicated in this position when referenced to the object. However, if you quote the same name, you can hide the location, so that all objects are transparent, so you can adapt to the various changes.
Third, use the cursor
1 Introduction
The explicit cursor can be used in the stored procedures and functions, and the cursor is equivalent to defining a query, in the later application, the query result of this cursor can be used.
When the table name changes, simply change the cursor defined on this table in the stored procedure and function, and the reference to the cursor is not changed, thereby avoiding the operation directly on the table.
2, specific operation
● 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 implements the function of registering men and women in the Man, Woman table, which uses a cursor Person. It can be seen that in the program body, although the content of the table Persons is referenced, it does not reference the Persons table name, but only quoted at the cursor definition.
● Modify the cursor definition to adapt to changes
When the Persons table name is changed to PERSONS_INFORMATION, simply 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 procedure is short, even without cursor, there is not much relationship. When the 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 packet. In this way, simply use the cursor once to be used in all processes and functions.
Of course, use the cursor also has its own shortcomings, it does not visually solve the problem of direct trusts, and the cursor is more useful in the network database.
