Shulou(Shulou.com)05/31 Report--

This article will explain in detail what the parameter types of mysql stored procedures are, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

There are three types of parameters 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 in cases where the stored procedure needs to return an operation result; 3, input and output parameters, identified by the "INOUT" keyword.

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

Stored procedure (Stored Procedure):

A set of programmable functions to complete a set of SQL statements for a specific function, compiled and created and saved in the database, which can be called and executed by the user by specifying the name of the stored procedure and giving parameters (when needed).

Advantages (why use stored procedures?) :

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

② batch processing: SQL+ cycle to reduce traffic, that is, "run batch"

③ unified interface to ensure the security of data

Compared with oracle database, the stored procedure of MySQL is relatively weak and less used.

I. creation and invocation of stored procedures

A stored procedure is a piece of code with a name that is used to perform 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_bodyproc_parameter: [IN | OUT | INOUT] param_name typecharacteristic: 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 the data table 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 that deletes all matches played by a given player

Mysql > delimiter $$# change the closing symbol of the statement from the semicolon; temporarily change to two $(which can be custom) mysql > CREATE PROCEDURE delete_matches (IN p_playerno INTEGER)-> BEGIN-> DELETE FROM MATCHES-> WHERE playerno = paired playerno;-> END$$Query OK, 0 rows affected (0.01 sec) mysql > delimiter; # restore the closing symbol of the statement to the semicolon

Parsing:

By default, stored procedures are associated with the default database. If you want to specify that the stored procedure is created under a specific database, prefix the procedure name with the database name.

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

2. Call stored procedure: call sp_name [(passing parameters)]

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 | 3 | +-+ 4 rows in set (0.00 sec)

Parsing:

The variable p_playerno that needs to pass parameters is set in the stored procedure. When the stored procedure is called, 57 is assigned to p_playerno by passing parameters, and then the SQL operation in the stored procedure is carried out.

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 that declare variables, etc.

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

BEGIN statements; END ENDEND

Note: each nested block and each statement in it must end with a semicolon, indicating the begin-end block at the end of the procedure body (also known as the compound statement compound statement) without 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

Labels serve two purposes:

① enhances the readability of the code

② requires tags in some statements (such as leave and iterate statements)

Parameters of the stored procedure

A stored procedure can have 0 or more parameters for the definition of the stored procedure.

MySQL stored procedures support three types of parameters, namely, input parameters, output parameters, and input / output parameters, identified by the IN, OUT, and INOUT keywords, respectively. Among them, the input parameter can be passed to a stored procedure, the output parameter is used in the case where the stored procedure needs to return an operation result, and the input / output parameter can act as both input parameter and output parameter.

There are 3 types of parameters:

IN input parameter: indicates that the caller passes a value to the procedure (the input value can be a literal or a variable)

OUT output parameter: indicates that the procedure sends a value to the caller (multiple values can be returned) (outgoing values can only be variables)

INOUT input and output parameters: indicates that the caller passes a value to the procedure and that the procedure sends a value to the caller (the value can only be a variable)

1. In input parameters

Mysql > delimiter $$mysql > create procedure in_param (in p_in int)-> begin-> select pairing;-> set pairing 2;-> select pairing;-> end$$mysql > delimiter; mysql > set @ pendant 1 * MySQL > call in_param (@ p_in) +-+ | p_in | +-+ | 1 | +-+-+ | P_in | +-+ | 2 | +-+ mysql > select @ p_in +-+ | @ p_in | +-+ | 1 | +-+ # you can see that p_in is modified in the stored procedure, but the value of @ p_id is not affected, because the former is a local variable and the latter is a global variable.

2. Out output parameters

Mysql > delimiter / / mysql > create procedure out_param (out p_out int)-> begin-> select paired outbound;-> set paired outbound 2;-> select paired outout;-> end-> / / mysql > delimiter; mysql > set @ paired outbound 1: MySQL > call out_param (@ p_out) +-+ | p_out | +-+ | NULL | +-# because out outputs parameters to the caller and does not receive input parameters, the p_out in the stored procedure is null+-+ | p_out | +-+ | 2 | +-+ mysql > select @ p_out +-+ | @ p_out | +-+ | 2 | +-+ # called the out_param stored procedure, output parameters, and changed the value of the p_out variable

3. Inout input parameters

Mysql > delimiter $$mysql > create procedure inout_param (inout p_inout int)-> begin-> select paired inoutout;-> select paired inoutout 2;-> end-> $mysql > delimiter; mysql > set @ paired inoutoutout1 MySQL > call inout_param (@ p_inout) +-+ | p_inout | +-+ | 1 | +-+-+ | p_inout | +-+ | 2 | +-+ mysql > select @ p_inout +-+ | @ p_inout | +-+ | 2 | +-+ # called the inout_param stored procedure, accepting the input parameters, but also outputting the parameters, changing the variables

Note:

① if the procedure has no parameters, it must also write parentheses after the procedure name

CREATE PROCEDURE sp_name ([proc_parameter [,...]])...

② ensures that the name of the parameter is not equal to the name of the column, otherwise the parameter name is treated as a column name in the process body

The specific parameter types of mysql stored procedures are shared here. I hope the above content can be helpful to you and you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.