MySQL Stored Procedure

 

MySQL stored procedure:

MySQL Stored Procedure

 

Stored procedures are an important function of database storage. However, MySQL did not support stored procedures before MySQL 5.0, which greatly compromises the application of MySQL. Fortunately, MySQL 5.0 has finally started to support stored procedures, which can greatly improve the processing speed of the database and the flexibility of database programming.

 

Format

 

MySQL stored PROCEDURE creation format: CREATE procedure process name ([process parameter [,...]) PROCEDURE

[Features...] process body

 

Here is an example:

 

1. mysql> DELIMITER //

 

2. mysql> create procedure proc1 (OUT s int)

 

3.-> BEGIN

 

4.-> select count (*) INTO s FROM user;

 

5.-> END

 

6.-> //

 

7. mysql> DELIMITER;

 

Parameters

 

MySQL stored procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, And INOUT. The format is as follows:

 

Create procedure ([[IN | OUT | INOUT] Parameter Name Data class...])

 

IN input parameter: indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter IN the stored procedure cannot be returned, which is the default value.

 

OUT output parameter: The value can be changed within the stored procedure and can be returned.

 

INOUT input and output parameters: this parameter is specified during the call and can be changed or returned.

 

. IN parameter example

 

Create:

 

1. mysql> DELIMITER //

 

2. mysql> create procedure demo_in_parameter (IN p_in int)

 

3.-> BEGIN

 

4.-> SELECT p_in;/* query input parameters */

 

5.-> SET p_in = 2;/* modify */

 

6.-> SELECT p_in;/* view the modified value */

 

7.-> END;

 

8.-> //

 

Mysql> DELIMITER;

 

OUT parameter example

 

Create:

 

1. mysql> DELIMITER //

 

2. mysql> create procedure demo_out_parameter (OUT p_out int)

 

3.-> BEGIN

 

4.-> SELECT p_out;/* view output parameters */

 

5.-> SET p_out = 2;/* modify the parameter value */

 

6.-> SELECT p_out;/* check for any changes */

 

7.-> END;

 

8.-> //

 

9. mysql> DELIMITER;

 

Example of the. INOUT Parameter

 

Create:

 

1. mysql> DELIMITER //

 

2. mysql> create procedure demo_inout_parameter (INOUT p_inout int)

 

3.-> BEGIN

 

4.-> SELECT p_inout;

 

5.-> SET p_inout = 2;

 

6.-> SELECT p_inout;

 

7.-> END;

 

8.-> //

 

9. mysql> DELIMITER;

 

Note

 

 

 

MySQL stored procedures can be annotated using two styles

 

Dual-mode bar :--

 

This style is generally used for single-line comments.

 

Style:/* Comment content */generally used for multi-line comment

From chizhidan_luck