Methods for creating stored procedures with in and out parameters in Mysql _mysql

The syntax for creating stored procedures in MySQL is hard to remember, unless you often deal with the storage process for the simple reason that grammar is not a trivial joke. If you control MySQL through the command line, you need to remember the exact syntax. A quick example can help you do that. In the MySQL Getting Started tutorial, we can see a lot of examples of how to create stored procedures and how to use in and out parameters to invoke stored procedures. These examples are simple enough to help you understand the syntax for creating a stored procedure with parameters in MySQL. These samples have been tested in MySQL 5.5. We will create and test these stored procedures with the following employee table:

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 |  |
+--------+----------+---------+--------+
4 rows in Set (0.22 sec)

Creating and using MySQL stored procedures with in parameters
here is a command-line way to create a MySQL saved example, we get a total from the employee table based on department, 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 to "//" as the identity of the end of the stored procedure, and then restore the default value. Using the USP prefix is a best practice for distinguishing between system-stored procedures and user-defined stored procedures. Now you can invoke the saved in the MySQL command line like this:

Mysql> call Usp_totalemployeebydeparment (2);
+-------+
| total |
+-------+
|   1 |
+-------+
1 row in Set (0.06 sec)

Creating and using stored procedures with in and out parameters

In this MySQL example, we created a stored procedure usp_getemployeename with an in and an out parameter. When this stored procedure is invoked, you need to pass 2 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)->
  n
  -> 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 (@name);
Query OK, 1 row affected (0.05 sec) is
invoked in MySQL command-line mode:
 
mysql> select @name;
+-------+
| @name |
+-------+
| Jack |
+-------+
1 row in Set (0.00 sec)

This is how you create and invoke all the contents of a stored procedure from the command-line, and in this tutorial we create multiple examples of stored procedures with in and out parameters. This is the best way to remember the syntax of MySQL data inventory.