淘先锋技术网

首页 1 2 3 4 5 6 7

MySQL知识（十五）——存储过程的调用、查看、修改和删除

2 调用存储过程

　　存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。存储函数的调用与MySQL中预定义的函数的调用方式相同。 
　　调用存储过程示例

mysql> DELIMITER //
mysql> CREATE PROCEDURE CountProc(IN sid INT,OUT num INT)
    -> BEGIN
    -> SELECT COUNT(*) INTO num FROM fruits WHERE s_id=sid;
    -> END //
Query OK,  rows affected

mysql> DELIMITER ;
mysql> CALL CountProc(,@num);
Query OK, 1 row affected

mysql> SELECT @num;
+------+
| @num |
+------+
|    3 |
+------+
1 row in set
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

3 查看存储过程

3.1 SHOW STATUS查看状态

SHOW [PROCEDURE|FUNCTION] STATUS [LIKE 'pattern']
  • 1
  • 1

例如：

mysql> SHOW PROCEDURE STATUS LIKE 'C%'\G;
*************************** 1. row ***************************
                  Db: test
                Name: CountProc
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2016-02-27 11:06:11
             Created: 2016-02-27 11:06:11
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set ( sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

3.2 SHOW CREATE查看定义

SHOW CREATE {PROCEDURE|FUNCTION} sp_name
  • 1
  • 1

示例：

mysql> SHOW CREATE PROCEDURE CountProc \G;
*************************** 1. row ***************************
           Procedure: CountProc
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `CountProc`(IN sid INT,OUT num INT)
BEGIN

SELECT COUNT(*) INTO num FROM fruits WHERE s_id=sid;

END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
 row in set ( sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

3.3 从表information.schema.Routines查看信息

示例：

mysql> SELECT * FROM information_schema.Routines
    -> WHERE ROUTINE_NAME='CountProc'  AND  ROUTINE_TYPE = 'PROCEDURE' \G
***************************  row ***************************
           SPECIFIC_NAME: CountProc
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: test
            ROUTINE_NAME: CountProc
            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 COUNT(*) INTO num FROM fruits WHERE s_id=sid;

END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: -- ::
            LAST_ALTERED: -- ::
                SQL_MODE: NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: latin1_swedish_ci
 row in set ( sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39

4 修改存储过程

　　使用ALTER语句可以修改存储过程或函数的特性，只能修改特性，如果想修改过程体只能删除存储过程再重新创建。

ALTER {PROCEDURE|FUNCTION} sp_name [characteriss]
  • 1
  • 1

例如，修改存储过程CountProc的定义，将读写权限改为MODIFIES SQL DATA，并指定调用者可以执行：

mysql> ALTER  PROCEDURE  CountProc  
MODIFIES SQL DATA
SQL SECURITY INVOKER ; 
Query OK, 0 rows affected

mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE
    -> FROM information_schema.Routines
    -> WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE='PROCEDURE';
+---------------+-------------------+---------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS   | SECURITY_TYPE |
+---------------+-------------------+---------------+
| CountProc     | MODIFIES SQL DATA | INVOKER       |
+---------------+-------------------+---------------+
1 row in set
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

5 删除存储过程

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name
  • 1
  • 1

示例：

mysql> DROP PROCEDURE CountProc;
Query OK, 0 rows affected
  • 1
  • 2
  • 1
  • 2

6 其他

为什么需要存储过程

有人说，不需要把复杂过程封装成MySQL存储过程，用程序（C++/Java）也能实现复杂逻辑。如果用程序调用API执行，其实效率相对较慢，原因在于你的应用程序要通过引擎把MySQL语句交付给MySQL引擎来执行，那就不如直接让MySQL负责它最精通最能够完成的工作，所以可以在开发当中可以将某些常用的操作封装成存储过程。

存储过程与自定义函数的区别

这里写图片描述

知识回顾

这里写图片描述