Mysql stored procedure study NOTE _ MySQL

Mysql stored procedure study notes bitsCN.com

Parameters

Mysql Stored Procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, and INOUT.

Create procedure | function ([[IN | OUT | INOUT] parameter name data class…])

IN input parameters

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 parameters

This value can be changed within the stored procedure and can be returned

INOUT input and output parameters

Can be changed and returned.

IN parameter example:

Create

Mysql> create procedure sp_demo_in_parameter (IN p_in INT) begin select p_in;/* query input parameter */SET p_in = 2;/* modify */select p_in; /* view the modified value */END;

Execution result:

Mysql> set @ p_in = 1

Mysql> call sp_demo_in_parameter (@ p_in)

+ -- +

| P_in |

+ -- +

| 1 |

+ -- +

+ -- +

| P_in |

+ -- +

| 2 |

+ -- +

Mysql> select @ p_in;

+ --- +

| @ P_in |

+ --- +

| 1 |

+ --- +

As shown above, although p_in is modified in the stored procedure, it does not affect the value of @ p_id.

OUT parameter example

Create:

Mysql> create procedure sp_demo_out_parameter (OUT p_out INT) begin select p_out;/* view the output parameter */SET p_out = 2;/* modify the parameter value */SELECT p_out; /* check whether there are changes */END;

Execution result:

Mysql> SET @ p_out = 1

Mysql> CALL sp_demo_out_parameter (@ p_out)

+ --- +

| P_out |

+ --- +

| NULL |

+ --- +

/* Not defined, return NULL */

+ --- +

| P_out |

+ --- +

| 2 |

+ --- +

Mysql> SELECT @ p_out;

+ --- +

| P_out |

+ --- +

| 2 |

+ ---- +

Example of INOUT parameter:

Mysql> create procedure sp_demo_inout_parameter (INOUT p_inout INT) begin select p_inout; SET p_inout = 2; SELECT p_inout; END;

Execution result:

Set @ p_inout = 1

Call sp_demo_inout_parameter (@ p_inout )//

+ --- +

| P_inout |

+ --- +

| 1 |

+ --- +

+ --- +

| P_inout |

+ --- +

| 2 |

+ --- +

Select @ p_inout;

+ ---- +

| @ P_inout |

+ ---- +

| 2 |

+ ---- +

User variable

1. use user variables on the mysql client

Mysql> SELECT 'Hello world' into @ x;

Mysql> SELECT @ x;

+ ----- +

| @ X |

+ ----- +

| Hello World |

+ ----- +

Mysql> SET @ y = 'Goodbye Cruel World ';

Mysql> select @ y;

+ ------- +

| @ Y |

+ ------- +

| Goodbye Cruel World |

+ ------- +

Mysql> SET @ z = 1 + 2 + 3;

Mysql> select @ z;

+ -- +

| @ Z |

+ -- +

| 6 |

+ -- +

2. use user variables in stored procedures

Mysql> create procedure GreetWorld () select concat (@ greeting, 'World ');

Mysql> SET @ greeting = 'hello ';

Mysql> CALL GreetWorld ();

+ ---------- +

| CONCAT (@ greeting, 'World') |

+ ---------- +

| Hello World |

+ ---------- +

3. pass Global User variables between stored procedures

Mysql> create procedure p1 () SET @ last_procedure = 'p1 ′;

Mysql> create procedure p2 () select concat ('last procedure was', @ last_procedure );

Mysql> CALL p1 ();

Mysql> CALL p2 ();

+ ---------------- +

| CONCAT ('last procedure was', @ last_procedure) |

+ ---------------- +

| Last procedure was p1 |

+ ---------------- +

Note:

① User variable names generally start @

② Misuse of user variables will make the program hard to understand and manage

Note

Mysql stored procedures can be annotated using two styles

Dual-mode bar :-

This style is generally used for single-line comments.

C style:/* comment content */generally used for multi-line comment

BitsCN.com