How to create a stored procedure in MySQL

Here’s a concrete example:

Create a stored procedure called getRecord in the TEST database:

DELIMITER $$
DROP PROCEDURE IF EXISTS `TEST`.`getRecord` $$
CREATE PROCEDURE `TEST`.`getRecord` (
IN in_id INTEGER.OUT out_name VARCHAR(20),
OUT out_age  INTEGER)
BEGIN
   SELECT name, age
   INTO out_name, out_age
   FROM Student where id = in_id;
END $$
DELIMITER ;
Copy the code

The stored procedure takes the student ID as input and outputs the student’s name and age to implement a simple query function.

After the stored procedure is created, run the following command to query its status:

Show procedure status like ‘g%’

Another query command:

show create procedure test.getRecord

The information_schema.Routines table from MySQL is similar to the TADIR(object registry) from SAP ABAP, so we can also query the stored procedure records from the information_schema.Routines table:

Select * from information_schema.Routines WHERE routine_name = ‘procedure’ \G;

For more of Jerry’s original articles, please follow the public account “Wang Zixi “: