Data room Charging System Reconstruction-storage process usage

From the beginning to the end of the computer room charging system, it is nearing the end. Like the fog in winter, it is becoming clearer and clearer as time passes away, this learning process is really wonderful.

Of course, it is meaningless not to sum up learning. Today, I will summarize the usage of stored procedures. In terms of user registration, it is to add users to the database to facilitate the use of more users. So what should we notice when registering users?

1. Identify whether the user already exists by using the card number.

If the user does not exist:

2. Write information into the student table.

3. Update the recharge record table

4. Update the balance in the card table.

The analysis result shows that if the user does not exist, we need to change the three tables at the same time. Of course, you can write one by one, but this is not what we want. Today there are three tables, and the next time there are thirty tables, are you writing one by one? So here we introduce the stored procedure.

1. What is a stored procedure?

Definition: store common or complex tasks in advance with SQL statements and a specified name, to enable the database to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically complete the command.

At this point, someone may ask: is the stored procedure just a bunch of SQL statements? Why does Microsoft add this technology?

What is the difference between a stored procedure and a general SQL statement?

Ii. Advantages of stored procedures:

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. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution.

2. when performing complex operations on the database (for example, performing update, insert, query, and delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used 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.

Iii. 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 the specified object.

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

Common formats

Create proceduresp_name

@ [Parameter name] [type], @ [parameter name] [type]

As

Begin

.........

End

4. Call the Stored Procedure

1. Basic Syntax: exec sp_name [parameter name]

5. delete a stored procedure

1. Basic Syntax:

Drop procedure sp_name

2. Notes

(1) You cannot delete another stored procedure in one stored procedure, but you can only call another stored procedure.

It is like what is used in registration in the data room charging system:

Create procedure [DBO]. [proc_register] @ cardno char (10), @ studentno char (10), @ name ntext, @ sex nchar (10), @ Department char (10 ), @ grade nchar (10), @ classgrade nchar (10), @ cash decimal (18, 2), @ cardstatue char (16), @ cardType char (8 ), @ chargemoney decimal (10, 4), @ chargedatetime datetime, @ username char (10), @ statue ntext, @ registerdatetime date, @ checkstatue char (10) asbegin -- insert into t_cardinfo (cardno, studentno, cardstatue, cash, cardType, registerdatetime, username, checkstatue) values (@ cardno, @ override, @ cardstatue, @ cash, @ cardType, @ registerdatetime, @ username, @ checkstatue) -- insert into t_studentinfo (studentno, name, sex, department, classgrade, statue, grade) values (@ studentno, @ name, @ sex, @ department, @ classgrade, @ statue, @ grade) -- insert into t_recharge (cardno, chargedatetime, chargemoney, username) values (@ cardno, @ chargedatetime, @ chargemoney, @ username) End

In this way, you can easily operate on three tables. Of course, the stored procedure is far more than that, and we will continue to study in the future.

Just like we need to modify three tables, when we need to do the same thing three times, we have to dare to think of a more efficient way, we don't know how many people have done this before.

Data room Charging System Reconstruction-storage process usage