Data room Charging System 3 STORAGE PROCESS

I. Definition

Official definition: In a large database system, a set of SQL statements for specific functions are compiled and stored in the database, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters.

Personal Understanding: The encapsulation of SQL statements for complex database operations, such as update, insert, query, and delete operations on multiple tables.

Ii. Basic Syntax: 1. Create a stored procedure

Basic Syntax: Create procedurepro_name ()

Begin

.........

End

Note: The process name should start with proc _ or proc _ whenever possible. We do not recommend that you use SP _ as the prefix, because the stored procedures of SQL Server System start with SP, in this way, the system itself will be searched first to reduce the query speed.

2. Call the Stored Procedure

Basic Syntax: callpro_name ()

Note: The stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters

3. delete a stored procedure

Basic Syntax:

Drop procedurepro_name //

Note: You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.

Now that we know about the basic stored procedure, we use the creation and use of the stored procedure in the charging system of the data center. Here is an example.

Iii. Example-refund of the IDC Charging System

1. First, create a stored procedure: Find the programmability in the database you have created-right-click the stored procedure-create a stored procedure,

2. Add the corresponding code in the stored procedure:

-- ===================================================== ====== -- Author: song Xiao -- create Date: July 22, June 29, 2014 -- Description: return the card. Delete the card_info and student_info records of the corresponding card number, add retundcard_info -- ======================================== ========= create procedure [DBO]. [proc_retundcard] -- add the parameter @ cardno varchar (20), -- the card number @ stuno varchar (20), -- the student ID of the person who returned the card @ userno varchar (20 ), -- the number of the person who returned the card @ onworkname varchar (20), -- the person who returned the card @ retunddate varchar (20), -- date of return @ retundtime varchar (20 ), -- card return time @ retundcash varchar (20), -- refund amount @ retundname varchar (20) -- card return person asbeginset nocount on; -- add the involved SQL statement Delete card_info where cardno [email protected] -- delete the Table Record of the corresponding card number Delete student_info where stuno [email protected] -- delete the student record of the corresponding card number insert into cancelcard_info (cardno, userno, onworkname, response, retundtime, retundcash, retundname) values (@ cardno, @ userno, @ onworkname, @ override, @ retundtime, @ retundcash, @ retundname) -- add an endgo record

3. Application Stored Procedure (layer D)

**************************************** * ******** 'File Name: sqlservercancelcarddal 'namespace: Dal 'internal volume: 'function:' file relation: 'OPERATOR: Song xiao' group: xx' generation Date: 21:12:19' version No: v1.0.0.0 'modify log: 'copyright description: '*************************************** * ******** imports entityimports idalimports sqlhelperimports system. data. sqlclientpublic class sqlservercancelcarddal: Implements icancelcard 'defines and instantiates the sqlhelper class private clssqlhelper as sqlhelpe R. sqlhelper = new sqlhelper. sqlhelper public function returncard (encard as cardentity, enstudent as studententity, encancelcard as cancelcardentity) as integer implements icancelcard. returncard defines and instantiates the parameter array to prevent SQL injection into dim paras as sqlparameter () = {New sqlparameter ("@ cardno", encancelcard. cardno), new sqlparameter ("@ stuno", enstudent. studentno), new sqlparameter ("@ userno", encancelcard. userno), new SQ Lparameter ("@ onworkname", encancelcard. onworkname), new sqlparameter ("@ retunrddate", encancelcard. retunddate), new sqlparameter ("@ returntime", encancelcard. retundtime), new sqlparameter ("@ returncash", encancelcard. retundcash), new sqlparameter ("returnname", encancelcard. retundname)} 'defines and instantiates string variables, the SQL statement dim strsql as string = "proc_cancelcard" 'Here strsql does not store a single SQL statement but a stored procedure 'commandtype here. text to text With commandtype. storedprocedure, the execution is not a statement but a stored procedure. Return clssqlhelper. execadddelupdate (strsql, commandtype. storedprocedure, paras) end functionend class

Note: The idal layer corresponds to the D layer, and the function parameters are related to the applied entities. Several entities are applied, and the parameters are added. I mainly use card, student, and card return information entities, so there are three parameters.

The stored procedure is used to make the code simpler. You do not need to write multiple functions to complete a function and effectively use encapsulation.