Let you know software development in advance (35): How to modify SQL scripts to meet your needs ?, Software Development SQL
Part 1 Database SQL language
How can I modify an SQL script to meet my needs?
The SQL script modification process is the same as that of the C language code. follow these steps:
Step 1: Read the requirements and find out what functions you want to accomplish.
Step 2: Read the original code as required and find the approximate location of the content to be modified.
Step 3: modify the code (including add, delete, and modify) and add this function.
Step 4: test the modified Code to verify that the new function meets your needs.
This document describes how to modify an SQL script by taking the actual requirement completion process as an example.
1. Requirement Description
In a software version, the following requirements must be met:
(1) adds a media type 4 based on the original media type, and its function attribute number is 4.
(2) query the total number of new media users stored in the mailbox and determine whether the maximum storage limit is reached.
(3) If the maximum storage limit is 1000, it indicates that the total number of new media users stored in the mailbox is not limited, that is, whether the mailbox is full or not.
(4) If the maximum storage limit is 0, it indicates that the mailbox cannot receive new media-type emails.
2. Find the location where the SQL script is modified
By understanding the requirements and reading the original script, you can find that you need to modify the following code location (the script is implemented based on the Sybase Database ):
......
......
If (@ mailtype = 1)
Begin
Select @ functionid = 1
End
Else if (@ mailtype = 2)
Begin
Select @ functionid = 1
End
Else if (@ mailtype = 3)
Begin
Select @ functionid = 3
End
Select @ tmpstr = funvalue from tb_cosprofile where cosinid = @ mboxserver and functionid = @ functionid
If (@ mailcnt <@ maxcnt)
Begin
Select @ isfull = 0
End
Else
Begin
Select @ isfull = 1
End
......
......
3. Complete requirements step by step
After finding the location where the code is modified, we need to implement the requirements below.
Step 1: Add a media type 4 based on the original media type and set its function attribute number to 4.
The modified code is as follows:
......
......
If (@ mailtype = 1)
Begin
Select @ functionid = 1
End
Else if (@ mailtype = 2)
Begin
Select @ functionid = 1
End
Else if (@ mailtype = 3)
Begin
Select @ functionid = 3
End
-- Zzx add 20140709 begin
Else if (@ mailtype = 4)
Begin
Select @ functionid = 4
End
-- Zzx add 20140709 end
......
......
Step 2: query the total number of new media users stored in the mailbox and determine whether the maximum storage limit is reached.
Compared with the original code, this requirement can be implemented previously, and will not be modified this time.
Step 3: If the maximum storage limit is 1000, it indicates that the total number of new media users stored in the mailbox is not limited, that is, you do not need to judge whether the mailbox is full. If the maximum storage limit is 0, the mailbox cannot receive new media-type emails.
When modifying the code, we have a principle:Try not to change the previous process. Therefore, to meet this requirement, we consider using "if... Else statement to process the new media type separately.
The modified code is as follows:
......
......
-- Zzx add 20140709 begin
If (@ functionid = 4)
Begin
If (@ maxcnt = 0)
Begin
Select @ isfull = 2 -- in this case, a special value is returned.
End
Else if (@ maxcnt = 1000)
Begin
Select @ isfull = 0
End
Else
Begin
If (@ mailcnt <@ maxcnt)
Begin
Select @ isfull = 0
End
Else
Begin
Select @ isfull = 1
End
End
End
-- Zzx add 20140709 end
Else
Begin
If (@ mailcnt <@ maxcnt)
Begin
Select @ isfull = 0
End
Else
Begin
Select @ isfull = 1
End
End
......
......
After the above three steps are modified, the final script is as follows:
......
......
If (@ mailtype = 1)
Begin
Select @ functionid = 1
End
Else if (@ mailtype = 2)
Begin
Select @ functionid = 1
End
Else if (@ mailtype = 3)
Begin
Select @ functionid = 3
End
-- Zzx add 20140709 begin
Else if (@ mailtype = 4)
Begin
Select @ functionid = 4
End
-- Zzx add 20140709 end
Select @ tmpstr = funvalue from tb_cosprofile where cosinid = @ mboxserver and functionid = @ functionid
-- Zzx add 20140709 begin
If (@ functionid = 4)
Begin
If (@ maxcnt = 0)
Begin
Select @ isfull = 2 -- in this case, a special value is returned.
End
Else if (@ maxcnt = 1000)
Begin
Select @ isfull = 0
End
Else
Begin
If (@ mailcnt <@ maxcnt)
Begin
Select @ isfull = 0
End
Else
Begin
Select @ isfull = 1
End
End
End
-- Zzx add 20140709 end
Else
Begin
If (@ mailcnt <@ maxcnt)
Begin
Select @ isfull = 0
End
Else
Begin
Select @ isfull = 1
End
End
......
......
4. Test the modified Code.
After the code is modified, you must perform a full self-test before submitting the version.
For SQL script testing, you need to construct some data. After the script is executed, check whether the specific process is executed as required.
In general, we should pay attention to the following points during the modification of the SQL script:
(1) Before modifying the code, you must understand the requirements and find out what functions you want to implement.
(2) In the process of modifying the code, it is necessary to strictly follow the programming specifications. Note the modified Code.
(3) The code modification should follow the minimum modification principle, that is, we should implement new functions without changing the original program process as much as possible.
(4) After the code is modified, you must perform full self-testing. This is the same as the testing of C language programs.
"Practice is true". Only through continuous practice can we improve our programming capabilities and grasp the correct and fast methods to achieve our needs.
(My microblogging: http://weibo.com/zhouzxi? Topnav = 1 & wvr = 5, No.: 245924426, welcome !)
If the program on this website is not written by you, it is not your fault. You do not need to modify the script, and you cannot change the script.
How to change the database
No primary key? At least one field is unique. Otherwise, how do you know that the data you modified is the one in the database?
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
