Detailed explanation of creating and calling MySQL stored procedures

Preface

Stored Procedure:

A set of programmable functions is a set of SQL statements to complete specific functions. They are compiled, created and saved in the database. Users can call and execute them by specifying the name of the stored procedure and giving parameters (when necessary).

Advantages (why use stored procedures?):

1. Encapsulate some highly repetitive operations into a stored procedure to simplify the calls to these SQLs

2. Batch processing: SQL + loop, reduce traffic, also known as "running batches"

3. Unified interface to ensure data security

Compared with Oracle database, MySQL stored procedures are relatively weaker and less used.

1. Creating and calling 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 database data dictionary.

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
Characteristics:
    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 games in which a given player has played

mysql> delimiter $$ #Temporarily change the end symbol of the statement from semicolon; to two $$ (can be customized)
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 ; #Restore the end symbol of the statement to a semicolon

Analysis:

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

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

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

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:

The variable p_playerno that needs to be passed as a parameter is set in the stored procedure. When the stored procedure is called, 57 is assigned to p_playerno through parameter passing, and then the SQL operation in the stored procedure is performed.

3. Stored procedure body

• The stored procedure body contains statements that must be executed when the procedure is called, such as DML and 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 nested block and each statement in it must end with a semicolon. The begin-end block (also called a compound statement) that indicates the end of the procedure body does not require a semicolon.

4. Label the statement blocks

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

For example:

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

Tags have two functions:

• Improve code readability
• In some statements (for example, leave and iterate statements), labels are required.

2. Parameters of stored procedures

A stored procedure can have zero or more parameters, which are used in the definition of the stored procedure.

3 parameter types:

• IN input parameter: indicates that the caller passes a value to the procedure (the passed value can be a literal or a variable)
• OUT output parameter: indicates that the procedure passes a value to the caller (can return multiple values) (the output value can only be a variable)
• INOUT input and output parameters: It indicates that the caller passes a value to the procedure, and the procedure passes 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 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 |
+-------+

#From the above, we can see that p_in is modified in the stored procedure, but it does not affect the value of @p_id, 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 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 |
+-------+
  #Because out is to output parameters to the caller and does not receive input parameters, p_out in the stored procedure is null
+-------+
| p_out |
+-------+
| 2 |
+-------+
mysql> select @p_out;
+--------+
| @p_out |
+--------+
| 2 |
+--------+
  #Call the out_param stored procedure, output the parameters, and change the value of the p_out variable

3. inout input parameters

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 |
+----------+

#Called the inout_param stored procedure, accepted the input parameters, and also output the parameters, changing the variables

Notice:

1 If the procedure has no parameters, you must also write parentheses after the procedure name.

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

2. Make sure the parameter name is not equal to the column name, otherwise the parameter name is treated as the column name in the procedure body.

Wall crack suggestion:

• Input values ​​use in parameters;
• The return value uses the out parameter;
• Try to use inout parameters as little as possible.

Summarize

This article ends here. I hope it can be helpful to you. I also hope you can pay more attention to more content on 123WORDPRESS.COM!