<2> storage procedure (SQL)

I introduced the use of triggers in my previous blog, and also mentioned that triggers are a special stored procedure. What is a stored procedure? They

What are the differences between the two?

In fact, the main difference is that the trigger is automatically executed when the conditions are met, and the stored procedure is manually called.

 

Brief Introduction

 

What is a stored procedure?

Definition: common or complex work is written in SQL statements in advance and stored with a specified name. You can specify the name of a stored procedure.

And provide parameters (if the stored procedure has parameters) to call it.

At this point, someone may ask: isn't the stored procedure just a bunch of SQL statements? What is the difference between stored procedures and general SQL statements?

What about it?

The stored procedure has its unique advantages, not just accumulating a pile of SQL statements:

1. The stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it.

Therefore, the use of stored procedures can speed up database execution.

2. When complex operations are performed on the database (such as updating, Insert, Query, and Delete multiple tables), you can use the stored procedure to seal this complex operation.

Together with the transaction processing provided by the database.

3. The stored procedure can be reused to reduce the workload of database developers.

4. High security. You can set that only one user has the right to use the specified stored procedure.

 

Types of stored procedures:

 

1. system stored procedure: starts with sp _. It is used to set the system and obtain information. related management work. For example, sp_help is used to obtain information about a specified object.

Information

2. The extended stored procedure starts with XP _ and is used to call the functions provided by the operating system.

3. User-Defined stored procedures, which we refer to as stored procedures

 

How to Use

 

 

How can we apply such a good thing to the system?

Take the refund function of the data center billing system as an example.

1. The first step is to think about how many SQL statements are involved in this function (if there is only one SQL statement, you don't have to create a stored procedure ),

List all SQL statements:

Delete Card_info where CID = @ CID delete information of the corresponding card number in the card table

Delete Student_info where SID = @ SID delete the student ID corresponding to the student table based on the student ID corresponding to the card number

Delete Line_info where CID = @ CID delete information of the corresponding card number in the Host record

Delete Recharge_info where CID = @ CID delete the information of the corresponding card number in the recharge record

Insert into ReturnCard_info (CID, ReturnCash, UID, Rdate, Rtime) values (@ CID, @ ReturnCash, @ UID, @ Rdate, @ Rtime)

 

1. Create a stored procedure open programmability under the corresponding database and create a new Stored Procedure

1. Code for adding a stored procedure

<Span style = "font-family: Microsoft YaHei; "> -- ============================================ =========-- Author: niu Qian -- Create date: July 22, June 11, 2014 -- Description: return the card, delete Card_info, Line_info, Student_info, Recharge_info, add ReturnCard_info -- ======================================== ========= alter procedure [dbo]. [PROC_ReturnCard] -- naming convention PROC _ + name -- Add the parameters for the stored procedure here @ CID varchar (20), @ ReturnCashvarchar (20), @ UID varchar (20 ), -- defines the parameters used @ Rdatevarchar (20), @ Rtime varchar (20), @ SID varchar (20) ASBEGIN--SET nocount on added to prevent extra result sets from -- interfering with SELECT statements. setnocount on; -- Insert statements forprocedure here -- add the involved SQL statement deleteCard_info where CID = @ incluwhere SID = @ SIDdeleteLine_info where CID = @ CID limit where CID = @ CIDinsertinto ReturnCard_info (CID, returnCash, UID, Rdate, Rtime) values (@ CID, @ ReturnCash, @ UID, @ Rdate, @ Rtime) END </span>

1. Call a stored procedure (layer D)

<Span style = "font-family: Microsoft YaHei;"> Public Function ReturnCard (ENCardinfo AsEN_Card_info, ENStudentinfo As region, ENLineinfo As EN_Line_info, ENReturnCardinfo As region) As Integer response. returnCard Dim strSql As String = "PROC_ReturnCard" 'strsql here does not store a single SQL statement, but the Stored Procedure 'defines the required parameter Dim sqlParams As SqlParameter () = {NewSqlParameter ("@ CID", ENCardinfo. CID), NewSqlParameter ("@ SID", ENStudentinfo. SID), NewSqlParameter ("@ ReturnCash", ENReturnCardinfo. returnCash), NewSqlParameter ("@ UID", ENReturnCardinfo. UID), NewSqlParameter ("@ Rdate", ENReturnCardinfo. rdate), NewSqlParameter ("@ Rtime", ENReturnCardinfo. rtime)} 'Note the CommandType here. storedProcedure ReturnclsSqlHelper. execAddDelUpdate (strSql, CommandType. storedProcedure, sqlParams) End Function </span>

 

If there is no stored procedure, you need to write five functions: the function for deleting card information, the function for deleting student information, and the function for deleting student information.

Functions recorded by superiors, functions for deleting recharge records, and functions for adding back-to-card records. Then, they are called one by one, not only with a large number of codes, but also with messy relationships.

With stored procedures, you only need to write one function to complete the work of five functions, simplifying the code logic.

The stored procedure and trigger are actually encapsulated into the database with SQL statements. The trigger can do the same thing,

The stored procedure should be used first.

 

 

Reference: http://www.knowsky.com/2951.html