Example of creating a MySQL Stored Procedure

The following describes how to create a MySQL stored procedure and describes the detailed steps. If you have encountered any problems with the MySQL stored procedure, you may wish to take a look at it.

-- Select database

 
 

  
  
mysql> use test;  
  
  
Database changed 
 
 

-- Create an example table

 
 

  
  
mysql> create table zzm(  
  
  
-> id int primary key auto_increment,  
  
  
-> name varchar(10)  
  
  
-> );  
  
  
Query OK, 0 rows affected (0.20 sec)  
  
  
mysql> insert into zzm(name) values('zhang');  
  
  
Query OK, 1 row affected (0.08 sec)  
  
  
mysql> insert into zzm(name) values('zeng');  
  
  
Query OK, 1 row affected (0.05 sec)  
  
  
mysql> insert into zzm(name) values('ming');  
  
  
Query OK, 1 row affected (0.05 sec)  
  
  
mysql> select * from zzm;  
  
  
+----+-------+  
  
  
| id | name |  
  
  
+----+-------+  
  
  
| 1 | zhang |  
  
  
| 2 | zeng |  
  
  
| 3 | ming |  
  
  
+----+-------+  
  
  
3 rows in set (0.00 sec)  
 
 

-- Change the command Terminator because the default command Terminator -- semicolon (;) is often used in procedure (;)
-- Therefore, when creating a procedure, you need to define a new terminator to indicate that the command for creating a procedure ends)
-- Change the end symbol to the dollar sign -- $
Mysql> delimiter $
-- Create MySQL Stored Procedure p3
-- The stored procedure is named p3, which contains two parameters,
-- One is IN for the input type). The parameter name is nameid and the type is int,
-- One is the output type marked with OUT), the parameter name is person_name, And the type is varchar (10)
-- This stored procedure is used to query all the contents of the zzm table and output the result set data set). Then
-- Query the field name whose ID is recorded in the table and output it to the second output type parameter.
-- No result set is output.

 
 

  
  
mysql> create procedure p3(IN nameid int, OUT person_name varchar(10))  
  
  
-> begin  
  
  
-> select * from test.zzm;  
  
  
-> select zzm.name into person_name from test.zzm where zzm.id = nameid;  
  
  
-> end  
  
  
-> $  
  
  
Query OK, 0 rows affected (0.00 sec)  
 
 

Mysql Stored Procedure Call Method

Use of mysql ifnull Functions

MySQL date functions

Provides you with an in-depth understanding of the MYSQL Cast function.

Learn more about mysql CONCAT () Functions