MySQL存储过程：实例教程，实现数据库操作与权限控制

存储过程可以看做是一个“加工作坊”，它接收“调用者”传递过来的“原料”（in参数），然后将这些“原料”“加工处理”成“产品”（存储过程的out参数或inout参数），再把“产品”返回给“调用者”。本节主要讲解如何在MySQL中使用存储过程，并结合“选课系统”讲解存储过程在该系统中的应用。

使用存储过程的优点如下：

·存储过程在服务器端运行，执行速度快。

·存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行即可，提高了系统性能。

·确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。

8.3.1 存储过程的使用

1.创建存储过程

在开始创建存储过程之前，先介绍一个很实用的命令：DELIMITER命令。

在MySQL中，服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候，存储过程体中可能包含多个SQL语句，每个SQL语句都是以分号为结尾的，这时服务器处理程序遇到第一个分号的时候就会认为程序结束，这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。例如：

DELIMITER $$

执行完这条命令后，程序结束的标志就换成两个美元符“$$”了。

要想恢复使用分号“；”作为结束符，运行下面命令即可：

DELIMITER；

存储过程可以由声明式SQL语句（如CREATE、UPDATE和SELECT等语句）和过程式SQL语句（如IF-THEN-ELSE语句）组成。创建存储过程使用CREATE PROCEDURE语句，语法格式如下：

CREATE PROCEDURE 过程名（[proc_parameter]）

begin

过程体

end

proc_parameter指定存储过程的参数列表，列表形式如下：

[IN|OUT|INOUT] param_name type

IN代表输入参数（默认情况下为IN参数），表示该参数的值必须由调用程序指定；

OUT代表输出参数，表示该参数的值经存储过程计算后，将OUT参数的计算结果返回给调用程序；

INOUT代表即是输入参数，又是输出参数，表示该参数的值即可以由调用程序指定，又可以将INOUT参数的计算结果返回给调用程序。

param_name表示参数名称；type表示参数的类型，该类型可以是MySQL数据库中的任意类型。

例：CREATE PROCEDURE st_Proc（）

BEGIN

SELECT *

FROM student

where dep='信工'；

end

2.调用存储过程

调用存储过程须使用call关键字，另外还要向存储过程传递in参数、out参数或者inout参数，语法格式如下：

call 过程名（）；

例：call st_proc（）；

例如：

set @student_no = '2012001'；

set @choose_number = 0；

call get_choose_number_proc（@student_no，@choose_number）；

select @choose_number；

存储过程get_choose_number_proc（）中的in参数与out参数的数据类型都为整数，也可以将这两个参数简化为一个inout参数。

delimiter $$

create procedure get_choose_number1_proc（inout number int）

reads sql data

begin

select count（*）into number from choose where student_no=number；

end

$$

delimiter；

调用：

set @number = '2012001'；

call get_choose_number1_proc（@number）；

select @number；

【例8.4】创建一过程xkstu，查询各选课学生的姓名，课程名，成绩。

【例8.5】创建带一个输入参数的存储过程yy，要求查询XSCJ库中指定专业的students表中的学生详情。

【例8.6】创建带两个输入参数的存储过程aa，查询指定学生的姓名，指定课程名的成绩。

【例8.7】创建带输入、输出参数的存储过程并调用它。

调用：

CALL CountProc（101，@num）；

mysql> SELECT @num；

【例8.8】创建一个过程ttd，求指定学生的总成绩。

create procedure ttd（in ssname char（10），out toa int ）

begin

select sum（grade）into toa from students，sc

where students.sno=sc.sno and sname=ssname；

end

【例8.9】创建一个过程ttdd，求指定学生的总成绩，平均成绩。

create procedure ttd（in ssname char（10），out toa int，out agv int ）

begin

select sum（grade）into toa from students，sc

where students.sno=sc.sno and sname=ssname

select avg（grade）into agv from students，sc(https://www.xing528.com)

where students.sno=sc.sno and sname=ssname

end

3.流程控制语句

在MySQL中，常见的过程式SQL语句可以用在一个存储过程体中。例如：IF语句、CASE语句、WHILE语句等。

（1）IF语句。IF-THEN-ELSE语句可根据不同的条件执行不同的操作。语法格式为：

IF search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list] ...

[ELSE statement_list]

END IF

说明：search_condition是判断的条件，statement_list中包含一个或多个SQL语句。当search_condition的条件为真时，就执行相应的SQL语句。

（2）CASE语句。语法格式为：

或者：

第一种格式中case_value是要被判断的值或表达式，接下来是一系列的WHEN-THEN块，每一块的when_value参数要与case_value值进行比较，如果为真，就执行statement_list中的SQL语句。如果前面的每一块都不匹配就执行ELSE块指定的语句。CASE语句最后以END CASE结束。

第二种格式中CASE关键字后面没有参数，在WHEN-THEN块中，search_condition指定了一个比较表达式，表达式为真时，执行THEN后面的语句。与第一种格式相比，这种格式能够实现更为复杂的条件判断，使用起来更方便。

（3）WHILE语句。语法格式为：

说明：语句首先判断search_condition是否为真，为真，则执行statement_list中的语句，然后再次进行判断，为真则继续循环，不为真则结束循环。

【例8.10】创建一个存储过程，实现的功能是删除一个特定学生的信息。

【例8.11】创建一个存储过程，有两个输入参数XH和KCM，要求当某学生某门课程的成绩小于60分时将备注修改为“有课程没过”，大于等于60分时将该成绩修改为60分。

【例8.12】创建存储过程，实现查询XSB表中学生人数的功能，该存储过程不带参数。

CREATE PROCEDURE DO_QUERY（）

SELECT COUNT（*）FROM XSB GROUP BY 学号；

调用该存储过程：

CALL DO_QUERY（）；

查询结果如图8.24所示。

图8.24 调用过程结果

【例8.13】假设例8.10中的存储过程已经创建，调用该存储过程。

CALL DELETE_STUDENT（'081101'）；

4.查看存储过程的定义

可以使用下面四种方法查看存储过程的定义、权限、字符集等信息。

（1）使用show procedure status命令查看存储过程的定义。

（2）查看某个数据库（例如choose数据库）中的所有存储过程名，可以使用下面的SQL语句：

select name from mysql.proc where db = 'choose' and type = 'procedure'；

（3）使用MySQL命令“show create procedure 存储过程名；”可以查看指定数据库指定存储过程的详细信息。

例如：查看get_choose_number_proc（）存储过程的详细信息，可以使用“show create procedure get_choose_number_proc\G”。

（4）存储过程的信息都保存在information_schema数据库中的routines表中，可以使用select语句查询存储过程的相关信息。

例如：下面的SQL语句查看的是get_choose_number_proc（）存储过程的相关信息。

select * from information_schema.routines where routine_name= 'get_choose_number_proc'\G

5.修改存储过程

修改存储过程是指修改已经定义好的存储过程。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语句。但是，这两个语句的结构是一样的，语句中的所有参数都是一样的。而且，它们与创建存储过程或函数的语句中的参数也是基本一样的。

修改存储过程，只能修改他们的权限，目前MySQL还不提供对已存在的存储过程代码的修改，如果要修改，只能通过先DROP掉，然后重新建立新的存储过程来实现。

8.3.2 存储过程与函数的比较

（1）存储过程与函数之间的共同特点在于：

➢ 应用程序调用存储过程或者函数时，只需要提供存储过程名或者函数名，以及参数信息，无需将若干条MySQL命令或SQL语句发送到MySQL服务器，节省了网络开销。

➢ 存储过程或者函数可以重复使用，可以减少数据库开发人员，尤其是应用程序开发人员的工作量。

➢ 使用存储过程或者函数可以增强数据的安全访问控制，可以设定只有某些数据库用户才具有某些存储过程或者函数的执行权。

➢ 函数必须有且仅有一个返回值，且必须指定返回值数据类型（返回值类型目前仅仅支持字符串、数值类型）。存储过程可以没有返回值，也可以有返回值，甚至可以有多个返回值，所有的返回值需要使用out或者inout参数定义。

（2）存储过程与函数之间的不同之处在于：

➢ 函数体内可以使用select…into语句为某个变量赋值，但不能使用select语句返回结果（或者结果集）。存储过程则没有这方面的限制，存储过程甚至可以返回多个结果集。

➢ 函数可以直接嵌入到SQL语句（例如select语句中）或者MySQL表达式中，最重要的是函数可以用于扩展标准的SQL语句。存储过程一般需要单独调用，并不会嵌入到SQL语句中使用（例如select语句中），调用时需要使用call关键字。

➢ 函数中的函数体限制比较多，比如函数体内不能使用以显式或隐式方式打开、开始或结束事务的语句，如start transaction、commit、rollback或者set autocommit=0等语句；不能在函数体内使用预处理SQL语句（稍后讲解）。存储过程的限制相对就比较少，基本上所有的SQL语句或MySQL命令都可以在存储过程中使用。

➢ 应用程序（例如Java、PHP等应用程序）调用函数时，通常将函数封装到SQL字符串（例如select语句）中进行调用；应用程序（例如Java、PHP等应用程序）调用存储过程时，必须使用call关键字进行调用，如果应用程序希望获取存储过程的返回值，应用程序必须给存储过程的out参数或者inout参数传递MySQL会话变量，才能通过该会话变量获取存储过程的返回值。