Test the Stored Procedure

Yesterday, I saw a post on 51testing about the test of the stored procedure. In terms of stored procedures, I have done a lot. Basically, 70% of my work is about Testing Stored Procedures. So for the first time, I posted my first opinion on 51testing. In my post, I briefly talked about how to test the stored procedure and what to pay attention.

Tonight, let's take a look at the Stored Procedure testing process in a bit more detail!

There are two main aspects of Stored Procedure testing. One is the business logic, that is, the implementation function. The second is the performance, seeking for the optimal SQL statement.

(1): business logic

Store a bunch of stored procedures in the database. You need to test whether the implemented functions are implemented without any additional "functions". You have obtained the desired data, no redundant data is obtained. Since data needs to be transmitted after it is obtained, if redundant data is available, although it can be processed during the acquisition, it will put a lot of pressure on the server. Over time, there will inevitably be bottlenecks. Furthermore, for a series of stored procedures, you need to check whether they have completed the required functions as required and whether they can run a series of processes.

For business logic tests, you can execute this statement in the database and manually test the statement after passing in some parameters. Speaking of this, we will certainly ask whether automatic test can be implemented? Of course, there are some skills here. I simply implemented it through the C # language. You can use ADO. Net to call a stored procedure and input some parameters. When you execute the stored procedure, you can click a button to finish all the stored procedures. In this process, the data to be collected is a difficult point, because you need to continue using this data. The test database is not used by you alone. Therefore, this data is flexible and requires careful consideration. Otherwise, it will often fail. To be more intelligent, you do not need to click a button to automatically execute all the stored procedures. Of course, it is automatically run at startup. There is a third-party tool: nunit. You only need to set the Stored ProcedureCodeAfter compilation, load the code and click execute to run the code at one time. If the Code fails to run, a color prompt is displayed. This tool actually looks simple, and there are many features in it. I am not talented and can't find more, but simply use the Running code. Use nunit in combination with several other plug-ins, such as Nant, nunit2report, and nantcontrib. These products can be used together to build an automatic phone test platform. For details about how to proceed, please refer to the next time.

(2) Performance of stored procedures.

Stored procedures are modified by SQL statements. When it comes to performance, it means the performance of SQL statements. The performance of an SQL statement can be achieved in many ways. Experienced colleagues can see the performance of most SQL statements at a glance. Therefore, a very simple method is to watch with the naked eye. Today, many software programs support viewing the running time of SQL statements, and you can intuitively see the performance of SQL statements in terms of time. In the same external environment, the shorter the time, the higher the efficiency obviously. I vaguely remember that I introduced a statement in a short article on 51testing, which can be directly executed on Oracle and sqlplus and can be directly checked to see the SQL running time. Well, I have a bad memory and forgot it, we should be able to find out. There are many ways to view the execution time of SQL statements. PL/SQL displays the execution time of SQL statements. You can also use ADO to call the stored procedure to view the execution time of SQL statements, which is also easy to implement.

 

after more than a year of work in the testing field, I finally found myself doing something. I am still worried about my work. In fact, I have time to make a summary. Therefore, if you have been diving for so long, record your testing work and express your work experience in the first article. I hope I can find a good job and get out of the shadows early.