1. 创建存储过程

mysql> 
use sample2; 

Database changed 

mysql> 
select * from t1; 

+------+ 

| id   | 

+------+ 

|  100 | 

|  200 | 

+------+ 

2 rows in set (0.03 sec) 


mysql> 
 DELIMITER $$ 

mysql> 
CREATE PROCEDURE proce01() 

    -> 
 READS SQL DATA 

    ->  
BEGIN 

    ->  
select * from t1; 

    -> 
END $$ 

Query OK, 0 rows affected (0.02 sec) 


mysql> 
DELIMITER ; 

mysql> 
 call proce01(); 

+------+ 

| id   | 

+------+ 

|  100 | 

|  200 | 

+------+ 

2 rows in set (0.00 sec) 


Query OK, 0 rows affected (0.01 sec)

2. 查看存储过程信息，包括定义：

mysql> 
 show procedure status like 'proce01' \G 

*************************** 1. row *************************** 

                  Db: sample2 

                Name: proce01 

                Type: PROCEDURE 

             Definer: root@localhost 

            Modified: 2018-05-31 15:26:07 

             Created: 2018-05-31 15:26:07 

       Security_type: DEFINER 

             Comment: 

character_set_client: gbk 

collation_connection: gbk_chinese_ci 

  Database Collation: utf8_general_ci 

1 row in set (0.00 sec) 


mysql> 
 show create procedure proce01 \G 

*************************** 1. row *************************** 

           Procedure: proce01 

            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `proce01`() 

    READS SQL DATA 

BEGIN 

select * from t1; 

END 

character_set_client: gbk 

collation_connection: gbk_chinese_ci 

  Database Collation: utf8_general_ci 

1 row in set (0.00 sec) 


mysql> 
select * from information_schema.routines where routine_name = 'proce01' \G 

*************************** 1. row *************************** 

           SPECIFIC_NAME: proce01 

         ROUTINE_CATALOG: def 

          ROUTINE_SCHEMA: sample2 

            ROUTINE_NAME: proce01 

            ROUTINE_TYPE: PROCEDURE 

               DATA_TYPE: 

CHARACTER_MAXIMUM_LENGTH: NULL 

  CHARACTER_OCTET_LENGTH: NULL 

       NUMERIC_PRECISION: NULL 

           NUMERIC_SCALE: NULL 

      DATETIME_PRECISION: NULL 

      CHARACTER_SET_NAME: NULL 

          COLLATION_NAME: NULL 

          DTD_IDENTIFIER: NULL 

            ROUTINE_BODY: SQL 

      ROUTINE_DEFINITION: BEGIN 

select * from t1; 

END 

           EXTERNAL_NAME: NULL 

       EXTERNAL_LANGUAGE: NULL 

         PARAMETER_STYLE: SQL 

        IS_DETERMINISTIC: NO 

         SQL_DATA_ACCESS: READS SQL DATA 

                SQL_PATH: NULL 

           SECURITY_TYPE: DEFINER 

                 CREATED: 2018-05-31 15:26:07 

            LAST_ALTERED: 2018-05-31 15:26:07 

                SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

         ROUTINE_COMMENT: 

                 DEFINER: root@localhost 

    CHARACTER_SET_CLIENT: gbk 

    COLLATION_CONNECTION: gbk_chinese_ci 

      DATABASE_COLLATION: utf8_general_ci 

1 row in set (0.01 sec)

3. 删除存储过程

mysql> 
 drop procedure proce01; 

Query OK, 0 rows affected (0.02 sec)


参考资料：《深入浅出MySQL》