The stored procedure of computer room reconfiguration

In the reconstruction of the computer room often encounter such a problem: many of the functions of the implementation of the need to involve more than one table, such as the recharge, checkout and return cards and other functions of the implementation. Therefore, we need to operate the database many times, the amount of code will not only increase, execution efficiency will be greatly compromised. As a result, stored procedures are created to greatly improve execution efficiency.

1. Introduction

A stored procedure is a set of statements designed to accomplish a particular function, compiled and stored in a database, and executed by the user by making a name for the stored procedure and giving parameters. Stored procedures are generated at the time of operation and are stored in the database, when they run faster than a single SQL statement.

2. Advantages and Disadvantages

1) Advantages

A, the reuse of strong. Stored procedures can be reused to reduce the amount of database development effort.

b, improve the efficiency of implementation. Stored procedures are compiled at the time of creation and do not need to be recompiled each time they are executed, which is faster and more efficient than normal SQL statements.

c, reduce network traffic. The stored procedure is located on the server, which only needs to pass the name and parameters of the stored procedure, thus reducing the amount of data transmitted over the network.

D, more secure. Parameterized query procedures can prevent SQL injection attacks.

2) Disadvantages

Although the use of stored procedures improves execution efficiency, if a large number of stored procedures are used in a program system, problems can occur if the data structure changes in late requirements, and later system maintenance can be very difficult and costly.

Therefore, in the use of stored procedures must be cautious, weigh the pros and cons, and try to achieve the best program.

3. Create

Take the top-up of the rebuilding room as an example create a stored procedure, the top-up business logic is:

1) Query the registry to determine if it has been registered, such as no registration information

2) Insert Student information

3) Insert Student Registration information

to establish a stored procedure in SQL Sever, right-click to select New stored procedure.

<span style= "font-size:20px;" >--=============================================--Author: <Akali>--Create Date: <2015 May 20   ; --Description: <Register>-============================================= CREATE PROCEDURE [dbo]. [Proc_register]--Create stored procedure--add parameters in stored procedure @CardID varchar (@StuID varchar), @StuName varchar (       , @StuSex varchar, @StuDepart varchar (TEN), @StuGrade varchar, @StuClass varchar (10), @StuText varchar, @StuCash varchar (TEN), @StuStatus varchar (@RegTime varchar), @R    Egdate varchar, @UserID varchar, @RegCash varchar, @CheckStatus varchar (+) as BEGIN --Add SQL statement SELECT * from T_reginfo where CardID [email protected] if @ @ROWCOUNT =0 insert INTO T_st Uinfo (Cardid,stuid, Stuname, Stusex,studepart,stugrade,stuclass,stutext,stustatus,stucash) VALUES (@CardID, @StuID, @StuNamE, @StuSex, @StuDepart, @StuGrade, @StuClass, @StuText, @StuStatus, @StuCash) insert into T_reginfo (CardID, Stuid, Regd Ate, Regtime, UserID, Regcash, CheckStatus) VALUES (@CardID, @StuID, @RegDate, @RegTime, @UserID, @RegCash, @CheckStatus  ) END </span>

such a stored procedure is done, and when it is needed, we can call directlyDlayers for efficient and fast completion.
4. Learning Summaryin the study of the process, the summary is relatively easy, the reason is that the previous summary of the database and the database has been learning, and now the summary of the stored procedure is very good. For a lot of database knowledge are very closely linked to the overall study of these aspects of knowledge, hope to get more experience it!

The stored procedure of computer room reconfiguration