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 mysqlusetest; Databasechanged -- create an example Table mysqlcreatetablezzm (

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 usetest; Databasechanged -- create an example using the table mysql createtablezzm (-idintprimarykeyauto_increment,-nameva

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

1. mysql> use test;  

2. Database changed

-- Create an example table

1. mysql> create table zzm(  

2. -> Id int primary key auto_increment,
3. -> Name varchar (10)
4. -> );
5. Query OK, 0 rows affected (0.20 sec)
6. Mysql> insert into zzm (name) values ('zhang ');
7. Query OK, 1 row affected (0.08 sec)
8. Mysql> insert into zzm (name) values ('zeng ');
9. Query OK, 1 row affected (0.05 sec)
10. Mysql> insert into zzm (name) values ('ming ');
11. Query OK, 1 row affected (0.05 sec)
12. Mysql> select * from zzm;
13. + ---- + ------- +
14. | Id | name |
15. + ---- + ------- +
16. | 1 | zhang |
17. | 2 | zeng |
18. | 3 | ming |
19. + ---- + ------- +
20. 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 a MySQL Stored Procedure p3 -- the stored procedure name is p3, which contains two parameters, -- one is the input type (marked with IN), the parameter name is nameid, the type is int, the other is the output type (marked with OUT), and the parameter name is person_name, type: varchar (10) -- this stored procedure is used to query all the contents of the zzm table and output the result set ), then -- query the field name of the Record ID in the table and output it to the second output type parameter. This query will not output the result set.

1. mysql> create procedure p3(IN nameid int, OUT person_name varchar(10))  

2. -> Begin
3. -> Select * from test. zzm;
4. -> Select zzm. name into person_name from test. zzm where zzm. id = nameid;
5. -> End
6. -> $
7. Query OK, 0 rows affected (0.00 sec)