What are the parameter types of mysql stored procedures?

There are three parameter types for mysql stored procedures: 1. Input parameters, identified by the "IN" keyword, can be passed to a stored procedure; 2. Output parameters, identified by the "OUT" keyword, used for The situation where the stored procedure needs to return an operation result; 3. Input and output parameters are identified with the "INOUT" keyword.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Stored Procedure:

A set of programmable functions, a set of SQL statements that are compiled to complete specific functions. Created and saved in the database, users can call and execute it by specifying the name of the stored procedure and giving parameters (when needed).

Advantages (Why use stored procedures?):

① Encapsulate some highly repetitive operations into a stored procedure, simplifying the calls to these SQL

　②Batch processing: SQL loop to reduce traffic, that is, "running batches"

　③Unified interface to ensure data security

Compared to the Oracle database, MySQL's stored procedures are relatively Less powerful and less used.

1. Creation and calling of stored procedures

　>A stored procedure is a piece of code with a name, used to complete a specific function .

　>The created stored procedure is saved in the data dictionary of the database.

1. Create a stored procedure

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

#Create a database and back up data tables for example operations

mysql> create database db1;
mysql> use db1;    
mysql> create table PLAYERS as select * from TENNIS.PLAYERS;
mysql> create table MATCHES  as select * from TENNIS.MATCHES;

Example: Create a stored procedure and delete it All games played by a given player

mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    -> 　　DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;　　#将语句的结束符号恢复为分号

Analysis:

By default, the stored procedure is associated with the default database. If you want to specify that the stored procedure is created under a specific database, then in Prefix the procedure name with the database name;

When defining the procedure, use the DELIMITER $$ command to temporarily change the end symbol of the statement from a semicolon; to two $$, so that the semicolon used in the procedure body is passed directly to the server without being interpreted by the client (such as mysql).

2. Call the stored procedure: call sp_name[(parameter passing)];

mysql> select * from MATCHES;
+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       7 |      1 |       57 |   3 |    0 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+
5 rows in set (0.00 sec)

mysql> call delete_matches(57);
Query OK, 1 row affected (0.03 sec)

mysql> select * from MATCHES;
+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+
4 rows in set (0.00 sec)

Analysis:

Set the parameters that need to be passed in the stored procedure Parameter variable p_playerno, when calling the stored procedure, assign 57 to p_playerno by passing the parameter, and then perform the SQL operation in the stored procedure.

3. Stored procedure body

　>The stored procedure body contains statements that must be executed when the procedure is called, such as: dml, ddl statements, if-then- else and while-do statements, declare statements for declaring variables, etc.

　>Procedure body format: starts with begin and ends with end (can be nested)

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

Note: Each nesting The block and each statement in it must end with a semicolon. The begin-end block (also called compound statement) that indicates the end of the procedure body does not require a semicolon.

4. Label the statement block

[begin_label:] BEGIN
　　[statement_list]
END [end_label]

For example:

label1: BEGIN
　　label2: BEGIN
　　　　label3: BEGIN
　　　　　　statements; 
　　　　END label3 ;
　　END label2;
END label1

The label has two functions:

　① Enhance the code Readability

　②In some statements (for example: leave and iterate statements), you need to use the label

2. Parameters of the stored procedure

Stored procedures can have 0 or more parameters, which are used for the definition of stored procedures.

MySQL stored procedures support three types of parameters, namely input parameters, output parameters and input/output parameters, which are identified by the three keywords IN, OUT and INOUT respectively. Among them, input parameters can be passed to a stored procedure, output parameters are used when the stored procedure needs to return an operation result, and input/output parameters can serve as both input parameters and output parameters.

3 parameter types:

• IN input parameter: indicates that the caller passes a value to the process (the passed value can be Literal or variable)

• OUT output parameters: Indicates that the process passes a value to the caller (can return multiple values) (the outgoing value can only be a variable)

• INOUT input and output parameters: It not only means that the caller passes a value to the process, but also means that the process passes a value to the caller (the value can only be a variable)

1. in input parameter

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;

mysql> set @p_in=1;

mysql> call in_param(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+

+------+
| P_in |
+------+
|    2 |
+------+

mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+
#以上可以看出，p_in在存储过程中被修改，但并不影响@p_id的值，因为前者为局部变量、后者为全局变量。

2. out output parameter

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;

mysql> set @p_out=1;

mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
　　#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
|     2 |
+-------+

mysql> select @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+
　　#调用了out_param存储过程，输出参数，改变了p_out变量的值

3. inout input parameter

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+

mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

Note:

① If the process has no parameters, you must also write parentheses after the process name

Example: CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……

　②Make sure that the name of the parameter is not equal to the name of the column, otherwise in the procedure body, the parameter name will be treated as a column name

[Related recommendations: mysql video tutorial】

The above is the detailed content of What are the parameter types of mysql stored procedures?. For more information, please follow other related articles on the PHP Chinese website!