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, 0 rows affected

mysql> DELIMITER ;
mysql> CALL CountProc(101,@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 (0.01 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
1 row in set (0.00 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
*************************** 1. 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: 2016-02-27 11:06:11
            LAST_ALTERED: 2016-02-27 11:06:11
                SQL_MODE: NO_ENGINE_SUBSTITUTION
         ROUTINE_COMMENT:
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 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负责它最精通最能够完成的工作，所以可以在开发当中可以将某些常用的操作封装成存储过程。

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

知识回顾