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 many stored procedures, it involves updating, inserting, or deleting table data, etc., in order to prevent the table data after the modification of the problem, but also easy to track the problem, generally for some important tables to establish a corresponding debug table. The fields in this debug table include all fields from the original table, as well as additional field information such as operation time, opcode, 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, which is defined 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
)
The added fields are shown in red, where opertime represents the operation time, result indicates the resulting code ( success or failure, etc. ),description Represents an action description,reservechar1 is a 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 easy 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, you can find some potential deficiencies in programming, such as too few indexes, grammatical errors and so on.
(2) must follow the company's specifications to the database script naming, can not think as long as the code is written well, how to name it doesn't matter.
(3) Whether it is a script to build a table or create a stored procedure, the layout of the code ( such as indentation, line wrapping, alignment, blank lines, etc. ) is important to try to make your code easier to read. Because we are the first person to write programs, followed by computers.
(4) when you need to add or delete a field in the original SQL statement or make other changes, it is recommended that the entire SQL statement is commented out, and then the modified statement is added below the deleted statement, Try not to modify the original statement directly, so as to facilitate future versions of the comparison.
(5) for if,else,else if, while,begin,end in a stored procedure The statement does not immediately follow, regardless of how many of the execution statements it includes, add the 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!) )