06-存储过程的查看

选择背景色：黄橙洋红淡粉水蓝草绿白色选择字体：宋体黑体微软雅黑楷体选择字体大小：小中大特恢复默认

9.3.2 存储过程的查看

存储过程创建好以后，用户可以通过SHOW PROCEDURE STATUS语句或SHOW CREATE PROCEDURE 语句来查看存储过程的状态信息，也可以通过INFORMATION_SCHEMA在数据库中进行查询，下面介绍这3种方法。

1.使用SHOW PROCEDURE STATUS语句查看存储过程的状态

SHOW PROCEDURE STATUS[LIKE'pattern’]

这个语句是一个MySQL的扩展。它返存储过程的特征，如所属数据库、名称、类型、创建者及创建和修改日期。如果没有指定样式，根据用户使用的语句，所有存储过程被列出。LIKE语句表示匹配存储过程的名称。

【范例9-5】

SHOW PROCEDURE STATUS语句的示例，代码如下。

mysql> SHOW PROCEDURE STATUS like 'a%'\G//

结果如下。

** Db: studb Name: avg_emp Type: PROCEDURE Definer: root@localhost Modified: 2015-03-30 15:01:25 Created: 2015-03-30 15:01:25 Security_type: DEFINER Comment: character_set_client: gbk collation_connection: gbk_chinese_ci Database Collation:utf8_general_ci 1 row in set (0.06 sec)

“SHOW PROCEDURE STATUS like 'a%'\G”语句获取了数据库中所有的名称以字母“a”开头的存储过程信息。通过得到的结果可以得出，以字母“a”开头的存储过程名称为avg_emp,该存储过程所在的数据库为studb,类型为procedure，创建时间等相关信息。

SHOW STATUS语句只能查看存储过程操作哪一个数据库、存储过程的名称、类型、谁定义的、创建和修改时间、字符编码等信息。但是，这个语句不能查询存储过程具体定义。如果需要查看详细定义，需要使用下面的SHOW CREATE语句

2.SHOW CREATE PROCEDURE查看存储过程的信息

SHOW CREATE PROCEDURE sp_name

该语句是一个MySQL的扩展。类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名存储过程的确切字符串。

【范例9-6】

SHOW CREATE PROCEDURE语句的示例，代码如下。

mysql> show create procedure avg_emp\G

结果如下。

*** Procedure: avg_emp sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=root@localhost PROCEDURE avg_emp(in deptno int,out avgage float) begin select avg(empage) into avgage from emp where dno=deptno; end character_set_client: gbk collation_connection: gbk_chinese_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)

执行上面的语句可以得出存储过程avg_emp的具体的定义语句，该存储过程的sql_mode、数据库设置的一些信息。

3.通过INFORMATION_SCHEMA.ROUTINES查看存储过程的信息

INFORMATION_SCHEMA是信息数据库，其中保存着关于MySQL服务器所维护的所有其他数据库的信息。该数据库中的ROUTINES表提供存储过程的信息。通过查询该表可以查询相关存储过程的信息，语法如下。

Select * from information_schema.routines Where routine_name='sp_name';

其中，routine_name字段存储所有存储子程序的名称；sp_name是需要查询的存储过程名称。

【范例9-7】

从INFORMATION_SCHEMA.ROUTINES表中查询存储过avg_emp的信息。

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='avg_emp' \G

查询结果显示num_from_employee的详细信息如下。

1.row SPECIFIC_NAME: avg_emp ROUTINE_CATALOG: def ROUTINE_SCHEMA: studb ROUTINE_NAME: avg_emp 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 avg(empage) into avgage from emp where dno=deptno; end EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: MODIFIES SQL DATA SQL_PATH: NULL SECURITY_TYPE: INVOKER CREATED: 2015-03-30 15:01:25 LAST_ALTERED: 2015-04-01 15:01:57 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.02 sec)

存储过程写好后，如果需要修改它的特性，可以使用alter语句来进行修改，其语法如下。

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

其中，sp_name为待修改的存储过程名称；characteristic来指定特性，可能的取值如下。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'

CONTAINS SQL表示存储过程包含SQL语句，但不包含读或写数据的语句；NO SQL表示存储过程中不包含SQL语句；READS SQL DATA表示存储过程中包含读数据的语句；MODIFIES SQL DATA表示存储过程中包含写数据的语句。SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。COMMENT ‘string’是注释信息。

【范例9-8】

修改存储过程avg_emp的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。代码执行如下。

mysql>ALTER PROCEDURE avg_emp -> MODIFIES SQL DATA -> SQL SECURITY INVOKER; Query OK, 0 rows affected (0.00 sec)

查看avg_emp修改后的信息如下。

SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE FROM information_schema.Routines WHERE ROUTINE_NAME='avg_emp' ;

查看结果如下。

结果显示，存储过程修改成功。从查询的结果可以看出，访问数据的权限（SQL_DATA_ACCESS）已经变成MODIFIES SQL DATA，安全类型（SECURITY_TYPE）已经变成了INVOKER。

Previous 上一篇本节目录 Next 下一篇