002MySQL学习009操作存储过程

目录

• 1 了解存储过程
• 1.1 是什么
• 1.2 优点
• 2 创建存储过程
• 2.1 语法
• 2.2 说明
• 2.3 关键字
• 2.3.1 delimiter
• 2.3.2 begin
• 2.3.3 end
• 2.3.4 set
• 2.3.5 declare
• 2.4 流程控制
• 2.4.1 判断语句
• 2.4.2 循环语句
• 2.5 实例
• 3 使用存储过程
• 3.1 语法
• 3.2 实例
• 4 查看存储过程
• 4.1 实例
• 5 修改存储过程
• 6 删除存储过程
• 6.1 语法
• 6.2 实例

1 了解存储过程

1.1 是什么

存储过程是一组为了完成特定功能的SQL语句集合。

使用存储过程的目的是将常用或复杂的工作预先用SQL语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。

1.2 优点

1）封装性

存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。

2）可增强SQL语句的功能和灵活性

存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

3）可减少网络流量

由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

4）高性能

存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。

5）提高数据库的安全性和数据的完整性

使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限。

2 创建存储过程

2.1 语法

create procedure 存储过程名([参数]) 存储过程体

如果开启了二进制日志，会导致执行失败，解决办法是：

1）使用命令设置：

set global log_bin_trust_function_creators = 1;

2）修改配置文件设置：

log_bin_trust_function_creators = 1

2.2 说明

1）存储过程名

存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即“数据库名.存储过程名”。需要注意的是，名称应当尽量避免选取与MySQL内置函数相同的名称，否则会发生错误。

2）参数

存储过程的参数列表。格式如下：

[in|out|inout] 参数名 参数类型

MySQL存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用in、out和inout三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。需要注意的是，参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的SQL语句会将参数名看作列名，从而引发不可预知的结果。

当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有1个或多个参数。

3）存储过程体

存储过程的主体部分，包含在过程调用的时候必须执行的SQL语句。这个部分以关键字begin开始，以关键字end结束。若存储过程体中只有一条SQL语句，则可以省略begin和end。

2.3 关键字

2.3.1 delimiter

在MySQL中，服务器处理SQL语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条SQL语句，这些SQL语句如果仍以分号作为语句结束符，那么MySQL服务器在处理时会以遇到的第一条SQL语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的SQL语句，这样显然不行。

为解决这个问题，通常可使用delimiter命令将结束命令修改为其他字符。

当使用delimiter命令时，应该避免使用反斜杠“\”字符，因为它是MySQL的转义字符。

2.3.2 begin

存储过程开始符号。

2.3.3 end

存储过程结束符号。

2.3.4 set

变量赋值。

2.3.5 declare

变量定义。

2.4 流程控制

2.4.1 判断语句

1）if语句

语法：

if 条件 then 命令
[elseif 条件 then 命令]
[else 命令]
end if;

举例：

delimiter $$
create procedure fixSex(in id int, in sex int)
begin
if sex = 1 then
update student s set s.sex = '男' where s.id = id;
elseif sex = 2 then
update student s set s.sex = '女' where s.id = id;
else
update student s set s.sex = '未知' where s.id = id;
end if;
end $$
delimiter ;

2）case语句

语法：

case 变量
when 条件 then 命令
[...]
else 命令
end case;

举例：

delimiter $$
create procedure fixSex(in id int, in sex int)
begin
case sex
when 1 then
update student s set s.sex = '男' where s.id = id;
when 2 then
update student s set s.sex = '女' where s.id = id;
else
update student s set s.sex = '未知' where s.id = id;
end case;
end $$
delimiter ;

2.4.2 循环语句

1）while语句

当条件成立时执行循环。

语法：

while 条件 do
命令
end while;

举例：

delimiter $$
create procedure addStudent(in start int, in num int)
begin
declare i int default 0;
while i < num do
insert into student(id) values (start + i);
set i = i + 1;
end while;
end $$
delimiter ;

2）repeat语句

当条件成立时结束循环。

语法：

repeat
命令
until 条件
end repeat;

举例：

delimiter $$
create procedure addStudent(in start int, in num int)
begin
declare i int default 0;
repeat
insert into student(id) values (start + i);
set i = i + 1;
until i >= num
end repeat;
end $$
delimiter ;

3）loop语句

语法：

LOOP_LABLE: loop
命令
if 条件 then
leave LOOP_LABLE;
end if;
end loop;

举例：

delimiter $$
create procedure addStudent(in start int, in num int)
begin
declare i int default 0;
LOOP_LABLE: loop
insert into student(id) values (start + i);
set i = i + 1;
if i >= num then
leave LOOP_LABLE;
end if;
end loop;
end $$
delimiter ;

2.5 实例

创建不带参数的存储过程：

delimiter $$
create procedure showScore()
begin
select * from score;
end $$
delimiter ;

创建带有参数的存储过程：

delimiter $$
create procedure showStuScore(in stu varchar(20))
begin
select * from score where student = stu;
end $$
delimiter ;

3 使用存储过程

3.1 语法

call 存储过程名称([参数]);

3.2 实例

mysql> call showScore();
+----+---------+-----------+-------+
| id | student | course | grade |
+----+---------+-----------+-------+
| 2 | 张三 | 英语 | 53 |
| 3 | 李四 | 计算机 | 48 |
| 4 | 李四 | 中文 | 38 |
| 5 | 王五 | 中文 | 95 |
| 6 | 赵六 | 计算机 | 70 |
| 7 | 赵六 | 英语 | 92 |
| 8 | 赵六 | 中文 | 73 |
| 9 | 孙七 | 英语 | 94 |
| 10 | 周八 | 计算机 | 90 |
| 11 | 周八 | 英语 | 85 |
| 12 | 吴九 | 计算机 | 90 |
| 13 | 吴九 | 中文 | 55 |
+----+---------+-----------+-------+
12 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

4 查看存储过程

4.1 实例

查看所有存储过程：

mysql> show procedure status;
+------+--------------+-----------+----------------+---------------+
| Db | Name | Type | Definer | Security_type |
+------+--------------+-----------+----------------+---------------+
| demo | showScore | PROCEDURE | root@localhost | DEFINER |
| demo | showStuScore | PROCEDURE | root@localhost | DEFINER |
+------+--------------+-----------+----------------+---------------+
2 rows in set (0.00 sec)

mysql>

查看存储过程的创建语句：

mysql> show create procedure showScore;
+-----------+------------------------------------------------------------+
| Procedure | Create Procedure                                           |
+-----------+------------------------------------------------------------+
| showScore | CREATE DEFINER=`root`@`localhost` PROCEDURE `showScore`()  |
|           | begin                                                      |
|           | select * from score;                                       |
|           | end                                                        |
+-----------+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

5 修改存储过程

可以通过先删除存储过程，然后重新创建存储过程的方法实现修改的操作。

6 删除存储过程

6.1 语法

drop procedure 存储过程名称

6.2 实例

mysql> drop procedure showScore;
Query OK, 0 rows affected (0.00 sec)

mysql>