Let you know about software development in advance (28): saving important table information during database storage and related suggestions

Part 1 Database SQL language

Saving important table information during database storage and related suggestions

 

1. Saving important table information in the Stored Procedure

In many stored procedures, table data may be updated, inserted, or deleted. To prevent problems with the modified table data, A debug table is usually created for some important tables. The fields in the debug table must include all fields in the original table, and the operation time, operation code, operation description, and other field information must be added.

For example, in a project, the following important table tb_XXX is included:

Create table tb_XXX

(

AAA varchar (30) not null, -- AAA

BBB varchar (30) not null, -- BBB

CCC int not null, -- CCC

......

)

 

Our debug table can be named tb_XXX_debuglog. Its definition is as follows:

Create table tb_XXX_debuglog

(

AAA varchar (30) not null, -- AAA

BBB varchar (30) not null, -- BBB

CCC int not null, -- CCC

......

Opertime varchar (19) not null, -- operating time

Result varchar (20) not null, -- result code

Description varchar (100) not null, -- operating description

Reservechar1 varchar (30) null -- reserved

)

 

The added fields are shown in red. opertime indicates the operation time, result indicates the result code (success or failure), description indicates the operation description, and reservechar1 indicates the reserved field.

During the storage process, before performing important operations on the tb_XXX table, you can insert the data in the table into the tb_XXX_debuglog table. When you need to find relevant information or troubleshooting, you can easily find it, improving the program's exception handling capability.

 

2. Some suggestions

(1) be sure to use it after writing the database scriptChecksqlBy checking scripts, you can find some potential defects in programming, such as too few indexes and incorrect syntax.

(2) The database script must be named according to the company's specifications. It does not matter if the code is well written.

(3) Whether it is a table creation script or a stored procedure creation script, code layout (such as indentation, line breaks, alignment, and leave blank rows) is very important, try to make your code easy to read. Because we first write programs for people, and then computers.

(4) to add or delete fields or make other modifications to the original SQL statement, we recommend that you comment out the entire SQL statement first, then, add the modified statement to the bottom of the deleted statement. Do not directly modify the statement as far as possible, so as to facilitate comparison of later versions.

(5) The execution of statements such as if, else, else if, while, begin, and end in the stored procedure does not follow the preceding steps, no matter how many execution statements it includes, you must add the statement block sign begin... End to facilitate reading.

 

Like C/C ++/Java and other programming languages, SQL database scripts must follow certain rules. We should not only allow the script to implement its functions, but also make the best performance possible. "Practice to learn": Our database programming capability can be improved only by continuously practicing and summarizing.

 

 

(My microblogging: http://weibo.com/zhouzxi? Topnav = 1 & wvr = 5, No.: 245924426, welcome !)