MySQL数据库---存储过程

定义

存储过程和函数是在数据库中定义的一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户器端和服务端的数据传输。

1.创建存储过程实现

语句:
create procedure 存储名(参数表)
存储过程的SQL语句;

参数表:每个参数由三部分组成，分别为输入/输出类型、参数名和参数类型,输入/输出类型有三种类型，分别为IN（表示输入类型）、OUT（表示输出类型）、INOUT（表示输入/输出类型）

实例:
首先有三个表,信息如下

创建一个存储过程:
解释:(
第3行:定义变量tid;
第4行:通过查询语句为变量tid赋值,即通过对比找到players中id与pid相同的,把这条记录的team_id赋值给tid,其实是方便以后用tid操作,变量后续会讲到;
第5条:删除队员的工资
第6条:删除队员的信息
第7条:通过tid更新teams表中的记录
$$在触发器一节讲过)

调用存储过程

2.变量

声明:一定是在定义存储过程中使用的

定义

格式:
declare 变量名 类型 [默认值];
举例:

定义一个变量t 数据类型为int型,默认值为10
语句: declare t int default 10;

赋值

格式:
第一种:set 变量名 = 值;
第二种:select 列名 into 变量名 from 表名 where 条件;

实例:
定义一个用来查询的过程,并且返回查询队员的姓名
解释:(
第345行:定义三个变量
第6行:通过参数对比球员id找到该球员的队伍id
第7行:通过队伍id找到该球员所在队伍名称
第8行:找到该球员的姓名
第9行:将找到的值,赋值给输出参数)

调用过程,并查一下返回参数(@是用户变量的意思)

3.光标(游标)

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录
个人理解：
如果查询的记录不止一条，那么光是用变量是接收不了的，所以光标可以看成一个小小存储器，把查询到的语句先放里面 ，然后一条一条的放到变量中

声明光标

格式:declare 光标名 cursor for 查询语句;

• 光标必须声明在处理程序之前，并且声明在变量和条件之后。

打开光标

格式:open 光标名

使用光标

格式:fetch 光标名 into 参数

关闭光标

格式:close 光标名;

• 光标使用完毕后一定要关闭

简单实例

说明:(
第5行:声明一个光标,查询球员的姓名和队伍id
第6行:打开光标
第7行:将光标里的值付给变量(下面会讲多条记录怎么弄)
第8行:显示变量的值
第9行:关闭光标)

4.流程控制

在存储过程和函数中，可以使用流程控制来控制语句的执行。在MySQL中，可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

(1).if语句

格式:
if 条件判断 then 执行语句
[else if 条件判断 then 执行语句]
[else 执行语句]
end if
实例:
该示例根据age与20的大小关系来执行不同的SET语句。如果age值大于20，将tmp的值加3；如果age值等于20，就将tmp的值加2；其他情况将tmp的值加1

(2).case

CASE语句可实现比IF语句更复杂的条件判断

格式:
case 要判断的值
when 值 then 执行的语句
[when 值 then 执行的语句]
[else 执行语句]
end case;
实例;

CASE level
WHEN 20 THEN SET attack = attack + 5;
WHEN 30 THEN SET attack = attack + 10;
WHEN 40 THEN SET attack = attack + 15;
ELSE SET attack = attack + 1; END CASE

(3).leave

LEAVE语句主要用于跳出循环控制.(c语言中的break)
格式:leave 循环标志

(4).iterate

LEAVE语句主要用于调到下一次循环(C语言中的continue)
格式:iterate 循环标志

(5).loop

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。LOOP语句本身没有停止循环，只有遇到LEAVE语句等才能停止循环。

格式:
[开始标志:]loop
执行语句
end loop[结束标志]
标志为了跳出和嵌套时好区分
实例:
计算1-99累加和

(6).repeat

当满足特定条件时，就会跳出循环语句

格式:
[开始标志:]repeat
执行语句
until 结束条件
end repeat[结束标志]
实例:
循环执行count加1的操作，count值为100时结束循环

REPEAT
SET @count=@count+1;
UNTIL @count=100
END REPEAT;

(7).while

格式:
[开始标志:]while 执行条件 do
执行条件
end while[结束标志]

实例:

WHILE @count<100 DO
SET @count = @count + 1; END WHILE;

5.查看存储过程

(1)

格式:
show procedure status [like 名称];
实例:

(2)

格式:show create procedure 过程名\G;

实例:

(3)

*格式:
select from information_schema.routines
where routine_name = ‘过程名’;
实例;

6.删除

格式:
drop procedure 列表名;