A stored Procedure is a set of SQL statements that are compiled and stored in a database to complete a specific function. The user can execute it by specifying the name of the stored Procedure and giving parameters.
Stored procedures can contain logical control statements and data manipulation statements, which can accept parameters, output parameters, return single or multiple result sets and return values.
Create stored procedure usage
delimiter

\d $$
create procedure p2()
begin
declare i int default 1;
while(i<=20000) do insert into t1 values(i,md5(i));
 set i=i+1;
  end while;
  end
  $$
  \d;
  call p1();#Call p1

in

mysql> \d $$
mysql> create procedure p3(in a int)
    -> declare i int default 1
    -> \d $$^C
mysql> \d $$
mysql> create procedure p3(in a int)
    -> begin
    -> declare i int default 1;
    -> while(i<=a)do insert into t1 values(i,md5(i));
    -> set i=i+1;
    -> end while;
    -> end
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> \d ;
mysql> call p3(4);

Query OK, 1 row affected (0.01 sec)

out

mysql> \d $$
mysql> create procedure p4(out a int)
    -> begin
    -> select count(Full name) into a from students;
    -> end;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> \d ;
mysql> select @b; #No definition of b
+------+
| @b   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> call p4(@b);#Use @ b to accept the output value
Query OK, 1 row affected (0.00 sec)

mysql> select @b;
+------+
| @b   |
+------+
|   22 |
+------+
1 row in set (0.00 sec)

Parsing @ b:

mysql> set @num=20;      ## Define a user session variable 
mysql> select @num;       ## Call a user session variable
+------+
| @num |
+------+
|   20 |

inout

mysql> delimiter $$
mysql> create procedure p5(IN p1 varchar(50), OUT p2 int)
    -> BEGIN
    ->    select count(*) into p2 from company.employee5  where post=p1;
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> \d ;


mysql> call p5('hr',@a);

mysql>select  @a;