Advance understanding of software development (28) Preservation of important table information in database stored procedure

1. Save the important table information in the stored procedure

In many stored procedures, will involve the table data updates, inserts or deletes, in order to prevent the modification of the table data problems, while easy to track the problem, generally for some important tables to create a corresponding debug table. The fields in this debug table include all the fields from the original table, along with additional field information such as operation time, opcode, and operation description.

For example, in a project, include one of the following important table tb_xxx:

CREATE TABLE Tb_xxx
    
(
    
     AAA                varchar not    null,    -AAA
    
     BBB                varchar ()    is not NULL, -   -BBB
    
     CCC                int not                     null,   --CCC
    
     ...
    
)

Our debug table can be named Tb_xxx_debuglog, which is defined as follows:

CREATE TABLE Tb_xxx_debuglog
    
(
    
     AAA                varchar) not    null-   -AAA
    
     BBB                varchar    Not NULL,   -BBB
    
     CCC                int is not           null,   CCC
    
     ...
    
    Opertime           varchar NOT NULL,--operating time result              varchar     is not NULL,   - -Result Code
    
    Description     varchar (MB) not    null,  --Operating description
    
    RESERVECHAR1  varchar ()            null   -reserved
    
)

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/Programming/project/

The added fields are shown in red, where opertime represents the operation time, result represents the resulting code (success or failure, etc.), description represents the operation description, and RESERVECHAR1 is the reserved field.

In the stored procedure, you can insert (dump) the data into the Tb_xxx_debuglog table before you perform an important operation on the Tb_xxx table. When you need to find relevant information or scheduling errors, it is easy to find, improve the program's ability to handle the exception.

2. A number of recommendations

(1) After the completion of the database script, it is necessary to use tools such as Checksql to check the script, you can find some of the potential programming deficiencies, such as the establishment of the index too little, syntax errors.

(2) must be in accordance with the company's specifications to name the database script, can not think as long as the code is written well, how to name does not matter.

(3) Whether it is a table script or a script to create a stored procedure, the layout of the code (such as indentation, newline, alignment, blank lines, etc.) is important to make your code as easy as possible to read. Because we are the first to write the program, followed by the computer.

(4) When you need to add or remove fields in the original SQL statement or make other changes, we recommend that the entire paragraph of the SQL statement commented out, and then the modified statement added to the bottom of the deleted statement, as far as possible not to directly modify the original statement, so as to facilitate the comparison of later versions

(5) For the stored procedure if, else, else if, while, begin, end and other statements from the line, the execution of the statement does not immediately follow, regardless of the number of execution statements included, to add a statement block Mark Begin...end, so as to facilitate reading.

As with C/c++/java programming languages, writing SQL database scripts also needs to follow certain rules. Not only do we let the script do what it does, but we want to make it as optimal as possible. "Practice out of the truth," only continuous practice and summary, our database programming ability will be improved.