MySQL 创建存储过程示例

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》