Create a stored procedure with IN and OUT parameters IN MySQL _ MySQL

This article describes how to create a stored procedure with the IN and OUT parameters IN MySQL, which simplifies the operation to a certain extent, if you need it, you can refer to the syntax for creating a stored procedure in MySQL, which is hard to remember. unless you often deal with the stored procedure, the reason is simple. The syntax is not a joke. If you control MySQL through the command line, you need to remember the accurate syntax. A quick example can help you do this. IN the MySQL Getting Started Tutorial, we can see a lot of examples about how to create a stored procedure and how to use the IN and OUT parameters to call the stored procedure. These examples are simple and can help you understand the syntax for creating a stored procedure with parameters in MySQL. These examples have passed the test in MySQL 5.5. We will use the following employee table to create and test these stored procedures:

mysql> select * from employee;+--------+----------+---------+--------+| emp_id | emp_name | dept_id | salary |+--------+----------+---------+--------+|  103 | Jack   |    1 |  1400 ||  104 | John   |    2 |  1450 ||  108 | Alan   |    3 |  1150 ||  107 | Ram   |  NULL |  600 |+--------+----------+---------+--------+4 rows in set (0.22 sec)

Create and use a MySQL stored procedure with In parameters
The following is an example of creating a MySQL database using the command line method. we obtain a total number from the employee table based on department, and dept_id is the foreign key of the department table.

mysql> DELIMITER //mysql> create procedure usp_totalEmployeeByDeparment(IN id INT)  -> begin  -> select count(*) as total from employee where dept_id = id;  -> end//Query OK, 0 rows affected (0.00 sec)

Mysql> DELIMITER;
First, we change the default delimiter "//" as the identifier of the end of the stored procedure, and then restore the default value. Using the "usp" prefix is the best practice for distinguishing system stored procedures from user-defined stored procedures. Now you can call and save the following in the MySQL command line:

mysql> call usp_totalEmployeeByDeparment(2);+-------+| total |+-------+|   1 |+-------+1 row in set (0.06 sec)

Create and use stored procedures with IN and OUT parameters

IN this MySQL example, we create an IN and an OUT parameter stored procedure usp_GetEmployeeName. When calling this stored procedure, you need to pass two parameters: id and name, one as the input parameter id, and the other as the output parameter to return the result.

Mysql> DELIMITER // mysql> create procedure usp_GetEmployeeName (IN id INT, OUT name VARCHAR (20)-> begin-> select emp_name into name from employee where emp_id = id; -> end // Query OK, 0 rows affected (0.52 sec) mysql> DELIMITER; mysql> call usp_GetEmployeeName (103, @ name); Query OK, 1 row affected (0.05 sec) mySQL> select @ name; + ------- + | @ name | + ------- + | Jack | + ------- + 1 row in set (0.00 sec)

This is how to create and call all the content of a stored procedure through the command line method. IN this tutorial, we created multiple stored procedures with the IN and OUT parameters. This is the best way to remember the syntax of MySQL data inventory.