SHOW STATUS语句查看存储过程和函数的状态

最新推荐文章于 2024-08-11 10:15:00 发布

weixin_34110749

最新推荐文章于 2024-08-11 10:15:00 发布

阅读量2.3k

点赞数 1

文章标签：数据库

原文链接：http://blog.51cto.com/lucifer119/1350978

版权

存储过程和函数创建以后，用户可以查看存储过程和函数的状态和定义。用户可以通过SHOW STATUS语句来查看存储过程和函数的状态，也可以通过SHOW CREATE语句来查看存储过程和函数的定义。用户也可以通过查询information_schema数据库下的Routines表来查看存储过程和函数的信息。本小节将详细讲解查看存储过程和函数的状态与定义的方法。

14.3.1 SHOW STATUS语句查看存储过程和函数的状态

MySQL中可以通过SHOW STATUS语句查看存储过程和函数的状态。其基本语法形式如下：

SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ' pattern ' ] ;
其中，PROCEDURE参数表示查询存储过程；FUNCTION参数表示查询存储函数；LIKE ' pattern '参数用来匹配存储过程或函数的名称。

【示例14-21】下面查询名为num_from_employee的存储过程的状态。代码执行如下：

mysql> SHOW PROCEDURE STATUS LIKE 'num_from_employee'\G
*************************** 1. row ***************************
Db: example
Name: num_from_employee
Type: PROCEDURE
Definer: root@localhost
Modified: 2009-10-29 21:44:39
Created: 2009-10-29 21:44:39
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
查询结果显示了存储过程的创建时间、修改时间和字符集等信息。

MySQL中可以通过SHOW CREATE语句查看存储过程和函数的状态。其基本语法形式如下：

SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;
其中，PROCEDURE参数表示查询存储过程；FUNCTION参数表示查询存储函数；sp_name参数表示存储过程或函数的名称。

【示例14-22】下面查询名为num_from_employee的存储过程的状态。代码执行如下：

mysql> SHOW CREATE PROCEDURE num_from_employee \G
*************************** 1. row ***************************
Procedure: num_from_employee
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_
CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost`
PROCEDURE `num_from_employee`
(IN emp_id INT,
OUT count_num INT )
READS SQL DATA
BEGIN
SELECT COUNT(*) INTO count_num
FROM employee
WHERE d_id=emp_id ;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
查询结果显示了存储过程的定义、字符集等信息。

注意：SHOW STATUS语句只能查看存储过程或函数是操作哪一个数据库、存储过程或函数的名称、类型、谁定义的、创建和修改时间、字符编码等信息。但是，这个语句不能查询存储过程或函数的具体定义。如果需要查看详细定义，需要使用SHOW CREATE语句
-family: "Times New Roman"'>字段的值返回。

存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法形式如下：

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME=' sp_name ' ;
其中，ROUTINE_NAME字段中存储的是存储过程和函数的名称；sp_name参数表示存储过程或函数的名称。

【示例14-23】下面从Routines表中查询名为num_from_employee的存储过程的信息。代码执行如下：

mysql> SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='num_
from_employee' \G
*************************** 1. row ***************************
SPECIFIC_NAME: num_from_employee
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: example
ROUTINE_NAME: num_from_employee
ROUTINE_TYPE: PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
SELECT COUNT(*) INTO count_num
FROM employee
WHERE d_id=emp_id ;
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: 2009-10-29 21:44:39
LAST_ALTERED: 2009-10-29 21:44:39
SQL_MODE: STRICT_TRANS_TABLES,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ROUTINE_COMMENT:
DEFINER: root@localhost
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)
查询结果显示num_from_employee的详细信息。

注意：在information_schema数据库下的Routines表中，存储着所有存储过程和函数的定义。如果使用SELECT语句查询Routines表中的存储过程和函数的定义时，一定要使用ROUTINE_NAME字段指定存储过程或函数的名称。否则，将查询出所有的存储过程或函数的定义

修改存储过程和函数是指修改已经定义好的存储过程和函数。MySQL中通过ALTER PROCEDURE语句来修改存储过程。通过ALTER FUNCTION语句来修改存储函数。本小节将详细讲解修改存储过程和函数的方法。

MySQL中修改存储过程和函数的语句的语法形式如下：

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
其中，sp_name参数表示存储过程或函数的名称；characteristic参数指定存储函数的特性。CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序中不包含SQL语句；READS SQL DATA表示子程序中包含读数据的语句；MODIFIES SQL DATA表示子程序中包含写数据的语句。SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。COMMENT 'string'是注释信息。

说明：修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。但是，这两个语句的结构是一样的，语句中的所有参赛都是一样的。而且，它们与创建存储过程或函数的语句中的参数也是基本一样的。

【示例14-24】下面修改存储过程num_from_employee的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。代码执行如下：

ALTER PROCEDURE num_from_employee
MODIFIES SQL DATA
SQL SECURITY INVOKER ;
执行代码，并查看修改后的信息。结果显示如下：

//执行ALTE PROCEDURE语句
mysql> ALTER PROCEDURE num_from_employee
-> MODIFIES SQL DATA
-> SQL SECURITY INVOKER ;
Query OK, 0 rows affected (0.00 sec)
//查询修改后num_from_employee表的信息
mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,
SECURITY_TYPE FROM informa-
tion_schema.Routines WHERE ROUTINE_NAME='num_from_employee' ;
+-------------------------------+-------------
---------------------+-------------------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS | SECURITY_TYPE |
+-------------------------------+------------
----------------------+-------------------------+
| num_from_employee | MODIFIES SQL DATA | INVOKER |
+-------------------------------+-----------
-----------------------+-------------------------+
1 row in set (0.00 sec)
结果显示，存储过程修改成功。从查询的结果可以看出，访问数据的权限（SQL_DATA_ACCESS）已经变成MODIFIES SQL DATA，安全类型（SECURITY_TYPE）已经变成了INVOKER。

【示例14-25】下面修改存储函数name_from_employee的定义。将读写权限改为READS SQL DATA，并加上注释信息'FIND NAME'。代码执行如下：

ALTER FUNCTION name_from_employee
READS SQL DATA
COMMENT 'FIND NAME' ;
执行代码，并查看修改后的信息。结果显示如下：

//执行ALTE FUNCTION语句
mysql> ALTER FUNCTION name_from_employee
-> READS SQL DATA
-> COMMENT 'FIND NAME' ;
Query OK, 0 rows affected (0.00 sec)
//查询修改后num_from_employee表的信息
mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,
ROUTINE_COMMENT FROM information_schema.Routines
WHERE ROUTINE_NAME='name_from_employee' ;
+--------------------------------+--------------
-----------------+-------------------------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS | ROUTINE_COMMENT |
+--------------------------------+-------------
------------------+-------------------------------+
| name_from_employee | READS SQL DATA | FIND NAME |
+--------------------------------+----------------
---------------+-------------------------------+
1 row in set (0.01 sec)
结果显示，存储函数修改成功。从查询的结果可以看出，访问数据的权限（SQL_DATA_ACCESS）已经变成READS SQL DATA，函数注释（ROUTINE_COMMENT）已经变成了"FIND NAME"。

删除存储过程和函数指删除数据库中已经存在的存储过程和函数。MySQL中使用DROP PROCEDURE语句来删除存储过程。通过DROP FUNCTION语句来删除存储函数。其基本形式如下：

DROP { PROCEDURE| FUNCTION } sp_name;
其中，sp_name参数表示存储过程或函数的名称。

【示例14-26】下面删除存储过程num_from_employee和存储函数name_from_empl-oyee。删除存储过程num_from_employee的代码如下：

DROP PROCEDURE num_from_employee ;
删除存储函数name_from_employee的代码如下：

DROP FUNCTION name_from_employee ;
代码执行结果如下：

//删除num_from_employee
mysql> DROP PROCEDURE num_from_employee ;
Query OK, 0 rows affected (0.00 sec)
//删除name_from_employee
mysql> DROP FUNCTION name_from_employee ;
Query OK, 0 rows affected (0.00 sec)
可以通过查询information_schema数据库下的Routines表来确认上面的删除是否成功。SELECT语句的执行结果如下：

mysql> SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='num_from_
employee' OR ROUTINE_NAME='name_from_employee';
Empty set (0.00 sec)
结果显示，没有查询出任何记录。这说明存储过程num_from_employee和存储函数name_from_employee都已经被删除。

本小节将在food表上创建名为food_price_count的存储过程。按照11.4小节中表11.1和表11.2来创建food表。存储过程food_price_count有3个参数。输入参数为price_info1和price_info2，输出参数为count。存储过程的作用是查询food表中食品单价高于price_info1且低于price_info2的食品种数，然后由count参数来输出。并且计算满足条件的单价的总和。

操作如下：

（1）按照11.4节的内容来创建food表，并插入记录。

（2）创建存储过程food_price_count。代码如下：

DELIMITER && //使用"DELIMITER &&"
将SQL语句的结束符号变成&&
CREATE PROCEDURE food_price_count (IN
price_info1 FLOAT,IN price_info2 FLOAT, OUT count INT )
READS SQL DATA
BEGIN
DECLARE temp FLOAT;
//定义变量temp
//定义光标match_price
DECLARE match_price CURSOR FOR SELECT
price FROM food;
//定义条件处理。如果没有遇到关闭光标，
就退出存储过程
DECLARE EXIT HANDLER FOR NOT FOUND
CLOSE match_price;
SET @sum=0;
//为临时变量sum赋值
//用SELECT…INOT语句来为输出变量count赋值
SELECT COUNT(*) INTO count FROM food
WHERE price>price_info1 AND price<price_info2 ;
OPEN match_price; //打开光标
REPEAT //执行循环
FETCH match_price INTO temp;
//使用光标match_price
//执行条件语句
IF temp>price_info1 AND temp<price_info2
THEN SET @sum=@sum+temp;
END IF;
UNTIL 0 END REPEAT; //结束循环
CLOSE match_price; //关闭光标
END &&
DELIMITER ;
//将SQL语句的结束符号变成";"
（3）使用CALL语句来调用存储过程。查询价格在2～18之间的食品种数。代码如下：

CALL food_price_count(2,18,@count) ;
代码执行如下：

mysql> CALL food_price_count(2,18,@count);
Query OK, 0 rows affected (0.00 sec)
（4）使用SELECT语句查看结果。代码如下：

SELECT @count, @sum ;
其中，count是存储过程的输出结果；sum是存储过程中的变量，sum中的值满足条件的单价的总和。代码执行结果如下： www.2cto.com

mysql> SELECT @count,@sum;
+-----------+---------+
| @count | @sum |
+-----------+---------+
| 3 | 20 |
+-----------+---------+
1 row in set (0.00 sec)
（5）使用DROP语句删除存储过程food_price_count。代码如下：

DROP PROCEDURE food_price_count ;
执行结果如下：

mysql> DROP PROCEDURE food_price_count;
Query OK, 0 rows affected (0.00 sec) 可以通过SHOW CREATE PROCEDURE来查看存储过程。代码执行结果如下：

mysql> SHOW CREATE PROCEDURE food_price_count\G
ERROR 1305 (42000): PROCEDURE food_price_count does not exist
这说明该存储过程已经删除。

这个存储过程的功能也可以通过存储函数来实现。存储函数的代码如下：

DELIMITER &&
CREATE FUNCTION food_price_count1(price_info1
FLOAT,price_info2 FLOAT )
RETURNS INT READS SQL DATA
BEGIN
RETURN (SELECT COUNT(*) FROM food
WHERE price>price_info1 AND price<price_info2 );
END &&
DELIMITER ;
存储函数只能返回一个值，所以只实现了计算满足条件的食品种数。使用RETURN来将计算的食品种数返回回来。调用存储函数与调用MySQL内部函数的方式是一样的。调用存储函数的语句执行结果如下：

mysql> SELECT food_price_count1(2,18);
+---------------------------------+
| food_price_count1(2,18) |
+---------------------------------+
| 3 |
+---------------------------------+
1 row in set (0.00 sec)
删除存储函数是通过DROP FUNCTION来实现的。删除存储函数的语句执行结果如下：

mysql> DROP FUNCTION food_price_count1;
Query OK, 0 rows affected (0.00 sec)