MySQL存储过程详解

MySQL存储过程

创建一个简单的存储过程

方法一：命令行创建，注意定义分隔符

#打开终端，进入mysql
mysql> delimiter $
mysql> create procedure pro_user()
    -> begin
    ->     select * from tb_user;
    -> end $

方法二：可视化工具navicate，右键函数–过程，创建存储过程

基本的语法

delimiter $ 定义分隔符$
create procedure pro_user()  --创建存储过程 create procedure 存储过程名
begin...end   --存储过程体语句块（代码块），一条或多条

存储过程的特点

• 能完成较复杂的判断与运算
• 可编程性强、灵活性
• SQL变成代码可重复使用
• SQL执行速度相对快
• 减少网络之间数据传输，节省开销

存储过程的变量

有一个需求：编写存储过程，使用变量取UID=2的用户名

• 创建存储过程
• 使用变量，需要先声明变量，变量声明语法：declare 变量名 变量类型 default 默认值；
• 给变量赋值，语法：set 变量名=值、select…into语法【如下】
• 将变量赋值给某个字段，作为筛选条件
• 调用存储过程，语法：call 存储过程名；
  变量赋值的方法
  1.set 变量名=值

begin
	declare a varchar default '';
	set a = '2';
	select username from user where uid = a;
end

2.select…into语法

begin
	declare a varchar default '';
	select username into a from user where uid = 2;
end

有一个需求：统计表user的行数，以及最早、最晚的注册时间

begin
	begin
		declare count int default 0;
		select count(*) into count from user;
		select count;   --如果需要返回变量，可以如此书写
	end;   --注意分号
	begin
		declare MaxTime timestamp;
		declare MinTime timestamp;
		select max(reqtime),min(reqtime) into MaxTime ,MinTime from user;
		select MaxTime,MinTime;   --此处不可以返回count，因为他是局部变量，作用域只在其begin...end内
	end;
end

存储过程名称定义：stats_user
调用该存储过程，查看结果：

call stats_user();   --存在两个结果，一个是count，一个是MaxTime,MinTime

以上count不可以被第二个代码块调用。

begin
	declare count int default 0;   --将count变成全局变量
	begin
		select count(*) into count from user;
		select count;   --如果需要返回变量，可以如此书写
	end;   --注意分号
	begin
		declare MaxTime timestamp;
		declare MinTime timestamp;
		select max(reqtime),min(reqtime) into MaxTime ,MinTime from user;
		select count,MaxTime,MinTime;   --可以将全局变量count返回。
	end;
end

变量总结–局部变量、全局变量
1.局部变量：declare定义的变量 相当于一个局部变量 在end之后失效，而且declare只能在begin，end中定义。
2.全局变量【会话变量】：相同的声明变量方法， 将其放在最外层的begin…end即为全局变量

存储过程的参数

1.存储过程的传入参数IN

有一个需求：编写存储过程，传入UID，返回UNAME
方案：UID作为一个参数，用户传入任意参数，可以找到对应的值，也就是UID不写死，需要有参数支持，此处可以用IN类型。

--如果是Navicat可以直接创建存储过程的同时指定参数 IN my_uid int（其中IN可以不写，默认是IN）
begin
	declare my_UNAME varcha(32) default '';
	select uname int my_UNAME from user where uid = my_uid;   --此处的my_uid也就是传入参数
	select my_UNAME;   --返回my_UNAME
end

将存储过程命名：testa
调用存储过程：

call testa('2');

1.传入参数：类型为IN，表示该参数的值必须在调用存储过程时指定，如果不显式指定为IN，那么默认就是IN类型
2.IN类型参数一般只用于传入，在调用存储过程中一般不做修改和返回
3.如果调用存储过程中需要修改和返回值，可以使用OUT类型参数

2.存储过程的传出参数OUT

有一个需求：编写存储过程，传入UID，返回UNAME

--如果是Navicat可以直接创建存储过程的同时指定参数 IN my_uid int,OUT my_uname varchar(32)（其中多个参数，逗号隔开）
begin
	select uname into my_uname from user where uid = my_uid;   --此处的my_uid也就是传入参数
	select my_uname ;   --此处实际上不需要返回，因为有OUT参数接收该值，只需要在调用语句中返回该OUT变量值即可
end

将存储过程命名：testa
思路：上面的存储过程有两个参数，IN、OUT两种，my_uid 传入参数可以任意指定，my_uname 本身是一个变量，是接收结果值的一个参数，所以需要在调用时定义一个变量，传入。
调用存储过程：

set @uname:='';
call testa('2',@uname);
select my_unameAA;

1.传出参数：在调用存储过程中，可以改变其值，并可返回
2.OUT是传出参数，不能用于传入参数值
3.调用存储过程时，OUT参数也需要指定，但必须是变量，不能是常量
4.如果既需要传入，又需要传出，可以使用INOUT类型参数

3.存储过程的可变参数INOUT

有一个需求：调用存储过程中，参数my_uid与my_uname既是传入，也是传出参数

--如果是Navicat可以直接创建存储过程的同时指定参数 INOUT my_uid int,INOUT my_uname varchar(32)（其中多个参数，逗号隔开）
begin
	set my_uid = 2;
	set my_uname = 'aa';
	select uname,uid into my_uname,my_uid from user where uid = my_uid;   --此处的my_uid也就是传入参数
end

将存储过程命名：testa
思路：上面的存储过程有两个参数，IN、OUT两种，my_uid 传入参数可以任意指定，my_uname 本身是一个变量，是接收结果值的一个参数，所以需要在调用时定义一个变量，传入。
调用存储过程：

set @uname:='';
set @uid = 0;
call testa(@uid,@uname);
select @uid as uidAA,@uname as unameAA;

1.可变变量INOUT：调用时可传入值，在调用过程中，可修改其值，同时也可返回值
2.INOUT参数集合了IN和UOT类型的参数功能
3.INOUT调用时传入的是变量，而不是常量

存储过程的条件语句

有一个需求：编写存储过程，如果给定UID是偶数，返回uname，否则只返回UID

--设置参数：IN my_uid int
begin
	declare my_uname varchar(32) default '';
	if(my_uid % 2 = 0)
	then
		select uname into my_uname from user where uid = my_uid;
		select my_uname;
	else
		select my_uid;
	end if;
	
end

将存储过程命名：testb

call testb(2);
call testb(3);

条件语句最基本的结构

if(条件表达式)
then
	（执行语句）
else
	（执行语句）
end if;   ---结束条件语句

有一个需求：根据用户传入的UID参数判断
（1）用户status是1，用户score+10分；
（2）用户status是2，用户score+20分；
（3）其他，+30分；

--设置参数：IN my_uid int
begin
	declare my_status int default 0;
	select status into my_satus from user where uid = my_uid;
	if(my_status = 1)
	then
		update user set score = score + 10 where uid = my_uid;
	else if(my_status = 2)
	then
		update user set score = score + 20 where uid = my_uid;
	else
		update user set score = score + 30 where uid = my_uid;
	end if;
	
end

将存储过程命名：testc

call testc(2);
call testc(3);

多条件语句结构：

if(条件表达式)
then
	（执行语句）
else if(条件表达式)
then
	（执行语句）
else
	（执行语句）
end if;   ---结束条件语句

存储过程的循环语句

while循环语句

有一个需求：使用循环语句，向表testa(uid)中插入10条uid连续的记录

begin
	declare i int default 0;
	while(i < 10) do
	begin
		select i;
		int i = i + 1;
		insert into testa(uid) values (i);
	end;
	end while;
end

保存存储过程名称为inserTesta

call inserTesta();

while循环结构体

	while(循环判断语句) do
	begin
		（循环执行语句）
	end;
	end while;   --结束while

repeat循环语句

有一个需求：使用repeat，向表testa(uid)中插入10条uid连续的记录

begin
	declare i int default 10;
	repeat
		begin
			select i;
			int i = i + 1;
			insert into testa(uid) values (i);
		end;
	until(i < 20)
	end repeat;
		
end

保存存储过程名称为inserTestb

call inserTestb();

repeat循环结构体

repeat
	begin
		(循环语句)
	end;
	until(跳出循环的条件语句)
	end repeat;

loop循环语句

有一个需求：使用repeat，向表testa(uid)中插入10条uid连续的记录

begin
	declare i int default 20;
	loop_name:loop
		if(i < 30)
		then 
			select i;
			int i = i + 1;
			insert into testa(uid) values (i);
		leave loop_name;
		end if;
	end loop;
end

保存存储过程名称为inserTestc

call inserTestc();

loop循环结构体

loop_name:loop
	if(条件表达式)
	then 
		(执行语句)
	leave loop_name;
	end if;
end loop;

存储过程游标

有一个需求：编写存储过程，使用游标，把UID为偶数的记录逐一更新用户名[个性化修改]

begin
	declare stopflag int default 0;--声明停止标签，停止为1，没有停止为0
	declare my_uname varchar(32) default '';
	declare uname_cur CURSOR for select uname from user where uid%2 = 0;--定义游标类型变量[CURSOR]，保存查询结果集
	declare continue handler for not found set stopflag = 1;--定义游标句柄，当没有数据的时候执行 set stopflag = 1;
	open uname_cur;--打开游标
	fetch uname_cur into my_uname;--取一条数据
	while (stopflag = 0) do 
	begin 
		update user set uname = concat(my_uname , '_cur')where uname = my_uname ;
		fetch uname_cur into my_uname;--取一条数据
	end;
	end while;
	close uname_cur;--关闭游标，释放内存资源
end

第一步：声明标签
第二步：定义游标类型变量，保存子查询的结果，是一个结果集，是一个临时内存区
第三步：声明游标句柄，设置stopfalg值，用于条件或循环语句，判断是否继续。定义continue handler来操作一个越界标识,使用语法：declare continue handler for NOT FOUND statemet(当没数据的时候要执行的语句)
第四步：打开游标
第五步：取出游标，执行操作
第六步：关闭游标，释放临时内存区资源

保存testq

call testq();

简单的自定义函数

有一个需求：创建一个函数，输入UID，返回uname

第一步： navicat界面右键函数-选择函数
第二步： 设置参数 IN my_uid int
**第三步：**设置返回类型varchar(32)
第四步：

begin
	declare my_uname varchar(32) default '';
	select uname into my_uname from user where uid = my_uid;
	return my_uname;
end

保存可能会出错，因为涉及数据访问权限问题：

CONTAINS SQL：包含SQL语句，但不包含读或写数据的语句
NO SQL：不包含SQL语句
READS SQL DATA：包含读数据的语句
MODIFIES SQL DATA：包含写数据的语句```
**第五步：**设置高级-definer为READS SQL DATA
**第六步：**保存函数为getUname,调用函数
```sql
selct getUname(2);

创建函数与过程的区别：

• 存储过程实现功能复杂；函数针对性更强
• 存储过程可以返回多个值；函数只能有一个返回值
• 存储过程一般独立的来执行；函数可以作为其他SQL的语句组成部分出现

命令行代码：

Delimiter $$
create function getUame (IN my_uid int) return varchar(32)
Reads sql data
begin
	declare my_uname varchar(32) default '';
	select uname into my_uname from user where uid = my_uid;
	return my_uname;
end;
$$
Delimiter

自定义函数的应用示例

有一个需求：输入用户id，获得accountid，UID，uname组合的UUID值，在全区游戏中作为用户的唯一标识
第一步： navicat界面右键函数-选择函数
第二步： 设置参数 IN my_uid int
**第三步：**设置返回类型varchar(32)
第四步：

begin
	declare uuid varchar(32) default '';
	select concat(uid,'_',uuname,'_',accountid ) into uuid from user where uid = my_uid;
	return uuid;
end

存储过程触发器

有一个需求：出于审计的目的，插入操作时，记录插入动作与插入时间
**第一步：**新建查询
第二步：

create trigger tr_user_insert after insert on user ---创建触发器tr_user_insert 在user表中执行insert语句之后
for each row ---针对每一行操作[影响范围]
begin
	insert into oplog(uid,uname,action.optime) values (NEW.uid,New.uname,'insert',now()); ---在oplog表中插入一条操作记录，NEW指插入的时候当前的插入值保存在NEW中
end

注意：触发器是表的属性
**第三步：**执行插入语句

insert into user(uid,uname) values (250.'AMI');

oplog表产生一条记录

触发器是一种对象，他能根据对表的操作事件触发一些动作，比如：insert、update、delete

有一个需求：记录user表的删除操作记录
**第一步：**新建查询
第二步：

create trigger tr_user_delete before delete on user ---创建触发器tr_user_delete 在user表中执行delete语句之前
for each row ---针对每一行操作[影响范围]
begin
	insert into oplog(uname,action.optime，old_value) values (OLD.uid,OLD.uname,'delete',now(),OLD.regtime); ---在oplog表中插入一条操作记录，OLD指删除之前的数据
end

注意：触发器是表的属性
**第三步：**执行插入语句

insert into user(uid,uname) values (250.'AMI');

oplog表产生一条记录

子程序的高级特性

deterministic 确定性
not deterministic 不确定性