存储过程的使用

通过之前的学习，已经能够定义一个完整的存储过程了。使用存储过程可以使程序执行效率更高、安全性更好，增强程序的可重用性和维护性。接下来将针对存储过程的使用进行详细的讲解。

调用存储过程

存储过程有多种调用方法。存储过程必须使用CALL语句调用。并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，调用存储过程的语法格式如下：

CALL sp_name ([parameter[,...]])

上述语法格式中,sp_name为存储过程的名称,parameter为存储过程的参数。

定义一个名为的存储过程，然后调用这个存储过程，具体操作如下:

定义存储过程 调用存储过程 查看返回结果

查看存储过程

MySQL存储了存储过程的状态信息，用户可以使用SHOW STATES语句和SHOW CREATE语句来查看，也可以直接从系统的information_schema数据库中查询？接下来将针对这三种方法进行详细的讲解。

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

SHOW STATUS语句可以查看存储过程的状态，其基本语法结构如下：

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

这个语句是一个MySQL的扩展。它返回子程序的特征，如数据库、名字、类型、创建者及创建、修改日期。如果没有指定样式，根据使用的语句，所有存储程序或存储函数的信息都被列出。上述语法格式中，PROCEDURE和FUNCTION分别表示查看存储过程和函数，LIKE语句表示匹配的名称。

例：SHOW STATUS语句的实例代码如下：

SHOW PROCEDURE STATUS LIKE 'C%' \G

代码执行如下：

上述代码中“SHOW PROCEDURE STATUS LIKE 'C%' \G”语句获取数据库中所有名称以C开头的存储过程。通过上面的语句可以看到，这个存储过程所在的数据库为day1111，存储过程的名称为CountProc1等相关信息。

SHOW CREATE语句查看存储过程的状态

除了SHOW STATUS语句外，MySQL还可以使用SHOW CREATE语句查看存储过程的状态，基本语法格式如下：

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

这个语句也是一个MySQL的扩展。类似于SHOW CREATE TABLE,它返回一个可用来重新创建已命名子程序的确切字符串。

例：SHOW CREATE语句的示例代码如下：

SHOW CREATE PROCEDURE day1111.CountProc1 \G

代码执行如下：

从information_schema.Routines表中查看存储过程的信息

在MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程的信息，查询语句如下：

SELECT * FROM information_schema.Routines

WHERE ROUTINE_NAME = 'CountProc1' AND ROUTINE_TYPE= 'PROCEDURE' \G

SQL语句执行结果如下

需要注意的是，在information_schema数据库下的Routines表中，存储所有存储过程的定义。使用SELECT语句查询Routine表中的存储过程的定义时，一定要使用ROUTINE_NAME字段指定存储过程的名称，否则将查询出所有存储过程的定义。如国有存储过程和函数名称形同，则需要同时指定ROUTINE_TYPE字段表明查询的是哪种类型的存储过程。

修改存储过程

在实际开发中，业务需求更改的情况时有发生，这样就不可避免地需要修改存储过程的特性，在MySQL中可以使用ALTER语句修改存储过程的特性，其基本语句格式如下：

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

上述语法格式中，sp_name表示存储过程或函数的名称；characteristic表示要修改存储过程的哪个部分，characteristic的取值具体如下。

（1）CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；

（2）NO SQL表示子程序中不包含SQL语句；

（3）READS SQL DATA表示子程序中包含读数据的语句；

（4）MODIFIES SQL DATA表示子程序中包含写数据的语句；

（5）SQL SECURITY{DEFINER | INVOKER}指明谁有权限来执行；

（6）DEFINER表示只有定义者自己才能够执行；

（7）INVOKER表示调用者可以执行；

（8）COMMENT’string’表示注释信息。

例：修改存储过程CountProc1的定义

将读写权限修改为MODIFIES SQL DATA，并指名调用者可以执行，代码如下：

ALTER PROCEDURE CountProc1

MODIFIES SQL DATA

SQL SECURITY INVOKER;

执行代码，并查看修改后的信息。结果显示如下

删除存储过程

当数据库中存在废弃的数据存储过程，需要删除。MySQL中可以使用DROP语句删除存储过程，其基本语法格式如下：

DROP {PROCEDURE | FUNCTION } [IF EXISTS] sp_name

上述语法格式中，sp_name为要移除的存储过程的名称，IF EXISTS表示如果程序不存在，它可以避免发生错误，产生一个警告。该警告可以使用SHOW WARNING进行查询。

例：删除存储过程CountProc1,代码如下：

DROP PROCEDURE CountProc1;