[IDC reconstruction]-the storage process is a matchmaker, and the reconstruction of the storage process matchmaker

[IDC reconstruction]-the storage process is a matchmaker, and the reconstruction of the storage process matchmaker

The following is what I have seen with my own eyes: Data Center reconstruction handsome guy (Reconstruction for short). I will share with you the experience of the storage process matchmaker (storage for short) Communicating with the database girl (database for short.

It was a windy day, And the handsome guy was walking alone on the campus in the first summer of the city. He had been thinking about how to interact more easily with the database beauty? In the past, when looking for a database beauty, you always had to make a call (link to a table) first, and then ask where the database beauty is (then link to a table ), then he can find the database beauty (store data in two tables). After restructuring, he finally came up with a way to locate the database beauty through the GPS (Stored Procedure) in his mobile phone, then suddenly I was pleasantly surprised by the beauty of the database. They finally walked together.

I. Advantages of storage matchmaker:

1. The Stored Procedure capability greatly enhances the functionality and flexibility of the SQL language.
2. Data security and integrity can be ensured.
3. Before running the stored procedure, the database has analyzed its syntax and provided an optimization execution plan.
4. reduces network traffic.
5. Put the computing program that embodies the enterprise rules under centralized control on the database server. When the enterprise rules change, you can change the stored procedure on the server without modifying any applications.

Ii. disadvantages of storage matchmaker:

1. debugging is troublesome.
2. Porting problems.
3. Re-compile the problem.
4. A large amount of use and maintenance troubles.

Iii. Example:

The following describes how to reconstruct the stored procedure in registration. The basic principle is to first write all the fields in the involved table (to facilitate layer D calls ), then, each table is independent and fields are written. Finally, you can determine whether to store information to the three tables at the same time, either not saved or saved.

<Span style = "font-family: SimSun; font-size: 24px; "> -- ============================================ =========-- Author: <Ma Kang> -- Create date: <2015-4-26> -- Description: <Stored Procedure + transaction implementation student registration card number, tables related to Card_Info, Student_Info, and ReCharge_Info> -- ========================== ================ ALTER procedure [dbo]. [PROC_AddCard] @ CardID char (10), @ Cash char (10), @ Status char (10), @ Creator char (10), @ StudentID char (10 ), @ StudentName char (10), @ Sex char (10), @ Department char (10), @ Grade char (10), @ IClass char (10 ), @ OnDate char (10), @ OnTime char (10), @ Explain char (10), @ AddCash char (10), @ IDate char (10 ), @ Time char (10) ASBEGINdeclare @ Err1 int, @ Err2 int, @ Err3 intbegin transactionINSERT into Card_Info (CardID, Cash, Status, Creator) VALUES (@ CardID, @ Cash, @ Status, @ Creator) set @ Err1 = @ ERRORINSERT into Student_Info (StudentID, CardID, StudentName, Sex, Department, Grade, IClass, OnDate, OnTime, Explain, Status, Creator) VALUES (@ StudentID, @ CardID, @ StudentName, @ Sex, @ Department, @ Grade, @ IClass, @ OnDate, @ OnTime, @ Explain, @ Status, @ Creator) set @ Err2 =errorinsert into ReCharge_Info (CardID, StudentID, AddCash, IDate, Time, Creator, Status) VALUES (@ CardID, @ StudentID, @ AddCash, @ IDate, @ Time, @ Creator, @ Status) set @ Err3 = @ ERRORif @ Err1 = 0 and @ Err2 = 0 and @ Err3 = 0 COMMIT TRANSACTIONelse ROLLBACK TRANSACTIONend </span>

Note: 1. the ALTER statement in the first line is modified. If you CREATE an ALTER statement, change it to CREATE.

2. If the table has an auto-increment Id field, you do not need to enter it here.

3. attributes in the program entity layer must be consistent with those in the table.

The following code is modified in layer D:

<Span style = "font-family: SimSun; font-size: 24px;"> Dim SQL As String = "PROC_AddCard" 'stored procedure name Dim sqlparams As SqlParameter () = {New SqlParameter ("@ StudentID", student. studentID), New SqlParameter ("@ StudentName", student. studentName), New SqlParameter ("@ Sex", student. sex), New SqlParameter ("@ Department", student. department), New SqlParameter ("@ Grade", student. grade), New SqlParameter ("@ IClass", student. IClass), New SqlParameter ("@ OnDate", student. onDate), New SqlParameter ("@ OnTime", student. onTime), New SqlParameter ("@ Explain", student. explain), New SqlParameter ("@ Status", student. status), New SqlParameter ("@ Creator", student. creator), New SqlParameter ("@ CardID", card. cardID), New SqlParameter ("@ Cash", card. cash), New SqlParameter ("@ AddCash", ReCharge. addCash), New SqlParameter ("@ IDate", ReCharge. IDate), New SqlParameter ("@ Time", ReCharge. time)} </span>

Iv. Summary:

I recently learned the stored procedure, which made it very convenient for me to build a data center charging system. It also gave me a better understanding of the power of SQL Server, it increases your desire for knowledge and strong confidence to go on. I hope these positive energy can be passed on to all readers.

If you have any errors, please confirm your message.