MySQL Stored Procedure usage and syntax

1. What is a stored procedure?

A set of SQL statement sets for specific functions, which are compiled and stored in the database. You can specify the name of a stored procedure and provide parameters (if the stored procedure has parameters) to execute it.

 

2. Advantages of stored procedures:

1. Faster System Operation

2. stored procedures are reusable components

3. stored procedures can be saved

4. The stored procedure can be transplanted.

 

 

3. Stored Procedure Syntax:

Create procedure stored procedure name

Begin

Compound statement

End

 

4. Write the simplest Stored Procedure

Create procedure pro1 ()

Begin

Select * from teacher;

End

This statement is used to create a stored procedure called pro1 () to query the information used by the teacher table.

 

How can we call a stored procedure?

Call pro1 ()

We can display all the information in the teacher table.

 

5. Write a stored procedure that can input parameters.

Create procedure pro2 (in a INT) // int indicates that input a is a parameter, and INT indicates the parameter type.

Begin

Set @ x =

End

That's all.

We can use classpro2 (122) when calling)

Then we can enter select @ X.

 

6. We can store output parameters.

Create procedure protest6 (out a int) // out indicates the output.

Begin

Declare B INT; // defines a local variable. Int represents the number type.

Set B = 5; // value assignment

Set a = B; // value assignment

End

 

We call the stored procedure first.

Parameters in call protest6 (@ q) can be defined at will, but not at least @

Then we write select @ Q, and we can see @ q = 5.

Create procedure protest13 ()

Begin

Declare a int default 6;

Insert into fish values ();

Select s from fish end

 

First, you must have a table named fish, and s is the field of the fish table.

 

Then we call the Stored Procedure Call protest13 () to obtain the data you want to query.