How to call stored procedures in mysql

This article mainly explains "how to call stored procedures in mysql". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "how to call stored procedures in mysql" together!

In mysql, a stored procedure can be invoked using the CALL statement, which takes the name of the stored procedure and any parameters that need to be passed to it, with the syntax "CALL sp_name([parameter[...]); "。

Operating environment of this tutorial: Windows 7 system, MySQL8 version, Dell G3 computer.

call a stored procedure

MySQL uses CALL statements to invoke stored procedures. When a stored procedure is invoked, the database system executes the SQL statement in the stored procedure and returns the result to the output value.

The CALL statement receives the name of the stored procedure and any parameters that need to be passed to it. The basic syntax is as follows:

CALL sp_name([parameter[...]]);

where sp_name represents the name of the stored procedure and parameter represents the parameter of the stored procedure.

Example 1:

create a stored procedure

Create a stored procedure named ShowStuScore, whose purpose is to query student score information from the student score information table

mysql> DELIMITER //mysql> CREATE PROCEDURE ShowStuScore() -> BEGIN -> SELECT * FROM tb_students_score; -> END //Query OK， 0 rows affected (0.09 sec)

The result shows that the ShowStuScore stored procedure has been created successfully.

Create a stored procedure named GetScoreByStu with the input parameter student name. The function of the storage process is to query the score information of the specified student from the student score information table by entering the student name

mysql> DELIMITER //mysql> CREATE PROCEDURE GetScoreByStu -> (IN name VARCHAR(30)) -> BEGIN -> SELECT student_score FROM tb_students_score -> WHERE student_name=name; -> END //Query OK, 0 rows affected (0.01 sec)

call a stored procedure

Call stored procedures named ShowStuScore and GetScoreByStu

mysql> DELIMITER ;mysql> CALL ShowStuScore();+--------------+---------------+| student_name | student_score |+--------------+---------------+| Dany | 90 || Green | 99 || Henry | 95 || Jane | 98 || Jim | 88 || John | 94 || Lily | 100 || Susan | 96 || Thomas | 93 || Tom | 89 |+--------------+---------------+10 rows in set (0.00 sec)Query OK, 0 rows affected (0.02 sec)mysql> CALL GetScoreByStu('Green');+---------------+| student_score |+---------------+| 99 |+---------------+1 row in set (0.03 sec)Query OK, 0 rows affected (0.03 sec)

Because a stored procedure is actually a function, the ( ) sign is required after the stored procedure name, even if no parameters are passed.

Thank you for reading, the above is "how to call stored procedures in mysql" content, after the study of this article, I believe we have a deeper understanding of how to call stored procedures in mysql, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!