Let you know in advance. Software Development (28): Preservation of important table information in database stored procedure and related suggestions

2nd part Database SQL language

Preservation of important table information in database stored procedure and related suggestions

1. Saving important table information in the stored procedure

In very many stored procedures, it involves updating, inserting, or deleting table data, and in order to prevent changes to the table data failure, it is convenient to trace the problem at the same time, usually to create a corresponding debug table for some important tables. The fields in this debug table will contain all the fields of the original table, and the same time you want to add the field information such as Operation time, operation code and operation description.

For example, in a project, one of the following important table Tb_xxxis included:

CREATE TABLE Tb_xxx

(

AAA varchar (+) NOT NULL,--AAA

BBB varchar (+) NOT NULL,--BBB

CCC int NOT NULL,--CCC

. . . . . .

)

Our debug table can be named Tb_xxx_debuglog, and its definition is as follows:

CREATE TABLE Tb_xxx_debuglog

(

AAA varchar (+) NOT NULL,--AAA

BBB varchar (+) NOT NULL,--BBB

CCC int NOT NULL,--CCC

. . . . . .

opertime varchar () NOT NULL,--Operating time

result varchar () NOT NULL,--Result code

Description varchar (+) NOT NULL,--Operating description

reservechar1 varchar (+) null--Reserved

)

Add the field as seen in the red Word, where opertime indicates the operation time, result indicates the results code ( success or failure, etc. ),description represents the description of the operation,reservechar1 is the reserved field.

In the stored procedure, the data inside can be inserted ( dumped ) into the tb_xxx_debuglog table before important operations are performed on the tb_xxx table. When you need to find relevant information or troubleshooting, it is very convenient to find, improve the program's exception handling ability.

2. some recommendations

(1) after writing the database script, be sure to use tools such as checksql to check the script, can find some potential deficiencies in programming, such as too few indexes, grammatical errors and so on.

(2) must be in accordance with the company's specifications to the database script naming, can not think of just code to write well, how to name it doesn't matter.

(3) Whether it is a script to create a table or a stored procedure, the layout of the code ( such as indentation, line wrapping, alignment, blank lines, etc. ) is very important to make your code read as easy as possible. Since we are the first to make code, then the computer.

(4) when it is necessary to add or remove fields from the original SQL statement or make other changes, it is recommended that the entire SQL statement be stared at, and then add the modified statement to the bottom of the deleted statement, Try not to make changes directly on the original statement, so as to facilitate the comparison of the version number.

(5) for if,else,else if, while,begin,end in a stored procedure And so on the statement from the line, run the statement do not immediately follow, no matter how many of the running statements it contains, add a statement block flag begin... end, so it's easy to read.

As with programming languages such as C/c++/java, it is necessary to write SQL database scripts in accordance with certain rules. Not only do we have to let the script do what it needs to do, but we want to make the performance as optimal as possible. "Practice out of the truth", only to continue to practice and summarize, our database programming ability will be improved.

(I Weibo: Http://weibo.com/zhouzxi?topnav=1&wvr=5, No.: 245924426, welcome attention!) )

Let you know in advance. Software Development (28): Preservation of important table information in database stored procedure and related suggestions