How to call stored procedure in mysql

In mysql, you can use the CALL statement to call a stored procedure. This statement receives the name of the stored procedure and any parameters that need to be passed to it. The syntax is "CALL sp_name([parameter[...]]) ;".

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

Calling stored procedures

The CALL statement is used in MySQL to call stored procedures. When a stored procedure is called, the database system executes the SQL statements in the stored procedure and returns the results as output values.

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[...]]);

Among them, sp_name represents the name of the stored procedure, and parameter represents the parameters of the stored procedure.

Example 1:

Create a stored procedure

• Create a stored procedure named ShowStuScore. The function of the stored procedure is to Query the student's grade information in the student grade 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, and the input parameter is the student’s name. The function of the stored procedure is to query the grade information of the specified student from the student grade 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)

Calling the stored procedure

• Call the 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 the stored procedure is actually a function, there needs to be a () symbol after the stored procedure name, even if no parameters are passed.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to call stored procedure in mysql. For more information, please follow other related articles on the PHP Chinese website!