MySQL存储过程创建与使用总结

存储过程在数据库中创建并保存，由SQL语句和控制结构组成。当需要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，可以使用存储过程。

一、创建MySQL存储过程的语法

delimiter //   
create procedure sp_name([proc_parameter[...]]) 
[characteristic ...] 
routine_body //
delimiter ;

说明：

1、[delimiter //]修改默认的命令结束符为[//]，过程体以[//]结束，[delimiter ;]恢复默认的结束符为[;]。
2、proc_parameter指定存储过程的参数列表，语法格式为：[in | out |inout ] para_name type，其中in表示输入参数，in参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回；out表示输出参数，out参数的值可以在存储过程内部被改变，并返回；inout表示既可以输入也可以输出，inout参数调用时指定，并且可被改变和返回。
3、characteristic参数有五个可选选项，分别为：
（1）LANGUAGE SQL：存储过程语言，默认是SQL。
（2）DETERMINISTIC：表示存储过程对同样的输入参数产生相同的结果；NOT DETERMINISTIC表示会产生不确定的结果（默认值）；
（3）[CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]： 指定使用SQL语句的限制。包括四种选择：CONTAINS SQL（默认值）：表示子程序包含SQL语句，但不包含读或者写数据的语句；NO SQL：表示子程序不包含SQL；READS SQL DATA：表示子程序包含读数据的语句，但是不包含写数据的语句；MODIFIES SQL DATA：表示子程序包含写数据的语句。
（4）SQL SECURITY [DEFINER | INVOKER]：指定有权执行存储过程的用户。DEFINER：代表定义者有权执行存储过程（默认）；
INVOKER：代表调用者有权执行存储过程。
（5）COMMENT：用于对存储过程进行描述。存储过程的注释性信息写在COMMENT里面，这里只能是单行文本。

二、存储过程的创建举例

首先创建以下三个表：student（学生）、course（课程）、score（成绩），并输入数据：

create table student(
    s_id int primary key auto_increment comment '学生学号',
    s_name char(20) comment '学生姓名',
    age int comment '学生年龄'
);

create table course(
    c_id int primary key auto_increment comment '课程编号',
    c_name char(20) comment '课程名称',
    credits int comment '课程学分'
);

create table score(
    s_id int comment '学生学号',
    c_id int comment '课程编号',
    score int comment '成绩',
    primary key(s_id,c_id)
);

为以上三张表插入必要的数据，命令如下：

insert into student
values(1001,'张平',20),(1002,'王刚',21),(1003,'张静静',19),(1004,'王涛',20),(1005,'王鹏飞',19);

insert into course
values(1,'数据库',4),(2,'数据结构',4),(3,'管理学',3),(4,'英语',4),(5,'电子商务',3);

insert into score 
values(1001,1,80),(1001,2,90),(1001,3,77),(1001,4,87),(1001,5,69),
(1002,1,87),(1002,2,67),(1002,3,78),(1002,4,98),(1002,5,78),
(1003,1,66),(1003,2,77),(1003,3,88),(1003,4,99),(1003,5,66),
(1005,1,81),(1005,2,83),(1005,3,62),(1005,4,68),(1005,5,72),
(1004,1,72),(1004,2,60),(1004,3,84),(1004,4,88),(1004,5,74);

（一）带输入参数（in参数）的存储过程

delimiter //
drop procedure if exists sp_query_student;

create procedure sp_query_student(in stu_name char(20)) reads sql data
begin
    if stu_name is null or stu_name='' then
    	select 'input error';
    else
    	select c_name,score from score,course where course.c_id=score.c_id and
    	s_id = (select s_id from student where s_name = stu_name);
    end if;
end; //

delimiter ;

创建存储过程sp_query_student，执行结果如下：

mysql> call sp_query_student('张静静');
+--------------+-------+
| c_name       | score |
+--------------+-------+
| 数据库       |    66 |
| 数据结构     |    77 |
| 管理学       |    88 |
| 英语         |    99 |
| 电子商务     |    66 |
+--------------+-------+
5 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

（二）带输入参数（in参数）和输出参数（out参数）的存储过程

delimiter //
drop procedure if exists sp_query_student_course;

create procedure sp_query_student_course(in stu_name char(20),out course_count int) reads sql data
begin
    if stu_name is null or stu_name='' then
    	select 'input error';
    else
    	select c_name,score from score,course where course.c_id=score.c_id and
    	s_id = (select s_id from student where s_name = stu_name);
    end if;
    select found_rows() into course_count;
end; //

delimiter ;

三、存储过程的调用

使用用call命令调用存储过程，格式如下：

call proc_name([para_name,...]);

四、存储过程的查询

select name from mysql.proc where db='数据库名';  --查看该数据库中包含的存储过程名称
select routine_name from information_schema.routines 
where routine_schema='数据库名';                  --查看该数据库中包含的存储过程名称  
show procedure status where db='数据库名';        --查看该数据库中包含的存储过程的详细信息

五、查看存储过程的详细信息

show create procedure proc_name;

六、修改存储过程

alter procedure sp_name [characteristic ...]

--将读写权限改为： modifies sql data，并指明调用者可以执行。
alter procedure sp_query_student_course modifies sql data sql security invoker;
--将读写权限改为： reads sql data，并加上注释信息： '版权所有：***'。
alter procedure sp_query_student_course reads sql data comment '版权所有：***' ;

七、删除存储过程

drop procedure proc_name;