Database Stored Procedure introduction and instance, database Stored Procedure instance

Database Stored Procedure introduction and instance, database Stored Procedure instance

I. Differences between stored procedures and functions:

1. In general, the functions implemented by stored procedures must be more complex, while the functions implemented by functions are more targeted.

2. For stored procedures, parameters (output) can be returned, while functions can only return values or table objects.

3. A stored procedure is generally executed as an independent part, and a function can be called as a part of a query statement. Because a function can return a table object, therefore, it can be located behind the FROM keyword in the query statement.

 

Ii. Advantages of stored procedures:

1. Faster execution-stored procedure statements in the database are compiled

2. Modular programming is allowed-reuse of similar methods

3. Improve System Security-prevent SQL Injection

4. Reduce network traffic-as long as the name of the stored procedure is transmitted

 

System stored procedures generally start with sp, and user-defined stored procedures generally start with usp

 

3. Define the Stored Procedure syntax. The content in "[" indicates the option.

Create proc stored procedure name

@ Parameter 1 Data Type [= default value] [output],

@ Parameter 2 Data Type [= default value] [output],

...

As

SQL statement

 

4. A simple example

Define the stored procedure:

Create proc usp_StudentByGenderAge

@ Gender nvarchar (10) [= 'male'],

@ Age int [= 30]

As

Select * from MyStudent where FGender = @ gender and FAge = @ age

 

Execute the stored procedure:

Situation One (call the default parameter ):

Exec usp_StudentByGenderAge

Situation Two (call the specified parameter ):

Exec usp_StudentByGenderAge 'femal', 50

Or specify the variable name exec usp_StudentByGenderAge @ age = 50, @ gender = 'femal'

 

Modify the Stored Procedure

Alter proc usp_StudentByGenderAge

@ Gender nvarchar (10) [= 'male'],

@ Age int [= 30],

-- Add output to indicate that this parameter must be assigned and returned during the stored procedure.

@ RecorderCount int output

As

Select * from MyStudent where FGender = @ gender and FAge = @ age

Set @ recorderCount = (select count (*) from MyStudent where FGender = @ gender and FAge = @ age)

 

-- The purpose of the output parameter is that the caller needs to pass a variable in and assign a value to the variable in the stored procedure. After the stored procedure is completed, return the corresponding execution result to the passed variable. (Same as the out principle in C)

 

Call (remember the syntax here !) Because there are other parameters before the stored procedure, write @ recorderCount. After the stored procedure is executed, the preceding query is completed, at the same time, the number of results obtained from the query is assigned to the @ count variable. (@ Count is passed as a parameter to usp_StudentByGenderAge. After the stored procedure is executed, return the obtained number to @ count)

Declare @ count int

Exec usp_StudentByGenderAge @ recorderCount = @ count output

Print @ count

 

5. Use stored procedures to complete Paging

1. Stored Procedure Code

Create proc usp_page

@ Page int, --- How many records are displayed on a page

@ Number int, --- the page number of data selected

As

Begin

Select * from

-- The content in the parentheses is specially arranged sequence numbers.

(

Select ROW_NUMBER () over (order by (Fid) as number

From MyStudent

) As t

Where t. number >=( @ number-1) * @ page + 1 and t. number <= @ number * @ page

End

2. ADO. NET code for implementing the paging effect:

1 private void button#click (object sender, EventArgs e)
{
2 string connStr = @ "server =. \ sqlexpress; database = MyDB; integrated security = true ";
3 using (SqlConnection conn = new SqlConnection (connStr ))
4 {
5 // open the database connection
6 conn. Open ();
7 // use the stored procedure name as the object for Command Processing
8 string usp = "usp_page ";
9 using (SqlCommand cmd = new SqlCommand (usp, conn ))
10 {
11 // The Stored Procedure statement is executed.
12 cmd. CommandType = CommandType. StoredProcedure;
// TextBox1.Text indicates how many records are displayed.
13 cmd. Parameters. AddWithValue ("@ page", textBox1.Text. Trim ());
14 // textBox. Text indicates the page on which the user selects
15 cmd. Parameters. AddWithValue ("@ number", textBox2.Text. Trim ());
16 // use list as the data source
17 List <Person> p = new List <Person> ();
18 using (SqlDataReader reader = cmd. ExecuteReader ())
19 {
20 if (reader. HasRows)
21 {
22 while (reader. Read ())
24 {
25 Person p1 = new Person ();
26 p1.FName = reader. GetString (1 );
27 p1.FAge = reader. GetInt32 (2 );
28 p1.FGender = reader. GetString (3 );
29 p1.FMath = reader. GetInt32 (4 );
30 p1.FEnglish = reader. GetInt32 (5 );
31 p. Add (p1 );
32}
33}
34}
35 dataGridView1.DataSource = p;
36}
37}
38}

The following is a custom Person class.

1 class Person 
2 {
3 public string FName { get; set; }
4 public int FAge { get; set; }
5 public string FGender { get; set; }
6 public int FMath { get; set; }
7 public int FEnglish { get; set; }
8 }