Oracle program development tips

Oracle has many advantages, but if it is used improperly, its powerful advantages cannot be exerted. Here is a little experience I accumulated in the Oracle development process to share with you.

1. The use of fields in database design

In the design of some tables, there are some commonly used paragraphs, which have basically become a standard. In large systems, you can often see these Traces of fields, although the names of the fields may vary. Commonly used fields are divided into the following categories:

1.WHO field

This type of field is mostly used to record the operation change information of each row of records, such as who added this row of records and who did it. After modification, etc., the detailed description is as follows:

##CREATION_DATEDATECREATED_BY I. Created by

Field name	Type	Description
##LAST_UPDATE_DATE	DATE	Last modified Date
LAST_UPDATED_BY	NUMBER(15)	Last modified Person
	##CREATION_DATE
NUMBER(15)	CREATED BY

In any system, there is usually a permission verification and login process. After logging in, the login person's information will be recorded in the system's memory. When this person logs in to a certain part of the database When the table is added, the ID value of the operator will be written into the table at the same time for later statistics and auditing

II. Creation time

is similar to the meaning of the creator. When creating, the current time of the system is written. The value of this field is generally taken from the server rather than the client. For example, in Oracle, you can use SYSDATE directly. As the value of this field

III. Last modified by

After a record is created, it may also be modified. Here, a record of the modified person is needed to facilitate later auditing.

But what should be noted here is that only the last modification information is recorded here. If a record has been modified multiple times, the modifications in the middle will not be traceable. If you need to record detailed modification information, you need to use the log function , has exceeded the functionality of this field.

IV. The last modification date

is written into the record at the same time as the last modification person. Similarly, it is also the time of the last modification recorded. All modifications in the middle are overwritten by the last modification.

2. Status and Validity Field

In some news content, there is often a timeliness involved, that is, a piece of news may only be visible to the outside world within a certain time period, and it will not be allowed beyond this time period. Release, and sometimes if there is a problem with some content and it needs to be temporarily blocked from the outside world, you can use the status field. The details are as follows:

Field nameSTATUS##START_DATE

Type

Description

NUMBER

STATUS

DATE

Effective start date

##END_DATE

DATE

Effective end date

I. Status

This field is generally expressed in numeric type, 0 means invalid, 1 means valid. Of course, when using, these two values ​​​​can be escaped and displayed as "valid" and "invalid". Does not affect storage methods.

Invalidation does not mean deletion. The expired content can be restored to normal use after adjustment by the management program and the status becomes valid.

II. Valid start date

If this field is filled with a specific value, the information will be valid only when the time exceeds this time. Before this date, the information will automatically be treated as invalid. It is particularly important to note that, If this field is left blank, it should be processed by skipping this condition check, which can achieve program flexibility.

III. Validity end date

The specific meaning is the same as above, except that after this period, the content will be treated as invalid.

3. Logical deletion

There are generally two methods of deletion in a database system: physical deletion and logical deletion. The so-called physical deletion means directly using delete and other commands in the database to actually delete the data from the database. , this kind of deletion will not be able to recover the data through normal channels. Although it can partially reduce the overall data volume, it is not conducive to audit tracking; logical deletion means that the data is not deleted, but the records are marked. That is, assigning a value to a certain field indicates that the record has been deleted.

The processing logic of logical deletion is only used by the application itself, because the data actually still exists in the database.

The fields involved are as follows:

##DELETE_DATEDELETED_BY

Field name	Type	Description
DELETED	##NUMBER	Whether to delete
##DATE	##Delete time
NUMBER	Delete person	I. Deletion flag This field has two values, 0 means normal, 1 means deleted. After marking the deletion mark, all query statements must also add the condition deleted=0 in the judgment condition, otherwise it will cause major mistakes. II． Deletion time Used together with the deletion flag to indicate the deletion time. You can use the current server time and fill it with sysdate. III． Deletion person Similar to the last modification person, the specific operator of the deletion needs to be recorded Deletion is different from the flag bit. The flag bit method is used. Although the front end cannot display the data, the backend management personnel The status can also be adjusted through the management program, but the deletion flag is different. After the deletion is confirmed, the record should be understood as not existing for the entire application. 4. Auto-increment field The so-called auto-increment field refers to a field that can be automatically added with use. The value of this field generally has no clear meaning and is only used for a unique identifier. This field is generally set as a primary key. If the application only targets Oracle and does not consider database independence, then sequence is the best choice. For those who used to use other databases such as MSSQL in the past, the use of Oracle is simply too laborious. It takes a lot of effort to create an auto-increment field, but because of this, it also brings about problems that cannot be achieved in other databases. Compared with the advantages, for example, an order system has both order headers and order lines. Generally, the order header is inserted first, and then the order lines are inserted. For auto-increment fields in databases such as MSSQL, the specific information can only be known after insertion. What is the ID value of The main thing is that the sequence effect is very high, and there is no need to worry about performance issues. 5. Flexible fields When designing the database table structure, it is best to set aside a few spare fields, because with the use of the system, there is generally a need to add fields. The advantage of reserved fields is that they can be enabled when needed. DDL operations are not required. The risk of later maintenance of the database is very low. Moreover, general DDL operations will cause the cascaded VIEW/PACKAGE and other programs to fail. Reserved fields If you add elastic fields, you won't have this problem. The reserved fields can also be divided into three types according to type: string type, numerical type and date type. You can reserve 10 fields for each type, or decide according to needs. You can use the following style: NUMBER_ATTRIBUTE1 STRING_ATTRIBUTE1 DATE_ATTRIBUTE1 If the elastic field is not enabled, will it take up too much storage space? The answer is no, because in In the structure of this large database, only when a field is actually used, it will occupy actual space. Otherwise, it is just a "description" and does not occupy actual space, so there will be no waste of space. 6. Split field This is not a type of field, but refers to a large table that can be appropriately split into different small tables for storage during table design. For example, the user table can include login names. , password, name, birthday, and a series of fields. In some cases, the number of member attributes included may reach hundreds. When the amount of data is small, no matter what kind of storage, there will be no performance problems, but when the amount of data is relatively large, performance issues must be considered. If the index is reasonable, no matter how large the amount of data is, the general query speed will not be too slow. However, in some special circumstances, when the index cannot be used, FTS (so-called full table scan) will occur. Then scan a small table and The time taken to scan a large table is completely different, so it is recommended to store larger tables separately and extract several commonly used fields separately, so that even if the whole table is scanned, the efficiency can be better controlled. When used, as long as the main table and sub-table have indexes, combining them to query is basically the same as a real large table, although the performance is definitely slower than a real large table A little, but compared to the performance improvements on the other hand, it's worth it. Currently some large systems adopt this splitting method 2. Reasonable use of views A view is a representation of a base table, it is not physically stored The data is only called from the base table when needed. It can be understood as a tool that encapsulates a SQL for easy use. There are many advantages of views: 1. Convenience If a query is very complex, it will be very inconvenient to reference it in the program. Especially when this SQL is called repeatedly in a program, the program will become very bloated. If the SQL is encapsulated into a view, it will Makes the whole program look very fresh and clean. 2. Flexibility When a table is referenced by multiple programs, if the structure of the table changes, then all programs need to make corresponding adjustments, and the workload is very large. If there is only one program in the program at this time, If a view is referenced, we only need to modify the view correctly, and all programs will have no problems. 3. Security A view can only include a limited number of fields of a base table, so that when permissions are public, users using the view can view other confidential fields of the base table. Views have many advantages. It is recommended to use views more in actual work. 3. PACKAGE PACKAGE is a program package. Its characteristic is that variables, functions and stored procedures can be included in one package at the same time, and all public variables can be shared within the entire package. , this is its most convenient place. Another feature of the package is that the external interface becomes simpler. No matter how complicated the package is inside, it only needs to open a limited interface to the outside world. These characteristics of the package can be found in any document. I won’t go into details here. I only emphasize one point here, which is about the invalidation of the package. When the aspx program references the oracle package, it is due to the system cache. , it will record the invalid status of the package. If the status of a package has been recompiled from invalid to valid, then the status recorded by the IIS cache may not be automatically updated and will still be processed as invalid, which will cause a system error. In order to avoid this kind of error, I usually add a public function to the package, named STATUS. This function simply returns a number 1. Before calling other functions of the package, call this function to check the current status of the package. If 1 can be returned correctly, you can continue with the subsequent operations. If the return is empty, it means there is a problem with the current package, and there can be an interaction to prompt the user to handle it. When your transaction processing requires the coordination of multiple packages, it is very painful if the previous package has been successfully processed but the later package cannot pass, because it may cause the transaction to be incomplete, so this It is most necessary to check the status of all packages in advance and eliminate all possible dangers in advance. 4. Index The role of index is very obvious, which can greatly improve the speed of checking. Especially for large tables, if there is no index, a full table scan will be required. This is This is a very time-consuming action, so corresponding indexes need to be created to speed up the process. But sometimes, the index will have a negative impact, such as a large number of insertion or modification and deletion operations, because every small action will also modify the index at the same time, which will greatly reduce the efficiency of DML operations, so , before a large number of DML operations, it is recommended to delete the index first, and then re-establish the index after the operation is completed, which can save a lot of time in terms of total time. For more articles related to Oracle program development tips, please pay attention to the PHP Chinese website!