06-查看存储过程或者函数

WHERE film_id = p_film_id

存储过程或者函数被创建后，用户可能需要查看存储过程、函数的状态、定义等信息，便于了解存储过程或者函数的基本情况。下面将介绍如何查看存储过程或函数相关信息。

1．查看存储过程或者函数的状态

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

下面演示的是查看过程film_in_stock的信息：

mysql> show procedure status like 'film_in_stock'\G

1. row

Db: sakila

Name: film_in_stock

Type: PROCEDURE

Definer: root@localhost

Modified: 2007-07-06 09:29:00

Created: 2007-07-06 09:29:00

Security_type: DEFINER

Comment:

1 row in set (0.00 sec)

2．查看存储过程或者函数的定义

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

下面演示的是查看过程film_in_stock的定义：

mysql> show create procedure film_in_stock \G

1. row

Procedure: film_in_stock

sql_mode:

Create Procedure: CREATE DEFINER='root'@'localhost' PROCEDURE 'film_in_stock'(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)

READS SQL DATA

BEGIN

SELECT inventory_id

FROM inventory

WHERE film_id = p_film_id

AND store_id = p_store_id

AND inventory_in_stock(inventory_id);

SELECT FOUND_ROWS() INTO p_film_count;

END

1 row in set (0.00 sec)

3．通过查看 information_schema. Routines了解存储过程和函数的信息

除了以上两种方法，我们还可以查看系统表来了解存储过程和函数的相关信息，通过查看information_schema. Routines就可以获得存储过程和函数的名称、类型、语法、创建人等信息。

例如，通过查看 information_schema. Routines得到过程 film_in_stock的定义：

mysql> select * from routines where ROUTINE_NAME = 'film_in_stock' \G

1. row

SPECIFIC_NAME: film_in_stock

ROUTINE_CATALOG: NULL

ROUTINE_SCHEMA: sakila

ROUTINE_NAME: film_in_stock

ROUTINE_TYPE: PROCEDURE

DTD_IDENTIFIER: NULL

ROUTINE_BODY: SQL

ROUTINE_DEFINITION: BEGIN

SELECT inventory_id

FROM inventory

AND store_id = p_store_id

AND inventory_in_stock(inventory_id);

SELECT FOUND_ROWS() INTO p_film_count;

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: 2007-07-06 09:29:00

LAST_ALTERED: 2007-07-06 09:29:00

SQL_MODE:

ROUTINE_COMMENT:

DEFINER: root@localhost

1 row in set (0.00 sec)